When you deploy MOSS 2007, you need to have a SQL server in place for the configuration database, content databases and various other databases. But what if you need to change that SQL server later? In theory, there are several options:
- Use connection aliases and change the alias to use another server.
- Use the stsadm utility with the renameserver option.
A connection alias is setup using the SQL Server Configuration Manager utility. I usually install the SQL management tools on the MOSS server which makes the tool available. Normally, you use aliases for security reasons as documented here.
In the diagram above, there is a simple farm with a MOSS server and a SQL server. The SQL server is configured to listen on TCP port 40000 with SQL Server Configuration Manager. The MOSS server is configured to connect to the SQL server using TCP port 40000. To let the MOSS server connect using TCP port 40000, you need to create an alias on the MOSS server. When you create an alias with SQL Server Configuration Manager, it will store the alias definition in HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo. For the example above, you would find the following value:
- Name: sql
- Type: REG_SZ
- Value: DBMSSOCN,sql.test.com,40000
From practical experience, I do not recommend using an alias to change the SQL server in a MOSS farm. Instead, use the stsadm command as follows:
stsadm -o renameserver -oldservername <oldname> -newservername <newname>
After running this command, restart all services or reboot the server.
Of course, you need to migrate all data from the old SQL server to the new one. You can restore backups to the new server and recreate logins to achieve that goal.
In summary, when you need to change the SQL server in a MOSS 2007 farm, use the stsadm command instead of SQL connection aliases. Use connection aliases to meet security requirements like connecting to SQL with a different port number.
