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 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>>
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
The final results will look similar to this.
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.
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.
del.icio.us Tags: Microsoft, BI, Setup, SQLAlias, SQL, Alias, SharePoint, PerformancePoint, ProClarity, FUTURESULTS