Move Databases

This section covers the processes required when moving databases to another server, for example when replacing the SQL server.

All services must be stopped during the move. This includes all the Manager services, the Gateway service, the Syncher service, as well as the IIS services.

You should replace the <INFINITY_SYSTEM> with the name of your database.

Once all the services have been stopped, you should detach the database. This can be done using TSQL:

EXEC sp_detach_db 'INFINITY_SYSTEM', 'true';

You should find and copy all four database files.

Warning: For performance reasons, it is recommended that the database files are copied directly onto separate physical disks, for example:

D:\MSSQL\DATA\INFINITY_SYSTEM.mdf (for the main data file)

E:\MSSQL\DATA\ INFINITY_SYSTEM_Archive.ndf (for the archive data file)

F:\MSSQL\DATA\ INFINITY_SYSTEM_Reporting.ndf (for the reporting data file)

G:\MSSQL\LOG\INFINITY_SYSTEM_log.ldf (for the log file)

Once the files have been copied to the new location, you should attach the database, repointing each file to the correct location.

CREATE DATABASE INFINITY_SYSTEM ON

(FILENAME = 'D:\MSSQL\DATA\INFINITY_SYSTEM.mdf'),

(FILENAME = 'E:\MSSQL\DATA\INFINITY_SYSTEM_Archive.ndf'),

(FILENAME = 'F:\MSSQL\DATA\INFINITY_SYSTEM_Reporting.ndf'),

(FILENAME = 'G:\MSSQL\LOG\INFINITY_SYSTEM_log.ldf')

FOR ATTACH

If you do not wish to copy the log file:

CREATE DATABASE INFINITY_SYSTEM ON

(FILENAME = 'D:\MSSQL\DATA\INFINITY_SYSTEM.mdf'),

(FILENAME = 'E:\MSSQL\DATA\INFINITY_SYSTEM_Archive.ndf'),

(FILENAME = 'F:\MSSQL\DATA\INFINITY_SYSTEM_Reporting.ndf')

FOR ATTACH_REBUILD_LOG

In order to make sure new databases are created on the correct folders, you should update the system settings to reflect the correct folder paths:

UPDATE INFINITY_SYSTEM..SystemSettings SET ss_Parameter='E:\MSSQL\DATA\' WHERE ss_Option='DBArchiveFolder'

UPDATE INFINITY_SYSTEM..SystemSettings SET ss_Parameter='F:\MSSQL\DATA\' WHERE ss_Option='DBReportingFolder'

You should make a backup of the web.config for each of the webservices (WSInfinity, WSConsole, WCFEngine, WCFConsole, etc.) involved on each of the web servers.

You should then modify the config file with the correct connection string, then restart the IIS services.

The server name and database name are normally only stored in the configuration file of the main manager service. If the username/password have also changed, then you will need to edit the configuration file of each of the services (Manager, SMTP, Export, POP3, etc.), before restarting the services.

You should modify the configuration string before restarting the service.

You should modify the configuration file before restarting the service.

It is recommended that all services are stopped during the move. This includes all the Manager services, the Gateway service, the Syncher service, as well as the IIS services.

You should replace the <INFINITY_PROJECT> with the name of your database.

Note: If your project uses a separate database for reporting, then you will need to repeat steps 2, 3 and 4 for the Reporting database.

Once all the services have been stopped, you should detach the database. This can be done using TSQL:

EXEC sp_detach_db 'INFINITY_PROJECT', 'true';

You should find and copy all four database files.

Warning: For performance reasons, it is recommended that the database files are copied directly onto separate physical disks, for example:

D:\MSSQL\DATA\INFINITY_PROJECT.mdf (for the main data file)

E:\MSSQL\DATA\ INFINITY_PROJECT_Archive.ndf (for the archive data file)

F:\MSSQL\DATA\ INFINITY_PROJECT_Reporting.ndf (for the reporting data file)

G:\MSSQL\LOG\INFINITY_PROJECT_log.ldf (for the log file)

Once the files have been copied to the new location, you should attach the database, repointing each file to the correct location.

CREATE DATABASE INFINITY_PROJECT ON

(FILENAME = 'D:\MSSQL\DATA\INFINITY_PROJECT.mdf'),

(FILENAME = 'E:\MSSQL\DATA\INFINITY_PROJECT_Archive.ndf'),

(FILENAME = 'F:\MSSQL\DATA\INFINITY_PROJECT_Reporting.ndf'),

(FILENAME = 'G:\MSSQL\LOG\INFINITY_PROJECT_log.ldf')

FOR ATTACH

If you do not wish to copy the log file:

CREATE DATABASE INFINITY_PROJECT ON

(FILENAME = 'D:\MSSQL\DATA\INFINITY_PROJECT.mdf'),

(FILENAME = 'E:\MSSQL\DATA\INFINITY_PROJECT_Archive.ndf'),

(FILENAME = 'F:\MSSQL\DATA\INFINITY_PROJECT_Reporting.ndf')

FOR ATTACH_REBUILD_LOG

You should update the connection strings in the sProject table (pro_ConnectionString and pro_ReportConnection).

Warning: If the connection strings are encrypted, then it is recommended that you use the Console to do this.

There are no configuration changes required after moving a project database. You should just restart the services to take into account the new connection string.

Moving Databases review

In this procedure you learned how to:

  • Detach a database
  • Attach a database
  • Update the settings / connection strings
  • Reconfigure and restart the services