Rename or move service application databases (SharePoint Foundation 2010)

  • Article

 

Applies to: SharePoint Foundation 2010

This article describes how to rename or move Microsoft SharePoint Foundation 2010 service application databases. Renaming service application databases is typically done to remove the GUID from the database name after the databases have been created by the SharePoint Products Configuration Wizard, or to bring the databases into alignment with your organization's naming standards. Moving service application databases can be done to load-balance a system.

The general process for renaming or moving service application databases is as follows:

  1. Rename or move the database by using SQL Server tools.

  2. Point the service application to the renamed or moved database.

    Pointing a service application to a renamed or moved service application database can be a complex process, especially if you must rename or move the databases for multiple service applications. This is because different types of service applications require different methods of pointing to the new database name or location. Based on the service application database that you are renaming or moving, you will use one of the following methods to point the service application to the renamed or moved database:

    • Delete the service application, re-create the service application, and then point the service application to the existing renamed or moved database.

      You can use this method for most service application databases, but this method can be complex for some service applications, such as Search.

    • Use Windows PowerShell to point the service application to the renamed or moved changed database.

      You can also use Central Administration for a subset of the service application databases that you can use Windows PowerShell to point to. When possible, we recommend that you use Central Administration to rename or move databases because that is the easiest way to perform the operation. Windows PowerShell is available for only some service application databases, and Central Administration is available for a subset of those service applications.

The following table shows the methods that can be used to point each service application to a renamed or moved database.

Methods available for pointing to renamed or moved service application databases

Service application or database Delete and re-create service application Use Windows PowerShell Use Central Administration

Usage and Health Data Collection

Possible

Possible

Preferred

Business Data Connectivity

Preferred

Not available

Not available1

Application Registry2

Not available

Not available

Not available

Subscription settings

Possible

Preferred

Not available

1It may appear that you can rename the Business Data Connectivity database in Central Administration or by using the Windows PowerShell cmdlet Set-SPBusinessDataCatalogServiceApplication. However, using either Central Administration or Set-SPBusinessDataCatalogServiceApplication creates a new database. It does not rename the existing database.

2Because there is no Windows PowerShell cmdlet to create the Application Registry service application, it cannot be renamed. It can be moved by using Microsoft SharePoint Foundation 2010 backup and recovery.

In this article:

  • Renaming or moving service application databases by using SQL Server

    This method includes the following procedures:

    • To stop a service application

    • To rename a database by using SQL Server Management Studio

    • Move a database by using SQL Server Management Studio and Windows

  • General method for pointing a service application to a renamed or moved database

    This method includes the following procedures:

    • To document service application settings

    • To delete a service application

    • To re-create a service application

    • To restart a service application

    Note

    This method does not apply to all service application databases. Refer to the guidance for the specific service application to determine which method to use.

  • Service application-specific guidance for renaming or moving databases

    • Pointing the Usage and Health Data Collection service application to a renamed or moved database

    • Pointing the Business Data Connectivity service application to a renamed or moved database

    • Pointing the Application Registry service application to a renamed or moved database

    • Pointing the Subscription Settings service application to a renamed or moved database

Renaming or moving service application databases by using SQL Server

To rename a service application database, you must use SQL Server. To move a service application database, you must use SQL Server and Windows Explorer. We recommend that you stop the services related to the service application before you rename or move the related database. In general, we expect that you will either rename or move a database. That is, you will typically not perform both actions.

The instructions in this article assume that you have installed SQL Server Management Studio on the database server. If this is not the case, you can download and install Management Studio at Microsoft SQL Server 2008 Management Studio Express (https://go.microsoft.com/fwlink/p/?LinkID=186132&clcid=0x409).

Important

On the database servers on which the operations are performed, you must be a member of the following:

  • Administrators group on the local server

  • db_owner fixed database role

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

To stop a service application

  1. Verify that the user account that is performing this procedure is a member of the Farm Administrators SharePoint group.

  2. For each service application that you plan to change the database for, you must stop the service or disable the service application by following the appropriate procedure for the service application that you are working with. For more information, see Manage service applications (SharePoint Foundation 2010) .

To rename a database by using SQL Server Management Studio

  1. In SQL Server Management Studio, connect to the source SQL Server instance, and then expand the Databases node.

  2. Right-click the database that you want to rename, click Rename, and then type the new name. Repeat this step for each database that you want to rename.

Move a database by using SQL Server Management Studio and Windows Explorer

Moving a database requires detaching the database from SQL Server, moving the files to the new location by using Windows Explorer, and then attaching the database to the new instance of SQL Server.

To detach a database from SQL Server

  1. In SQL Server Management Studio, open the source SQL Server instance, and then expand the Databases node.

  2. Right-click the database, point to Tasks, and then click Detach. Repeat this step for each database that you want to move.

To move database files to a new location by using Windows Explorer

  1. In Windows Explorer, locate the .mdf, .ndf, and .ldf files for the service application databases.

  2. Select the .mdf, .ndf, and .ldf files for the databases that you want to move, and then either copy or move them to the destination directory.

To attach a database to a new instance of SQL Server

  1. In Management Studio, open the destination SQL Server instance.

  2. Right-click the Databases node, point to Tasks, and then click Attach.

  3. In the Attach Database dialog box, browse to where you transferred the .mdf, .ndf, and .ldf files, select the .mdf file for the database that you want to attach, and then click OK.

  4. Repeat for each database that you are moving.

General method for pointing a service application to a renamed or moved database

The method for pointing a service application to a renamed or moved database that works for most service applications is to delete the service application and then re-create the service application. When you re-create the service application, use the new name or new location.

This is the only method that can be used to point to moved or renamed databases for the Business Data Connectivity service application.

This method includes the following steps:

  1. To document service application settings

  2. To delete a service application

  3. To re-create a service application

  4. To restart a service application

To document service application settings

To delete a service application

To re-create a service application

To restart a service application

  1. In Central Administration, under System Settings, click Manage services on server.

  2. In the Service list, next to the appropriate service or services that support the service application that you are restarting, click Start.

Service application-specific guidance for renaming or moving databases

This section describes specific guidance for each kind of service application.

Pointing the Usage and Health Data Collection service application to a renamed or moved database

You can use either Central Administration or Windows PowerShell to point the Usage and Health Data Collection service application to a renamed or moved database.

To point the Usage and Health Data Collection service application to a renamed or moved database by using Central Administration

  1. Verify that the user account that is performing the procedure is a member of the Farm Administrators SharePoint group.

  2. In Central Administration, click Monitoring, and then on the Monitoring page, click Configure usage and health data collection.

    The Configure web analytics and health data collection page appears.

  3. Clear the Enable health data collection check box, and then click OK.

    You return to the Monitoring page.

  4. Click Configure usage and health data collection to return to the Configure web analytics and health data collection page.

  5. Select the Enable health data collection check box

  6. In the Logging Database Server section, specify the updated database server and database name, and then click OK.

To point the Usage and Health data collection service application to a renamed or moved database by using Windows PowerShell

  1. Verify that you meet the following minimum requirements: See Add-SPShellAdmin.

  2. On the Start menu, click All Programs.

  3. Click Microsoft SharePoint 2010 Products.

  4. Click SharePoint 2010 Management Shell.

  5. At the Windows PowerShell command prompt, type the following command:

    Set-SPUsageApplication -Identity "<ServiceApplicationName>" -DatabaseName "<DbName>" -DatabaseServer "<SQLServerName>"

    Where:

    • <ServiceApplicationName>is the name of the usage and health data collection service application.

    • <DbName> is the name of the database.

    • <SQLServerName> is the name of the database server.

For more information, see Set-SPUsageApplication.

Pointing the Business Data Connectivity service application to a renamed or moved database

You must delete the Business Data Connectivity service application, rename or move the database, and then re-create the service application to point to a renamed or moved database. For information, see General method for pointing a service application to a renamed or moved database.

Pointing the Application Registry service application to a renamed or moved database

Because there is no Windows PowerShell cmdlet to create the Application Registry service application, the associated database cannot be renamed. You can move the database by using SharePoint Foundation 2010 backup and recovery. For more information, see Back up a service application in SharePoint Server 2010 and Restore a service application in SharePoint 2010 ProductsBack up a service application (SharePoint Foundation 2010) and Restore a service application (SharePoint Foundation 2010).

Pointing the Subscription Settings service application to a renamed or moved database

You can point the Subscription Settings service application to a renamed or moved database by using Windows PowerShell.

To point the Subscription Settings service application to a renamed or moved database by using Windows PowerShell

  1. Verify that you meet the following minimum requirements: See Add-SPShellAdmin.

  2. Record the Subscription Settings service application name and the database name that you plan to configure.

  3. On the Start menu, click All Programs.

  4. Click Microsoft SharePoint 2010 Products.

  5. Click SharePoint 2010 Management Shell.

  6. At the Windows PowerShell command prompt, type the following command:

    Set-SPSubscriptionSettingsServiceApplication -Identity "<ServiceApplicationName>" -DatabaseName "<DatabaseName>" -DatabaseServer "<DatabaseServer>"

    Where:

    • <ServiceApplicationName> is the name of the Subscription Settings service application.

    • <DatabaseName> is the name of the renamed or moved database.

    • <DatabaseServer> is the location of the renamed or moved database. Do not include this parameter if you are just renaming the database in the same location.

    For more information, see Set-SPSubscriptionSettingsServiceApplication.

See Also

Concepts

Deploy by using DBA-created databases (SharePoint Foundation 2010)
Database types and descriptions (SharePoint Foundation 2010)
Manage service applications (SharePoint Foundation 2010)