CaryHsu - 學無止盡: Merge Replication

顯示具有 Merge Replication 標籤的文章。顯示所有文章

2023年12月27日星期三

如何設定 SQL Server Merge Replication 透過 HTTPS/SSL 進行遠端同步

SQL SERVER 合併式複寫，可以讓二台 SQL Server 進行資料的同步，此技術非常的適合許多跨地區的資料庫進行，通常許多公司會透過 VPN 的方式進行綁定不同的網路(路由)，所以其實基本上還是有互通的，所以當資料庫同步時，都是通過預設的 1433 Port 進行同步，但如果彼此之間因為網路的不同時，如果要進行資料同步時，由於異質網路的關於，所以就需要在防火牆開啟 1433 Port，這對許多公司的政策都是不允許的，因為會有安全性的問題。

本篇介紹的，就是要解決這個問題，其實就是散發的資料/數據，透過網頁 HTTPS/SSL 進行同步的動作，本篇測試時雖然有許多參考資料，但其實在自已進行測試時，你會發現有許多的問題，所以我才會特別的整理此篇，希望提供給大家參考。

環境說明：

SQL Server 2019 為發行與散發
SQL Server 2017 為訂閱端

在選擇 IIS 進行同步的部份，基本上可以不用與 SQL Server 在同一台上，但此次我是與 SQL Server 設定在同一台上進行。

1. 預用 Web Server (IIS) 的功能，在啟用時，除了預設的項目外，也需要啟用下列的項目。

Web Server (IIS) -> Web Server -> Security -> Basic Authentication
Web Server (IIS) -> Web Server -> Application Development -> .Net Extensibility 3.5
Web Server (IIS) -> Web Server -> Application Development -> .Net Extensibility 4.7
Web Server (IIS) -> Web Server -> Application Development -> ISAPI Extensions
Web Server (IIS) -> Web Server -> Application Development -> ISAPI Filters

2. 設定 Web 複寫目錄

在設定 Web Synchronization 的部份，其實也是可以透過 SSMS 的工具來進行設定，但在設定後發現仍有問題，而且其中有一些限制，所以我還是手把手的逐一設定，至少那一個部份有錯，也比較好找出錯誤的部份，下列是一些例圖，所以大家在參考一下就好，當然要用這個進行也是可以的。

2-1 IIS 啟用後，請在下列的位置下新增一個目錄，名稱為 "SQLReplication"

C:\inetpub\wwwroot\

請盡可能的將資料夾建立在此，要不然後面要設定相關的安全性，問題有點多會不好排除問題。

2-2 我的 SQL Server 版本為 2019，所以請至下列的目錄，將 "replisapi.dll" 的檔案複制到 2-1 中的 "SQLReplication" 中。

C:\Program Files\Microsoft SQL Server\150\COM\

由於 SQL Server 2019 為 64位元，所以此元件也是 64 位元的，這 "非常" "非常" "非常" 的重要，千萬不要搞錯了。

2-3 請透過系統管理者的權限開啟一個 Dos Command 視窗後，然後輸入下列的指令註冊此元件。

regsvr32 C:\inetpub\wwwroot\SQLReplication\replisapi.dll

3. 設定 IIS 站台目錄

3-1 請先確認 IIS 的預設站台可以正常瀏覽。

https://cary-sql2019/iisstart.htm

3-2 新增一個目錄針對此 SQLReplication

3-3 新增時，請點選 "Test Settings" 確認沒有任何的錯誤。

4. 設定 IIS 站台目錄(SQLRrplication) 啟用 HTTPS/SSL 的功能，這部份可以透過申請的 SSL 憑證或憑證伺服器進行，但這部份我透過自我簽署的憑證進行。

4-1 請至主機 -> Server Certificates -> Create Self-Signed Certificates

4-2 請至預設站台 (Default Web Site) -> Bindings -> Add -> https

4-3 設定 SSL Settings，勾選 Require SSL 的設定，當使用者存取網頁都必須透過 HTTPS/SSL 進行存取。

5. 設定站台目錄(SQLRrplication)的驗證項目，為了安全性，所以必須啟用 Basic Authentication，並停用 Anonymous Authentication。

6. 設定 replisapi.dll 模組對應至此站台目錄(SQLReplication)中。

7. 測試是否可以正常的執行模組 (replisapi.dll)

https://cary-sql2019/SQLReplication/replisapi.dll

https://cary-sql2019/SQLReplication/replisapi.dll?diag

請確認 "Class Initialization test" 的狀態 (Status) 都是 SUCCESS 而且沒有任何的錯誤，要不然後續在進行同步時，會有問題。

8. 由於是自我簽署的憑證，所以在訂閱端透過瀏覽器進行訪問時，會出現如下列的錯誤訊息，所以一定要把憑證匯出從 IIS 端後，再匯入至用戶端(訂閱端)，確認此錯誤訊息不會出現，要不然後續進行資料同步時，就會發生問題。

8-1 憑證匯出

請至 IIS -> Server Certificates -> Export，建議設定密碼進行保護。

8-2 憑證匯入

請將匯出的憑證複制至用戶端(訂閱端)，然後選擇 "Install PFX"

匯入的位置，請手動選擇，將憑證匯入至 "Trusted Root Certification Authorities" 中即可，我選擇第一項時，其實無效，透過瀏覽器確認時，仍有錯誤，所以請選擇手動匯入。

另外在匯入時，選擇 Current User or Local Machine 都可以，我這邊是選擇匯入至 Current User.

完成上述的設定後，基本上就完成大半部，後續的複寫的部份，簡單的透過介面進行 SQL Replication 的建立就可以了。

9. 建立合併式複寫 (Merge Replication) 的發行與散發的部份，這部份沒有特別需要注意的，所以我就不特別截圖說明。

10. 針對剛剛建立好的合併式複寫設定 Web Synchronization 的功能，請將網址填入至下列的欄位中。

11. 建立訂閱端設定，基本上也透過介面進行設定，我就將特別的幾個畫面整理如下。

11-1 請選擇 pull subscriptions 的方式

11-2 請勾選 Use Web Synchronization 的方式

11-3 設定透過 Web Synchronization 時，相關的設定

12. 設定完成，可以發現在同步後，可以透過訂閱端上的 SQL Agent -> Job History 來進行確認，訂閱端是如何進行同步，如下列的資訊。

Date 12/26/2023 5:44:38 PM

Log Job History (CARY-SQL2019-db-replication-nodomain-replication-WIN-VB0R8UQVUA2-db-replication- 0)

Step ID 1

Server WIN-VB0R8UQVUA2

Job Name CARY-SQL2019-db-replication-nodomain-replication-WIN-VB0R8UQVUA2-db-replication- 0

Step Name Run agent.

Duration 00:00:01

Retries Attempted 0

Message

-XSERVER WIN-VB0R8UQVUA2

-XCMDLINE 0

-XCancelEventHandle 0000000000001ADC

-XParentProcessHandle 0000000000001CE4

2023-12-26 09:44:38.896 Connecting to Subscriber 'WIN-VB0R8UQVUA2'

2023-12-26 09:44:38.943 The upload message to be sent to Publisher 'CARY-SQL2019' is being generated

2023-12-26 09:44:38.943 The merge process is using Exchange ID '947521E2-682C-429A-B87A-8CD68F331656' for this web synchronization session.

2023-12-26 09:44:38.990 Uploading data changes to the Publisher

2023-12-26 09:44:39.068 No data needed to be merged.

2023-12-26 09:44:39.068 Request message generated, now making it ready for upload.

2023-12-26 09:44:39.068 Upload request size is 2058 bytes.

2023-12-26 09:44:39.397 Uploaded a total of 1 chunks.

2023-12-26 09:44:39.397 The request message was sent to 'https://cary-sql2019/SQLReplication/replisapi.dll'

2023-12-26 09:44:39.397 Downloaded a total of 3 chunks.

2023-12-26 09:44:39.397 The response message was received from 'https://cary-sql2019/SQLReplication/replisapi.dll' and is being processed.

2023-12-26 09:44:39.397 Connecting to Subscriber 'WIN-VB0R8UQVUA2'

2023-12-26 09:44:39.412 The changes contained in the message downloaded from Publisher 'CARY-SQL2019' will be applied after having collected and displayed the upload statistics.

2023-12-26 09:44:39.412 Downloading data changes to the Subscriber

2023-12-26 09:44:39.694 [100%] Downloaded 1 change(s) in 'test-db1' (1 insert): 1 total

2023-12-26 09:44:39.709 [100%] Web synchronization progress: 99% complete.

=============================================================

Article Download Statistics:

============================

test-db1:

Inserts: 1

Relative Cost: 100.00%

Session Statistics:

============================

Download Inserts: 1

Change Delivery Time: 0 sec

Schema Change and Bulk Insert Time: 0 sec

Delivery Rate: 0.00 rows/sec

Total Session Duration: 0 sec

=============================================================

2023-12-26 09:44:39.709 Connecting to Subscriber 'WIN-VB0R8UQVUA2'

2023-12-26 09:44:39.709 The upload message to be sent to Publisher 'CARY-SQL2019' is being generated

2023-12-26 09:44:39.709 The merge process is using Exchange ID '63480F83-5715-4207-8BF7-9E94D78F0A86' for this web synchronization session.

2023-12-26 09:44:39.756 Uploading data changes to the Publisher

2023-12-26 09:44:39.756 [100%] Request message generated, now making it ready for upload.

2023-12-26 09:44:39.756 [100%] Upload request size is 2073 bytes.

2023-12-26 09:44:39.803 [100%] Uploaded a total of 1 chunks.

2023-12-26 09:44:39.803 [100%] The request message was sent to 'https://cary-sql2019/SQLReplication/replisapi.dll'

2023-12-26 09:44:39.803 [100%] Downloaded a total of 3 chunks.

2023-12-26 09:44:39.803 [100%] The response message was received from 'https://cary-sql2019/SQLReplication/replisapi.dll' and is being processed.

2023-12-26 09:44:39.803 Connecting to Subscriber 'WIN-VB0R8UQVUA2'

2023-12-26 09:44:39.819 The changes contained in the message downloaded from Publisher 'CARY-SQL2019' will be applied after having collected and displayed the upload statistics.

2023-12-26 09:44:39.819 [100%] Downloading data changes to the Subscriber

2023-12-26 09:44:39.819 [100%] Web synchronization progress: 99% complete.

2023-12-26 09:44:39.834 [100%] Merge completed after processing 1 data change(s) (1 insert(s), 0 update(s), 0 delete(s), 0 conflict(s)).

=============================================================

Article Download Statistics:

============================

test-db1:

Inserts: 1

Relative Cost: 100.00%

Session Statistics:

============================

Download Inserts: 1

Change Delivery Time: 0 sec

Schema Change and Bulk Insert Time: 0 sec

Delivery Rate: 0.00 rows/sec

Total Session Duration: 0 sec

=============================================================

2022年4月6日星期三

SQL Server - 如何設置單向式合併式複寫

SQL Server - Replication 複寫是一個在很早的版本就已存在的技術，由於其特性，所以其實有許多的使用者使用，基本上四種常見的複寫上有何差異我就不在特別的介紹，因為已有許多的文章進行說明與比較，而在上一篇文章中，我介紹如何在 AWS RDS for SQL Server 與地端的 SQL Server 進行合併式複寫，這一篇我就再來進一步的介紹如果強制設定單向式的合併式複寫。

優點：

一般複寫在維護上，最怕的就是遇到衝突或是複寫過慢等問題，由於在交易式複寫 (Transactional replication) 與點對點式複寫 (Peer-to-peer replication) 的部份，一遇到衝突，絕大部份需要透過重新建立的方式進行，所以當業務上的需求只需要將資料複寫至遠端時，設定設定成單向式複寫，即可保證目的端的資料不會被變動到。

前一篇設定參考：

如何透過Merge Replication 複寫機制，同步 EC2 與 AWS RDS for SQL Server 的資料庫https://caryhsu.blogspot.com/2022/03/merge-replication-ec2-aws-rds-for-sql.html

詳細的設定方式，可能參考上述文章的介紹，但在加入表格 (Articles) 時，可以再進一步的設定即可，所以我就直接說重點的部份。

1. 先選擇好你要複寫的表格後，然後再點選右上角的 [Article Properties] -> [Properties for All Table Articles].

2. 開啟設定後，您會看到 [Synchronization direction] 的部份，預設為 Bidrectional ，這時可以開啟此設定，你會看到有三個選項。

Bidirectional: 雙向式復寫，這也是預設值。
Download to Subscriber, prohibit Subscriber changes: 只允許發行端可進行修改，而且當訂閱端進行資料變更時，會發生錯誤訊息，並且不允許進行變更，另外也不會將訂閱端的資料回寫回發行端。
Download to Subscriber, allow Subscriber changes: 允許發行端與訂閱端皆可進行修改，但並不會將訂閱端的資料回寫回發行端，而且發行端的數據，也會覆蓋到訂閱端。

此處我們建議可以設定第2項與第3的部份，二者的差異在於，當如果設定為 [Download to Subscriber, prohibit Subscriber changes] 時，如果你在訂閱端進行資料的修改時，就會發生下列的錯誤訊息。

錯誤訊息：
No row was updated.

The data in row 1 was not committed.
Error Source: .Net SqlClient Data Provider.
Error Message: Table '[Person].[Address]' into which you are trying to insert, update, or delete data has been marked as read-only, Only the merge process can perform these operations.

另外如果在已設定好的機器上，需要進行上述的修改時，也是一樣可以的，只是最後在修改後，需要再進行重建快照的動作後即可。

2022年3月8日星期二

如何透過Merge Replication 複寫機制，同步 EC2 與 AWS RDS for SQL Server 的資料庫

近日接到客戶需求，想要進行地端的DB與 AWS RDS for SQL Server 進行複寫，而AWS 的官方目前有交易式複寫(Transaction Replication)的文件，但針對合併式複寫(Merge Replication)卻沒有，我在實際進行架設後，看似沒有問題的背後，其實包含了許多不同的問題，所以我特別的整理此篇文件，希望可以幫助到大家。

建立方式：

1. 請先建立RDS for SQL Server，並請在同一個網段中建立一台EC2，你可以透過 Windows with SQL Server 的 AMI 進行建立，建立完成後，確認EC2可以透過 SSMS 連接到 RDS。

2. 請透過 RDS DB instance endpoint 連線到 RDS 後，先確認 RDS 的主機名稱。

a. 請輸入下列的指令。

Select @@servername;

b. 使用nslookup 的指令反查 RDS DB instance endpoint 對應的 IP為何。

c. 請將輸出結果加一筆記錄到您的DNS Server中，或是在EC2中的 hosts 記錄中，加入一筆記錄，也是可以的。

PS: hosts 的檔案路徑: C:\Windows\System32\drivers\etc\hosts

3. 請確認在EC2上，可以透過電腦名稱連線到 RDS 端。

4. 請先在來源端，也是 EC2 上，建立複寫。

選擇需要進行複寫的資料庫

選擇合併式複寫 (Merge Replication)

選擇需要同步的表格(Table)有那些

此步驟主要是說明由於合併式複寫(Merge Replication) 會在有同步的表格最後加入一個識別欄位，所以此種複寫方式會變動到表格的結構，這點在請注意。

可以在此步驟中可以加入需要過濾的條件，但此步驟我們就先不加入。

在此處主要是設定來源端 EC2 上如何進行權限的設定，你可以透過 Windows 的帳號或是 SQL Server 的帳號都可以，但需要在 SQL Server 中有相關的權限。

建立完成後，即可看到已建立好一個發行集。

5. 設定完成複寫的發行集後，請在來源端的目錄路徑，設定此目錄可以允許讀寫。

路徑：C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\ReplData\

路徑的部份，其中15的部份可能會因為版本的不同而有不同，所以在請注意一下。

6. 請給予使用者「everyone」相關的權限。

此部份的權限設定很重要，如果沒有設定的話，你在最後的同步時，會發生下列的錯誤訊息。

Error messages:

The schema script 'testtable_2.sch' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001) Get help: http://help/MSSQL_REPL-2147201001

The process could not read file 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\ReplData\unc\EC2AMAZ-F9DPPG4_SQLTESTDB_REP-MERGE\20220305040629\testtable_2.sch' due to OS error 5. (Source: MSSQL_REPL, Error number: MSSQL_REPL0) Get help: http://help/MSSQL_REPL0

Access is denied.  (Source: MSSQL_REPL, Error number: MSSQL_REPL5) Get help: http://help/MSSQL_REPL5

7. 請在設定完成後，先將來源端的資料庫進行備份，並將檔案還原到RDS上。

7-1 另外由於 RDS 在還原資料庫的部份會有點不同，所以可以參考下列的文件。

How do I perform native backups of an Amazon RDS DB instance that's running SQL Server?

https://aws.amazon.com/premiumsupport/knowledge-center/native-backup-rds-sql-server/

8. 接下來請接著設定訂閱集，此時同樣在來源端EC2上進行設定即可。