Overview
This article shows how to create client SQL alias and test the connection to the SQL server using out of the box tools.
What is SQL Alias
According to an article in TechNet:
“The alias encapsulates the required elements of a connection string, and exposes them with a name chosen by the user. Aliases can be used with any client application. By creating server aliases, your client computer can connect to multiple servers using different network protocols, without having to specify the protocol and connection details for each one. In addition, you can also have different network protocols enabled all the time, even if you only need to use them occasionally. If you have configured the server to listen on a non-default port number or named pipe, and you have disabled the SQL Server Browser service, create an alias that specifies the new port number or named pipe.”
FromĀ http://technet.microsoft.com/en-us/library/ms190445.aspx
Ok, let`s get that visualized:
Before starting with the diagrams will try to explain the communication between the client and server
If you are familiar with the OSI Models we have 7 Layers of communication..
You have the following protocols used by SQL:
- The SQL Server Network Interface (SNI) protocol unifies communications over TCP, Named Pipes, and Shared Memory.
- The Tabular Data Stream (TDS, and also known as TSQL) protocol is an application layer protocol where packets are encapsulated in the packets built for the protocol stack.
- SQL Server Browser runs as a Windows service on the server. SQL Server Browser listens for incoming requests for SQL Server resources and provides information about SQL Server instances that are installed on the computer
So basically when creating an alias you have all the required information encapsulated in a packet using the TDS protocol with the desired name.
Figure 1: When we have a server with a direct connection to the SQL server
Operating System is accessing the DB server directly by its FQDN
Figure 2: When we have a server with a SQL Alias configured, connected to the SQL server.
Operating System is accessing the DB by the Alias configured. This is happening on the application layer on the client machine where the Alias is encapsulating all the needed information (the real FQDN to the SQL server as well) and sending it to the lower layer and finally to the destination server (SQL-DB-01 in our case).
Why use aliases and how can they help with SharePoint?
Ok why we need to set up an SQL alias?
Well there are many reasons you might need to create SQL aliases. They may not be apparent at first but if you give it some thought you will understand that they can be very helpful at many scenarios
- Be able to relocate SQL databases in a much easier fashion. Changing database servers can be really difficult when it comes to SharePoint – you can find how you can do it for MOSS 2007 here, SharePoint 2010 and SharePoint 2013
- You can technically upgrade SQL without touching SharePoint
- You can upgrade SQL with no downtime š
- Aliases can allow you to clone your SharePoint environment much easier. Think about the difficulties many have encountered when you need to test something on Production, but you have no test environment. Cloning a production environment is difficult, and aliases can help you throughout the initiative.
- You can do stunts like virtualizing your SQL server without affecting SharePoint
- It is recommended by Microsoft for every SharePoint installation š
- There are many, many more…
Ok, hope you guys get the idea and how it actually works. Time to get nasty and start with the configuration.. It is actually very simple. Firstly let`s check what are the prerequisites.
Prerequisites:
- Admin Rights on the machine where you will configure the Alias
- Running and configured SQL server (2008R2 or higher)
- Login account on the SQL server with the needed permissions
- According to the configuration you might have other prereqs depending on the requirements like ports named pipes etc.
Configuring SQL Alias on the client machine
The following changes and configurations need to be done on the client machine only NOT THE DB SERVER!
Basically we are just changing registry parameters. If your OS is 64bit you have to do these changes for both x64 and x86 environments. This is required so you cover 32-bit and 64-bit software (applications, services and other processes).
For 32-bit, startĀ C:\windows\system32\cliconfg.exe
- Enable the protocol you will use
- Select the tabĀ AliasĀ and click the buttonĀ Add
- SelectĀ TCP/IPĀ and define the name of the alias. The fill in the hostname of your database server and select theĀ Dynamically determine port -checkbox. After that clickĀ OK
- Now you have defined your SQL alias name. ClickĀ OK
For 64-bit, start “C:\windows\syswow64\cliconfg.exe”
We are in an 64bit environment. You will need to do the same steps you did in (1) for the cliconfg.exe in syswow64-folder.
Ok, you are done with the configuration! But before we proceed we need be sure that our alias is working and out account is accessing the server or the needed database using this alias.
Configuring Alias with PowerShell:
If you want you can script the creation of alias with PowerShell. Here`s a simple script how you this can be done:
#This is the name of your SQL Alias
$AliasName = “SQLAlias”
#This is the name of your SQL server (the actual name!)
$ServerName = “SP-WFE-01”
#These are the two Registry locations for the SQL Alias locations
$x86 = “HKLM:\Software\Microsoft\MSSQLServer\Client\ConnectTo”
$x64 = “HKLM:\Software\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo”
#We’re going to see if the ConnectTo key already exists, and create it if it doesn’t.
if ((test-path -path $x86) -ne $True){
write-host “$x86 doesn’t exist”
New-Item $x86
}
if ((test-path -path $x64) -ne $True){
write-host “$x64 doesn’t exist”
New-Item $x64
}
#Adding the extra “fluff” to tell the machine what type of alias it is
$TCPAlias = “DBMSSOCN,” + $ServerName
$NamedPipesAlias = “DBNMPNTW,\\” + $ServerName + “\pipe\sql\query”
#Creating our TCP/IP Aliases
New-ItemProperty -Path $x86 -Name $AliasName -PropertyType String -Value $TCPAlias
New-ItemProperty -Path $x64 -Name $AliasName -PropertyType String -Value $TCPAlias
#Creating our Named Pipes Aliases
New-ItemProperty -Path $x86 –Name $AliasName -PropertyType String -Value $NamedPipesAlias
New-ItemProperty -Path $x64 –Name $AliasName -PropertyType String -Value $NamedPipesAlias
Script provided by Habanero Consulting and modified by me;
Testing the alias and database connection – an awesome little trick!
Many folks use some 3rd party tools or some custom scripts to test the connection but many don’t even test the connection assuming that everything is ok. And when the time for the implementation comeĀ bad things may happen. š
So it is always good to test and double check the configuration before proceed any further!
This method for testing is very helpful because it takes less than a half minute to understand that something is not working and the good thing is that this is an out of the box tool in Windows.
Just navigate to any folder you like and create an empty text file. You can name it “TestSqlConnection” for example. Rename the file extension from txt to UDL and voila
You have a DB connection testing tool
Because I like to have many of the tasks scripted will show you how to create this UDL file easily and fast with PowerShell:
PS C:\Windows\system32> new-item c:\testSQLconnection.udl -itemtype file
And here`s our output:
Directory: C:\
Mode LastWriteTime Length Name
—- ————- —— —-
-a— 4/4/2013 11:48 AM 0 testSQLconnection.udl
Ok, we have our UDL file created, Let see what`s inside:
Now, in the field Select or enter a server name: Type the name of your SQL alias.
In the logon information choose the way your account will access the DB and click on Test Connection or you can just click on the arrow in the field Select the database on the server.
If you are not able to see the DBs or you receive an error it is most likely to have some connection issues with the server but the alias. To confirm that you can change the server name to the SQL Server FQDN and check again. Most likely you will receive the same error. You can go back and check the prerequisites again.
If you want to test the connection with other account but the one you are logged in you don’t have to log off and log on back with the desired account but you can start the UDL with different credentials. There are two ways you can do this:
- Go to the file location and right click while holding the shift button on your keyboard then select run as different account. (This works on Windows Server 2008 or higher)
- Start Run and type runas.exe /profile /user domain\userĀ UDL file location>
This works on every Windows machine. You can also start cmd.exe
Ok then! SQL Alias configured and tested, we are ready to set up our Front End server doesn`t matter if it is SharePoint some Website or some application accessing the DB.
Summary
Using an SQL Server Alias, you will be easily able to change the Database Server of your Front End servers. This is very useful when moving or virtualizing your database server or setting up an test environment. It takes less than 5 minutes and can save a lot of time in some scenarios.
Hope you enjoyed the article!
Ognyan Guglev is a Senior SharePoint Support Consultant at OneBit Software and from now on a keen SharePoint blogger š Follow more activities on this blog through RSS.
