Export (0) Print
Expand All

Move the Search service application databases in SharePoint 2013

SharePoint 2013
 

Applies to: SharePoint Server 2013, SharePoint Foundation 2013

Topic Last Modified: 2014-06-19

Summary: Learn how to move the Search service application databases in SharePoint 2013.

You can move service application databases to another farm database server or an instance of a database to balance the load of the system. The process involves the two main tasks: move the databases files and then point the service application to the moved database.

NoteNote:
You can use the same procedures if you need to rename the Search service application database on the same database server.

In this article:

NoteNote:
Administrators typically use the SharePoint 2013 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:
ImportantImportant:
The account, or accounts, that perform the operations must have the following memberships and permissions:
  • Member of the Farm Administrators SharePoint group.

  • Member of the Administrators group on the local server.

  • Read permission on the source location and write permission on the target location.

  • db_owner fixed database role for all of the databases that you are moving.

  • db_creator and securityadmin roles for all of the databases that you are moving.

The Search Service account must have the following roles:
  • db_owner fixed database role on the Administration, Link, and Crawl databases.

  • SPSearchDBAdmin database role on the Analytics Reporting database.

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

To move the Search service application databases, you must use SQL Server, SQL Server Management Studio, and Windows Explorer. To point to the moved databases, you must use Windows PowerShell. Complete the following steps in the listed order.

  1. Pause the Search service application.

    ImportantImportant:
    The Search service application must be paused until after the databases are restored in the new location and the service is pointed to the moved databases.
  2. Set all search databases to read-only.

    This is necessary to prevent new database transactions during the backup and restore process.

  3. Back up the Search service application databases but do not resume the Search service application.

  4. Enable the max degree of parallelism to 1 in the new SQL Server.

  5. Restore the Search service application databases to the new location.

  6. Point the Search service application to the moved databases.

  7. Resume the Search service application.

To pause the Search service application by using Windows PowerShell
  1. Verify that you have the following memberships:

    • securityadmin fixed server role on the instance of SQL Server.

    • db_owner fixed database role on all databases that are to be updated.

    • Administrators group on the server on which you are running the Windows PowerShell cmdlets.

    An administrator can use the Add-SPShellAdmin cmdlet to grant permissions to use SharePoint 2013 cmdlets.

    NoteNote:
    If you do not have permissions, contact your Setup administrator or SQL Server administrator to request permissions. For additional information about Windows PowerShell permissions, see Add-SPShellAdmin.
  2. Start the SharePoint 2013 Management Shell.

    • For Windows Server 2008 R2:

      • On the Start menu, click All Programs, click Microsoft SharePoint 2013 Products, and then click SharePoint 2013 Management Shell.

    • For Windows Server 2012:

      • On the Start screen, click SharePoint 2013 Management Shell.

        If SharePoint 2013 Management Shell is not displayed on the Start screen:

      • Right-click Computer, click All apps, and then click SharePoint 2013 Management Shell.

    For more information about how to interact with Windows Server 2012, see Common Management Tasks and Navigation in Windows Server 2012.

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

    $ssa = Get-SPEnterpriseSearchServiceApplication <SearchServiceApplicationName>
    Suspend-SPEnterpriseSearchServiceApplication -Identity $ssa
    

    Where:

    • <SearchServiceApplicationName> is the name of the Search service application associated with the database move.

To change the read-only mode for Search service application databases
  1. Verify that the user account that is performing this procedure is a member of the db_owner fixed database role for the content database.

  2. Open SQL Server Management Studio and connect to the database server.

  3. In Object Explorer, expand Databases.

  4. Set the following databases to read-only mode:

    • Search Administration

    • Analytics Reporting

    • Crawl

    • Link

    1. Right-click the database that you want to set to read/write or read-only, and then click Properties.

    2. In the Database Properties dialog box, on the Options properties page, in the State section, select True or False in the list next to Database Read-Only, and then click OK.

    3. Click Yes.

To back up the Search service application databases
  1. Verify that the user account that performs this procedure is a member of the SQL Server db_backupoperator fixed database role on the database server where each database is stored.

  2. Start SQL Server Management Studio and connect to the database server where the Search service application databases are stored.

  3. In Object Explorer, expand Databases.

  4. Right-click the database that you want to back up, point to Tasks, and then click Back Up.

  5. In the Back Up Database dialog box, in the Source area, select the kind of backup that you want to perform from the Backup type list.

    For more information about the type of backup to use, see Recovery Models (SQL Server) in SQL Server Books Online.

  6. In the Backup component area, click Database.

  7. Either use the default name or specify a name for the backup set in the Name box.

  8. Specify the expiration date for the backup set.

    This date determines when the backup set can be overwritten by subsequent backups that have the same name. By default, the backup set is set to never expire (0 days).

  9. In the Destination area, specify where you want to store the backup.

  10. Click OK to back up the database.

  11. Repeat steps 1-10 for the following databases:

    • Search Administration

    • Analytics Reporting

    • Crawl

    • Link

To set the max degree of parallelism to 1 in the new server that hosts SQL Server
  1. Start SQL Server Management Studio and connect to the new server that hosts SQL Server where you'll move the Search service application databases.

  2. In Object Explorer, right-click the database server and then click Properties.

  3. Click Advanced.

  4. In the Max Degree of Parallelism box, select 1 to limit the number of processors to use in parallel plan execution.

    For more information, see Configure the max degree of parallelism Server Configuration Option.

To restore the Search service application databases to a new database server
  1. Verify that the user account that you are using to restore the databases is a member of the SQL Server sysadmin fixed server role on the database server where each database is stored.

  2. Start SQL Server Management Studio and connect to the database server.

  3. In Object Explorer, expand Databases.

  4. Right-click the database that you want to restore, point to Tasks, point to Restore, and then click Database.

  5. In the Restore Database dialog box, on the General page, select the database to restore to from the To database list.

  6. Select the restore source from the From database list.

  7. In the Select the backup sets to restore section area, select the check box next to the database.

  8. On the Options tab, select the recovery state from the Recover state section.

    For more information about which recovery type to use, see Recovery Models (SQL Server) in SQL Server Books Online.

  9. Click OK to restore the database.

  10. Repeat steps 1-9 for each database that is associated with the service application.

To set the Search service application databases to read/write
  1. Follow the steps in To change the read-only mode for Search service application databases.

You must use Windows PowerShell 3.0 to point to the moved Search service application databases: Search Administration, Crawl, Link, and Analytics Reporting.

To point the Search service application to moved databases by using Windows PowerShell
  1. Verify that you have the following memberships:

    • securityadmin fixed server role on the instance of SQL Server.

    • db_owner fixed database role on all databases that are to be updated.

    • Administrators group on the server on which you are running the Windows PowerShell cmdlets.

    An administrator can use the Add-SPShellAdmin cmdlet to grant permissions to use SharePoint 2013 cmdlets.

    NoteNote:
    If you do not have permissions, contact your Setup administrator or SQL Server administrator to request permissions. For additional information about Windows PowerShell permissions, see Add-SPShellAdmin.
  2. Start the SharePoint 2013 Management Shell.

    • For Windows Server 2008 R2:

      1. On the Start menu, click All Programs.

      2. Click Microsoft SharePoint 2013 Products.

      3. Click SharePoint 2013 Management Shell.

    • For Windows Server 2012:

      • On the Start screen, click SharePoint 2013 Management Shell.

        If SharePoint 2013 Management Shell is not displayed on the Start screen:

      1. Right-click Computer.

      2. Click All apps.

      3. Click SharePoint 2013 Management Shell.

    For more information about how to interact with Windows Server 2012, see Common Management Tasks and Navigation in Windows Server 2012.

  3. Point the Search Administration database to the new location. At the Windows PowerShell command prompt, type the following commands:

    $ssa = Get-SPEnterpriseSearchServiceApplication <SearchServiceApplicationName>
    $ssa | Set-SPEnterpriseSearchServiceApplication [-DatabaseName "<NewDbName>"] -DatabaseServer "<NewServerName>"
    

    Where:

    • <NewDbName> is the name of the database.

    • <NewServerName> is the new database location.

  4. Point the Analytics Reporting database to the new location. At the Windows PowerShell command prompt, type the following commands:

    Add-SPServerScaleOutDatabase -ServiceApplication $ssa -DatabaseServer <NewServerName> [-DatabaseName <NewDbName>]
    $temp = Get-SPServerScaleOutDatabase -ServiceApplication $ssa
    Remove-SPServerScaleOutDatabase -Database $temp[0] -ServiceApplication $ssa
    
  5. Point the Crawl database to the new location. At the Windows PowerShell command prompt, type the following commands:

    $CrawlDatabase0 = ([array]($ssa | Get-SPEnterpriseSearchCrawlDatabase))[0] 
    $CrawlDatabase0 | Set-SPEnterpriseSearchCrawlDatabase [-DatabaseName "<NewDbName>"] -DatabaseServer "<NewServerName>"
    
  6. Point the Link database to the new location. At the Windows PowerShell command prompt, type the following commands:

    $LinksDatabase0 = ([array]($ssa | Get-SPEnterpriseSearchLinksDatabase))[0] 
    $LinksDatabase0 | Set-SPEnterpriseSearchLinksDatabase [-DatabaseName "<NewDbName>"] -DatabaseServer "<NewServerName>"
    
  7. Set all Search service instances to Online. Run the following commands for each search service in the farm, until the Search service instance is reported as Online. At the Windows PowerShell command prompt, type the following commands:

    get-SPEnterpriseSearchServiceInstance -Identity <Search Server> Do {write-host -NoNewline .;Sleep 10; $searchInstance = Get-SPEnterpriseSearchServiceInstance -Identity <Search Server>} while ($searchInstance.Status -ne "Online")
    

    Where:

    • <Search Server> is the name of the server that hosts the search components.

  8. Resume the Search service application. At the Windows PowerShell command prompt, type the following commands:

    $ssa = Get-SPEnterpriseSearchServiceApplication <SearchServiceApplicationName>
    Resume-SPEnterpriseSearchServiceApplication -Identity $ssa
    

    Where:

    • <SearchServiceApplicationName> is the name of the Search service application associated with the database move.

  9. Restart each server that hosts a search component.

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