Description

This guide explain the steps which are required to migrate the databases from “localhost” to an external database server.

Requirements

• Have TEOS Manage installed and have administrator access to the TEOS server
• Have SQL Server Management Studio (SSMS) installed
• Have sysadmin access to the database instance which stores the TEOS databases
• Have sysadmin access to the second database instance where the TEOS databases need to be stored

How to migrate the TEOS databases

1. To prevent that any changes will be done in the databases during the migration process we advise to stop the service called “World Wide Web Publishing Service”. Once this service has stopped IIS will no longer be active and TEOS will be offline.
    
2. Now we need to login to the database instance which is currently used by TEOS, we can login using SSMS.
   
    
3. We need to create backup files for the TEOS databases, a backup is needed for the following databases: 
   - 0000 
   - TDM Master 
   - CustomerDB 
   - Google Calendar Logins 
   - Sony Sales App (removed from 3.3.0 and above) 
   - SonyManagementPortal-Analytics 
   - SonyManagementPortal-Iot (version 2.2 and above) 
   -SonyManagmentPortal 
   - Yammer Logins (removed from 3.3.0 and above) 
   - TDM-Master (version 3.3.0 and above) 
   - TDM-teos (version 3.3.0 and above)
   
    
4. A backup can be created once you right mouse click a database, navigate to “Tasks” and here you will find an option called “Back up”.
   
    
5. Once selected a window will open where you can configure the backup, in this window please configure the destination for the backup file. By default this is: 
   <SQLInstallPath>\<DatabaseInstance>\MSSQL\Backup  
   
   Please note that the filename should end with .bak, this is the file extension for MSSQL database files. For example: tdm-master.bak 
   
   Once everything has been set select “OK” to create the database.
   
    
6. Please repeat the previous steps for all TEOS databases, once all the backups have been created you can continue with the next step.
    
7. Once the backups are ready you can navigate to the backup folder, copy the files and move them to the remote SQL server. We advise to copy them in the same directory on the remote SQL server, this way MSSQL will always be able to use the files. 
   
   The backup file path is: <SQLInstallPath>\<DatabaseInstance>\MSSQL\Backup 
    
8. Once the files have been copied a second connection can be established in SSMS, this time to the remote SQL server. This can be done using the following option:
   
    
9. A login screen will appear where you can enter the credentials for the remote SQL server.
   
    
10. Once connected to the remote SQL server you can restore a database by right mouse clicking “Databases”, then select “Restore Database”.
     
11. In the window which will open you need to configure the following fields:
    - Device: This is the field where you can link the backup file, please link this to the .bak backup file. 
    
    Once you have linked the .bak file the database information will automatically be loaded, select “OK” to restore the database on the remote SQL server.
     
12. Repeat the previous steps until all TEOS databases have been restored on the remote SQL server.
     
13. Once the databases are restored we need to create the SQL users and link them to the correct database. To do this please make sure you have opened the connection to the local database instance, once this has been opened navigate to: Security > Logins
    
     
14. In this overview you will find all the users available in the SQL instance, the following users are used by TEOS: 
    - analyticsuser 
    - customeraccount 
    - googlecalendar 
    - iotuser 
    - isenuc 
    - managementuser 
    - sonysalesapp 
    - yammerlogin
     
15. Once you double click a user the properties of the user will be shown, it is very important to use the exact same username together with the permissions configured in the tab called “User Mapping”. The passwords for the users cannot be found in this overview, they are available on the TEOS webserver in the web application called “backbone”. The passwords can be found in the following file: <InstallationPath>\TEOS Manage\www\backbone\web.config 
    
    This file can be opened with a text editor, for example “Notepad”. Once opened you will find a “ConnectionString” section which contains the password for the SQL users. 
    
    In the below example the password can be found in the bold text:  
    
    <add name="DefaultConnection" connectionString="Data Source=localhost;Initial Catalog=SonyManagmentPortal;User Id=managementuser;Password=xxxxxxxxxxx;" providerName="System.Data.SqlClient" />
     
16. Now that we have all the information for the SQL users we can start creating the users on the remote SQL server. Users can be created once you right mouse click “Logins” and then select “New Login”.
    
     
17. Please create the following users on the remote SQL server with the password from the web.config file and set the permissions configured in the “User Mapping” tab. 
    - analyticsuser 
    - customeraccount 
    - googlecalendar 
    - iotuser 
    - isenuc 
    - managementuser 
    - sonysalesapp 
    - yammerlogin
     
18. Now that we have adjusted all the SQL users we need to adjust the connection strings in the database “0000 - TDM Master”. The connection strings are located in the following tables: 
    - dbo.customers 
    - dbo.sql_servers
     
19. To change this we need to open the tables using SSMS, this can be done as followed:
    
     
20. The values for “connectionstring” and “stats_connectionstring” contain a connection string, this needs to be replaced with the updated connection string.   
    
    
    Important: Please make a backup of the current value before adding the updated connection string.
    
    Please find below an example of a connection string which can be used: server=<RemoteSQLhostname>;Database=customerdb;User Id=customeraccount;Password=<Password>;
    
    The example connection string can be copied and adjusted, once adjusted this can be copied into the “connectionstring” and stats_connectionstring value.
     
21. Once “dbo.customers” has been adjusted we need to open the table “dbo.sql_servers” using the same method. In this table there is another “connectionstring” value which uses a different format. Please adjust the bold part of the connection string so it contains the remote SQL server. 
    
    server=<RemoteSQLhostname>;Database=0000 - TDM Master; User Id=isenuc;Password=xxxxxxx;
     
22. Once all the SQL changes have been done we need to adjust the connection string information for all the web applications. To do this please login to the TEOS webserver and open the root of the installation directory of TEOS Manage. This is the following file path: 
    <InstallationPath>\TEOS Manage\www\
    
     
23. In this folder you will find all the directories for the web applications used by TEOS, each web application uses its own web.config file. The web.config file is the file which contains all the information regarding the database connections, this means we need to adjust all the web.config files to make sure they use the remote SQL server for the connection. 
    
    The following files need to be adjusted: 
    <InstallationPath>\TEOS Manage\www\4.1\Designer\web.config 
    <InstallationPath>\TEOS Manage\www\4.1\html5player\web.config <InstallationPath>\TEOS Manage\www\4.1\PresentationService\web.config <InstallationPath>\TEOS Manage\www\backbone\web.config 
    <InstallationPath>\TEOS Manage\www\backbone\employeeapp\web.config <InstallationPath>\TEOS Manage\www\dataservice\web.config 
    <InstallationPath>\TEOS Manage\www\EventsWebService\web.config <InstallationPath>\TEOS Manage\www\fileprocessor\web.config 
    <InstallationPath>\TEOS Manage\www\FileSyncWebService\web.config <InstallationPath>\TEOS Manage\www\googlecalendar\web.config 
    <InstallationPath>\TEOS Manage\www\html5player\web.config 
    <InstallationPath>\TEOS Manage\www\htmlplayerRoot_app\web.config <InstallationPath>\TEOS Manage\www\LicenseWebService\web.config <InstallationPath>\TEOS Manage\www\logging\web.config 
    <InstallationPath>\TEOS Manage\www\meetingroom\web.config 
    <InstallationPath>\TEOS Manage\www\office365\web.config 
    <InstallationPath>\TEOS Manage\www\powerservice\web.config 
    <InstallationPath>\TEOS Manage\www\PresentationService\web.config <InstallationPath>\TEOS Manage\www\ProofOfPlayService\web.config <InstallationPath>\TEOS Manage\www\Sony Sales App\web.config 
    <InstallationPath>\TEOS Manage\www\StatsWebService\web.config <InstallationPath>\TEOS Manage\www\updateservice\web.config 
    <InstallationPath>\TEOS Manage\www\watcherservice\web.config 
    <InstallationPath>\TEOS Manage\www\WebserviceRoot\web.config <InstallationPath>\TEOS Manage\www\tdm5\appsettings.json 
    
    These files contain connection strings which are currently pointing to the local SQL server, they need to be adjusted so the connection strings are pointing to the remote SQL environment. We need to adjust the “Data Source=” or ‘Server=’ value, this is correctly set to “localhost”. We need to change this to the remote SQL server, in our example this is remotesql.teosmanage.com. 
    
    Example connection string after adjustment: 
    <add name="DefaultConnection" connectionString="Data Source=remotesql.teosmanage.com;Initial Catalog=SonyManagmentPortal;
    User Id=managementuser;Password=xxxxxxxxxxxxx;" providerName="System.Data.SqlClient" /> 
    
    Once all the web.config files have been adjusted you can continue with the next step.

Once this step has been completed we have migrated the TEOS databases, last thing left to do is to start the “World Wide Web Publishing Service” on the TEOS web server. Once started please verify if TEOS is online again and login to the web interface. Please verify if the preview function is working, the preview function can be found here: 
Workplace Solutions > Signage > Play button in front of a presentation

In case this displays the presentation the migration has been successfully executed, in case this does not work please verify if the users have been created with the correct password and permissions. 

In case you cannot find the cause of the issue feel free to let us know and we are glad to assist.