Monday, September 10, 2012

Moving all databases to another server without changing connection string

Today I'm gonna talk about how to moving all databases to another server without changing connection string in web.config, app.config and whatever configuration file.

Problem
Today I met an issue that my current SQL Server needs to be operated for some reasons and the SQL Server service will be offline for a few days. Our application is a global service for the company and it can't be offline for a long time. This means we have to move all databases from the current server to a backup server.

Solution
The basic idea is that create a backup server and restore all databases on it. Then setup a redirect function on the original server so that all requests can be redirected to the new server. This function is called "Alias" in SQL Server, which can be configured in Sql Server Configuration Manager -> SQL Native Client.

There are two summary steps to complete this solution:
1. Backup all databases from the original databases and restore them on the backup server.
2. Setup an Alias on the original server to redirect all requests to the backup server.

A. Backup and Restore
1. Open your SQL Server Management Studio from Start -> All Programs -> Microsoft SQL Server 2008 -> SQL Server Management Studio
2. Login to the current DB server with your credential.
3. Right click you database, select Tasks -> Back Up...
4. Change the backup directory, and then click OK.
5. Copy the backup file to the new DB Server.
6. Follow step 1 to login the new DB server.
7. Right click the Databases node and then click the Restore Database...
8. Select From device, choose the backup file, and then select the database name from the To database drop down list. Click OK button.
9. Now, you have moved the target database from the original server to the backup server.

B. Setup Alias
After you move all database from the original server to the backup server, the next step is setup a redirect settings in the original server to redirect all requests to the backup server. This setting is called 'Alias' in SQL Server.

1. Select SQL Server Configuration Manager from Start -> All Programs -> Microsoft SQL Server 2008 -> Configuration Tools
2. Expand the SQL Native Client 10.0 Configuration -> Aliases. Generally there will be two menus, one is for 32 bit, the other is for 64 bit.
3. Right click the Aliases, select New Alias...
4. There are four fields need to be entered. The Alias Name, the Port No, the Protocol and the Server.
The Alias Name should be the 'Data Source' in your connection string
The default Port No is 1433
The Protocol should choose TCP/IP
The Server field should be the new SQL Server name.
5. After filling this dialog, click OK button to complete the setup. Make sure you setup the alias both in 32 bit and 64 bit.
6. Now, you backup server is ready to go. Disable you current SQL Server service and try your applications, it should work.