SharePoint Installation/Deployment Best Practice - Using SQL Server Aliases to avoid issues if the SQL Database Server is Renamed, a SQL Instance is added or the SQL Port is changed

One of the problems SharePoint 2007 is that the database name and server name is held in several tables within the configuration database. When you need to change the name of the database server from Central Administration, several references in the configuration tables need to change. There is the hard way and the easy way of doing this as below:

Changing SharePoint Database Server Name - the HARD/WRONG way

Here is the typical process to change the database Server that SharePoint is using:

(see http://blog.sqlauthority.com/2008/09/09/sql-server-error-fix-sharepoint-stop-working-after-changing-server-computer-name/)

1. Move or attach all the sharepoint databases to the new server.
2. Change the location of the config database with
   
   stsadm.exe -o setconfigdb -databaseserver ServerName -farmuser MyUserName – farmpassword MyPassword
3. Delete the Central Administration Site in IIS
4. Re-run the the SharePoint Products and Technologies Configuration Wizard.
5. DONE

Changing SharePoint Database Server Name - the EASY/RIGHT way

The above process is a bit painful - and I never like deleting a core component of SharePoint like Central Admin. 

To greatly simplify migraton if you should need to change the port or the server name of your SharePoint database server, you should instead use SQL Server Aliases for your connection from SharePoint to SQL. To do this, go to a command prompt on all servers (as they all connect to the database) and enter the following to open the SQL Client Configuration Tool (note that it doesn't have an i between the n and the f):

cliconfg

Once this is open, you can add a TCP/IP alias as neccessary, which points to your physical Server name, port or instance. Use this alias name when entering the database server in SharePoint - and you wont' look back! The server name change process is then as follows:

1. Move or attach all the sharepoint databases to the new server.
2. Change the Alias

3. DONE!

DDK