Don’t Tell SharePoint The Name of Your SQL Server

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.

blog-Clicfg

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.

blog-Clicfg2

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.

Post to Twitter Post to Facebook Post to LinkedIn Post to Delicious Post to Digg

5 thoughts on “Don’t Tell SharePoint The Name of Your SQL Server”

Leave a Reply

Your email address will not be published. Required fields are marked *