Export (0) Print
Expand All

Rename or move service application databases (SharePoint Server 2010)

 

Applies to: SharePoint Server 2010, SharePoint Foundation 2010

Topic Last Modified: 2011-08-05

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

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 Server 2010 backup and recovery.

3It 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:

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/p/?LinkID=186132&clcid=0x409).

importantImportant
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 Server 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.

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.

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 following service applications:

  • Business Data Connectivity

  • User Profile

  • PerformancePoint

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.

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

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.

You can point the Search service application to Crawl and Property databases that have been renamed by using Central Administration or by using Windows PowerShell 2.0. You must use Windows PowerShell 2.0 to point to a renamed Search Administration database.

To point the Search service application to renamed or moved Crawl and Property databases 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 Application Management.

  3. On the Application Management page, click Manage service applications.

  4. Click the name of the Search Service Application.

  5. On the Search Administration page, click Modify.

  6. On the Manage Search Topology page, the following three databases are listed: Administration, Crawl, and Property. You can point to renamed or moved Crawl or Property databases by using this procedure.

    noteNote
    You cannot point to a renamed or moved Search Administration database by using Central Administration.
  7. Click the database that you want to change, and then click Edit Properties.

  8. In the Database Server text box, type the new server location if there is one; in the Database Name text box, type the new name for the database; and then click OK.

  9. On the Manage Search Topology page, click Apply Topology Changes.

    It might take several minutes for the changes to take effect.

To point the Search service application to renamed or moved Crawl, Property, and Administration databases 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 commands:

    • Point the Search Administration database to its new name or location.

      $searchapp | Set-SPEnterpriseSearchServiceApplication -DatabaseName "<NewDbName>" -DatabaseServer "<NewServerName>" 
      
      
      

      Where:

      • <NewDbName> is the name of the renamed database.

      • <NewServerName> is the new database location.

    • Monitor whether the search instances have finished re-provisioning.

      Do {write-host -NoNewline .;Sleep 10; $searchInstance = Get-SPEnterpriseSearchServiceInstance -Local} while ($searchInstance.Status -ne "Online")
      
    • Set the crawl database to its new name or location.

      $CrawlDatabase0 | Set-SPEnterpriseSearchCrawlDatabase -DatabaseName "<NewDbName>" -DatabaseServer "<NewServerName>" 
      
      

      Where:

      • <NewDbName> is the name of the renamed database.

      • <NewServerName> is the new database location.

    • Monitor whether the search instances have finished re-provisioning.

      Do {write-host -NoNewline .;Sleep 10; $searchInstance = Get-SPEnterpriseSearchServiceInstance -Local} while ($searchInstance.Status -ne "Online")
      
    • Set the property database to its new name or location.

      $PropertyDatabase0 | Set-SPEnterpriseSearchPropertyDatabase -DatabaseName "<NewDbName>" -DatabaseServer "<NewServerName>" 
      
      

      Where:

      • <NewDbName> is the name of the renamed database.

      • <NewServerName> is the new database location.

    • Monitor whether the search instances have finished re-provisioning.

      Do {write-host -NoNewline .;Sleep 10; $searchInstance = Get-SPEnterpriseSearchServiceInstance -Local} while ($searchInstance.Status -ne "Online")
      
    • Restart the search service instance.

      get-SPEnterpriseSearchServiceInstance | start-SPEnterpriseSearchServiceInstance
      
    • Resume running the search service application.

      $searchapp.Resume()
      

You can use either Central Administration or Windows PowerShell to point the Web Analytics service application to renamed or moved databases. You can also delete and re-create the service application.

To point to renamed or moved Web Analytics databases by using Central Administration
  1. Verify that the user account that is performing this procedure is a member of the Farm Administrators SharePoint group.

  2. In Central Administration, click Application Management.

  3. On the Application Management page, click Manage service applications.

  4. Click the Web Analytics Service Application. The ribbon becomes active.

  5. Click Properties on the ribbon.

    The Edit Web Analytics Service Application wizard opens.

  6. Click Next on the first page. On the Edit Web Analytics Service Application Topology page, point to the database name, click Edit Properties, and then specify the new database server or database name. Repeat this step for each database.

  7. Click OK to dismiss the Edit Database dialog box, and then click Next.

    You should see a message that the Web Analytics service application was successfully edited.

  8. Click OK.

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

  10. In the Service list, next to the Web Analytics Data Processing Service and Web Analytics Web Service, click Start.

To point the Web Analytics 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-SPWebAnalyticsServiceApplication -Identity "<ServiceApplicationName>" [-ListOfReportingDatabases <ReportingServerAndDatabases>] [-ListOfStagingDatabases <StagingServerAndDatabases>]
    

    Where:

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

    • <ReportingServerAndDatabase> is an XML string that contains the server name and the database name for the Reporting database.

      noteNote
      Despite the name of the option, only one Reporting database is supported per Web Analytics service application.
    • <StagingServerAndDatabases> is an XML string that contains the server name and the database names for the staging databases.

    The following example shows how to use the Set-SPWebAnalyticsServiceApplication cmdlet:

    Set-SPWebAnalyticsServiceApplication -Identity WebAnalyticsServiceApplication1 -ListOfReportingDatabases "<ReportingDatabases><ReportingDatabase ServerName='SharePointReporting' DatabaseName='WAReporting'/></ReportingDatabases>" -ListOfStagingDatabases "<StagingDatabases><StagingDatabaseServerName=''SharePointReporting' ' DatabaseName='WAStaging1'/> <StagingDatabase ServerName='''SharePointReporting' DatabaseName = 'WAStaging2'/></StagingDatabases>" 
    
    

For more information, see Set-SPWebAnalyticsServiceApplication.

The State Service database stores temporary data. You can use Windows PowerShell to point the State Service service application to a renamed or moved database by performing one of the following procedures:

To add a new database to the State Service service application and remove an old 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 to create a new database:

    New-SPStateServiceDatabase -Name "<NewDatabaseName>" 
    

    Then type the following command to remove the old database:

    Remove-SPStateServiceDatabase -Name "<OldDatabaseName>"
    

    Where:

    • <NewDatabaseName> is the name of the new database that you want to create.

    • <OldDatabaseName> is the name of the old database that you want to disassociate with the State service and detach from SQL Server.

For more information, see New-SPStateServiceDatabase and Remove-SPStateServiceDatabase.

To point the State Service 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 State Service service application ID and the database ID that you will 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 to dismount the database:

    Dismount-SPStateServiceDatabase -Identity <DatabaseID>
    

    Where:

    • <DatabaseID> is the State Service database to remove from the service application.The type must be a valid GUID in the form 12345678-90ab-cdef-1234-567890bcdefgh, a valid name of a state database, or an instance of a valid SPStateServiceDatabase object.

    For more information, see Dismount-SPStateServiceDatabase.

  7. Rename or move the database. For details, see To rename a database by using SQL Server Management Studio or Moving a database by using SQL Server Management Studio and Windows .

  8. At the Windows PowerShell command prompt, type the following command to mount the renamed or moved database:

    Mount-SPStateServiceDatabase -Name "<DatabaseName>" -DatabaseServer "<ServerName>"
    

    Where:

    • <DatabaseName> is the name of the database to associate with the State service.

    • <ServerName> is the name of the SQL Server that hosts the State service database.

    For more information, see Mount-SPStateServiceDatabase.

You can point the Managed Metadata service application to a renamed or moved database by using either Central Administration or Windows PowerShell. For information, see Create, update, publish, or delete a managed metadata service application (SharePoint Server 2010).

You can point the Word Automation service application to a renamed or moved database by using Central Administration or Windows PowerShell.

This procedure has the following steps integrated into them, and do not require that they have already been performed:

  1. Stopping a service application

  2. Renaming a database in SQL Server Management Studio

  3. Moving a database by using SQL Server Management Studio and Windows Explorer

noteNote
You do not need to stop the Word Automation service before pointing to a renamed or moved database. However, stopping the Word Automation service will not cause a problem if you choose to do so.
To point the Word Automation service application to a renamed or moved database by using Central Administration
  1. Verify that the user account that is performing this procedure is a member of the Farm Administrators SharePoint group.

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

  3. In the Service list, next to the Word Automation service, click Stop.

  4. In the Quick Launch, click Application Management.

  5. On the Application Management page, click Manage service applications.

  6. Click the Word Automation service application. The ribbon becomes active.

  7. Click Properties on the ribbon.

    The Edit Word Automation Service Application dialog box opens.

  8. Change the database server or database name, and then click OK.

  9. In the Quick Launch, click System Settings.

  10. On the System Settings page, under Servers, click Manage services on server.

  11. In the Service list, next to the Word Automation service, click Start.

To point the Word Automation 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 Word Automation 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:

    $app = Get-SPServiceApplication -Name "<ServiceApplicationName>"
    Set-SPWordConversionServiceApplication -Identity $app -DatabaseName "<DatabaseName>" -DatabaseServer "<DatabaseServer>"
    

    Where:

    • <ServiceApplicationName> is the name of the Word Automation 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 pointing to a renamed database in the same location.

    For more information, see Set-SPWordConversionServiceApplication.

You can point the Secure Store service application to a renamed or moved database by using Central Administration. Alternatively, you can rename or move the database, and then delete or re-create the service application. If you choose to delete and re-create the service application, you must use the same passphrase that you used when you initially configured the service application. For more information, see General method for pointing a service application to a renamed or moved database.

To point the Secure Store Service service application to a renamed or moved database by using Central Administration
  1. Verify that the user account that is performing this task is a member of the Farm Administrators SharePoint group.

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

  3. In the Service list, next to the Secure Store service, click Stop.

  4. In the Quick Launch, click Application Management.

  5. On the Application Management page, click Manage service applications.

  6. Click the Secure Store Service service application. The ribbon becomes active.

  7. Click Properties on the ribbon.

    The Edit Secure Store Service Application page opens.

  8. Change the database server or database name, and then click OK.

    noteNote
    If you use an incorrect name, SharePoint Server 2010 creates a new database.
  9. In the Quick Launch, click System Settings.

  10. On the System Settings page, under Servers, click Manage services on server.

  11. In the Service list, next to the Secure Store service, click Start.

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.

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 Server 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 Products.

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

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

noteNote
The PerformancePoint service application cannot be deleted and then re-created to point to an existing database.
To point the PerformancePoint 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 PerformancePoint 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-SPPerformancePointServiceApplication -Identity "<ServiceApplicationName>" -SettingsDatabase "<OptionalServerName\DatabaseName>"
    

    Where:

    • <ServiceApplicationName> is the name of the PerformancePoint service application.

    • <OptionalServerName\DatabaseName> is the location of and the name of the renamed or moved database. Do not include the location if you are just renaming the database.

    For more information, see Set-SPPerformancePointServiceApplication.

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.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft