CaryHsu - 學無止盡: 2022

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月10日星期四

Bastion Host 防禦/堡壘主機最佳作法於 AWS 上

AWS 上可以建立自已的虛擬主機，也就是 EC2，但為了安全性等原因，所以通常會將 EC2 放置在私有網段 (Private Subnet)，而會找一台主機放置在公開網段 (Public Subnet)，這也是我們所說的 Bastion Host (防禦/堡壘主機)，但這樣就沒有問題了嗎？其實還是有下列的問題。

可能遇到的問題：

1. Key Pair 的管理問題，由於 Key Pair 是以檔案的方式進行管理，所以當使用者離職後，就無法有效的管理。
2. 必須將 Bastion Host 放置於公開網段，而且需要開啟埠端口(RDP 3389/SSH 22)。
3. 無法稽核使有用者輸入過那些指令。
4. 不需要特別登入 AWS Console 即可進行連結。
5. 可以同時支援 Windows 與 Linux 的使用，這邊比較難的事，在 Session Manager 登入時只能透過文字介面與 PowerShell 的介面方式登入，但本篇我們會說明如何透過 Session Manager Port Forward 的功能達到圖型化介面 GUI 的方式進行登入。

由於目前已有許多的客戶已在透過 Systems Manager Session Manager 來當作連線的方式，所以接下來我們來說明該如何完成上述的項目，藉以達到一個遠端登入最好的管理方式。

1. 請先建立一個私有網段 (Private Subnet)，也就是不要附加 Internet Gateway (IGW) 或 NAT 於 Route Table 中。

VPC with public and private subnets (NAT)
https://docs.aws.amazon.com/vpc/latest/userguide/VPC_Scenario2.html

2. 由於在私有網段 (Private Subnet) ，所以必須建立相對應的 VPC Endpoint，請參考下列的文章，分別建立三個 VPC Endpoint。

ssm.region.amazonaws.com
ec2messages.region.amazonaws.com
ssmmessages.region.amazonaws.com

上述前二個是 SSM 的必要條件，而第三個是當你要使用 SSM Session Manager 時，就一定要使用。

另外由於我們想將 Session Manager 的所有活動記錄，也就是使用者所有的輸入指令都記錄下來，所以我們要將 Session activity logs 全部放置在同一個 S3 中，然後可以再透過 Amazon Athena 進行查詢，藉以找出是否有人有輸入特殊指令，如刪除檔案、查看敏感檔案等。

com.amazonaws.region.s3

另外由於 S3 的 endpoint 比較不同，所以最後，我們必須在 EC2 中的 Security Group 中特別設定加入一組 prefix list 這樣才可以正常的輸出 Session activity logs 到 S3 中。

Step 6: (Optional) Create a Virtual Private Cloud endpoint
https://docs.aws.amazon.com/systems-manager/latest/userguide/setup-create-vpc.html

3. 請先針對 Systems Manager 建立一個最小權限的 IAM role，藉以後續可以附加在 EC2 Instance 中。

IAM Policy: AmazonSSMManagedInstanceCore

另外由於上述的 Policy 不包含 S3，所以也請同時加上 S3 的權限，如果你想簡單化這部份，你也可以直接使用 IAM Role [AmazonEC2RoleforSSM] 即可，但如果想要細部設定只可以使用特定的 S3 時，即可參考下列的連結。

Step 4: Create an IAM instance profile for Systems Manager
https://docs.aws.amazon.com/systems-manager/latest/userguide/setup-instance-profile.html

4. 上述的動作完成後，即已完成環境的設定，所以此時，請嘗試啟動一台 Windows EC2，並且在啟動的過程中，將你在步驟3建立的 IAM 給附加上。

注意，此部份你不需要開啟任何的 3389 port 或是 22 port，所以我的 inbound 是空白的，沒有新增開啟任何的規則。

5. 此時你可以先到 Systems Manager -> Fleet Manager 中確認你的主機是否已有顯示在清單中。

6. 此時你可以透過 EC2 中的 Connect -> Session Manger 進行連線，或是從 Systems Manager -> Session Manager 中進行連線，連線成功後，你會發現只有 PowerShell 的命令視窗，此時代表你已成功並且完成相關的設定。

7. 目前新功能上來說，也可以透過 Systems Manager -> Fleet Manager -> Node actions -> Connect with Remote Desktop 直接連接，但可惜這個方法目前無法使用剪貼簿導向，但可以看出在使用上已更加的方便了。

8. 接下來，我要說明如何透過 Systems Manager Session Manager 中的 Port Forwarding 的功能進行。

9. 請設定一個身份，可以執行 Session Manager 的動作，你也可以參考下列的文章。

Quickstart default IAM policies for Session Manager

https://docs.aws.amazon.com/systems-manager/latest/userguide/getting-started-restrict-access-quickstart.html

10. 設定完成後，請完成安裝 AWS Command Line Interface (CLI) 與 Systems Manager CLI Extension，藉以可以啟動 Port Forwarding 的功能。

AWS Command Line Interface

https://aws.amazon.com/cli/

(Optional) Install the Session Manager plugin for the AWS CLI

https://docs.aws.amazon.com/systems-manager/latest/userguide/session-manager-working-with-install-plugin.html

11. 安裝完成後，我們嘗試執行下列的指令，進行 Port Forwarding 的功能。

aws ssm start-session --target i-00e2a0ca3c861a3b5 --document-name AWS-StartPortForwardingSession --parameters '{"portNumber":["3389"], "localPortNumber":["9999"]}' --region ap-northeast-1

12. 最後，我們再透過 RDP Client 的軟體進行連線，你就會發現，可以在不開任何的 Port 的情況下，進行連線了。

13. 最後，我們要設定將所有的 Session Manager 的記錄輸出至 S3 中，所以請至 Systems Manager -> Preferences -> Edit

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上進行設定即可。