Migration process for MS SQL Server

This migration process is the same for Microsof SQL Server and Microsoft SQL Server Express.

For additional information, see the following Microsoft Knowledge Base article: https://msdn.microsoft.com/en-us/library/ms189624.aspx.

 

Prerequisites:

oSource and target SQL Server instances must be installed. They may be hosted on different machines.

oThe target SQL Server instance must have at least the same version as the source instance. Downgrade is not supported!

oSQL Server Management Studio must be installed. If the SQL Server instances are on different machines, it must be present on both.

 

Migration:ssms

1.Stop the ERA Server or ERA Proxy Service.

2.Log into the source SQL Server instance via SQL Server Management Studio.

3.Create a full database backup of the database to be migrated. We recommend that you specify a new backup set name. Otherwise if the backup set has already been used, the new backup will be appended to it, which will result in an unnecessarily large backup file.

4.Take the source database offline, select Tasks > Take Offline.

db_migration_SQL_takeoffline

5.Copy the backup (.bak) file that you created in step 3 to a location that is accessible from the target SQL Server instance. You may need to edit access rights for the database backup file.

6.Bring the source database online again but do not start ERA Server yet!

7.Log into the target SQL Server instance with SQL Server Management Studio.

8.Restore your database on the target SQL Server instance.

db_migration_SQL_restore

9.Type a name for your new database into the To database field. You can use the same name as your old database if your prefer.

10. Select From device under Specify the source and location of backup sets to restore and then click … .

db_restore

11. Click Add, navigate to your backup file and then open it.

12. Select the most recent possible backup to restore (the backup set may contain multiple backups).

13. Click the Options page of the restore wizard. Optionally, select Overwrite existing database and ensure that the restore locations for the database (.mdf) and for the log (.ldf) are correct. Leaving the default values unchanged will use the paths from your source SQL server, so please check these values.

oIf you are unsure where the DB files are stored on the target SQL Server instance, right-click an existing database, select properties and click the Files tab. The directory where the database is stored is displayed in the Path column of the table shown below.

db_restore02

14. Click OK in the restore wizard window.
 

15. Ensure that the new database server has SQL Server Authentication enabled. Right-click the server and click Properties. Navigate to Security and verify that SQL Server and Windows Authentication mode is selected.

db_migration_auth_SQL

16. Create a new SQL Server login (for ERA Server/Proxy) in the target SQL Server with SQL Server authentication and map the login to a user in the restored database.

oDo not enforce password expiration!

oRecommended characters for usernames:

Small ASCII letters, numbers and character underscore "_"

oRecommended characters for passwords:

ASCII characters ONLY, including big and small ASCII letters, numbers, spaces, special characters

oDo not use non-ASCII characters, curly braces {} or @

oPlease note that if you do not follow the character recommendations above, you may have database connectivity problems or you will need to escape the special characters in the later steps during database connection string modification. Character escaping rules are not included in this document.

db_new_login

17. Map the login to a user in the target database. In the user mappings tab, ensure that the database user has the roles: db_datareader, db_datawriter, db_owner.

db_SQL_login_map

18. To enable the latest database server features, change the restored database Compatibility level to the newest. Right-click the new database and open the database Properties.

db_properties

validation-status-icon-infoNOTE: SQL Server Management Studio is unable to define compatibility levels later than that of the version in use. For example SQL Server Management Studio 2008 is unable to set compatibility level for SQL Server 2014.

19.  Make sure the TCP/IP connection protocol is enabled for SQLEXPRESS and the TCP/IP port is set to 1433. You can do so by opening Sql Server Configuration Manager, navigate to SQL Server Network Configuration > Protocols for SQLEXPRESS. Right-click TCP/IP and select Enabled. Then double-click TCP/IP, switch to Protocols tab, scroll down to IPAll and into Port field type 1433. Click OK and restart the SQL Server service.

SQL_server_confMan_tcpIpProperties

20.Find startupconfiguration.ini on the machine, where ERA Server/Proxy is installed.

oFor Windows Vista and later:

% PROGRAMDATA %\ESET\RemoteAdministrator\Server\EraServerApplicationData\Configuration\startupconfiguration.ini

oFor earlier Windows versions:

% ALLUSERSPROFILE %\ Application Data\ESET\RemoteAdministrator\Server\EraServerApplicationData\Configuration\startupconfiguration.ini

oFor Linux:

/etc/opt/eset/RemoteAdministrator/Server/StartupConfiguration.ini

21. Change the database connection string in ERA Server/Proxy startupconfiguration.ini

oSet the address and port of the new database server.

oSet new ERA user name and password in the connection string.

The final result should look like:
 

validation-status-icon-infoNOTE: In the configuration below, please always replace:

TARGETHOST with the address of the target database server

TARGETERADBNAME with the name of the target ERA database (after migration)

TARGETERALOGIN with the login name for the new ERA database user on the target database server

TARGETERAPASSWD with the password of the new ERA database user on the target database server

DatabaseType=MSSQLOdbc

DatabaseConnectionString=Driver=SQL Server;Server=TARGETHOST,1433;Uid=TARGETERALOGIN;Pwd={TARGETERAPASSWD};CharSet=utf8;Database=TARGETERADBNAME;

22. Start the ERA Server/Proxy and verify that the ERA Server/Proxy service is running correctly.