DB Planning: SQL Alias or CNAME Record?

Question

When planning connectivity with the Database Tier, it's wise not to hard-code any specific database info into the SP client connection.

I've gotten many recommendations to use SQL Alias on the client to point to the DB role in a generic way that doesn't need change in the event that you have to move the Database.

I've also heard people say that they just point the client to a CNAME in the DNS and just update the CNAME when the database moves.

What are the pros/cons of each?

Thanks in advance for any thoughts!

Answer 1

Here are the pros and cons you were asking for. The biggest reason to use a SQL Alias over a cname is so that you can alias multiple instances on the same SQL Server.

SQL Alias

Pros

• Supports server/instance aliasing
• Server Admin can configure as needed. Does not require a domain admin
• No need to worry about DNS caching
• Nearly complete control over the speed that servers are updated to point to the new database server
• Knowledge of every server affected by the change

Cons

• Changing the database location requires making updates to all servers that use the alias. Moving a database is a rare occurrence though so this is not as significant of an issue as it might appear
• It is not possible to do all server changes at precisely the same time so there may be some outages while each server is updated

CNAME

Pros

• Centrally managed so one update affects all servers

Cons

• Cannot alias specific instances of SQL server on the same Sql Server machine
• Changing a database server can cause crashes dependent systems and reboots may be required
• Due to DNS Caching, latency, etc.. you do not have complete control over when each server will pick up the DNS change
• Requires Domain Admin to make server changes which may also incur scheduling issues
• No knowledge of all affected servers as the cname can be used by anyone once it is created

Answer 2

My preference is for a SQL Alias, as its something that I'll able to (hopefully) ensure that I have more direct control over its setup and settings than I would with a DNS entry. Sure, in a perfect world I'd have the rights to manage DNS in addition to installing and configuring SharePoint, but the reality is that in most cases as a consultant I don't have those kinds of privileges in a client's environment.

That doesn't mean I couldn't get DNS entries created, but I'd much rather do it in a way that I am the one carrying out the action and am able to directly confirm that everything's done the way I want it rather than have to submit a request to the DNS owner, justify my request, wait for it to be completed, and then go through the process of validating that it was actually completed the way I asked for it.