Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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!

share|improve this question
    
As a side note: Instructions on how to change the Alias –  Mike T Sep 26 '12 at 4:34
add comment

3 Answers

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.

share|improve this answer
    
Wouldn't you still need to have a DNS name for the alias defined? –  tplive Dec 1 '11 at 13:14
    
I've never needed one... The big drawback to an alias, as Dave Wise alludes to below, is that you have to create it on every SharePoint server in the farm, but they are pretty self-contained and don't require a DNS entry. SQL aliases are local to the server they're created on. –  John Ferringer Dec 1 '11 at 14:11
add comment

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
share|improve this answer
add comment

I believe a better question would have been... SQL Alias or DNS Alias?

SQL Alias (AKA WINS)

Benefits:

  • Good to use if you are not a DNS administrator and you want to have full control over the alias setup.

Drawbacks:

  • You have to configure it on every SP server (WFE and APP servers)
  • You have to install SQL Server add-in on non-SQL server machines

DNS Alias

Benefits:

  • Provides better resiliency

  • Higher availability

  • Better disaster recovery

  • Moving targets geographically

When configuring DNS Alias in DNS manager, I suggest you use A record, and not CNAME.

DNS Alias is now the MS recommended approach to configuring SQL Server instances for SharePoint. reference (see the start of the second video)

Instructions on how to setup DNS Alias can be found here

I personally use DNS alias. On the SQL Server machine, I use the same IP for all the instances and the SQL Server machine itself, but the ports are different. The main reason is that I am not a fan of using multi-homing (multiple ip on a single NIC).

Ex: let's say I have server called SPSQL01 and two instances on that server called: SPDB01 and SPDB02.

  1. Create A record for each of those instance in the DNS Manager
  2. In the SQL Server, go to the SQL Server Configuration Manager
  3. Under SQL Server Network Configuration, select "Protocols for SPDB01"
  4. Select Protocol tab, set Listen All to No
  5. Select IP Addresses tab, for IP1 set Active=Yes, Enabled=Yes, IP Address=ip set in DNS manager for this instance, TCP Dynamic Ports=make blank, TCP Port=1433 or your choice
  6. Select Apply, close.
  7. Under SQL Server Network Configuration, select "Protocols for SPDB02"
  8. Repeat step 4-6, using the same IP, but different port.
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.