This article describes how to rename or move Microsoft SharePoint Server 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
Search: Property Possible Possible Preferred
Search: Crawl Possible Possible Preferred
Search: Administration Possible Preferred Not available
Web Analytics: Staging Possible Possible Preferred
Web Analytics: Reporting Possible Possible Preferred
Business Data Connectivity Preferred Not available Not available1 Application Registry2 Not available Not available Not available
State Service Not available Preferred Not available
User Profile: Profile Preferred Not available Not available User Profile: Social
Tagging
Preferred Not available Not available
User Profile: Synchronization
Preferred Not available Not available
Metadata Management Possible Possible Preferred
Word Automation service Possible Possible Preferred
Secure Store Possible Not available3 Preferred
PerformancePoint Not available Preferred Not available
Subscription settings Possible Preferred Not available
1
It 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.
2
Because 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 Server 2010 backup and
recovery.
3
It may appear that you can rename the Secure Store database by using the Windows PowerShell cmdlet Set-SPSecureStoreServiceApplication. However, using Set-
SPSecureStoreServiceApplication creates a new database. It does not rename the existing
database. 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 Explorer
General method for pointing a service application to a renamed or moved database
(http://technet.microsoft.com/library/d72521b3-4b64-48b4-b96a-71a2d96222d1.aspx#General) 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
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 Search service application to renamed or moved databases Pointing the Web Analytics service application to renamed or moved databases Pointing the State Service service application to a renamed or moved database Pointing the Managed Metadata service application to a renamed or moved database Pointing the Word Automation service application to a renamed or moved database Pointing the Secure Store 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 User Profile service application to renamed or moved databases
Pointing the PerformancePoint 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
(http://go.microsoft.com/fwlink/?LinkID=186132&clcid=0x409).
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.
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 Server 2010).
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.
Important
To stop a service application