NAKIVO > Blog > Multiplatform

如何設定 MS SQL Server 複寫

Updated: 7 5 月, 2026

撰文:: NAKIVO 團隊

Microsoft SQL Server 是一款可安裝於 Windows Server 作業系統上的資料庫管理軟體。各行各業的公司都會使用資料庫,許多軟體解決方案也仰賴資料庫運作,無論是集中式或分散式資料庫皆然。資料庫的可用性與資料的一致性對企業至關重要,因此資料庫的備份與複製已成為必要措施。

瞭解 SQL Server 的複製類型、SQL Server 中複製的運作原理,以及如何執行 SQL Server 複製。

NAKIVO for Windows 備份

NAKIVO for Windows 備份

快速將 Windows 伺服器和工作站備份至本地、遠端及雲端。數分鐘內即可還原整台機器及特定物件,實現低 RTO 並確保最大正常運作時間。

探索解決方案

什麼是 SQL Server 複製？

MS SQL Server 複製是指將資料(包括特定資料庫物件)從一個資料庫複製到另一個資料庫,並在來源與目標資料庫之間維持資料同步副本的過程。透過 SQL Server 的複製功能,您可以建立主資料庫的完全相同副本,並在兩個資料庫之間同步變更,同時維持資料的一致性與完整性。

MS SQL Server 複製所使用的術語

在深入探討如何配置和設定 MS SQL Server 複寫之前,讓我們先簡要地瀏覽一下主要術語和複寫模型。

Articles 這些是待複製的基本單元,例如資料表、儲存程序、函式和檢視表。透過使用篩選器,可對文章進行垂直或水平縮放。針對同一個物件,可以建立多篇文章。

A publication 是一組邏輯文章集合。這是資料庫中指定用於複製的最後一組實體。

A filter 是一組針對資料行的條件。MS SQL Server 複寫功能允許您使用篩選器並選取自訂實體進行複寫,藉此減少流量、冗餘,以及儲存於資料庫副本中的資料量。例如,您可以透過篩選器僅選取最重要的資料表和欄位,然後僅複寫這些資料。

Agents 這些是 Microsoft SQL Server 的元件,可作為關聯式資料庫管理系統的背景服務,並用於排程自動執行各項工作,例如 Microsoft SQL Server 資料庫的備份與複製。代理程式共有五種類型:快照代理程式、日誌讀取代理程式、分發代理程式、合併代理程式以及佇列讀取代理程式。

Metadata 是用來描述資料庫實體的資料。系統內建了多種元資料函式,可讓您取得有關 MS SQL Server 執行個體、資料庫執行個體及資料庫實體的資訊。

SQL 資料庫複製中的角色

在 MS SQL 資料庫複製中,主要有三個角色:分發者、發佈者和訂閱者。

  • A Distributor 是一個已配置用於從發佈源收集交易,並將其分發給訂閱者的 MS SQL 資料庫執行個體。分發器作為儲存複製交易的資料庫。

    分發器資料庫可同時視為發佈者和分發器。在本地分發器模型中,單一的 MS SQL Server 執行個體同時執行發佈者和分發器。若要讓訂閱者配置為使用單一的 MS SQL Server 執行個體來取得不同的發佈內容(集中式分發),則可採用遠端分發器模型。在此模型中,發佈者和分發器分別在不同的伺服器上執行。

  • A Publisher 這是用於配置發佈的主要資料庫副本,可將資料提供給其他已設定用於複製程序的 MS SQL 伺服器。發佈者可以擁有多個發佈。
  • A Subscriber 是一種從發佈中接收複製資料的資料庫。一個訂閱者可以從多個發佈者和發佈中接收資料。當只有一個訂閱者時,採用單一訂閱者模型;當多個訂閱者連線至單一發佈時,則採用多訂閱者模型。

    Subscription 這是要求提供一份必須交付給訂戶的出版物副本。訂閱是用來定義必須接收的出版物資料,以及接收這些資料的地點和時間。訂閱分為兩種類型:

    • Push subscription:變更後的資料會由發佈者強制傳送至訂閱者資料庫。訂閱者無需發出任何請求。
    • Pull subscription:訂閱者請求發佈者端所做的資料變更。代理程式在訂閱者端執行。

    訂閱資料庫是 MS SQL 複製模型中的目標資料庫。

    MS SQL Server replication scheme

在 多發佈者–多訂閱者 在此模型中,發佈者可在其中一台 MS SQL 伺服器上擔任訂閱者的角色。使用此 MS SQL Server 複製模型時,請務必避免任何潛在的更新衝突。

MS SQL Server 複製類型

MS SQL Server 複製是一項用於在資料庫之間持續或定期(按預定間隔)複製與同步資料的技術。就複製方向而言,MS SQL Server 複製可分為單向、一對多、雙向及多對一。MS SQL Server 複製共有四種類型:快照複製、交易式複製、對等複製以及合併複製。

快照複製

Snapshot replication 用於精確複製資料,使其與建立資料庫快照當下的狀態完全一致。此類複製方式適用於:資料變動頻率不高、資料庫副本比主資料庫舊一些並非關鍵問題,或是短時間內發生大量變更的情況。 快照複製不使用變更追蹤功能。

例如,當匯率或價格清單每天更新一次,且必須從主伺服器分發至分支機構的伺服器時,即可使用快照複製。

How snapshot replication works

交易複製

Transactional replication 這是一種定期自動複製機制,資料會從主資料庫即時(或近即時)地分發至資料庫副本。 交易式複製比快照複製更為複雜。所有執行的交易以及資料庫的最終狀態都會被複製,這使得在複本上監控完整的交易歷史成為可能。

在交易式複製流程的開始階段,會將快照套用至訂閱者,隨後隨著資料的變更,資料會持續從主資料庫傳輸至資料庫複本。交易式複製廣泛用於單向複製。

How transactional replication works

交易式複寫的使用情境:

  • 建立一個資料庫伺服器,並搭配資料庫複本,以便在主資料庫伺服器發生故障時進行故障移轉。
  • 接收關於透過在各分支機構部署多個發佈者,並在總部部署一個訂閱者,所執行的操作之報告。
  • 讓變更一經發生即刻同步。
  • 來源資料庫中的資料經常變動。

點對點複製

Peer-to-peer replication 用於同時將資料庫資料複製至多個訂閱者。當您的資料庫伺服器分散於世界各地時,即可使用此種 Microsoft SQL Server 複製類型。可在任一資料庫伺服器上進行變更,這些變更將傳播至所有資料庫伺服器。點對點複製有助於縮放使用資料庫的應用程式。其主要運作原理基於交易式複製。

Peer-to-peer replication

以下將說明如何在分佈於全球各地的資料庫伺服器之間,運用 MS SQL Server 的點對點複製功能。

Peer-to-peer replication in a distributed environment

合併複製

Merge replication 這是一種雙向複製機制,通常用於伺服器至客戶端環境中,用於在資料庫伺服器無法持續連線時同步資料。當兩台資料庫伺服器之間建立網路連線時,合併複製代理程式會偵測兩端資料庫所做的變更,並修改資料庫以同步並更新其狀態。合併複製與交易式複製類似,但資料會從發佈者複製到訂閱者,反之亦然。

Merge replication

此類資料庫複製是所有 MS SQL Server 複製類型中最複雜的一種,且極少被使用。 舉例來說,合併複製可由多個與共用倉庫協作的對等儲存庫使用。每個儲存庫皆可變更倉庫資料庫中的資訊,同時,在貨物運送或物資送抵倉庫後,所有儲存庫的資料庫狀態都必須保持更新。當更新後的資訊必須同時提供給主(或中央)資料庫與分支資料庫時,即可採用合併複製。

MS SQL Server 複製功能之需求

必須為傳入流量開啟以下端口:

  • TCP 1433、1434、2383、2382、135、80、443
  • UDP 1434

在安裝 MS SQL Server 之前,請務必設定 Windows 防火牆,並在每台主機上啟用適用於傳入流量的相關連接埠。參與 MS SQL 複寫的主機必須透過主機名稱相互解析。

在設定 MS SQL Server 複寫之前,必須為 MS SQL Server 安裝以下軟體:

  • .NET Framework – 一組函式庫
  • MS SQL Server – 資料庫伺服器軟體
  • MS SQL Server Management Studio (SSMS) – 一款透過圖形使用者介面 (GUI) 管理 MS SQL 資料庫的軟體。

註: 本文以 MS SQL Server 2016 為例進行設定。您可運用相同原則,在更新版本的 SQL Server 中設定複寫功能。

請注意,若您在存放來源資料庫的第一台機器上安裝了 MS SQL Server 2016,則第二台機器也必須安裝 MS SQL Server 2016,資料庫才能正常運作。

例如,若要設定 MS SQL 交易式複製,您可將第二台資料庫伺服器(即訂閱者所在的位置)的版本,設定為與發布者所在的來源資料庫伺服器版本相差不超過兩個版本。 若 MS SQL Server 上的發佈者版本為 2016,則分發者可配置於 2016、2017、2019 及 2022 版本,而訂閱者則可配置於 MS SQL Server 2012、2014、2016、2017 及 2019 版本。 發佈者的版本不得低於發佈源的版本。例如,若在第二台機器上安裝 MS SQL Server 2008,複製功能將無法運作。

MS SQL 資料庫複寫的基本建議

在設定 MS SQL Server 環境之前,請先考慮以下幾點:

  • 識別碼欄位和觸發器存在某些限制。
  • 發佈內容僅可包含具有主鍵的表格。
  • 建議您不要對大型資料庫設定快照建立排程,以免佔用過多的運算資源。
  • 在修改位於訂閱端上的資料庫副本中的資料時,請務必謹慎。當有修改資料的交易即將進行,而該資料已被編輯或刪除時,複製作業可能會暫停,直到此問題解決為止。

設定環境

首次設定 MS SQL 複製時,建議您先在測試環境中進行。例如,我們會在虛擬機器上運行的 SQL Server 中設定複製。本教學將使用兩台分別運行 Windows Server 2016 和 MS SQL Server 2016 的主機來說明 MS SQL Server 複製。

讓我們來看看撰寫這篇部落格文章所使用的測試環境設定,以便更深入了解 MS SQL Server 複製的設定。

Host 1

  • IP 位址:192.168.101.101
  • 主機名稱:MSSQL01
  • MS SQL Server 執行個體 ID:MSSQLSERVER1

Host 2

  • IP 位址:192.168.101.102
  • 主機名稱:MSSQL02
  • MS SQL Server 執行個體 ID:MSSQLSERVER2

這兩台電腦的磁碟配置中均包含 C: 磁碟機和 D: 磁碟機。

安裝 MS SQL Server 時,您可以暫時停用 Windows 防火牆,以便練習設定 MS SQL Server 複寫。

由於本教學重點在於設定 MS SQL Server 複寫,因此這篇部落格文章不會深入探討如何安裝 MS SQL Server。在此範例中,兩台 MS SQL Server 皆未安裝 PolyBase。

MS SQL Server 安裝完成後,請確認您已安裝 MS SQL Server 複寫所需的特點。 請注意,在安裝 MS SQL Server 時,必須選取資料庫引擎服務,例如 SQL Server 複寫和 R-Services。本範例使用預設安裝路徑(C:Program FilesMicrosoft SQL Server)。

The components that must be installed with SQL Server

其他設定:

  • 混合驗證模式(Windows 驗證與 MS SQL Server 驗證)
  • 資料根目錄:D:MSSQL_Server
  • 系統資料庫目錄:D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLData
  • 使用者資料庫目錄:D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLData
  • 使用者資料庫日誌目錄:D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLData
  • 備份目錄:D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLBackup

在電腦上安裝完 MS SQL Server 2016 和 SQL Server Management Studio 後,您即可為資料庫複寫做好 MS SQL Server 的準備。

準備進行 MS SQL Server 複製

在開始資料庫複製之前,您必須先設定伺服器。在本範例中,將使用一個 Windows 帳戶來執行 MS SQL Server 複製代理程式。

  1. 建立 mssql 在兩台伺服器上建立使用者,並設定相同的密碼。
  2. 該 mssql 在此範例中,使用者屬於以下群組:
    • 管理員(指本機上的本機管理員,而非網域管理員)
    • SQLRUserGroupMSSQLSERVER1
    • SQLServer2005SQLBrowserUser$MSSQL01
  3. 您可以按下 Win+R,開幕 CMD,並執行 lusrmgr.msc 指令。

本範例中使用的兩台 Windows Server 電腦未加入 Active Directory。若您使用 Active Directory,則可建立 mssql 網域控制器上的使用者。

連線至 MS SQL Server

  1. 執行 SQL Server Management Studio。
  2. 請以(參見螢幕截圖)身分登入 sa 透過使用 SQL Server 驗證。
    • MSSQL01MSSQLSERVER1 是第一台伺服器上的主機名稱及 MS SQL 執行個體名稱。
    • MSSQL02MSSQLSERVER2 是第二台伺服器的主機名稱及 MS SQL 執行個體名稱。

    Log into MS SQL Server instance by using SQL Server authentication

同樣地,您也可以在第二台伺服器(MSSQL02)上連線至第二個 MS SQL Server 執行個體(MSSQLSERVER2)。 您也可以透過在 SQL Server Management Studio 中輸入憑證,從第一個 MS SQL Server (MSSQL01) 連線至第二個 MS SQL Server 執行個體 (MSSQLSERVER2)。您可以在單一執行個體的 SQL Server Management Studio 中連線至兩個 MS SQL Server 執行個體 (MSSQL01 和 MSSQL02)。

要執行此操作,請在物件總管中按一下 Connect > Database engine. 在本教學中,我們將透過 SQL Server Management Studio 來設定 MS SQL 伺服器,從 MSSQL01 連線至 MSSQLSERVER1,並從 MSSQL02 連線至 MSSQLSERVER2。

啟動代理程式

登入 MS SQL Server 執行個體後,您會發現代理程式並未執行。預設情況下,SQL Server 代理程式不會自動啟動。您可以手動啟動此服務,但建議將此服務設定為在 Windows 啟動後自動執行。

Starting SQL Server agent

若要設定 Agent 服務自動啟動:

  1. 新聞 Win+R, 執行 cmd, 並執行 services.msc 指令。
  2. 開啟 SQL Server Agent 服務的屬性,並設定 S輸入新創公司類型至 Automatic.

    SQL Server Agent is running and starts automatically after Windows boot

為 MS SQL Server 設定使用者

在 SQL Server Management Studio 中連線至 MSSQLSERVER1 執行個體後,我們需要設定使用者:

  1. 前往 Object Explorer 並開啟 Security > Logins.
  2. 右鍵點擊 Logins 並選擇 New Login. 選擇 Windows authentication.
  3. 請輸入登入名稱 mssql 在 概述 章節。
  4. 點擊 Search,然後按下 Check names 請確認,然後點擊 OK 點擊兩次以儲存設定。

    Configuring users and permissions

  5. 現在 MSSQL01mssql 將 Windows 使用者新增至可登入資料庫的使用者清單中(同理,新增 mssql 使用者至 登入 (在第二台機器 MSSQL02 的 SQL Server Management Studio 中)。
  6. 加入 mssql 使用者至 sysadmins 伺服器角色在 安全 在 SQL Server Management Studio 中設定資料庫。
  7. 前往 MSSQL01MSSQLSERVER1 > Server roles, 右鍵點擊 sysadmin,並開啟 Properties.
  8. 在 會員 頁面,點擊 Add,請輸入您的使用者名稱 mssql, 然後點擊 Check names.
  9. 勾選使用者名稱旁的核取方塊 MSSQL01mssql 然後點擊 OK.

    Adding a user to server roles on MS SQL Server

  10. 請在您的第二台機器上進行相同的設定(本例中為 MSSQL02)。
  11. 請重新啟動這兩台機器。

    現在,您可以在兩台伺服器上使用 Windows 驗證登入。

    Log in to MS SQL Server instance by using Windows authentication

從備份匯入資料庫

讓我們從備份中匯入一個範例資料庫,然後將該資料庫從第一台機器複製到第二台機器。該 AdventureWorks 2016 本範例中使用此資料庫作為範例資料庫。

  1. 複製 AdventureWorks2016.bak 將資料庫備份檔案複製到您的 MSSQL 備份目錄中。以我們的情況為例,第一台伺服器上的此目錄為 D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLBackup
  2. 匯入範例資料庫。在第一台電腦的 SQL Server Management Studio 中,前往 MSSQL01MSSQLSERVER1, 右鍵點擊 Databases, 並選擇 Restore Database 在右鍵選單中。

    Restoring a sample database to reveal MS SQL Server replication configuration

  3. 在 還原資料庫 在視窗中,選取所需的參數:
    • 來源: Device.
    • 點擊 three dots 瀏覽資料庫備份檔案。
      • 在 選擇備份裝置 視窗中,請選擇備份媒體類型: file.
      • 點擊 Add.
    • 請選擇所需的 .bak 檔案 – D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLBackupAdventureWorks2016.bak
    • 點擊 OK,然後按下 OK 再次。
  4. 該 AdventureWorks 2016 資料庫已成功還原。

    Restoring a sample database in MS SQL Server

您可以從第二台伺服器上的備份匯入資料庫,該伺服器將運行資料庫複本。此方法可減少網路流量,因為複製過程會從備份建立後產生的變更開始進行,無需將整個資料庫資料複製到空的資料庫中。

從第二台伺服器上的備份還原資料庫,並將資料庫重新命名為 AdventureWorks 2016,其中"r"代表"複本"。

最後,我們得到:

主機名稱 MSSQL 執行個體名稱 資料庫名稱
MSSQL01MSSQLSERVER1 AdventureWorks 2016
MSSQL02MSSQLSERVER2 AdventureWorks 2016

匯入資料庫後,您必須進行一些調校,以準備您的 MS SQL 伺服器

  1. 在 MSSQL01 機器,前往 MSSQL01MSSQLSERVER1 > Security > Logins, 請選擇 MSSQL01mssql. 右鍵點擊(或雙擊) mssql 使用者與選取 Properties.
  2. 在 伺服器角色,選取 dbcreator 角色。

    Enabling the dbcreator role for mssql user

  3. 在 使用者對應 頁面中,選取已對應至此登入帳戶的使用者,並勾選 AdventureWorks2016 資料庫核取方塊(選取 AdventureWorks 2016 (並在第二台伺服器上相應地進行設定)。
  4. 在 資料庫角色成員資格 在該區段中,勾選 db_owner 核取方塊。

    Configuring user mapping on MS SQL Server

  5. 點擊 OK 以儲存設定。

在 MSSQL02 電腦上執行相同的設定。接著,您可以設定資料庫複寫所需的 MS SQL Server 元件。

設定資料庫複寫

在圖形化模式下配置複製是最方便的方法。接下來的配置將在 SQL Server Management Studio 中進行。 本範例將說明交易型資料庫複製,因為這是最常用的 MS SQL Server 複製類型之一。

下圖截圖顯示了 SQL Server Management Studio 中主資料庫伺服器(MSSQL01MSSQLSERVER1)的檢視畫面,以及第二台伺服器(MSSQL02MSSQLSERVER2)的檢視畫面。

The view of two MS SQL Server instances in MS SQL Server Management Studio

設定分發

分發功能可適用於多個發佈者和訂閱者。在此範例中,分發功能是設定在儲存來源資料庫的主伺服器上。在主伺服器(MSSQL01MSSQLSERVER1)上,按右鍵 Replication 然後,在快顯選單中選取 Configure Distribution.

Configuring Distribution

該 "配置分發精靈" 開啟。

  1. Distributor. 在此範例中,請選取目前在主伺服器 (MSSQL01MSSQLSERVER1) 上執行的資料庫執行個體,使其擔任分發器。按一下 Next 每次都要繼續進行精靈中的下一步。
  2. SQL Server Agent Start. 若您尚未依照上述說明設定 MS SQL Server Agent 自動啟動,系統將顯示以下訊息。請選擇 Yes, configure the SQL Server Agent service to start automatically.

    Configuring the Distributor and MS SQL Server Agent service startup options

  3. Snapshot Folder. 此處可保留預設路徑。初始化複製時需要一個快照。請確保快照目錄的位置所在的磁碟上有足夠的可用空間。可用空間的數量必須至少等於被複製資料庫的大小。
  4. Distribution Database. 輸入分發資料庫名稱。您可以保留預設名稱(分銷) 以及用於存放發行版資料庫檔案和日誌檔案的資料夾。

    Configuring snapshot folder and distribution database folders

  5. Publishers. 定義可存取分發器的 MS SQL Server 複寫發佈者。在主 MS SQL Server 執行個體(該執行個體託管將被複寫的來源資料庫)上,選取分發資料庫名稱旁的核取方塊。在此範例中,該執行個體為 MSSQL01MSSQLSERVER1,而分發資料庫的名稱為 分發.
  6. Wizard Actions. 選取 Configure distribution 在精靈的最後一步中設定發佈設定。在此範例中,我們不會產生供日後執行的腳本檔案。

    Selecting the Publisher and the distribution database

  7. Complete the Wizard. 查看發行版配置摘要,然後點擊 Finish 建立分銷商。

    Finishing configuring distribution

  8. 該 成功 若經銷商已成功建立並完成設定,則應顯示此狀態。

    Configuring the Distributor

如果您在設定 SQL Server Agent 自動啟動時遇到錯誤,請前往服務設定並檢查 SQL Server Agent 的啟動模式(請參閱本文前段關於如何設定 Agent 啟動的方式)。

您也可以在 SQL Server Management Studio 中開啟 SQL Server Agent 的屬性,檢查服務狀態與重新啟動選項。右鍵點擊 SQL Server Agent 在清單的末尾 物件總管 並點擊 Properties 以檢視或編輯代理人的屬性。

Checking MS SQL Server Agent startup options

設定發佈者

完成分發設定後,即可設定發佈者。發佈者應設定在存放待複製主資料庫的主伺服器(MSSQL01MSSQLSERVER1)上。請選取 Replication, 右鍵點擊 Local Publications 然後,在快顯選單中選取 New Publication.

Creating a new publication

該 新出版物精靈 開啟。

  1. Publication Database. 選取您要複製的資料庫 (AdventureWorks 2016 (在此情況下)。點擊 Next 請在精靈的每個步驟中點擊以繼續。

    Selecting a publication database

  2. Publication Type. 在此步驟中,您可以為資料庫選擇 MS SQL Server 的複製類型。我們來選擇"交易式發佈",這是一種廣泛使用的複製類型。
  3. Articles. 選取要發佈為文章的所需物件,例如資料表、程序、檢視、索引檢視及使用者自訂函數。如有需要,可選擇複製資料表中的自訂欄位,並設定文章屬性。在此範例中,已選取部分資料表。

    Selecting the transactional publication type and articles

  4. Filter Table Rows. 此範例中未新增任何篩選器(這是篩選器的預設設定)。如有需要,您可以新增篩選器。
  5. Snapshot Agent. 指定何時執行快照代理程式。讓我們將代理程式設定為立即執行。選取 Create a snapshot immediately and keep the snapshot available to initialize subscriptions.

    Filter options and Snapshot Agent options

  6. Agent Security. 選擇 Use the security settings from the Snapshot Agent. 點擊 Security Settings 按鈕以選擇代理程式將以哪個帳戶執行。

    在 快照代理程式安全性 在彈出的視窗中,輸入 mssql 您先前建立的 Windows 使用者。請選擇"連線至發佈者" By impersonating the process account. 按一下"確定"以儲存設定並返回精靈。

    Configuring agent security options

    定義所需的使用者後,您可以在 快照代理程式 以及 日誌讀取代理程式 章節。

    Agent security options are configured

  7. Wizard Actions. 請勾選上方的核取方塊,以便在精靈的最後一步建立發行版。
  8. Complete the Wizard. 檢查您的發佈設定,然後點擊 Finish 建立一份新的出版物。

    Selecting wizard actions and completing the wizard

在 建立出版物 在該視窗中,您可以監控建立新出版物的進度。請稍候片刻,若一切操作正確,您應會看到成功狀態。

Creating the publication

發行已建立完成,您可透過前往"物件總管"來查看該發行 Replication > Local Publications.

The publication is created

設定訂閱者

如您所知,MS SQL Server 的複製可分為拉取式複製或推送式複製。若您設定推送式複製,應將訂閱者設定為在主資料庫伺服器(此處為 MSSQL01)上執行代理程式。若您設定拉取式複製,則必須將訂閱者設定為在第二台機器(MSSQL02)上執行代理程式,也就是將建立資料庫複本的那台機器。

現在,讓我們配置推送複製,並在存放 master 資料庫的第一台 MS SQL Server(MSSQL01MSSQLSERVER1)上建立新的訂閱。

在物件總覽中,前往 Replication, 右鍵點擊 Local Subscriptions 然後,在快顯選單中選取 New Subscriptions.

Creating a new subscription

該 新訂閱精靈 開啟。

  1. Publication. 選取要為其建立新訂閱的發佈。在本範例中,發佈者的名稱是 MSSQL01MSSQLSERVER1,而發佈名稱(先前已建立)是 AdvWorks_Pub. 點擊 下一頁 請在精靈的每個步驟中點擊以繼續。
  2. Distribution Agent Location. 請選擇"推送訂閱"或"拉取訂閱"以設定複製類型。在本範例中,我們希望所有代理程式皆在來源伺服器端執行,因此選擇第一個選項以建立推送訂閱。此設定可讓您集中管理 MS SQL Server 複製。

    Selecting the publisher and distribution agent location

  3. Subscribers. 預設情況下,執行精靈的伺服器(此處為 MSSQL01MSSQLSERVER1)會顯示為"訂閱者",且尚未定義訂閱資料庫。現在讓我們新增一個"訂閱者",並選取位於第二台資料庫伺服器(MSSQL01MSSQLSERVER2)上的訂閱資料庫的位置。點擊 Add Subscriber 然後,在快顯選單中選取 Add SQL Server Subscriber.
    • 在彈出視窗中,輸入第二個 MSSQL Server 執行個體的憑證(本例中為 MSSQL01MSSQLSERVER2),然後按一下 Connect.

      Adding MS SQL Server subscriber

    • 選取將儲存資料庫副本的第二台伺服器的核取方塊(MSSQL02MSSQLSERVER2),然後在 訂閱資料庫 從下拉式選單中,選取一個新資料庫,或從備份還原的現有資料庫,作為資料庫副本使用。

      在我們的範例中,該 AdventureWorks 2016 是透過還原主伺服器(來源)而在第二台伺服器上建立的 AdventureWorks 2016 從備份中還原資料庫以啟動複製。複製是透過僅複製新資料來啟動,而非在啟動複製程序後複製整個資料庫。因此, AdventureWorks 2016 在當前的範例中,已選取為訂閱資料庫。

      Selecting a subscriber and a subscription database

  4. Distribution Agent Security. 點擊三個點的按鈕(…),並為分發代理選取使用者及其他安全性選項。

    在 分銷代理商的安全性 在彈出的視窗中,將"分發代理程式"設定為在 MSSQL01 在以下位置主機 mssql 使用者帳戶。輸入該帳戶的 mssql Windows 使用者。請選擇 Connect to the Distributor by impersonating the process account 並選擇 Connect to the Subscriber by impersonating the process account. 點擊 好的 以儲存設定。

    Distribution Agent security settings

    現在您的訂閱設定已完成。

    Distribution Agent security settings are configured

  5. Synchronization Schedule. 選取代理程式的位置,它位於分發器上以 持續運行 針對當前的訂閱者。
  6. Initialize Subscriptions. 選取 Initialize 勾選方塊,並在下拉式選單中選擇 Immediately 以設定何時初始化訂閱。您也可以選擇 記憶體最佳化 如有需要,請選擇此選項。

    Synchronization schedule options and initialize subscription options

  7. Wizard Actions. 勾選上方核取方塊,即可在精靈結束時建立訂閱。
  8. Complete the Wizard. 您可以查看您的訂閱設定,並點擊 Finish 以建立訂閱。

    Selecting subscription wizard actions and completing the wizard

  9. 請等待訂閱建立完成。如果您看到 成功 狀態為此,表示訂閱已成功建立。

    The progress of creating subscriptions and the action status

  10. 在 SQL Server 中設定複寫功能後,物件總覽中會顯示三個工作,您可以透過前往 SQL Server Agent > Jobs.

    MS SQL Server Agent jobs are created for MS SQL Server replication

完成複製設定

完成對分發器、發佈者和訂閱者的設定後,即可檢查 MS SQL Server 的複製狀態。

  1. 在第一台伺服器(MSSQL01MSSQLSERVER1)上,啟動複寫監控器以查看 MS SQL Server 的複寫狀態。在 SQL Server Management Studio 中,選取您的 MS SQL Server 執行個體(MSSQLSERVER1),前往 Replication, 右鍵點擊 Local Publications 然後,在快顯功能表中選取 Launch Replication Monitor.

    Launching the Replication Monitor to check MS SQL Server replication status

  2. 有一種 日誌讀取代理程式 在我們的案例中發生錯誤。若要查看錯誤詳細資訊,請在左側窗格中選取來源資料庫(發佈者),然後選取 Agents 在右側窗格中點選該標籤,然後雙擊錯誤名稱。

    The error status of the Log Reader Agent

  3. 在彈出的視窗中,您可以查看代理程式記錄和錯誤訊息。錯誤訊息如下:
    • 此程序無法在 MSSQL01MSSQLSERVER1 上執行 sp_replcmds。來源:MSSQL_REPL。錯誤編號:MSSQL_REPL20011。
    • 無法以資料庫主體身分執行,因為主體"dbo"不存在、無法假冒此類主體,或您沒有權限。(來源:MSSQLServer,錯誤編號:15517)。

    Viewing the Log Reader Agent history to fix errors

    第二則錯誤訊息顯示缺少某種權限。讓我們來修正這個錯誤。

  4. 在 MS SQL Management Studio 中建立一個新查詢,並執行此查詢。在主視窗中,按一下 New Query 按鈕。
  5. 在主視窗的 SQL 查詢區塊中,輸入以下查詢:

    USE AdventureWorks2016

    GO

    EXEC sp_changedbowner 'sa'

    GO

    點擊 Execute 按鈕。

    Viewing Snapshot Agent Status to run database replication in SQL Server

    指令已成功執行。

  6. 接下來,請前往 MSSQL01MSSQLSERVER1 > Replication > Local Publications > [AdventureWorks2016]: AdvWorks_Pub. 右鍵點擊發行名稱,然後在快顯功能表中選取 檢視快照代理程式狀態。您可以點擊 Action > Refresh 以更新狀態並 Reinitialize All Subscriptions 將快照套用至每個訂閱者。

    現在一切都已解決,不再顯示任何錯誤,MS SQL Server 的複製功能應該可以正常運作。

    The running status of the subscription

檢查複製機制如何運作

讓我們來看看 MS SQL Server 複寫的實際運作。檢視某個資料表的內容 AdventureWorks 2016 儲存於第一台 MS SQL 伺服器上的資料庫 (MSSQL01MSQLSERVER1)。在我們的範例中,我們將選取來自 人員.地址類型 表格。要執行此操作,請執行以下查詢:

USE AdventureWorks2016;

GO

SELECT *

FROM Person.AddressType

;

執行此查詢的結果顯示於下圖截圖中:

Viewing the content of the table of the master database

在第二台伺服器上執行類似的查詢,以顯示所有 人員.地址類型 的 AdventureWorks 2016 資料庫儲存於 MSSQL02MSSQLSERVER2 上。

USE AdventureWorks2016r;

GO

SELECT *

FROM Person.AddressType

;

若將上方的截圖與下方的截圖進行比較, 人員.地址類型 在兩個資料庫中完全相同(一個是位於第一台伺服器的來源資料庫,另一個是位於第二台伺服器的目標資料庫,即該資料庫的複本)。

Viewing the content of the table of the second database that will be used as a database replica

我們來刪除資料表中的一行 人員地址類型 來自的表格 AdventureWorks 2016 資料庫(來源)位於第一台伺服器(MSSQL01MSSQLSERVER1)上。執行查詢以刪除一筆包含 "帳單" 在名稱後面,並顯示該表格的內容:

DELETE FROM Person.AddressType WHERE Name='Billing';

SELECT * FROM Person.AddressType;

Deleting the line in the table of the master database

如您所見,第一行包含 地址類型識別碼 1 和名稱 "帳單" 已從 人員.地址類型 表格在 AdventureWorks 2016 資料庫位於 MSSQL01 機器。

交易複寫正在執行中。讓我們檢查 人員.地址類型 表格在 AdventureWorks 2016 資料庫位於 MSSQL02 機器。再次執行與上述類似的查詢,以查看資料表的內容:

USE AdventureWorks2016r;

GO

SELECT *

FROM Person.AddressType

;

由於複製的關係,第一行也從 人員.地址類型 次要資料庫中用作資料庫副本的資料表(AdventureWorks 2016)。您可以在下方的螢幕截圖中看到結果。

The first line is deleted from the table in the database replica

SQL Server 中的資料庫複寫功能運作正常。

結論

MS SQL Server 複製共有四種類型:快照複製、交易式複製、點對點複製以及合併複製。由於交易式複製被廣泛使用,因此我們在本文中配置了此類型的 MS SQL Server 複製。 要使資料庫複製正常運作,必須設定分發器 (Distributor)、發佈者 (Publisher) 和訂閱者 (Subscriber)。訂閱者可設定在來源伺服器(推送式複製)或目標伺服器(拉取式複製)上。

不過,您應考慮同時使用複製與 MS SQL 資料庫的備份 以提高成功的機會 資料庫資料還原.

試試看 NAKIVO Backup & Replication

試試看 NAKIVO Backup & Replication

立即申請免費試用,全面體驗本解決方案的所有資料保護特點。15 天免費試用。無功能或容量限制。無需提供信用卡資訊。

免費試用

People also read

Picture
如何從 USB 隨身碟執行 ESXi
Picture
免費版 ESXi：限制與限制事項
Picture
Nutanix 簡介：是什麼、為什麼以及如何運作