Setting up a Local SQL Alias on a SharePoint Farm

Updated on July 2, 2010 by FUTURESULTS:  On June 17, 2010 Microsoft Updated the article – Move all databases (Office SharePoint Server 2007) that is referenced in my blog post below.  They also added instructions for SharePoint Server 2010.  The new process basically follows the recommendation that I have made in this blog post to use a SQL Alias.  Glad to see Microsoft took direction from FUTURESULTS!  So basically, the long process that was originally posted by Microsoft has been updated for SharePoint 2007 and 2010 and follows my recommendations below.

Have you ever wanted to replace an older MOSS 2007 SQL database server with a newer faster server?  If you have, you probably have found the TechNet article “Move all databases (Office SharePoint Server 2007)”.   The process described is very long and time-consuming.  The method that I am about to describe is not supported by Microsoft.  The only approved method is “Move all databases (Office SharePoint Server 2007)”.

Another way to do change database servers is to use a “Local SQL Alias”.  This method allows you to move all databases between servers with a more simplistic path.  My current thoughts are that a Local SQL Alias should be used on all new setups where the database and server product (MOSS, PerformancePoint, ProClarity, …) are on different machines.  The benefit of setting up the “Local SQL Alias” is that it allows you to change out database servers fairly easily.  You could also use a process like this to point to a DEV or TEST database.

A SQL Server Local Connection Alias can be thought of as a way to make a connection to a database instance using an easy-to-use name for the database server.  The concept is similar to a DNS alias except at a database level.  You can define an alias on the local client (such as the MOSS farm boxes) by using either the SQL Server Connection Manager (shipped with SQL Server 2005) or the SQL Server Client Network Utility shipped with Windows “C:\WINDOWS\system32\cliconfg.exe”.

The goal is to set up a local alias that looks something like:

old database server name = new database server name

If you are using named instances this works as well.  Just create an Alias with the instance name included.

Pre-Setup Steps for MOSS 2007

Perform the following steps before creating your alias or moving the SharePoint databases to new SQL server instance.

Quiesce the SharePoint farm

  • Stsadm -o quiescefarm -maxduration 15   <15 = duration in min that you need to have DB unavailable>
  • This locks the Farm DB’s for a set period of time for users who are accessing it, and will not allow any more to connect to the sites in the farm.

Backup and Restore Databases

  • Backup all the database on the old SQL server (OldServerName)
  • Restore all the databases on new SQL server (NewServerNameInName) where InName = instance name.

Stop Web Sites and Services

Stop all the web sites in IIS Manager (Start/Stop each site that is related to this SharePoint farm)

  • If you want to stop the entire farm (on a single server), from a cmd use iisreset /stop

Stop the services on all the Web Front Ends (WFEs) and Application Servers (Start>Run > services.msc).  See “***Note” below before running this command.

***Note:  This is all services that can be used in your farm.  Record the state of each of the following before disabling it.  This will be needed when you restart the services.

  • Microsoft Single Sign-On service
  • Office Document Conversions Launcher service
  • Office Document Conversions Load Balancer service
  • Office SharePoint Server Search service
  • Windows SharePoint Services Administration service
  • Windows SharePoint Services Search service
  • Windows SharePoint Services Timer service
  • Windows SharePoint Services Tracing service
  • Windows SharePoint Services VSS Writer service

Create Alias

Create the alias on all the SharePoint WFEs and Application servers

  • Start > Run > cliconfg.exe

Note: you need to have admin rights to run SQL configuration manager command line tool

First enable what protocols you need:

  • Select TCP/IP Enable>>
  • Select Named Pipes Enable>>

SQL Server Client Network Utility - General Tab

SQL Server Client Network Utility - General Tab With Selections

Next, Click on Alias Tab>Click Add

Select TCP/IP under Network libraries

For Server Alias: “OldServerName

Connection Parameters: Server Name: “NewServerName\InName” where InName equals a new instance name

Uncheck the Dynamically determine port

Click OK

Edit Network Library Configuration

The final results will look similar to this.

SQL Server Client Network Utility - Alias Tab

Click Apply and OK

If you select “Edit” on the SQL Server Client Network Utility Screen above, you will be able to review the corresponding Network libraries “Named Pipes” entry that was created.  It is an optional review step however and the results are shown below.

Post-Setup Steps

Restart Web Site and Services

Restart the services (Start>Run>services.msc)

  • Using your documented services states from above, enable those services that apply to your setup.

Start the web sites (Start>intemgr>Start each site that related to SharePoint)

  • If you used the iisreset /stop above, now use iisreset /start

Make sure all Application Pools are up and running.

  • Look at the sites in IIS

Open Central Admin

  • Validate access to CA

Open Home Site

  • Validate access to all your sites.

This process is pretty simplistic and should probably be thought of as a best practice so that you can easily move the database in the future.  You need to take care in documenting this setup so that if future issues occur, you can review your alias setup information.

FUTURESULTS, LLC Blog and FUTURESULTS, LLC Website are both created by Robert Lambrecht. Tags: , , , , , , , , ,
This entry was posted in Microsoft BI, Setup and tagged , , , , , , , . Bookmark the permalink.

4 Responses to Setting up a Local SQL Alias on a SharePoint Farm

  1. Pingback: ProClarity Analytics Server Migration to a New Database Server | FUTURESULTS, LLC Blog

  2. Pingback: Database Migrations – Configure Logins and Permissions | FUTURESULTS, LLC Blog

  3. Pingback: SharePoint 2007 and 2010 – Move all Databases | FUTURESULTS, LLC Blog

  4. Pingback: 2010 in review | FUTURESULTS, LLC Blog

Leave a Reply

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

You are commenting using your 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