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) 或 NATRoute 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 的動作,你也可以參考下列的文章。


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


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?

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



下列選擇的部份,二者的差異在於RDS只能進行訂閱,發行與轉發者都是不可以的,所以請選擇第一點。

請點選 [Add SQL Server Subscriber] 將 RDS 的節點加入,此時請要透過在前面設定的節點名稱加入,如果透過 RDS endpoint 加入時,會有錯誤發生。


請設定連線到 RDS 中的帳號與密碼。



這個部份,由於我們的訂閱端主要是負責接收資料,並沒有要進行寫入的動作,所以請設定為 Client。





9. 建立完成後,你可以透過 Replication Monitor 持續確認同步的情況,當然如有同步有錯誤的話,可以透過此工具確認得到同步的錯誤為何。





2021年11月27日 星期六

SQL Server 備份效能與磁碟效能分析 - II

上一篇 (SQL Server 備份效能與磁碟效能分析 - I),我們主要介紹如何驗證 EBS Volume 的方式,本篇我們就來介紹在 SQL Server 備份時,該如何進行效能調整的部份。

進行備份前,由於我們要模擬一個比較大的資料庫,所以我找到Stack Overflow 有提供下載,所以我此次測試的資料庫約有50G的大小[1]。

在進行資料庫備份時,我們通常可以透過下列的指令進行備份。

BACKUP DATABASE [StackOverflow2013] TO  DISK = N'S:\db_backup\20210909-test3.bak' WITH NOFORMAT, NOINIT,  NAME = N'StackOverflow2013-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

BACKUP DATABASE successfully processed 6165657 pages in 154.625 seconds (311.522 MB/sec).

首先我們先來說明一下,上述的指令主要就是將資料庫 StackOverflow2013 備份到 S:\db_backup\20210909-test3.bak 的位置,而 stats = 10 的話,就代表每完成10%就顯示出,藉以了解備份的進度,最後可以看出花了多少的時間與大約每秒的備份速度。

從前一篇的說明,我們知道整體的 Throughput 會與 Thread 和 BlockSize 有關,但從Microsoft 的文件上來看[2],SQL Server Backup 中的 BlockSize 最高只能設定至 64K,所以這就斷了一條路,我們只好從 Thread 下手,我們嘗試將原本的備份檔案,從一個檔案,改寫成多個檔案後,如下列的語法,即可將整體的 Throughput 拉上來。

BACKUP DATABASE [StackOverflow2013] 

TO 

  DISK = N'S:\db_backup\20210909-test1.bak'
  , DISK = N'S:\db_backup\20210909-test2.bak'
  , DISK = N'S:\db_backup\20210909-test3.bak'
  , DISK = N'S:\db_backup\20210909-test4.bak'

WITH 

    NAME = N'StackOverflow2013-Full Database Backup'
    , INIT, STATS = 10

BACKUP DATABASE successfully processed 6165657 pages in 95.388 seconds (504.981 MB/sec).

由於 SQL Server 在備份上是採取當寫一個檔案時,會以一個 Thread 進行,所以上述的動作,我們分成四個 Thread 進行,所以你可以看出最後的 Throughput 可以達到 504MB,但其實如果我將檔案分成更多個時,是否會有更好的表現,我的答案是不一定的,因為這個會取決於你的 Data File 的分檔數量與 Core 數量等,所以這個可以測試確認後,找出一個合適的方式。

除了上述的作法,還有什麼方式可以進行調整,答案是有的,我們可以透過另外二個參數來進行,一個是 BUFFERCOUNT,而另一個就是 MAXTRANSFERSIZE,二個數值的調整會影響到記憶體的使用量,根據微軟的文件說明[2],可能會造成 OOM 的情況,所以也要請大家注意。

BACKUP DATABASE [StackOverflow2013] TO  DISK = N'S:\db_backup\20210909-test3.bak' WITH NOFORMAT, NOINIT,  NAME = N'StackOverflow2013-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10, BUFFERCOUNT = 7, MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536

BACKUP DATABASE successfully processed 6165657 pages in 95.358 seconds (505.140 MB/sec).

其中 BufferCount 仍是預設值,我設定為7,關於此值的設定,會與你的備份設備裝置相關,大約也在7-10之間[3],所以我仍是以7為主,但在 MaxTransferSize 的部份,預設值為 1,048,576 (1M),所以我將此值的設定調整加大後,你也可以看到,也是有加快速度的效果。

相反的,如果將這個值設定變小時,會有什麼情況,可想而知,備份的速度將會非常的慢,如下列所示,我將 MaxTransferSize 從預設的 1M 改成 64k後,整體的備份速度就從311MB左右,跌至 63 MB。

BACKUP DATABASE [StackOverflow2013] TO  DISK = N'S:\db_backup\20210909-test.bak' WITH NOFORMAT, NOINIT,  NAME = N'StackOverflow2013-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10, BUFFERCOUNT = 7, MAXTRANSFERSIZE = 65536, BLOCKSIZE = 65536

BACKUP DATABASE successfully processed 6165673 pages in 753.965 seconds (63.887 MB/sec).

另外當我們如果相要進行備份時的預設參數為何,可以透過下列的語法,即可在進行備份時,將相關的參數寫入到 Error Log 中進行確認。

DBCC TRACEON (3213, 3605, -1);

輸出結果:

Backup/Restore buffer configuration parameters
Memory limit:               12151 MB
BufferCount:                7
Sets Of Buffers:            1
MaxTransferSize:            1024 KB
Min MaxTransferSize:        64 KB
Total buffer space:         7 MB
Tabular data device count:  1
Fulltext data device count: 0
Filestream device count:    0
TXF device count:           0
Filesystem i/o alignment:   512
Media Buffer count:         7
Media Buffer size:          1024 KB

最後是否還有其他的方式,其實還是有一招的,就是透過 Windows 的軟體式陣列來達到,所以我用二顆 EBS Volume 來組成一個 Disk Array 0進行,並將測試的結果分享如下。

1. 透過 DiskSpd 進行測試。



2. 透過 SQL Server 預設值進行備份 (不調整任何的參數)。

BACKUP DATABASE [StackOverflow2013] TO  DISK = N'S:\db_backup\20210909-test3.bak' WITH NOFORMAT, NOINIT, NAME = N'StackOverflow2013-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

BACKUP DATABASE successfully processed 6165673 pages in 99.999 seconds (481.697 MB/sec).

從上述的測試上來看,的確二顆 EBS Volume 綁成一顆後,可以看到很好的效果,而且從數字上來看也是有很大的成長,而且 Thoughtput 與 IOPS 可以成長到 8000+8000 與 500MB + 500MB 的效果,你要知道,如果你要執行 1000 MB 的 Throughput 時,至少 IOPS 要設定至 64,000 以上才可以,但這個方式就可以用較少的成本達到效果,但我強烈的不建議將 SQL Server Data File 放在此 Disk Array 上,因為一來這是一顆 Disk Array 0,再來軟體式的沒有硬體式的穩定,所以請特別注意使用的方式,千萬不要誤用,很重要!!!很重要!!!很重要!!!

上述的一系列的調整與測試的整理,希望可以提供給大家參考,如果有其他更好的方式,也歡迎提供。

參考連結:
=============
[1]. How to Download the Stack Overflow Database
https://www.brentozar.com/archive/2015/10/how-to-download-the-stack-overflow-database-via-bittorrent/
[2]. BACKUP (Transact-SQL)
https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver15
[3]. How It Works: How does SQL Server Backup and Restore select transfer sizes
https://techcommunity.microsoft.com/t5/sql-server-support-blog/how-it-works-how-does-sql-server-backup-and-restore-select/ba-p/315454


SQL Server 備份效能與磁碟效能分析 - I

本篇主要介紹關於磁碟測試與資料庫備份的調校,本篇提到許多的方式與從不同的面向來確認,所以特別整理成此篇,也希望可以提供給大家進行參考。

由於篇幅過長,所以拆成幾個部份進行分享。

磁碟的選擇有許多種,但在選擇磁碟種類時,通常可以選擇可用容量、IOPS與吞吐量等,但往往很多人只會關心到 IOPS ,但其實 IOPS 的值的高低,並不代表最後磁碟的效益,比如說如果要存取 1MB 的資料時,如果 Block Size 只有 64K 時,就需要 16 次的 IOPS 才可以完成,但是如果 Block Size 是 256K 時,只要 4 次的 IOPS 就可以完成,所以從這個例子上來看,我們關心的,真的就不只是 IOPS 的高低,而應該是要看最後的吞吐量才是重點。 

另外,許多的磁碟在底層會有合併寫入的動作,比如說每次寫入的 Block Size 為 64K, 而在實際底層則會合併 256K 後,才會進行一次的寫入動作,所以在 IOPS 的部份,也要注意是否有這樣的情況發生。

 本篇我已 AWS 的平台介紹當你選擇一個 EBS Volume 與 EC2 時,該如何進行評估,並且在最後的時候,如果運行 SQL Server 備份時,該如何進行備份效能的提升。 

資源資訊: EC2 - Z1D-6xlarge 

Instance vCPU Mem (GiB) Networking Performance (Gbps)*** SSD Storage (GB) 
z1d.6xlarge 24 192 10 1 x 900 NVMe SSD 

EBS: 
Volume Size: 4,096 GB 
IOPS: 8,000

我們從文件上來看,當 32,000 IOPS 以下時,吞吐量可以達到 500MB/s ,所以我們需注意相關的EC2類型並沒有相關的限制,在其他的類型,如上述的網路速度,10G 與 10G up 是不同的,10G 是穩定在 10G 的傳輸速度,但 10G 則是可以維持在10G一段時間,我們簡稱信用額度與爆衝值,所以二者在長期的一段時間上來看,是有不同的,詳細的說明,可以參考文件[1]。 

另外一個要注意的重點就是每一種 Instance Type 都會有不同的限制,除了上述提到的部份,還有 Baseline IOPS與Maximum IOPS,如 z1d.large 的 Baseline IOPS 只有 3,333, 而 Maximum IOPS 有13,333, 所以如果你建立一顆 IOPS 8000的 Volume 在 z1d.large 上時,你基本只能到3333,但可以最大到13,333, 但這個最大值是在24小時內,最多只能維持30分鐘,超過30分鐘,就只能執行在 Baseline IOPS 左右了,所以還是有限制存在的,也是我選擇一個較大的 Instance Type 類型進行測試的原因[1]。 

現在我們進入主題,那我們該如何進行 Volume 的測試,根據 AWS 的官網文件,我們可以透過 DiskSpd 與 CrystalDiskMark 二個軟體,但我二個都測試過後,我其實比較喜歡使用 DiskSpd, 另外在測試前,請記得檢查電源管理中的模式是否設定為高效能,這個在 AWS 的 Windows 中是設定為預設啟用,但如果你設定為省電模式時,效能我有比較過,真的差很多,這又是一個故事了(血淚@@). 

Control -> Hardware -> Power Options -> Preferred Plans -> High Performance 


首先在測試前,我要提到一個重要的觀念,當在測試前,如果我們希望測試出預期的IOPS與Throughput 時,有二個重要的要素,一個是你的線程 (Thread),而另一個則是你的 BlockSize,所以後續的測試,我們來看這二者間的關係。

首先我們先下載 DiskSpd 的工具,然後再我透過下列的指令進行。

diskspd -b8K -d30 -t1 -h -r -w40 -L -Z1G -c20G D:\iotest.dat

b: BlockSize
d: 測試時間
t: Thread
w: 讀寫入率
c: 預計測試的檔案大小

D:\ 這就是我掛上的 EBS Volume,如上述提到的。

下列是我進行的測試,我主要分成不同的 BlockSize 與 Thread 進行,並將相關的測試結果整理如下。






從上述可以看出當如果 Thread 數太低時,會造成無法拉高整體的 IOPS ,但當 BlockSize 設定夠高時,的確是可以拉高整體的 Throughput,所以可以透過上圖的測試找出實際二者之間的關係,這個對後續在使用或效能調整時是很重要的,後續我再以 SQL Server 為案例分享該如何進行。


參考連結: 
============ 
[1]. Amazon EC2 Instance Types 
[2]. Benchmark EBS volumes