16 September 2011

Using SQL aliases to make database migration much simpler

I recently saw an entire SharePoint farm be destroyed while attempting to simply move from one SQL server to another.  The problem is that SharePoint like a lot of other application have multiple places to configure the SQL database server(s), Miss one and your migration will fail.

After a lot of late night reading and finding this answer all over the place - The easy solution to migrating is to create SQL aliases on the farm members.  The ideal way though is to initially build the farm using SQL aliases.

(For my fellow administrators and non DBAs - a SQL alias is like having a local host file IP entry, but for SQL)

Configuring the SQL alias
This process should be done on all servers making connections to the SQL server.

  • Run c:\Windows\System32\cliconfg.exe
  • Select the Alias Tab click add
  • Specify the server alias (this is what you will now be able use to refer to as the SQL server)
  • Specify Server name and instance name if there is one

This will now create an alias called "SQLALIAS" and we can use this to connect to MOSSDRSQL\MOSS

To verify everything is working properly we art going to configure two ODBC connection.  The one will use the server name and the other the alias.  (This is jusr for testing you do not need ODBC connection for your alias to work.)

Connecting using the actual name

  • Start  - Admin tools - Data sources ODBC
  • User DSN
  • Add
  • SQL Driver
  • Name SQLNAME

Complete the Wizard and test the data source

Connecting using the alias

  • Start  - Admin tools - Data sources ODBC
  • User DSN
  • Add
  • SQL Driver
  • Name Alias
  • Server: SQLALIAS

Complete the Wizard and test the data source

You should now see that you can connect to the same server and databases using either the actual name or the alias.  If your application is configured, even if it is at multiple places to use the alias you only need to change the alias server name and all those configuration will now point to the new server.

So when it is time to migrate to a new SQL server you can move the databases and by simply changing your alias you can skip having to reconfigure your entire application.  The advantage to building your farm using an alias is that you won't be restricted to remain using the "old server" name.  This can be especially useful if the "old server" is a common SQL environment that might be serving other databases you might still need to access with the old name.

No comments:

Post a Comment