0 out of 2 rated this helpful - Rate this topic

Move the User Profile service application databases in SharePoint Server 2013

SharePoint 2013

Published: October 16, 2012

Summary: Learn how to move the User Profile service application databases in SharePoint Server 2013.

Applies to: SharePoint Foundation 2013 | SharePoint Server 2013

This article describes how to move the User Profile service application database. Moving service application databases to another farm database server or database instance can be done to load-balance a system. The process involves the two main tasks, each with sub-tasks, of moving the databases files and then pointing the service application to the moved database.

In this article:

Before you begin
Move the service application database
Point the service application to a moved database

Before you begin

Note:
Administrators typically use the SharePoint Central Administration website and the SharePoint Management Shell to manage deployments. For information about accessibility for administrators, see Accessibility for SharePoint 2013. Because SharePoint 2013 runs as websites in Internet Information Services (IIS), administrators and users depend on the accessibility features that browsers provide. SharePoint 2013 supports the accessibility features of supported browsers. For more information, see the following resources: Plan browser support Accessibility for SharePoint 2013 Accessibility features in SharePoint 2013 Products Keyboard shortcuts Touch

Note:

Administrators typically use the SharePoint Central Administration website and the SharePoint Management Shell to manage deployments. For information about accessibility for administrators, see Accessibility for SharePoint 2013.

Because SharePoint 2013 runs as websites in Internet Information Services (IIS), administrators and users depend on the accessibility features that browsers provide. SharePoint 2013 supports the accessibility features of supported browsers. For more information, see the following resources:

Important:
The account, or accounts, performing the operations, must have the following rights and permissions: A member of the Farm Administrators SharePoint group. A member of the Administrators group on the local server Have read permission on the source location and write permission on the target location. Have the db_owner fixed database role for all of the databases that you are moving. 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.

Important:

The account, or accounts, performing the operations, must have the following rights and permissions:

A member of the Farm Administrators SharePoint group.
A member of the Administrators group on the local server
Have read permission on the source location and write permission on the target location.
Have the db_owner fixed database role for all of the databases that you are moving.

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.

Move the service application database

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 move the related database.

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

To stop the service application

Verify that the user account that is performing this procedure is a member of the Farm Administrators SharePoint group.
You must stop the Business Data Connectivity service service application by following the appropriate procedure for the service application that you are working with. For more information, see Start or stop a service (SharePoint 2013).

To detach the database from SQL Server

Verify that the user account that is performing this procedure has the db_owner fixed database role for all of the databases that you are moving.
In SQL Server Management Studio, open the source SQL Server instance, and then expand the Databases node.
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

Verify that the user account that is performing this procedure has read permission on the source location and write permission on the target location.
In Windows Explorer, locate the .mdf, .ndf, and .ldf files for the service application databases.
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

Verify that the user account that is performing this procedure has the db_owner fixed database role for all of the databases that you are moving.
In Management Studio, open the destination SQL Server instance.
Right-click the Databases node, point to Tasks, and then click Attach.
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.
Repeat for each database that you are moving.

Point the service application to a moved database

The method for pointing a service application to a 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.

To document service application settings

Before you delete and re-create a service application, document the settings for the service application. To do this, use the recommended Windows PowerShell cmdlets that are described in the article Document farm configuration settings in SharePoint 2013.

To delete the service application by using Central Administration

To delete a service application, follow the procedure in Create, edit, or delete User Profile service applications in SharePoint Server 2013, but do not delete the service application database.

To create the service application

To re-create a service application, follow the procedure in Create, edit, or delete User Profile service applications in SharePoint Server 2013.

Note:
When you create a new User Profile Service application, you must also export and import the encryption key for profiles. For more information, see the Export the encryption key for the User Profile service application section in this article "Attach databases and upgrade to SharePoint 2013".

To start the service application

Verify that the user account that is performing this procedure is a member of the Farm Administrators SharePoint group.
In Central Administration, under System Settings, click Manage services on server.
In the Service list, next to the service application, click Start.