SharePoint 2010 - Move all databases

Move all databases (SharePoint Server 2010)

This article describes how to move all of the databases associated with Microsoft SharePoint Server 2010 from one database server to another database server. If your databases are hosted on different servers, this procedure applies to the database server that hosts the configuration database. For moving content databases only, see Move content databases (SharePoint Server 2010). For moving service application databases only, see Rename or move service application databases (SharePoint Server 2010).

Moving databases makes all farm sites and assets unavailable to users until the process is completed.

The following kinds of databases hosted on a single database server can be moved by using the procedures in this article:

• Configuration database
• Central Administration content database
• Content databases
• Service application databases

Moving all databases

The process of moving all of the databases from one database server to another database server requires you to work in both SharePoint Server 2010 and SQL Server. The following list summarizes the process of moving all databases, with detailed steps presented in the subsequent procedures:

1. Prepare the new database server.
2. Close any open Windows PowerShell management shell windows and any open Stsadm command prompt windows.
3. In the Services Microsoft Management Console snap-in, stop all of the services related to SharePoint Server 2010 and Internet Information Services (IIS). For details.
4. In SQL Server, detach the databases from the current instance. For details, see To detach databases.
5. Using Windows Explorer, copy or move the .mdf, .ldf, and .ndf files associated with the database from the source server to the destination server. For details.
6. In SQL Server, ensure that all of the SQL Server logins, fixed server roles, fixed database roles, and permissions for the databases from the source server have also been configured correctly on the destination server.
7. In SQL Server, attach the database to the new instance.
8. Use SQL Server connection aliases to point to the new database server, and then use the connection alias to update all Web servers. A connection alias is a defined alternate name that can be used to connect to an instance of SQL Server. You have to configure the alias on all Web servers and application servers in the farm.
9. Restart the services.
10. The following are the minimum permissions that are required to perform this process:

• You must be a member of the Farm Administrators SharePoint group.
• On the computer that is running the SharePoint Central Administration Web site, you must be a member of the Administrators group.
• On the database server from which the databases are being moved, you must be a member of the following:
• The Administrators group
• The db_backupoperator fixed database role
• On the database server to which the databases are being moved, you must be a member of the following:
• The Administrators group
• The db_owner fixed database role

In some environments, you must coordinate the move procedures with the database administrator. Be sure to follow any applicable policies and guidelines for managing databases.

Detailed Steps:

To prepare the new database server

• Configure the new database server. (With Same Version of previous one.)

To close any open management sessions

• Close any open Windows PowerShell management shell windows and any open command prompt windows if you have been running the Stsadm command-line tool.

To stop the farm

1. On the server that is running the Central Administration Web site, in the Services snap-in, stop the following services:
• SharePoint 2010 Administration
• SharePoint 2010 Timer
• SharePoint 2010 Tracing
• SharePoint 2010 User Code Host
• SharePoint 2010 VSS Writer
• SharePoint Foundation Search V4
• World Wide Web Publishing Service
• SharePoint Server Search 14
• Web Analytics Data Processing Service
• Web Analytics Web Service
2. On the server that is running the Central Administration Web site, at the command prompt, type iisreset /stop.

To detach databases

• In SQL Server Management Studio on the original database server, detach the databases that you want to move from the instance that they are attached to. If you are running many databases, you may want to run a Transact-SQL script to detach databases.

To move database files to the new server

• Using Windows Explorer, locate the .mdf, .ldf, and .ndf files associated with each database that you are moving, and then copy or move them to the destination directory on the new computer that is running SQL Server.

To set up permissions on the new server

• On the destination database server, open SQL Server Management Studio and transfer your logins and permissions from the original instance to the destination instance. We recommend that you transfer permissions by running a script.

To attach databases to the new instance of SQL Server

• On the destination database server, attach the databases to the new instance.

To point the Web application to the new database server by setting up SQL Server connection aliases

1. Start SQL Server Configuration Manager. On the Start menu, point to All Programs, point to Microsoft SQL Server 2008, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. Expand SQL Native Client Configuration, right-click Aliases, and then click New Alias.
3. In the Alias Name field, enter the name of the original SQL Server instance, for Protocol, verify that TCP/IP is selected, for Server, enter the name of the new server that is hosting the SharePoint Server 2010 databases, and then click OK.
4. Repeat this procedure on each Web and application server.
5. Optional. If your environment relies on System Center Data Protection Manager (DPM) 2010 or a third-party application that uses the Volume Shadow Copy Service (VSS) framework for backup and recovery, you must install the SQL Server connectivity components on each Web server or application server by running SQL Server setup.

To reconfigure Web Analytics database locations

1. On the SharePoint Central Administration Web site, under Application Management, click Manage Service Applications.
2. Select the Web Analytics service application, and then click Properties.

The Edit Web Analytics Service Application wizard appears.

3. Click Next.
4. On the second page of the wizard, update the location of each Web Analytics database to the new SQL Server instance, and then click Next.
5. In Central Administration, under System Settings, click Manage Services on Server.
6. Stop and restart the Web Analytics Data Processing Service, and the Web Analytics Web Service.
7. After moving databases, you should manually run the health rule "Web Analytics: Verifies that the SQL Server Service Broker is enabled for the Web Analytics staging databases." To manually run the health rule, follow these steps:
• In Central Administration, click Monitoring.
• In the Health Analyzer section, click Review rule definitions.
  
  The All Rules page is displayed.
• Under Category: Configuration, click the health rule Web Analytics: Verifies that the SQL Server Service Broker is enabled for the Web Analytics staging databases.
  
  The Health Analyzer Rule Definition dialog box opens.
• On the ribbon of the Health Analyzer Rule Definitions dialog box, click Run Now.

To restart the services in the farm

1. On the server that is running the Central Administration Web site, at the command prompt, type iisreset /start.
2. In the Microsoft Management Console Services snap-in, start all of the services related to SharePoint and Internet Information Services (IIS). These include the following services:
• SharePoint 2010 Administration
• SharePoint 2010 Timer
• SharePoint 2010 Tracing
• SharePoint 2010 User Code Host
• SharePoint 2010 VSS Writer
• SharePoint Foundation Search V4
• World Wide Web Publishing Service
• SharePoint Server Search 14
• Web Analytics Data Processing Service
• Web Analytics Web Service