TIP: SQL ALIASES NOT necessary when your SharePoint farm has SQL CLUSTER…

This morning I had a great discussion with an esteemed colleague Bart,  whose outcome should be of interest & use to many SharePointers…

TYPICALLY with SQL Server there is merit to “abstracting” the actual SQL Server and Instance from external use by defining SQL-specific “SQL Server Aliases” on each of the CLIENT computers which would try to connect to the SQL server.

CRITICALLY IMPORTANT – in numerous articles online you’ll see over & over & over again authors Remoting-into the SQL SERVER itself for the purpose of defining a SQL Server Alias. They do so because it’s easy to validate the alias by using the already-present SQL Mgmt Studio to try & connect to the alias. THIS APPROACH LEADS TO THE MISTAKEN IMPRESSION THAT ALIASES ARE DEFINED ON THE SERVER !! . This is NOT the case. SQL “server aliases” must be defined on EACH “CLIENT” SERVER which will need to connect to the SQL SERVER. Even I originally got misled by these articles, and it was a VERY aggravating experience to say the LEAST (but I’m not bitter – much)…


In our (SharePoint “resilient” medium server farm deployment) case that means defining SQL server aliases on each of the Web(2) and App/Index(1) servers which point to the IP & port # of the SQL server. In so doing whenever (say) a web server needed access to the DB server, it would reference (say) the (system-registry-stored) SQL Server Alias “SharePointDBServer”, which internally would resolve to (say) “\Named_Instance_01 via TCP/IP protocol”. Use of this abstraction would then allow for future expansion or migration to a different (bigger, faster) server, and all that would be required would be to adjust the definition of the alias defined on each web server, and SharePoint would be none-the-wiser…

Schematically it looks like this:

Web Front End Server 1 => “SharePointDBServer” (alias)�
=> resolved locally (within WFE 1) to 17.2.43:1433\Named_Instance_01 via TCP/IP�
Web Front End Server 2 => “SharePointDBServer” (alias)
                                                 => resolved locally (within WFE 2) to 17.2.43:1433\Named_Instance_01 via TCP/IP
App/Index Server 1 => “SharePointDBServer” (alias)
                                          => resolved locally (within App 1) to 17.2.43:1433\Named_Instance_01 via TCP/IP

Cool. Great. Excellent. But it gets even better when you’re using a SQL CLUSTER in your farm.  🙂

In this case we actually won’t even NEED SQL server aliases (again, defined on each “client” server of the SQL server), because we don’t have a SINGLE SQL server, but in fact TWO SQL servers in a CLUSTER…

What does clustering actually do?  

When clustering 2 SQL servers it is necessary to define a “Virtual Server” – both a virtual NAME and a virtual IP address. This virtual object behaves like ONE server, but in fact routes SQL traffic between the “active server” and the “passive server” in the cluster unbeknownst to the clients (typically all traffic to the active server unless it dies, and thence to the passive-now-become-active server)…

This virtual server name (say “SharePointCluster”) performs the SAME level of abstracting (insulating) SharePoint from the actual SQL server name & instance name, only with a LOT less work, including NO need to define SQL Aliases on ANY machines. 🙂  

Schematically it looks like this:

Web Front End Server => “SharePointDBServer” (virtual server defined @ SQL servers)
                                              => to virtual IP�
                                             => real IP {active SQL Server}  OR
                                             => real IP {passive SQL Server}

Kudos to esteemed colleague Bart for surfacing the entire discussion & realizations. This nuance-of-SharePoint-and-SQL doesn’t seem to get much coverage, so I thought I’d bring it up. Hopefully this explanation will benefit others, and generate an overall increased understanding of how SharePoint farms are intelligently put together.

This entry was posted in Sharepoint Tips and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s