SQL Aliases have been around for a while however I am amazed at how many folks either don’t know about them or don’t use them. A SQL Alias is just a really easy way to do a string substitution of a connection string just before a connection is made to a SQL server. The SQL Alias configuration is stored in the registry and the anywhere the server name portion of the connection string matches the server alias the substation is made.
Lets look at a quick example, suppose you have a web application you are working with on your laptop which makes a connection to your local SQL instance. You store the SQL connection strings in the web.config but when you deploy the application to a QA or production server you will most likely not be connecting back to the SQL instance on your laptop. This is a great scenario for SQL Alias, choose an alias name, in this example I chose TX-DB-A as my alias and my local dev machine is TX-DEV-01. Once created I then update the connection string in my web.config to use TX-DB-A as my server name.
In production, where I am to deploy my web application project I need to create a similar alias however this time it references a different server name.
At this point I can deploy my web application from my laptop to production without having to remember to edit the web.config.
Note in these two examples I used a naming format of [Location]-[Environment]-[Designator] where the servers are in Texas (TX) and DEV and PROD represent my development and production environments respectively. the Designator is the unique identifier and for real servers I use a number but for SQL Alias I use an alpha character such as A, B, C, etc. The reason for this I that in any error logs or when looking at any configurations and trying to troubleshoot connection issues I need to determine if what I am seeing is a SQL alias or a real server. For example, I cannot ping TX-DB-A, and in production if I see errors connecting to TX-DB-A I need to check on a different server than I would if I noticed those same errors in my development environment.
Spence did a good write up on why you really need SQL Alias to get around a SharePoint bug however I personally always recommend SQL Alias – even if you never use it it’s a level of indirection which costs you nothing but allows flexibility when needed. And the good part is its really easy to test the failover to another SQL instance if needed. The downside is the SQL Alias is machine wide and you cannot point the alias for some DBs to one server and others to another, unless you already broke out your Alias before your configured SharePoint.
So consider this pattern â€“ for each new DB you add to SharePoint create a 1:1 SQL alias.
DBName Alias SQLServer
Content_DB_1 SQL-CONTENT-DB1 sql1.contoso.com
Content_DB_2 SQL-CONTENT-DB2 sql1.contoso.com
Content_DB_3 SQL-CONTENT-DB3 sql1.contoso.com
Content_DB_4 SQL-CONTENT-DB4 sql1.contoso.com
Now you can move Content DBs between servers with little impact.
1. Set Content DB 1 to Read only
2. Take backup of Content DB 1
3. Restore Content DB 1 on sql2.contoso.com
4. Update SQL alias on all servers
5. Set Content DB to read/write
Now this obviously does not scale when you are talking about hundreds of DBs but you get my point. MySite Content DBs might use a different SQL Alias than Intranet or Team Content DBs, etc.