5

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!

Improve this question
2
  • As a side note: Instructions on how to change the Alias
    – Mike T
    Sep 26, 2012 at 4:34
  • This is an old topic, yes, but I used the information here to go down the route of using SQL Client Aliases only to discover that SQL Client Aliases are specific to the Microsoft SQL Client and cannot be used with JDBC or any other third-party client. Makes total sense in hindsight, but we didn't see it coming. Keep this in mind before deciding one way or the other. We'd invested a lot of effort in deploying SQL Client Aliases before discovering this issue.
    – user25815
    May 13, 2014 at 15:54

4 Answers 4

Reset to default
7

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
Improve this answer
4

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.

Improve this answer
2
  • Wouldn't you still need to have a DNS name for the alias defined?
    – tplive
    Dec 1, 2011 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, 2011 at 14:11
2

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.
  • Good if you change the default listening port of SQL Server 1433 for something else. (Improve the database server security)

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. !! Recent versions of Windows operating systems have the SQL Server Client Network Utility built-in. (Command Prompt: **cliconfg**). You do not need to install anything.

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.
Improve this answer
0

Be careful if you use a DNS Host (A) instead of an Alias (CNAME) as "Hossein Aarabi" recommends.

Because if you use a Host (A), Kerberos will not work any more... Except if you create a new "Service Principal Name" (SPN) for the new Name on the MSSQL service account.

SetSPN -s MSSQLSvc/<NewName.domain.ext>:<Port> <MSSQL Domain Service Account>
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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