How To Move the Commerce Server Databases from One SQL Server to Another

It may be necessary to move the Commerce Server databases from one SQL Server to another. This article describes which Commerce Server databases can be moved and how to move those databases.

A Commerce Server installation may contain several databases, depending on the number of sites installed and options configured. After Commerce Server is installed, it may be necessary to move the databases to a new server.

All databases, except for the Data Warehouse database (or databases) can be moved to a new server. For the Data Warehouse database, you must install a new Data Warehouse resource for your site and re-import your data.

To move the databases to a new server, it is recommended that you back up your existing database by using the SQL Server Enterprise Manager, and then to restore the database to your new server. There are additional steps, depending on the database that is being moved.

Moving MSCS_Admin Database

1. Start SQL Enterprise Manager.
2. Back up MSCS_Admin from the source server, copy the backup to the new server, and then restore.
3. Modify the connection string for the Administration Database to point to the new server. To do so, follow these steps:
   1. Right-click Commerce Server Manager, and then click Properties. The Commerce Server Manager Properties dialog box appears.
   2. On the Administration Database tab, click Modify. The Data Link Properties dialog box appears.
   3. After making the necessary changes in the Data Link Properties dialog box, click OK.
   4. For information about how to complete the Data Link Properties dialog box, click Help in the Data Link Properties dialog box to open the Microsoft Data Link Help file.
      
      NOTE: After you change a property value in Commerce Server Manager, you must unload the IIS applications on the site from memory on each Web server in order for the change to take effect.

Moving the _Commerce Database (or Catalog, Transaction, TransactionConfig, Campaign, Profile Databases)

1. Back up the database (or databases) on the source server, copy the backups to the new server, and then restore.
2. Modify the connection strings for each resource to point to the new server, depending on the type of resource.
   1. To change the connection string for a global resource
      
      
   2. Expand Commerce Server Manager, and then expand Global Resources.
   3. Right-click the global resource to be configured, and then click Properties.
   4. Click the Connection Strings tab to configure the connection strings for the global resource, select a connection string, and then click Modify. The Data Link Properties dialog box appears.
   5. After making the necessary changes in the Data Link Properties dialog box, click OK.
      
      NOTE: After you change a property value in Commerce Server Manager, you must unload the IIS applications on the site from memory on each Web server in order for the change to take effect.
   1. To change the connection string for a site resource
      
      
   2. Expand Commerce Server Manager, expand Commerce Sites, and then expand the site that you want to administer.
   3. Expand Site Resources, right-click the site resource to be configured, and then click Properties.
   4. In the Properties dialog box, select the Connection Strings tab, select a connection string, and then click Modify. The Data Link Properties dialog box appears.
   5. After making the necessary changes in the Data Link Properties dialog box, click OK.
      
      NOTE: After you change a property value in Commerce Server Manager, you must unload the IIS applications on the site from memory on each Web server in order for the change to take effect.
   If you have moved a Profile database, you must also modify the connection strings for each of your Data Sources that have moved.
   To modify the Profile Database Data Source connection strings
   1. Expand Commerce Server Manager, expand Global Resources, and then expand Profiles.
   2. Expand Profile Catalog, and then expand Data Sources.
   3. Right-Click ProfileService_SQLSource.
   4. Click Properties.
   5. Click the Partitions tab in the ProfileService_SQLSource property sheet.
   6. Select the SQLSource, and then click Edit.
   7. Click Edit in the property sheet.
   8. Enter your new server name.
   9. Click OK until the dialog box closes.

After the Databases Are Moved

Click Start, click Run, and then run IISRESET to restart the IIS services. This is necessary to load the new connection strings from the Admin database.

Commerce Server Documentation - Setting Database Connection Strings