移動 SharePoint Server 中的所有資料庫Move all databases in SharePoint Server

摘要:了解如何將所有與 SharePoint Server 2016 和 SharePoint 2013 相關聯的資料庫移至新資料庫伺服器。Summary: Learn how to move all databases associated with SharePoint Server 2016 and SharePoint 2013 to a new database server.

您可使用 SharePoint 管理中心網站 或 SQL Server 工具將所有與 SharePoint Server 相關聯的資料庫移至新資料庫伺服器。You can use the SharePoint Central Administration website, or SQL Server tools to move all databases that are associated with SharePoint Server to a new database server.

開始之前Before you begin

本文中的程序說明如何移動下列架設在單一資料庫伺服器的資料庫種類:The procedures in this article explain how to move the following kinds of databases that are hosted on a single database server:

  • 設定資料庫Configuration database

  • 管理中心 內容資料庫Central Administration content database

  • 內容資料庫Content databases

  • 服務應用程式資料庫Service application databases

重要

[!重要事項] 若要在同一個 SQL Server 執行個體內移動資料庫檔案,建議您使用 ALTER DATABASE 陳述式的 FILENAME 子句。如需詳細資訊,請參閱 移動使用者資料庫To move database files within the same instance of SQL Server we recommend that you use the FILENAME clause of the ALTER DATABASE statement. For more information, see Move User Databases.

注意

[!附註] 若要將資料庫移至另一個 SQL Server 執行個體或移至另一個伺服器,建議您使用資料庫卸離與附加 (SQL Server)SQL Server 資料庫的備份與還原中所找到的程序。To move a database to another instance of SQL Server or to another server, we recommend that you use procedures found in Database Detach and Attach (SQL Server) or Back Up and Restore of SQL Server Databases.

下列是執行此程序的基本必要權限:The following are the minimum permissions that are required to perform this process:

  • 您必須是「SharePoint 伺服器陣列管理員」群組的成員。You must be a member of the Farm Administrators SharePoint group.

  • 在執行 SharePoint 管理中心網站的電腦上,您必須是「管理員」群組的成員。On the computer that is running the SharePoint Central Administration Web site, you must be a member of the Administrators group.

  • 在進行資料庫移動的來源資料庫伺服器上,您必須是下列身分的成員:On the database server from which the databases are being moved, you must be a member of the following:

    • 管理員群組The Administrators group

    • db_backupoperator 固定資料庫角色The db_backupoperator fixed database role

  • 在資料庫所移至的目的地資料庫伺服器上,您必須是下列身分的成員:On the database server to which the databases are being moved, you must be a member of the following:

    • 管理員群組The Administrators group

    • db_owner 固定資料庫角色The db_owner fixed database role

在某些環境中,您必須與資料庫管理員協調移動程序。請務必遵循所有適用於管理資料庫的原則和準則。In some environments, you must coordinate the move procedures with the database administrator. Be sure to follow applicable policies and guidelines for managing databases.

重要

[!重要事項] 移動資料庫時,使用者將無法使用所有伺服器陣列網站及資產。請在正常上班時間外完成此作業。When you move databases, all farm sites and assets are unavailable to users until the process is complete. Complete this operation outside normal business hours.

移動所有資料庫Move all databases

若要將某個資料庫伺服器的所有資料庫移至其他資料庫伺服器,您必須在 SharePoint Server 與 SQL Server 中作業。To move all databases from one database server to another database server, you have to work in both SharePoint Server and SQL Server.

開始這項作業之前,請先檢閱此程序的各個步驟:Before you begin this operation, review the steps in this process:

  1. 準備新資料庫伺服器。Prepare the new database server.

  2. 關閉所有開啟的 SharePoint 管理命令介面 視窗。Close all open SharePoint Management Shell windows.

  3. 關閉所有與 SharePoint Server 和 Internet Information Services (IIS) 相關的服務。Stop all services that are related to SharePoint Server and Internet Information Services (IIS).

  4. 將資料庫從目前的 SQL Server 執行個體卸離。Detach the databases from the current SQL Server instance.

  5. 將所有與資料庫 (.mdf, .ndf, and .ldf) 相關聯的檔案,複製或移動到執行 SQL Server 的新目的地伺服器。Copy or move all files that are associated with the databases (.mdf, .ndf, and .ldf), to the new destination server that runs SQL Server.

  6. 確認所有 SQL Server 登入資料、固定伺服器角色、固定資料庫角色與資料庫權限都已在新目的地資料庫伺服器正確設定。Make sure that all of the SQL Server logins, fixed server roles, fixed database roles, and permissions for the databases are configured correctly on the new destination database server.

    注意

    [!附註] 作為資料庫移動目的地的伺服器所擁有的資料庫資訊,務必要與目前的 SQL Server 執行個體所擁有的資料庫資訊相同。如需有關如何達成這一點的詳細資訊,請參閱如何在 SQL Server 的執行個體之間傳送登入和密碼。如需詳細資訊,請參閱伺服器層級角色資料庫層級角色It is important that the destination server where you move the databases has the same database information that the current SQL Server instance has. For details about how to do this, see How to transfer logins and passwords between instances of SQL Server. For more information, see Server-Level Roles and Database-Level Roles.

  7. 將資料庫附加到執行 SQL Server 的新目的地伺服器。Attach the databases to the new destination server that runs SQL Server.

  8. 使用 SQL Server 連線別名指向新資料庫伺服器,並更新所有應用程式伺服器。Use SQL Server connection aliases to point to the new database server and update all web servers.

    如果您不要使用 SQL Server 連線別名,請使用下列其中一個程序來更新 SharePoint Server 伺服器陣列的資料庫連線。If you do not want to use SQL Server connection aliases use one of the following procedures to update the database connections for your SharePoint Server farm.

    • 案例 1:如果您使用 SharePoint Server 和 SQL Server AlwaysOn 可用性群組來提高可用性或做為災害復原用途,請使用此程序來更新資料庫連接。Scenario 1: Use this procedure to update the database connections if you use SharePoint Server and SQL Server AlwaysOn Availability Groups for high availability or disaster recovery.

    • 案例 2:如果您必須使用手動步驟,或者,如果您將資料庫從 SharePoint Server 單一伺服器陣列角色安裝移至新的單一伺服器陣列角色安裝,請使用此程序。Scenario 2: Use this procedure if you must use manual steps or if you move the databases from a SharePoint Server Single-server farm role installation to a new Single-server farm role installation.

  9. 重新啟動您在步驟 3 中停止的所有服務。Restart all services that you stopped in step 3.

準備新的資料庫伺服器To prepare the new database server

依照<設定 SharePoint Server 的 SQL Server 安全性>中的程序設定新資料庫伺服器。Use the procedures in Configure SQL Server security for SharePoint Server to configure the new database server.

新資料庫伺服器執行的 Windows Server 和 SQL Server 版本,必須與現有的資料庫伺服器相同,或為下列其中一個版本:The new database server must run either the same version of Windows Server and SQL Server as the existing database server or one of the following versions:

若為 SharePoint Server 2016:For SharePoint Server 2016:

  • Windows Server 2012 R2Windows Server 2012 R2

  • Windows Server 2016Windows Server 2016

  • SQL Server 2014 Service Pack 1 (SP1)SQL Server 2014 Service Pack 1 (SP1)

  • SQL Server 2016SQL Server 2016

若為 SharePoint 2013:For SharePoint 2013:

  • Windows Server 2008 R2Windows Server 2008 R2

  • Windows Server 2008 R2 Service Pack 1 (SP1)Windows Server 2008 R2 Service Pack 1 (SP1)

  • Windows Server 2012Windows Server 2012

  • SQL Server 2008SQL Server 2008

  • SQL Server 2012SQL Server 2012

  • SQL Server 2014SQL Server 2014

現有 SharePoint Server 和 Windows Server 的版本也必須支援 DB 要移往的新 SQL Server 的版本。如需詳細資訊,請參閱<SharePoint Server 2016 的硬體及軟體需求>和<SharePoint 2013 的硬體及軟體需求>。The version of the existing SharePoint Server and Windows Server must also support the version of the new SQL Server where the DBs are being moved. For more information, see Hardware and software requirements for SharePoint Server 2016 and Hardware and software requirements for SharePoint 2013.

關閉所有開啟的 SharePoint 管理命令介面 工作階段To close all open sessions of SharePoint Management Shell

  1. 關閉所有開啟的 SharePoint 管理命令介面 視窗,然後所有開啟的命令提示字元視窗。Close all open SharePoint Management Shell windows, and all open command prompt windows.

停止伺服器陣列To stop the farm

  1. 在執行 管理中心的伺服器上,停止下列服務:On the server that is running Central Administration, stop the following services:

    • SharePoint AdministrationSharePoint Administration

    • SharePoint 計時器SharePoint Timer

    • SharePoint TracingSharePoint Tracing

    • SharePoint User Code HostSharePoint User Code Host

    • SharePoint VSS WriterSharePoint VSS Writer

    • World Wide Web Publishing 服務World Wide Web Publishing Service

    • SharePoint Server Search 16SharePoint Server Search 16

  2. 在執行 管理中心的伺服器上的命令提示字元中,輸入 iisreset /stopOn the server that is running Central Administration, at a command prompt, type iisreset /stop.

卸離資料庫To detach databases

  1. 在 SQL Server Management Studio 的原始資料庫伺服器上,將要移動的資料庫從所在的執行個體加以卸離。如果您執行許多資料庫,可能需要執行 Transact-SQL 指令碼來卸離資料庫。In SQL Server Management Studio on the original database server, detach the databases that you want to move from the instance to which they are attached. If you are running many databases, you may want to run a Transact-SQL script to detach databases.

    如果有下列一項情況,就無法卸離資料庫:A database cannot be detached if any one of the following is true:

將資料庫檔案移至新伺服器To move database files to the new server

  1. 確認執行此程序的使用者帳戶是下列項目的成員:Verify that the user account that is performing this procedure is a member of the following:

    在進行資料庫移動的來源資料庫伺服器上,您必須是下列身分的成員:On the database server from which the databases are being moved, you must be a member of the following:

    • 管理員群組The Administrators group

    • db_backupoperator 固定資料庫角色The db_backupoperator fixed database role

      在資料庫所移至的目的地資料庫伺服器上,您必須是下列身分的成員:On the database server to which the databases are being moved, you must be a member of the following:

    • 管理員群組The Administrators group

    • db_owner 固定資料庫角色The db_owner fixed database role

  2. 使用 [Windows 檔案總管] 找出與每一個移動之資料庫相關聯的 .mdf, .ldf 與 .ndf 檔案。Use Windows Explorer to locate the .mdf, .ldf, and .ndf files that are associated with each database that you are moving.

  3. 將檔案複製或移動到執行 SQL Server 之新電腦上的目的地目錄。Copy or move the files to the destination directory on the new computer that is running SQL Server.

在新伺服器上設定權限To set up permissions on the new server

  1. 確認執行此程序的使用者帳戶是下列項目的成員:Verify that the user account that is performing this procedure is a member of the following:

    • 管理員群組The Administrators group

    • db_owner 固定資料庫角色The db_owner fixed database role

  2. 在目的地資料庫伺服器上,啟動 Management Studio,將登入認證及權限從原始執行個體傳送至目的地執行個體。建議您以執行指令碼的方式來傳送權限。如何在 SQL Server 的執行個體之間傳送登入和密碼中有提供範例指令碼。On the destination database server, start Management Studio and transfer your logon credentials and permissions from the original instance to the destination instance. We recommend that you transfer permissions by running a script. An example script is available in How to transfer logins and passwords between instances of SQL Server.

    如需在執行個體之間傳送 SQL Server 中繼資料的詳細資訊,請參閱在另一個伺服器執行個體上提供可用的資料庫時,管理中繼資料For more information about how to transfer SQL Server metadata between instances, see Managing Metadata When Making a Database Available on Another Server Instance.

將資料庫附加到 SQL Server 的新執行個體To attach databases to the new instance of SQL Server

  1. 確認執行此程序的使用者帳戶是下列項目的成員:Verify that the user account that is performing this procedure is a member of the following:

    • 管理員群組The Administrators group

    • db_owner 固定資料庫角色The db_owner fixed database role

  2. 在目的地資料庫伺服器上,將資料庫附加至新的執行個體。如需詳細資訊,請參閱<附加資料庫>和<sp_attach_db (Transact-SQL)>。On the destination database server, attach the databases to the new instance. For more information, see Attach a Database and sp_attach_db (Transact-SQL).

下列程序提供方法來連接新的 SQL Server 執行個體,或更新資料庫連接。請使用最適合您的 SharePoint Server 伺服器陣列環境的程序。The following procedures provide methods to connect to the new SQL Server instance or update the database connections. Use the procedure that works best for your SharePoint Server farm environment.

重要

如果您使用 SharePoint Server 與 SQL Server AlwaysOn 可用性群組,您必須指向 AG Listner。If you're using SharePoint Server and SQL Server AlwaysOn Availability Groups, you must point to the AG Listner.

設定 SQL Server 連線別名,將 Web 應用程式指向新的資料庫伺服器To point the web application to the new database server by setting up SQL Server connection aliases

  1. 在 SharePoint Server 伺服器陣列的所有伺服器上,都必須執行此程序,而這些伺服器連線至主控資料庫的 SQL Server 執行個體。This procedure must be performed on all servers in the SharePoint Server farm that connect to the instance of SQL Server that hosts the databases.

  2. 確認執行此程序的使用者帳戶是下列項目的成員:Verify that the user account that is performing this procedure is a member of the following:

    • 管理員群組The Administrators group

    • db_owner 固定資料庫角色The db_owner fixed database role

  3. 啟動 SQL Server 用戶端網路公用程式 (cliconfg.exe)。此公用程式通常位於 C:\Windows\SysWOW64 資料夾。Start the SQL Server Client Network Utility (cliconfg.exe). This utility is typically located in the C:\Windows\SysWOW64 folder.

  4. 在 [一般] 索引標籤上,確認已啟用 TCP/IP。On the General tab, verify that TCP/IP is enabled.

  5. 在 [ Alias ] 索引標籤上按一下 [新增]。[Add Network Library Configuration] 視窗隨即出現。On the Alias tab, click Add. The Add Network Library Configuration window appears.

  6. 在 [伺服器別名] 方塊中,輸入目前 SQL Server 執行個體的名稱。In the Server alias box, enter the name of the current instance of SQL Server.

  7. 在 [網路程式庫] 區域中,按一下 [TCP/IP]。In the Network libraries area, click TCP/IP.

  8. 在 [ Connection parameters ] 區域在 [伺服器名稱] 方塊中輸入新的伺服器名稱和要與別名產生關聯的執行個體,然後按一下 [確定]。這是伺服器的新主控的 SharePoint Server 資料庫的名稱。In the Connection parameters area, in the Server name box, enter the new server name and instance to associate with the alias, and then click OK. This is the name of the new server that is hosting the SharePoint Server databases.

  9. 在連線至 SQL Server 的新執行個體的伺服器陣列中,對所有伺服器重複執行步驟 3 至 8。Repeat steps 3 through 8 on all servers in the farm that connect to the new instance of SQL Server.

  10. (選用) 如果您的環境仰賴 System Center 2012 - Data Protection Manager (DPM) 或是使用磁碟區陰影複製服務架構的協力廠商應用程式來執行備份及復原,則必須在每一部網頁伺服器及應用程式伺服器上執行 SQL Server 安裝程式,來安裝 SQL Server 連線元件。如需詳細資訊,請參閱從安裝精靈安裝 SQL Server 2014 (安裝程式)Windows Server 安裝與升級Optional. If your environment relies on System Center 2012 - Data Protection Manager (DPM) or a third-party application that uses the Volume Shadow Copy Service framework for backup and recovery, you must install the SQL Server connectivity components on each web server or application server by running SQL Server setup. For more information, see Install SQL Server 2014 from the Installation Wizard (Setup) and Windows Server Installation and Upgrade.

您可以使用這些 Microsoft PowerShell 指令程式來部署、 管理及移除 SQL Server 與 SharePoint Server 中的可用性群組:You can use these Microsoft PowerShell cmdlets to deploy, manage, and remove availability groups in SQL Server with SharePoint Server:

  • 新增 DatabaseToAvailabilityGroupAdd-DatabaseToAvailabilityGroup

  • 移除 DatabaseFromAvailabilityGroupRemove-DatabaseFromAvailabilityGroup

  • 取得 AvailabilityGroupStatusGet-AvailabilityGroupStatus

如果您使用 SharePoint Server 和 SQL Server AlwaysOn 可用性群組來提高可用性或做為災害復原用途,請使用下列程序來更新資料庫連接。Use the following procedure to update the database connections if you use SharePoint Server and SQL Server AlwaysOn Availability Groups for high availability or disaster recovery.

案例 1: 使用 PowerShell 更新資料庫連接Scenario 1: To update the database connections by using PowerShell

  1. 確認您具備下列成員資格:Verify that you have the following memberships:

    • SQL Server 執行個體上的 securityadmin 固定伺服器角色。securityadmin fixed server role on the SQL Server instance.

    • 所有要更新之資料庫上的 db_owner 固定資料庫角色。db_owner fixed database role on all databases that are to be updated.

    • 正在執行 PowerShell Cmdlet 之所在伺服器上的系統管理員群組。Administrators group on the server on which you are running the PowerShell cmdlets.

      系統管理員可以使用 Add-SPShellAdmin Cmdlet 授與使用 SharePoint Server Cmdlet 的權限。An administrator can use the Add-SPShellAdmin cmdlet to grant permissions to use SharePoint Server cmdlets.

      注意

      [!附註] 如果您不具備上述權限,請連絡安裝程式系統管理員或 SQL Server 系統管理員要求權限。如需 PowerShell 權限的其他資訊,請參閱 Add-SPShellAdminIf you do not have permissions, contact your Setup administrator or SQL Server administrator to request permissions. For additional information about PowerShell permissions, see Add-SPShellAdmin.

  2. 啟動 SharePoint 管理命令介面。Start the SharePoint Management Shell.

  3. 在 PowerShell 命令提示字元處,輸入下列命令:At the PowerShell command prompt, type the following commands:

    Add-DatabaseToAvailabilityGroup -AGName "<AGGroupName>" -DatabaseName "<DatabaseName>" [-FileShare "<\\server\share>"]
    

    其中:Where:

    • <AGGroupName> 是可用性群組的名稱。<AGGroupName> is the name of the Avaliability Group.

    • <DatabaseName> 是您加入至可用性群組的資料庫名稱<DatabaseName> is the name of the database that you are adding to the Availability Group

    • 如果使用選用 -FileShare 參數,則 <\server\share> 是所使用伺服器和共用的名稱。If the optional -FileShare parameter is used, <\server\share> is the name of the server and the share that you use.

  4. 對於您移動的所有資料庫,包括設定資料庫與管理中心內容資料庫,重複這些步驟。Repeat these steps for all databases that you move, including the Configuration and Central Administration Content databases.

在下列情況下,請使用下一個程序:Use the next procedure for the following scenarios:

  • 如果您必須使用手動步驟If you must use manual steps

  • 如果您將資料庫從 SharePoint Server 2016 單一伺服器陣列角色類型移至新的單一伺服器陣列角色類型,或從 SharePoint 2013 單一伺服器安裝移至新的單一伺服器安裝。If you move the databases from a SharePoint Server 2016 Single-Server Farm role type to a new Single-Server Farm role type or from a SharePoint 2013 single-server installation to a new single-server installation.

    注意

    [!附註] 單一伺服器陣列角色會取代舊版 SharePoint Server 所提供的獨立安裝模式。如需詳細資訊,請參閱<SharePoint Server 2016 的 MinRole 伺服器角色概觀>。The Single-Server Farm role replaces the Standalone Install mode available in previous SharePoint Server releases. For more information, see Overview of MinRole Server Roles in SharePoint Server 2016.

  • 如果您使用可用性群組,則必須手動將資料庫加入至適當的可用性群組,以提供高可用性/災害復原支援。如需詳細資訊,請參閱<將資料庫加入至可用性群組 (SQL Server)If you use Availability Groups then you must manually add the databases to the availability groups as appropriate to their high availability/disaster recovery support. For more information, see Add a Database to an Availability Group (SQL Server)

  • 如果您使用 SQL 鏡像,請確定鏡像設定正確。如需詳細資訊,請參閱<設定資料庫鏡像 (SQL Server)>和<資料庫鏡像 (SQL Server)>。If you use SQL Mirroring then make sure your mirroring is setup appropriately. For more information, see Setting Up Database Mirroring (SQL Server) and Database Mirroring (SQL Server).

    案例 2: 使用 Microsoft PowerShell 更新資料庫連接Scenario 2: To update the database connections by using Microsoft PowerShell

  1. 啟動 SharePoint 管理命令介面。Start the SharePoint Management Shell.

  2. 在 PowerShell 命令提示字元處,輸入下列命令:At the PowerShell command prompt, type the following commands:

    $db = get-spdatabase -identity <guid>
    

    其中:Where:

    • <GUID> 是您所移動的資料庫識別碼。<GUID> is the ID of the database that you move.

      注意

      [!附註] 使用未含任何參數的 Get-spdatabase ,以查看所有具有 GUID 之資料庫的清單。Use Get-spdatabase without parameters to see a list of all databases with GUIDs.

    $db.ChangeDatabaseInstance("<DBServerName>")
    

    其中:Where:

    • <DBServerName> 是新 SQL Server 的名稱或別名,或是 AlwaysOn 可用性群組接聽程式 DNS 名稱。<DBServerName> is the name or alias of the new SQL Server or is the AlwaysOn Availability Group listener DNS name.

    • $db.Update()
      
  3. 如果您使用 SQL Server 資料庫鏡像,請記得在 SharePoint 資料庫上填入 FailoverServiceInstance 屬性。If you use SQL Server database mirroring then you must remember to populate the FailoverServiceInstance property on the SharePoint database.

    $db.failoverserviceinstance("<DBServerName>")
    

    其中:Where:

    • <DBServerName> 是鏡像的 SQL Server 的名稱或別名。<DBServerName> is the name or alias of the mirrored SQL Server.
    $db.update()
    
  4. 對於您移動的所有資料庫,包括設定資料庫與管理中心內容資料庫,重複這些步驟。Repeat these steps for all databases that you move, including the Configuration and Central Administration Content databases.

重新啟動伺服器陣列中的服務To restart the services in the farm

  1. 確認執行此程序的使用者帳戶為 SharePoint 伺服器陣列管理員群組的成員。Verify that the user account that is performing this procedure is a member of the Farm Administrators SharePoint group.

  2. 在執行 SharePoint 管理中心網站的伺服器上的命令提示字元中,輸入 iisreset /startOn the server that is running the SharePoint Central Administration website, at a command prompt, type iisreset /start.

  3. 在 Microsoft Management Console [服務] 嵌入式管理單元中,啟動所有與 SharePoint Server 及 IIS 相關的服務。這包括下列服務:In the Microsoft Management Console Services snap-in, start all of the services that are related to SharePoint Server and IIS. These include the following services:

    • SharePoint AdministrationSharePoint Administration

    • SharePoint 計時器SharePoint Timer

    • SharePoint TracingSharePoint Tracing

    • SharePoint User Code HostSharePoint User Code Host

    • SharePoint VSS WriterSharePoint VSS Writer

    • World Wide Web Publishing 服務World Wide Web Publishing Service

    • SharePoint Server 搜尋SharePoint Server Search

另請參閱See also

概念Concepts

SharePoint Server 中的資料庫類型和描述Database types and descriptions in SharePoint Server

其他資源Other Resources

快速參考指南:SharePoint Server 2016 資料庫Quick reference guide: SharePoint Server 2016 databases

支援 SharePoint 2013 的資料庫Databases that support SharePoint 2013

Add a database server to an existing farm in SharePoint 2013Add a database server to an existing farm in SharePoint 2013