Ulysses Ludwig’Blog
Configure a SQL Server Alias for Sharepoint (SQL Server 2008)
A SQL Server alias is a tidy way to protect yourself from configuration changes in a SharePoint farm. Anybody who works with SharePoint on a regular basis knows that it is very difficult to move SharePoint to a different SQL Server. When disaster strikes wouldn’t it be nice if you could simply change SQL Server that SharePoint references? To do so, install the SQL client access tools on each server and create a SQL Alias in SQL Server Configuration Manager, from there your SharePoint Server can be re-homed with little effort.
Note, there are two methods to create a SQL Server alias, one for machines that already have SQL Server client tools installed, and another for machines that have no SQL components at all. If your machine does not have any SL software installed and you’d like to creat an alias, follow the steps diagrammed below after running this in the server’s run dialog: cliconfg.exe.
For a server with NO SQL Server Tools Installed
From the command line run cliconfg.exe
![clip_image001[1]](../../../../wp-content/uploads/2010/02/clip_image0011_thumb.png "clip_image001[1]")
Enable TCP/IP
![clip_image002[1]](../../../../wp-content/uploads/2010/02/clip_image0021_thumb.png "clip_image002[1]")
Add a new alias as follows
![clip_image003[1]](../../../../wp-content/uploads/2010/02/clip_image0031_thumb.png "clip_image003[1]")
Once your alias is entered, launch SharePoint Products and technologies wizard and build your SharePoint farm.
Verify on your SQL server that TCP/IP communication is allowed in the sql server configuration manager
![clip_image005[1]](../../../../wp-content/uploads/2010/02/clip_image0051_thumb.png "clip_image005[1]")
For a server that has SQL Server Tools Installed
Open SQL Server Configuration Manager
Enable TIP/IP if you haven’t done so already
Under the 32 bit Client 10.0 Configuration (32 Bit)
** yes, one would think you need to do it under the 64 bit but it looks like enterprise manager thunks down to 32 bit
Repeat the steps above for SQL Native Client 10.0 Configuration
** SharePoint uses the 64 Bit Native Client
Test your connection using enterprise manager
In the event of a SQL failure, or if you simply want to migrate to a different SQL Server, all you have to do is update the SQL Server Alias in each client machine to reference the new SQL Server. The example below shows how to connect using a named instance on a new server
Or just connect to another server’s default instance
a WordPress rating system
a WordPress rating system
About the author
Ulysses Ludwig is a SharePoint architect with over 16 years in the IT and computer industry. Ulysses' primary focus is SharePoint but he dabbles in the latest web technologies and likes to develop software in his spare time.
Related Posts
9 Comments for Configure a SQL Server Alias for Sharepoint (SQL Server 2008)
[...] that I have a more detailed article about how to create a SQL Alias on a server with no SQL Client tools [...]
We had to do this when we migrated from a 32bit named instance to a new 64bit server default instance.
Do you know is there any way to migrate away from having to have these SQL Alias’ forever?
a WordPress rating system
a WordPress rating system
I’m not sure what your question is, do you want the alias or do you not want the alias? As you could have guessed, the alias is very useful for migrating from one physical sql server to another because you can simply kill the old server and replace it with an alias (assuming you remove it from DNS properly), however, that old name may confuse users in central admin since it references what looks to be a servername. All in all, once you have the alias in place you want to keep it… why wouldn’t you as it allows you to point to a new SQL instance with no effort which is especially useful for SQL upgrade or SQL disaster scenarios.
a WordPress rating system
a WordPress rating system
We have had a SQL Alias in place for 3 years now, and my customer wants to get rid of the SQL Alias, as they don’t like seeing the old serve name in SharePoint and we have had one third party reporting product that it didn’t work on.
a WordPress rating system
a WordPress rating system
You can do this via PowerShell as well:
New-ItemProperty HKLM:SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo -name SPSQL -propertytype String -value “DBMSSOCN,DatabaseServer,1433″
New-ItemProperty HKLM:SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo -name SPSQL -propertytype String -value “DBMSSOCN,DatabaseServer,1433″
a WordPress rating system
a WordPress rating system
Hi there I have no SQL alias set up. I have the following problem.
I have a SharePoint server 2010 with the WFE and SQL Server on the same box.
I wish to set up a NEW SQL box and move all the Databases accross to the NEW SQL .
The Current SQL instance is \SQL Instance
will remain as thr WFE and is appropriately named.
I hope to call the NEW SQL something to suit inftrastructures naming convention SQL03. Do I move all the DBs and just point to this NEW server as being the Current Farm?
a WordPress rating system
a WordPress rating system
This is a great question and really gets to the point of the SQL Alias… I think a lot of people miss the concept or meaning of the word. With that said, here’s what I would do in your situation.
(Test this in a Lab for Staging server first please )
1) Leave your farm as is (temporarily)
2) Create a SQL Alias in your CURRENT farm pointing to your CURRENT SQL Server + Database + Instance
3) Use the “stsadm -o setconfigdb -databaseserver alias” command to point the farm to the config database via the alias
4) Detach and reattach your contend databases using “stsadm -o addcontentdb” command
5) Move the databases to the new server (take care to create the logins and re-attache them)
6) Try pointing your your farm to the new SQL Server by simply changing the pointer of the alias (no changes in SharePoint needed for this step)
a WordPress rating system
a WordPress rating system
all I did was create a CNAME record and pointed SharePoint to that. Why do I have to go through these steps?
a WordPress rating system
a WordPress rating system
[...] http://www.sharepointassist.com/2010/02/02/configure-a-sql-server-alias-for-sharepoint-sql-server-20… [...]