Share via

Move all databases (SharePoint Server 2010)

  • Article

 

Applies to: SQL Server 2012, SQL Server 2008, SQL Server 2005

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).

Important

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. For details, see To prepare the new database server.

  2. Close any open Windows PowerShell management shell windows and any open Stsadm command prompt windows. For details, see To close any open management sessions.

  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, see To stop the farm.

  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, see To move database files to the new server.

    Note

    You can also back up all databases and restore them to the new server. Procedures for backing up and restoring all databases are not included in this article. For more information, see How to: Back Up a Database (SQL Server Management Studio) (https://go.microsoft.com/fwlink/p/?LinkID=179208) and How to: Restore a Database Backup (SQL Server Management Studio) (https://go.microsoft.com/fwlink/p/?LinkID=183032).

  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. For details, see To set up permissions on the new server.

  7. In SQL Server, attach the database to the new instance. For details, see To attach databases to the new instance of SQL Server.

  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. For details, see To point the Web application to the new database server by setting up SQL Server connection aliases.

    Note

    The use of SQL Server client aliases is recommended as part of hardening SQL Server for SharePoint environments. For more information, see Harden SQL Server for SharePoint environments (SharePoint Server 2010).

  9. Restart the services. For details, see To restart the services in the farm.

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.

To prepare the new database server

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

      Note

      The final two services are part of the Web Analytics service application. If you are running the Web Analytics service application and choose to rename your server, you must also reconfigure the Web Analytics database locations. For details, see To reconfigure Web Analytics database locations.

  2. On the server that is running the Central Administration Web site, at the command prompt, type iisreset /stop.

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

To attach databases to the new instance of SQL Server

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.

    Note

    If SQL Server Configuration Manager is not installed, you must run SQL Server setup to install it.

  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 all servers in the farm that connect to the new instance of SQL 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. For more information, see How to: Install SQL Server 2008 R2 (Setup) (https://go.microsoft.com/fwlink/p/?LinkID=186119).

To reconfigure Web Analytics database locations

  1. Note

    This procedure is required only if you are running a Web Analytics service application and if you have renamed your server instead of using SQL Server connection aliases.

    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.

    Note

    The SharePoint Web Analytics feature relies on SQL Server service broker to function. The SQL Server service broker cannot be started manually. A SharePoint timer job runs one time per day to ensure that SQL Server service broker is enabled on the necessary databases.

  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.

      Note

      In order to see the health rule, you may need to click the right arrow at the bottom of the All Rules page.

    • 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

See Also

Other Resources

Resource Center: SQL Server and SharePoint Server 2010 Databases