升級或修補複寫的資料庫Upgrade or patch replicated databases

適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) - 僅限 Windows 適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) - Windows only

SQL ServerSQL Server 支援從舊版 SQL ServerSQL Server 升級複寫資料庫。升級節點時,不需要停止其他節點上的活動。supports upgrading replicated databases from previous versions of SQL ServerSQL Server; it is not required to stop activity at other nodes while a node is being upgraded. 請確定您遵守有關拓撲中支援之版本的規則:Ensure that you adhere to the rules regarding which versions are supported in a topology:

  • 散發者可以是任何版本,只要其高於或等於發行者版本 (在許多情況下,散發者與發行者為同一執行個體)。A Distributor can be any version as long as it is greater than or equal to the Publisher version (in many cases the Distributor is the same instance as the Publisher).
  • 發行者可以是任何版本,只要它小於或等於散發者版本即可。A Publisher can be any version as long as it less than or equal to the Distributor version.
  • 訂閱者版本視發行集的類型而定:Subscriber version depends on the type of publication:
    • 交易式發行集的訂閱者可以是兩個發行者版本內的任何版本。A Subscriber to a transactional publication can be any version within two versions of the Publisher version. 例如:SQL Server 2012 (11.x) 發行者可以有 SQL Server 2014 (12.x) 和 SQL Server 2016 (13.x) 訂閱者,而 SQL Server 2016 (13.x) 發行者可以有 SQL Server 2014 (12.x) 和 SQL Server 2012 (11.x) 訂閱者。For example: a SQL Server 2012 (11.x) Publisher can have SQL Server 2014 (12.x) and SQL Server 2016 (13.x) Subscribers; and a SQL Server 2016 (13.x) Publisher can have SQL Server 2014 (12.x) and SQL Server 2012 (11.x) Subscribers.
    • 合併式發行集的訂閱者可以是等於或小於版本生命週期支援週期所支援發行者版本的所有版本。A Subscriber to a merge publication can be all versions equal to or lower than the Publisher version which are supported as per the versions life cycle support cycle.

SQL Server 的升級路徑視部署模式而有所不同。The upgrade path to SQL Server is different depending on the deployment pattern. SQL Server 在一般情況下會提供兩種升級路徑:SQL Server offers two upgrade paths in general:

  • 並存:部署平行環境,並將資料庫及相關聯的執行個體層級物件 (例如登入、作業等) 移到新環境。Side-by-side: Deploy a parallel environment and move databases along with the associated instance level objects, such as logins, jobs, etc. to the new environment.
  • 就地升級:允許 SQL Server 安裝媒體透過取代 SQL Server 位元並升級資料庫物件,將現有的 SQL Server 安裝升級。In-place upgrade: Allow the SQL Server installation media to upgrade the existing SQL Server installation by replacing the SQL Server bits, and upgrading the database objects. 針對執行 Always On 可用性群組或容錯移轉叢集執行個體的環境,就地升級會結合輪流升級,將停機時間縮到最短。For environments running Always On Availability Groups or Failover Cluster Instances, an in-place upgrade is combined with a rolling upgrade to minimize downtime.

複寫拓撲並存升級所採用常見方法是將部分發行者/訂閱者配對移到新的並存環境,而不是移動整個拓撲。A common approach that has been adopted for side-by-side upgrades of replication topologies is to move publisher-subscriber pairs in parts to the new side-by-side environment as opposed to a movement of the entire topology. 這種階段式方法可協助控制停機時間,並針對取決於複寫的商務將影響降低到某個程度。This phased approach helps control downtime and minimize the impact to a certain extent for the business dependent on replication.

本文主要針對升級 SQL Server 版本。The majority of this article is scoped towards upgrading the version of SQL Server. 不過,使用 Service Pack 或累積更新修補 SQL Server 時,應該也會使用就地升級程序。However, the in-place upgrade process should also be used when patching SQL Server with a service pack or cumulative update as well.

警告

升級複寫拓樸是多步驟程序。Upgrading a replication topology is a multi-step process. 建議您先在測試環境中嘗試升級複寫拓撲的複本,再於實際生產環境上執行升級。We recommend attempting an upgrade of a replica of your replication topology in a test environment before running the upgrade on the actual production environment. 這有助於制訂順暢處理升級所需的任何操作文件,而不會在實際升級過程期間導致昂貴且長時間的停機。This will help iron out any operational documentation that is required for handling the upgrade smoothly without incurring expensive and long downtimes during the actual upgrade process. 我們已看到客戶升級其複寫拓樸時,透過為其生產環境使用 Always On 可用性群組和/或 SQL Server 容錯移轉叢集執行個體,大幅減少停機時間。We have seen customers reduce downtime significantly with the use of Always On Availability Groups and/or SQL Server Failover Cluster Instances for their production environments while upgrading their replication topology. 此外,建議您先備份所有的資料庫 (包括 MSDB、Master、散發資料庫及參與複寫的使用者資料庫),再嘗試升級。Additionally, we recommend taking backups of all the databases including MSDB, Master, Distribution database(s) and the user databases participating in replication before attempting the upgrade.

複寫矩陣Replication Matrix

交易式與快照複寫相容性矩陣Transactional & Snapshot Replication compatibility matrix

發行者Publisher 散發者Distributor 訂閱者Subscriber
SQL Server 2019SQL Server 2019 SQL Server 2019SQL Server 2019 SQL Server 2019SQL Server 2019
SQL Server 2017SQL Server 2017
SQL Server 2016SQL Server 2016
SQL Server 2017SQL Server 2017 SQL Server 2019SQL Server 2019
SQL Server 2017SQL Server 2017
SQL Server 2019SQL Server 2019
SQL Server 2017SQL Server 2017
SQL Server 2016SQL Server 2016
SQL Server 2014SQL Server 2014
SQL Server 2016SQL Server 2016 SQL Server 2019SQL Server 2019
SQL Server 2017SQL Server 2017
SQL Server 2016SQL Server 2016
SQL Server 2019SQL Server 2019
SQL Server 2017SQL Server 2017
SQL Server 2016SQL Server 2016
SQL Server 2014SQL Server 2014
SQL Server 2012SQL Server 2012
SQL Server 2014SQL Server 2014 SQL Server 2019SQL Server 2019
SQL Server 2017SQL Server 2017
SQL Server 2016SQL Server 2016
SQL Server 2014SQL Server 2014
SQL Server 2017SQL Server 2017
SQL Server 2016SQL Server 2016
SQL Server 2014SQL Server 2014
SQL Server 2012SQL Server 2012
SQL Server 2008 R2SQL Server 2008 R2
SQL Server 2008SQL Server 2008
SQL Server 2012SQL Server 2012 SQL Server 2019SQL Server 2019
SQL Server 2017SQL Server 2017
SQL Server 2016SQL Server 2016
SQL Server 2014SQL Server 2014
SQL Server 2012SQL Server 2012
SQL Server 2016SQL Server 2016
SQL Server 2014SQL Server 2014
SQL Server 2012SQL Server 2012
SQL Server 2008 R2SQL Server 2008 R2
SQL Server 2008SQL Server 2008
SQL Server 2008 R2SQL Server 2008 R2
SQL Server 2008SQL Server 2008
SQL Server 2019SQL Server 2019
SQL Server 2017SQL Server 2017
SQL Server 2016SQL Server 2016
SQL Server 2014SQL Server 2014
SQL Server 2012SQL Server 2012
SQL Server 2008 R2SQL Server 2008 R2
SQL Server 2008SQL Server 2008
SQL Server 2014SQL Server 2014
SQL Server 2012SQL Server 2012
SQL Server 2008 R2SQL Server 2008 R2
SQL Server 2008SQL Server 2008
     

合併式複寫相容性矩陣Merge Replication compatibility matrix

發行者Publisher 散發者Distributor 訂閱者Subscriber
SQL Server 2019SQL Server 2019 SQL Server 2019SQL Server 2019 SQL Server 2019SQL Server 2019
SQL Server 2017SQL Server 2017
SQL Server 2016SQL Server 2016
SQL Server 2014SQL Server 2014
SQL Server 2012SQL Server 2012
SQL Server 2008 R2SQL Server 2008 R2
SQL Server 2008SQL Server 2008
SQL Server 2017SQL Server 2017 SQL Server 2019SQL Server 2019
SQL Server 2017SQL Server 2017
SQL Server 2017SQL Server 2017
SQL Server 2016SQL Server 2016
SQL Server 2014SQL Server 2014
SQL Server 2012SQL Server 2012
SQL Server 2008 R2SQL Server 2008 R2
SQL Server 2008SQL Server 2008
SQL Server 2016SQL Server 2016 SQL Server 2019SQL Server 2019
SQL Server 2017SQL Server 2017
SQL Server 2016SQL Server 2016
SQL Server 2016SQL Server 2016
SQL Server 2014SQL Server 2014
SQL Server 2012SQL Server 2012
SQL Server 2008 R2SQL Server 2008 R2
SQL Server 2008SQL Server 2008
SQL Server 2014SQL Server 2014 SQL Server 2019SQL Server 2019
SQL Server 2017SQL Server 2017
SQL Server 2016SQL Server 2016
SQL Server 2014SQL Server 2014
SQL Server 2014SQL Server 2014
SQL Server 2012SQL Server 2012
SQL Server 2008 R2SQL Server 2008 R2
SQL Server 2008SQL Server 2008
SQL Server 2012SQL Server 2012 SQL Server 2019SQL Server 2019
SQL Server 2017SQL Server 2017
SQL Server 2016SQL Server 2016
SQL Server 2014SQL Server 2014
SQL Server 2012SQL Server 2012
SQL Server 2012SQL Server 2012
SQL Server 2008 R2SQL Server 2008 R2
SQL Server 2008SQL Server 2008
SQL Server 2008 R2SQL Server 2008 R2
SQL Server 2008SQL Server 2008
SQL Server 2019SQL Server 2019
SQL Server 2017SQL Server 2017
SQL Server 2016SQL Server 2016
SQL Server 2014SQL Server 2014
SQL Server 2012SQL Server 2012
SQL Server 2008 R2SQL Server 2008 R2
SQL Server 2008SQL Server 2008
SQL Server 2008 R2SQL Server 2008 R2
SQL Server 2008SQL Server 2008
     
     

在升級之前執行異動複寫的記錄讀取器代理程式Run the Log Reader Agent for Transactional Replication Before Upgrade

升級 SQL ServerSQL Server 之前,您必須確定所有來自已發行資料表的認可交易都已經由記錄讀取器代理程式處理過。Before you upgrade SQL ServerSQL Server, you must make sure that all committed transactions from published tables have been processed by the Log Reader Agent. 若要確定已經處理過所有交易,請針對每個包含交易式發行集的資料庫執行下列步驟:To make sure that all transactions have been processed, perform the following steps for each database that contains transactional publications:

  1. 確定已在針對資料庫執行記錄讀取器代理程式。Make sure that the Log Reader Agent is running for the database. 依預設,代理程式會持續執行。By default, the agent runs continuously.
  2. 停止在已發行資料表上的使用者活動。Stop user activity on published tables.
  3. 提供時間讓記錄讀取器代理程式將交易複製到散發資料庫,然後再停止代理程式。Allow time for the Log Reader Agent to copy transactions to the distribution database, and then stop the agent.
  4. 執行 sp_replcmds 以確認已處理所有的交易。Execute sp_replcmds to verify that all transactions have been processed. 這個程序中所產生的結果集應該是空的。The result set from this procedure should be empty.
  5. 執行 sp_replflush 以關閉 sp_replcmds 的連接。Execute sp_replflush to close the connection from sp_replcmds.
  6. 將伺服器升級至最新版的 SQL ServerSQL ServerPerform the server upgrade to the latest version of SQL ServerSQL Server.
  7. 如果 SQL ServerSQL Server Agent 和記錄讀取器代理程式在升級之後沒有自動啟動,則將其重新啟動。Restart SQL ServerSQL Server Agent and the Log Reader Agent if they do not start automatically after the upgrade.

升級之後為合併式複寫執行代理程式Run Agents for Merge Replication After Upgrade

升級之後,請為每一個合併式發行集執行快照集代理程式,並為每一個訂閱執行合併代理程式來更新複寫中繼資料。After upgrade, run the Snapshot Agent for each merge publication and the Merge Agent for each subscription to update replication metadata. 您不必套用新的快照集,因為不需要重新初始化訂閱。You do not have to apply the new snapshot, because it is not necessary to reinitialize subscriptions. 升級之後,第一次執行合併代理程式時會更新訂閱中繼資料。Subscription metadata is updated the first time the Merge Agent is run after upgrade. 這表示在發行者升級時,訂閱資料庫可以持續在線上運作並保持使用中狀態。This means that the subscription database can remain online and active during the Publisher upgrade.

合併式複寫會將發行集與訂閱中繼資料儲存在發行集與訂閱資料庫中的許多系統資料表內。Merge replication stores publication and subscription metadata in a number of system tables in the publication and subscription databases. 執行快照集代理程式會更發行集中繼資料,而執行合併代理程式會更新訂閱中繼資料。Running the Snapshot Agent updates publication metadata and running the Merge Agent updates subscription metadata. 只有要產生發行集快照集時才需要它。It is only required to generate a publication snapshot. 如果合併式發行集使用參數化篩選,則每個資料分割也會有快照集。If a merge publication uses parameterized filters, each partition also has a snapshot. 您不需要更新這些分割快照集It is not necessary to update these partitioned snapshots.

您可以從 SQL Server Management StudioSQL Server Management Studio、複寫監視器或命令列執行代理程式。Run the agents from SQL Server Management StudioSQL Server Management Studio, Replication Monitor, or from the command line. 如需如何執行快照集代理程式的詳細資訊,請參閱下列文章:For more information about running the Snapshot Agent, see the following articles:

如需如何執行合併代理程式的詳細資訊,請參閱下列文章:For more information about running the Merge Agent, see the following articles:

在使用合併式複寫的拓撲中升級 SQL ServerSQL Server 之後,如果您想要使用新功能,請變更任何發行集的發行集相容性層級。After upgrading SQL ServerSQL Server in a topology that uses merge replication, change the publication compatibility level of any publications if you want to use new features.

升級至 Standard、Workgroup 或 Express EditionUpgrading to Standard, Workgroup, or Express Editions

SQL ServerSQL Server 的一個版本升級到另一個版本之前,請確認您目前使用的功能在您想要升級後的版本中受到支援。Before upgrading from one edition of SQL ServerSQL Server to another, verify that the functionality you are currently using is supported in the edition to which you are upgrading. 如需詳細資訊,請參閱 SQL Server 版本和支援的功能中的<複寫>一節。For more information, see the section on Replication in Editions and supported features of SQL Server.

升級複寫拓撲的步驟Steps to upgrade a replication topology

這些步驟概述升級複寫拓撲中伺服器時應該採用的順序。These steps outline the order in which servers in a replication topology should be upgraded. 無論您執行的是交易式或合併式複寫,都適用相同的步驟。The same steps apply whether you're running transactional or merge replication. 不過,這些步驟並未涵蓋點對點複寫、佇列更新訂閱,也未涵蓋立即更新訂閱。However, these steps do not cover Peer-to-Peer replication, queued updating subscriptions, nor immediate updating subscriptions.

就地升級In-place upgrade

  1. 升級散發者。Upgrade the Distributor.
  2. 升級發行者和訂閱者。Upgrade the Publisher and the Subscriber. 這些可以依任何順序升級。These can be upgraded in any order.

注意

若為 SQL 2008 和 2008 R2,必須同時完成發行者和訂閱者的升級,以與複寫拓樸矩陣保持一致。For SQL 2008 and 2008 R2, the upgrade of the publisher and subscriber must be done at the same time to align with the replication topology matrix. SQL 2008 或 2008R2 的發行者或訂閱者不能有 SQL 2016 (或更高版本) 的發行者或訂閱者。A SQL 2008 or 2008R2 publisher or subscriber cannot have a SQL 2016 (or greater) publisher nor subscriber. 如果無法同時升級,請使用中繼升級將 SQL 執行個體升級至 SQL 2014,再將其升級至 SQL 2016 (或更高版本)。If upgrading at the same time is not possible, use an intermediate upgrade to upgrade the SQL instances to SQL 2014, and then upgrade them again to SQL 2016 (or greater).

並存升級Side by side upgrade

  1. 升級散發者。Upgrade the Distributor.
  2. 在新的 SQL Server 執行個體上重新設定散發Reconfigure Distribution on the new SQL Server instance.
  3. 升級發行者。Upgrade the Publisher.
  4. 升級訂閱者。Upgrade the Subscriber.
  5. 重新設定所有的發行者-訂閱者配對,包括重新初始化訂閱者。Reconfigure all Publisher-Subscriber pairs, including reinitialization of the Subscriber.

將散發者並存移轉至 Windows Server 2012 R2 的步驟Steps for side-by-side migration of the Distributor to Windows Server 2012 R2

如果您規劃將 SQL Server 執行個體升級至 SQL Server 2016 (或更新版本),且您目前的 OS 是 Windows 2008 (或 2008 R2),則必須將 OS 並存升級至 Windows Server R2 或更新版本。If you are planning to upgrade your SQL Server instance to SQL Server 2016 (or greater), and your current OS is Windows 2008 (or 2008 R2), then you will need to perform a side-by-side upgrade of the OS to Windows Server R2 or greater. 使用此中繼 OS 升級的原因是 SQL Server 2016 不能安裝在 Windows Server 2008/2008 R2 上,且 Windows Server 2008/20008 R2 不允許就地升級至 Windows Server 2016。The reason for this intermediate OS upgrade is that SQL Server 2016 cannot be installed on a Windows Server 2008/2008 R2, and Windows Server 2008/20008 R2 does not allow in-place upgrades directly to Windows Server 2016. 雖然您可以執行從 Windows Server 2008/2008 R2 到 Windows Server 2012 的就地升級,然後就地升級到 Windows Server 2016,但通常不建議這樣做,因為停機時間與多出來的複雜度會使得您無法獲得簡單的復原路徑。While it's possible to perform an in-place upgrade from Windows Server 2008/2008 R2 to Windows Server 2012, and then to Windows Server 2016, doing so is generally not recommended due the downtime and added complexity preventing an easy roll-back path. 針對參與容錯移轉叢集的 SQL Server,並存升級是唯一可用的升級路徑。A side-by-side upgrade is the only upgrade path available for SQL Server instances participating in a failover cluster. 您可以在獨立 SQL Server 執行個體,或 Always On 容錯移轉叢集執行個體 (FCI) 內的 SQL Server 執行個體上執行下列步驟。The following steps can be performed on either a standalone SQL Server instance, or one within an Always On Failover Cluster Instance (FCI).

  1. 在 Windows Server 2012 R2/2016 上使用不同的 Windows 叢集和 SQL Server FCI 名稱或獨立主機名稱,設定新的 SQL Server 執行個體 (獨立或 Always On 容錯移轉叢集)、版次和版本作為散發者。Set up a new SQL Server instance (either standalone, or Always On Failover Cluster), edition, and version as your distributor on Windows Server 2012 R2/2016 with a different windows cluster and SQL Server FCI name or standalone host name. 您必須使目錄結構與舊散發者保持相同,以確保可以在新環境的相同路徑中找到複寫代理程式可執行檔、複寫資料夾及資料庫檔案路徑。You will need to keep the directory structure same as the old distributor to ensure that the replication agents executables, replication folders, and database file paths are found at the same path on the new environment. 這會減少任何必要的移轉後/升級後步驟。This will reduce any post migration/upgrade steps required.
  2. 請確定您的複寫已同步,然後關閉所有的複寫代理程式。Ensure that your replication is synchronized and then shut down all of the replication agents.
  3. 關閉目前的 SQL Server 散發者執行個體。Shut down the current SQL Server Distributor instance. 如果這是獨立執行個體,請關閉伺服器。If this is a standalone instance, shut down the server. 如果這是 SQL FCI,則在叢集管理員中將整個 SQL Server 角色離線,包括網路名稱。If this is a SQL FCI, then take the entire SQL Server role offline in cluster manager, including the network name.
  4. 移除舊 (目前的散發者執行個體) 環境的 DNS 和 AD 電腦物件項目。Remove the DNS and AD computer object entries for the old (current distributor instance) environment.
  5. 變更新伺服器的主機名稱,以符合舊伺服器的主機名稱。Change the hostname of the new server to match that of the old server.
    1. 如果這是 SQL FCI,請使用與舊執行個體相同的虛擬伺服器名稱重新命名新 SQL FCI。If this is a SQL FCI, rename the new SQL FCI with the same virtual server name as the old instance.
  6. 使用 SAN 重新導向、儲存體複製或檔案複製,從前一個執行個體中複製資料庫檔案。Copy the database files from the previous instance using SAN redirection, storage copy, or file copy.
  7. 將新的 SQL Server 執行個體上線。Bring the new SQL Server instance online.
  8. 重新啟動所有的複寫代理程式,並確認代理程式都成功執行。Restart all of the replication agents and verify if the agents are running successfully.
  9. 驗證複寫是否正常運作。Validate if replication is working as expected.
  10. 使用 SQL Server 安裝媒體,將您的 SQL Server 執行個體就地升級至新版 SQL Server。Use the SQL Server setup media to run an in-place upgrade of your SQL Server instance to the new version of SQL Server.

注意

若要減少停機時間,建議您將「並存移轉」散發者作為一個活動執行,並將「就地升級至 SQL Server 2016」作為另一個活動執行。To reduce downtime, we recommend that you perform the side-by-side migration of the distributor as one activity and the in-place upgrade to SQL Server 2016 as another activity. 這可讓您採取階段式方法、降低風險,並將停機時間縮至最短。This will allow you to take a phased approach, reduce risk and minimize downtime.

合併式複寫的 Web 同步處理Web Synchronization for Merge Replication

合併式複寫的 Web 同步處理選項要求,必須將 SQL ServerSQL Server Replication Listener (replisapi.dll) 複製到用於同步處理之 Internet Information Services (IIS) 伺服器上的虛擬目錄。The Web synchronization option for merge replication requires that the SQL ServerSQL Server Replication Listener (replisapi.dll) be copied to the virtual directory on the Internet Information Services (IIS) server used for synchronization. 當您設定 Web 同步處理時,「設定 Web 同步處理精靈」會將檔案複製到虛擬目錄。When you configure Web synchronization, the file is copied to the virtual directory by the Configure Web Synchronization Wizard. 如果您升級安裝在 IIS 伺服器上的 SQL ServerSQL Server 元件,就必須將 replisapi.dll 從 COM 目錄手動複製到 IIS 伺服器上的虛擬目錄。If you upgrade the SQL ServerSQL Server components installed on the IIS server, you must manually copy replisapi.dll from the COM directory to the virtual directory on the IIS server. 如需設定 Web 同步處理的詳細資訊,請參閱 設定 Web 同步處理For more information about configuring Web synchronization, see Configure Web Synchronization.

從舊版還原複寫的資料庫Restoring a Replicated Database from an Earlier Version

若要確定從舊版還原複寫資料庫的備份時有保留複寫設定:還原到與建立備份的伺服器和資料庫同名的伺服器和資料庫。To ensure replication settings are retained when restoring a backup of a replicated database from a previous version: restore to a server and database with the same names as the server and database at which the backup was taken.

另請參閱See Also

SQL Server 複寫SQL Server Replication
複寫管理常見問題集 Replication Administration FAQ
複寫回溯相容性 Replication Backward Compatibility
支援的版本與版本升級 Supported Version and Edition Upgrades
升級 SQL ServerUpgrade SQL Server
Upgrading a Replication Topology to SQL Server 2016 (將複寫拓撲升級至 SQL Server 2016)Upgrading a Replication Topology to SQL Server 2016