如何設定 MS SQL Server 複寫

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

NAKIVO for Windows 備份

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

探索解決方案

什麼是 SQL Server 複製？

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

MS SQL Server 複製所使用的術語

在深入探討如何配置和設定 MS SQL Server 複寫之前,讓我們先簡要地瀏覽一下主要術語和複寫模型。
文章 這些是待複製的基本單位,例如資料表、儲存程序、函式及檢視。可透過篩選器對文章進行垂直或水平縮放。針對同一物件,可建立多篇文章。
一份出版物 是一組邏輯上的文章集合。這是資料庫中指定用於複製的最後一組實體。
一個濾鏡 是一組針對資料項目的條件。MS SQL Server 複製功能允許您使用篩選器並選取自訂實體進行複製,藉此可減少流量、冗餘,以及儲存於資料庫複本中的資料量。例如,您可以透過篩選器僅選取最重要的資料表和欄位,然後僅複製這些資料。
代理人 這些是 MS SQL Server 的元件,可作為關聯式資料庫管理系統的背景服務,並用於排程自動執行各項工作,例如 MS SQL 資料庫的備份與複製。代理程式共有五種類型:快照代理程式、日誌讀取代理程式、分發代理程式、合併代理程式,以及佇列讀取代理程式。
元資料 是用來描述資料庫實體的資料。有各種內建的元資料函式,可讓您擷取有關 MS SQL Server 執行個體、資料庫執行個體及資料庫實體的資訊。

SQL 資料庫複製中的角色

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

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

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

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

  訂閱 這是一項要求提供出版物副本的請求,該副本必須送達訂閱者手中。訂閱是用來定義必須接收的出版物資料,以及接收這些資料的地點與時間。訂閱分為兩種類型:

  • 推播訂閱: 已變更的資料會由分發器強制傳輸至訂閱者資料庫。訂閱者無需發出任何請求。
  • 拉取訂閱: 訂閱者請求取得發佈者端所做的資料變更。代理程式在訂閱者端執行。

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

  

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

MS SQL Server 複製類型

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

快照複製

快照複製 用於精確複製資料,使其與建立資料庫快照當下的狀態完全一致。此類複製方式適用於:資料變動頻率不高、資料庫副本比主資料庫舊一些並非關鍵問題,或是短時間內發生大量變更的情況。 快照複製不使用變更追蹤功能。
例如,當匯率或價格清單每天更新一次,且必須從主伺服器分發至分支機構的伺服器時,即可使用快照複製。

交易複製

交易複製 這是一種週期性的自動化複製機制,資料會從主資料庫即時(或近即時)地分發至資料庫副本。 交易式複製比快照式複製更為複雜。所有執行的交易以及資料庫的最終狀態都會被複製,這使得在複本上監控完整的交易歷史成為可能。
在交易式複製流程開始時,會將一個快照套用至訂閱者,隨後當資料發生變更時,資料便會從主資料庫持續傳輸至資料庫複本。交易式複製廣泛用作單向複製。

事務性複製的使用情境:

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

點對點複製

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

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

合併複製

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

此類資料庫複製是所有 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 複製功能的設定方式。
主持人 1

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

主持人 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)。

其他設定:

• 混合驗證模式(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 伺服器進行資料庫複寫的準備工作。

準備進行 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 執行個體名稱。

   

同樣地,您可以在第二台伺服器(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)。
要執行此操作,請在”物件總覽”中按一下 連線 > 資料庫引擎. 在本教學中,我們將透過 SQL Server Management Studio 來設定 MS SQL 伺服器,從 MSSQL01 連線至 MSSQLSERVER1,並從 MSSQL02 連線至 MSSQLSERVER2。

啟動代理程式

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

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

1. 新聞 Win+R, run cmd, 並執行該 services.msc 指令.
2. 開啟 SQL Server Agent 服務的屬性,並設定 S請輸入”新創”類型至 自動.

   

為 MS SQL Server 設定使用者

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

1. 前往 物件總覽 並開啟 安全性 > 登入.
2. 右鍵點擊 登入 並選擇 新帳號登入. 選擇 Windows 驗證.
3. 請輸入登入名稱 mssql 在 概述 章節。
4. 點擊 搜尋, 然後按下 檢查姓名 請確認,然後點擊 確定 點擊兩次以儲存設定。

   

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

   

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

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

    

從備份檔案匯入資料庫

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

1. 複製該 AdventureWorks2016.bak 將資料庫備份檔案儲存至您的 MSSQL 備份目錄中。以本例而言,第一台伺服器上的此目錄為 D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLBackup
2. 匯入範例資料庫。在第一台電腦上的 SQL Server Management Studio 中,請前往 MSSQL01MSSQLSERVER1, 右鍵點擊 資料庫, 並選擇 還原資料庫 在快顯選單中。

   

3. 在 還原資料庫 在視窗中,選取所需的參數:
   • 來源: 裝置.
   • 點擊 三個點 以瀏覽資料庫備份檔案。
     • 在 選擇備份裝置 視窗中,請選擇備份媒體類型: 檔案.
     • 點擊 新增.
   • 請選擇所需的 .bak 檔案 – D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQL備份AdventureWorks2016.bak
   • 點擊 確定,然後按下 確定 再次。
4. 該 AdventureWorks2016 資料庫已成功還原。

   

您可以從第二台伺服器上的備份匯入資料庫,該伺服器將運行資料庫複本。此方法可減少網路流量,因為複製過程會從備份建立後產生的變更開始進行,而無需將整個資料庫資料複製到一個空資料庫中。
從第二台伺服器上的備份還原資料庫,並將資料庫重新命名為 AdventureWorks2016r,其中”r”代表”複本”。
最後,我們得到:

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

1. 在 MSSQL01 機器,前往 MSSQL01MSSQLSERVER1 > 安全性 > 登入, 選擇 MSSQL01mssql. 右鍵點擊(或雙擊) mssql 使用者與選取 屬性.
2. 在 伺服器角色, 勾選位於 dbcreator 角色.

   

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

   

5. 點擊 確定 以儲存設定。

在 MSSQL02 機器上進行相同的設定。接著,您即可設定資料庫複寫所需的 MS SQL Server 元件。

為您的環境找到合適的方案

探索 NAKIVO 各款靈活的版本與授權模式,這些方案專為提供 Enterprise 級資料保護而設計,且費用符合您的預算。

查看價格

設定資料庫複寫

在圖形化模式下進行複製設定是最方便的方法。接下來的設定將在 SQL Server Management Studio 中進行。 本範例將說明”交易型資料庫複製”,因為這是最常用的 MS SQL Server 複製類型之一。
下圖截圖顯示了在 SQL Server Management Studio 中,主資料庫伺服器(MSSQL01MSSQLSERVER1)的檢視畫面,以及第二台伺服器(MSSQL02MSSQLSERVER2)的檢視畫面。

設定分發

分發功能可應用於多個發佈者和訂閱者。在此範例中,分發功能是配置在儲存來源資料庫的主伺服器上。在主伺服器(MSSQL01MSSQLSERVER1)上,右鍵點擊 複製 然後,在快顯選單中,選擇 設定分發.

該 “配置分發精靈” 開啟。

1. 經銷商. 在此範例中,請選取目前在主伺服器(MSSQL01MSSQLSERVER1)上執行的資料庫執行個體,使其擔任分發器。按一下 下一個 每次都要繼續進行精靈中的下一步驟。
2. SQL Server 代理程式啟動. 若您尚未依照上述說明將 MS SQL Server Agent 設定為自動啟動,系統將顯示以下訊息。請選擇 是的,請將 SQL Server Agent 服務設定為自動啟動.

   

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

   

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

   

7. 完成精靈向導. 檢查”分發配置摘要”,然後點擊 完成 以建立分銷商。

   

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

   

若您發現設定 SQL Server Agent 自動啟動時發生錯誤,請前往服務設定,並檢查 SQL Server Agent 的啟動模式(請參閱本部落格文章上方的”如何設定 Agent 啟動”說明)。
您也可以在 SQL Server Management Studio 中開啟 SQL Server Agent 的屬性,並檢查服務狀態與重新啟動選項。右鍵點擊 SQL Server 代理程式 在清單的末尾,位於 物件總覽 並點擊 屬性 以檢視或編輯代理人的屬性。

設定發佈者

完成”分發”設定後,即可設定”發佈者”。”發佈者”應設定在儲存待複製主資料庫的主伺服器(MSSQL01MSSQLSERVER1)上。請選擇 複製, 右鍵點擊 本地出版物 然後,在快顯選單中,選擇 新出版物.

該 “新出版物精靈” 開啟。

1. 出版物資料庫. 選取您要進行複製的資料庫(AdventureWorks2016 (在此情況下)。點擊 下一頁 請在精靈的每個步驟中點擊以繼續。

   

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

   

4. 篩選表格中的資料列. 此範例中未新增任何篩選器(這是篩選器的預設設定)。如有需要,您可以新增篩選器。
5. 快照代理程式. 指定快照代理程式的執行時間。讓我們將代理程式設定為立即執行。請選取 立即建立快照,並確保該快照可供初始化訂閱使用.

   

6. 代理安全. 選擇 使用快照代理程式中的安全性設定. 點擊 安全性設定 按鈕,以選擇代理程式將以哪個帳戶執行。

   在 快照代理程式安全性 在彈出的視窗中,輸入該的 mssql 您先前建立的 Windows 使用者。請選擇”連線至發佈者” 透過冒充該程序帳戶. 按一下”確定”以儲存設定並返回精靈。

   

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

   

7. 精靈動作. 請勾選上方的核取方塊,以便在精靈的最後一步中建立發行。
8. 完成精靈向導. 檢查您的發佈設定,然後點擊 完成 建立一份新的出版物。

   

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

發行已建立完成,您可透過前往以下位置,在”物件總覽”中查看該發行: 複製 > 本地出版物.

訂閱者的設定

如您所知,MS SQL Server 的複製可分為”拉取式”或”推送式”複製。若您設定推送式複製,應將訂閱者設定為在主資料庫伺服器(此處為 MSSQL01)上執行代理程式;若您設定拉取式複製,則必須將訂閱者設定為在第二台機器(MSSQL02)上執行代理程式,也就是將建立資料庫複本的那台機器。
現在,讓我們在存放 master 資料庫的第一台 MS SQL Server(MSSQL01MSSQLSERVER1)上設定推送式複製並建立新的訂閱。
在”物件總覽”中,前往 複製, 右鍵點擊 本地訂閱服務 然後,在快顯選單中,選擇 新訂閱.

該 新訂閱精靈 開啟。

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

   

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

     

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

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

     

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

   在 分銷代理商的安全性 在彈出的視窗中,將”分發代理程式”設定為在 MSSQL01 在以下位置主機 mssql 使用者帳戶。請輸入該帳戶的 mssql Windows 使用者。請選擇 透過假冒程序帳戶來連線至分發器 並選擇 透過假冒程序帳戶來連線至訂閱者. 點擊 好 以儲存設定。

   

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

   

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

   

7. 精靈動作. 勾選上方的核取方塊,即可在精靈結束時建立訂閱。
8. 完成精靈向導. 您可以查看您的訂閱設定,並點擊 完成 以建立訂閱。

   

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

   

10. 在 SQL Server 中設定複寫後,物件總覽中會顯示三個工作,您可以透過前往以下位置來查看它們: SQL Server 代理程式 > 工作.

    

完成複製設定

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

1. 在第一台伺服器(MSSQL01MSSQLSERVER1)上,啟動複寫監控程式以查看 MS SQL Server 的複寫狀態。在 SQL Server Management Studio 中,選取您的 MS SQL Server 執行個體(MSSQLSERVER1),然後前往 複製, 右鍵點擊 本地出版物 然後,在快顯選單中,選擇 啟動複製監控程式.

   

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

   

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

   

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

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

   USE AdventureWorks2016

   GO

   EXEC sp_changedbowner 'sa'

   GO

   點擊 執行 按鈕。

   

   指令已成功執行。

6. 接下來,請前往 MSSQL01MSSQLSERVER1 > 複製 > 本地出版物 > [AdventureWorks2016]: AdvWorks_Pub. 右鍵點擊發行物名稱,然後在快顯選單中選取 檢視快照代理程式狀態. 您可以點擊 動作 > 重新整理 以更新狀態並 重新初始化所有訂閱 將快照套用至每個訂閱者。

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

   

檢查複製機制如何運作

讓我們來看看 MS SQL Server 複製功能的實際運作。檢視某個資料表的內容: AdventureWorks2016 儲存於第一台 MS SQL 伺服器上的資料庫(MSSQL01MSQLSERVER1). 在本範例中,我們將選取來自 Person.AddressType 資料表。要執行此操作,請執行以下查詢:
USE AdventureWorks2016;
GO
SELECT *
FROM Person.AddressType
;
執行該查詢的結果如以下螢幕截圖所示:

在第二台伺服器上執行類似的查詢,以顯示所有 Person.AddressType 的 AdventureWorks2016r 資料庫儲存於 MSSQL02MSSQLSERVER2。
USE AdventureWorks2016r;
GO
SELECT *
FROM Person.AddressType
;
若將上方的截圖與下方的截圖進行比較,該 Person.AddressType 在兩個資料庫中完全相同(一個是位於第一台伺服器的來源資料庫,另一個是位於第二台伺服器的目標資料庫,即該來源資料庫的複本)。

讓我們在 人員地址類型 來自該的表格 AdventureWorks2016 位於第一台伺服器(MSSQL01MSSQLSERVER1)上的資料庫(來源)。執行此查詢以刪除一筆包含 “帳單” 在名稱後面,並顯示該表格的內容:
DELETE FROM Person.AddressType WHERE Name='Billing';
SELECT * FROM Person.AddressType;

如您所見,第一行中的 地址類型識別碼 1 和名稱 “帳單” 已從 Person.AddressType 表格位於 AdventureWorks2016 位於該處的資料庫 MSSQL01 機器。
交易複製正在執行中。讓我們檢查 Person.AddressType 表格位於 AdventureWorks2016r 位於該處的資料庫 MSSQL02 機器。再次執行與上述類似的查詢,以查看該資料表的內容:
USE AdventureWorks2016r;
GO
SELECT *
FROM Person.AddressType
;
由於複製的關係,第一行也從 Person.AddressType 位於次要資料庫中、用作資料庫複本的資料表(AdventureWorks2016r)。您可以在下方的螢幕截圖中看到結果。

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

結論

MS SQL Server 複製共有四種類型——快照複製、交易式複製、點對點複製以及合併複製。由於交易式複製被廣泛使用,因此我們在本文中配置了此種 MS SQL Server 複製類型。 要讓資料庫複製正常運作,必須分別設定”分發器”、”發佈者”和”訂閱者”。訂閱者可設定在來源伺服器(推送式複製)或目標伺服器(拉取式複製)上。
不過,您應考慮同時使用複製與 MS SQL 資料庫的備份 以提高成功的機會 資料庫資料還原.

試試看 NAKIVO Backup & Replication

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

免費試用