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
Add a new alias as follows
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
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
Rating: 9.5/10 (11 votes cast)
Rating: +4 (from 4 votes)
Configure a SQL Server Alias for Sharepoint (SQL Server 2008), 9.5 out of 10 based on 11 ratings
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.
One would think that SharePoint would prevent you from loading up a SharePoint backup on a new domain with conflicting SIDs and usernames, but it actually allows you to do so without too much grief. I recently...
9 Comments for Configure a SQL Server Alias for Sharepoint (SQL Server 2008)