CaryHsu - 學無止盡

SQL Server 叢集版本升級問題與相關注意檢查事項

2024-04-02T19:29:00.001+08:00

最近協助客戶進行升級的動作，但客戶表示已有升級多台 SQL Server 主機，發現單機升級的部份都可以正常的進行，但在進行叢集環境時，竟然發現都是失敗的，所以心想該不會真的叢集升集也是有造成這個問題的。

進一步的確認 SQL Server 的安裝日誌，發現有下列的錯誤訊息。

(01) 2024-03-20 17:29:45 Slp: Running Action: RunRemoteDiscoveryAction

(01) 2024-03-20 17:29:45 Slp: Running discovery on remote machine: SQL01

(01) 2024-03-20 17:29:45 Slp: Running discovery on local machine

(01) 2024-03-20 17:29:46 Slp: Discovery on SQL01 failed due to exception

(01) 2024-03-20 17:29:46 Slp: Microsoft.SqlServer.Management.Sdk.Sfc.EnumeratorException: Failed to retrieve data for this request. ---> Microsoft.SqlServer.Configuration.Sco.SqlRegistryException: The network path was not found.

(01) 2024-03-20 17:29:49 Slp: Exception type: Microsoft.SqlServer.Management.Sdk.Sfc.EnumeratorException

(01) 2024-03-20 17:29:49 Slp: Message:

(01) 2024-03-20 17:29:49 Slp: Failed to retrieve data for this request.

(01) 2024-03-20 17:29:49 Slp: HResult : 0x80131500

安裝日誌路徑：%programfiles%\Microsoft SQL Server\nnn\Setup Bootstrap\Log

檢視與讀取 SQL Server 安裝程式記錄檔

https://learn.microsoft.com/zh-tw/sql/database-engine/install-windows/view-and-read-sql-server-setup-log-files?view=sql-server-ver16

從上述的錯誤來看，其實在叢集進行升級時，二個節點間會進行確認，所以常見在 Admin Share 或相關服務等沒有開啟時，在安裝的檢查畫面時就會發生錯誤，造成無法進行安裝。建議的作法上，可以在進行安裝時，逐一檢查下列的項目，在檢查後，的確認發現客戶的相關服務已關閉，造成安裝檢查失敗，在服務啟動後，即可進行安裝完成。

建議檢查項目

1) 建議透過 Local Administrator 的身份登入

2) 檢查下列的機碼值是否存在 PendingFileRenameOperations

HKLM\SYSTEM\CurrentControlSet\Control\Session Manager

已值應該是不存在或是空白的，如果此值不是空白的話，請先嘗試進行重啟進行確認。

3) 確認下列的 Windows Services 是否有啟動，如果沒有啟動的話，請嘗試的啟動下列的服務。

Alerter

Cluster Service

Computer Browser

Cryptographic Services <====This must be running on all nodes.

DHCP Client

Distributed File System

Distributed Link Tracking Client

Distributed Link Tracking Server

DNS Client

Event Log

IPSEC Policy Agent

License Logging Service

Logical Disk Manager

Messenger

Net Logon

Network Connectors

NTLM Security Support Provider

Plug and Play

Process Control

Remote Procedure Call (RPC) Locator

Remote Procedure Call (RPC) Service

Remote Registry Service <====This must be running on all nodes.

Removable Storage

Security Accounts Manager

Server

Spooler

Task Scheduler <====This must be running on all nodes.

TCP/IP NetBIOS Helper

Telephony

Time Service

Windows Management Instrumentation Driver Extensions

Windows Time Service

Workstation

4) 確認 Admin Shares 是否有開放，預設的情況下是有開啟的，但有些客戶的環境會進行停用，所以也請嘗試啟用。

Net use \\\IPC$

Net use \\\C$

Net use \\\D$ (drive may not exist)

Net use \\\Admin$

SQL Server 載入防毒軟體模組的處理

2024-02-13T22:48:00.000+08:00

防毒軟體相信在很多的主機上都會安裝，在 SQL Server 上也是不例外，但因為防毒軟體造成的影響，在真的遇到時，如果發現兇手是防毒時，真的會爆火氣，但在沒有相對的證據指出是防毒造成的影響時，真的很難說服客戶進行移除。

另一方面，就算客戶暫時停用防毒程式，但這樣就真的沒有影響了嗎？其實答案為 "否"，因為已載入至執行的程式中，就算你把主程式停用，但已載入的部份，還是有很大的影響，所以通常要說服客戶進行移除防毒。一般在證據的收集上，如果是服務無法啟動，我們就透過微軟的一個內部人幾乎都要會的軟體 Process Monitor 進行收集，藉以找出是否是被其他的程式所影響，這也是一個我們主要找出的方法之一。

近日，遇到一個效能的案件，透過先前介紹的工具完整的收集客戶問題發生的區間，其中就發現 SQL Server 竟然載入了 TrendMicro 的 perfiCrcPerfMonMgr.dll 模組，進一步的確認的確是趨視 (TrendMicro) 的防毒軟體，如下所示。

CaryHsu - 學無止盡: 如何進行 SQL Server 效能資料收集與分析 - 以 LogScout, PSSDiag, SQL Nexus 工具為例

DMV - -- sys.dm_os_loaded_modules --

C:\Program Files (x86)\Trend Micro\OfficeScan Client\perfiCrcPerfMonMgr.dll

後來在想為何這個模組會載入至 SQL Server，而且進一步的查詢，發現這個載入其實代表安裝防毒軟體時，沒有設定排除 SQL Server，其實常見許多的客戶也是沒有進行這個設定，一但沒有設定，後續可能會造成效能問或相關檔案的毀損，所以最後的方法，就是要設定下列的作法即可，設定後，一來不會將相關的模組植入至 SQL Server 的執行中，也可以避免防毒軟體掃描造成相關的 mdf， ldf 的檔案毀損。

建議設定與解決方法：

Configure antivirus software to work with SQL Server - SQL Server | Microsoft Learn

最後建議，除了可以透過上述的 DMV 進行收集之外，也可以透過上面介紹的 LogScout 或 PSSDiag 統一收集後，也可以透過 SQL Nexus 進行分析，找出此問題，所以再次強烈的推薦給大家。

SQL Server CPU 在 NUMA 節點上，CPU 負載不平衡的處理

2024-01-31T19:31:00.000+08:00

最近接到客戶反應，為何 SQL Server 的主機上的 CPU 的負載只有在其中的一個 NUMA 的節點上，所以想請我們協助判斷是否為何有這種情況，如下列的例圖。

在說明如何排查問題時，我們先來介紹一下何為 NUMA 節點，NUMA 全名為 "Non-uniform Memory Access"，主要用於加速 CPU 與 Memory 的效能存取，簡單的說，在 CPU 上會放在一個 CPU socket 的插座上，假設底層有 Dual Processor，就代表有二顆 CPU 與二個 CPU 插座，而在 SQL Server 中就會有二個 NUMA 節點，如下圖所示。

進一步的驗證，可以透過系統資訊(MSINFO)與 SQL Server 中的 Error Log 中進行確認。

系統資訊：

Processor(s): 2 Processor(s) Installed.

[01]: Intel64 Family 6 Model 63 Stepping 2 GenuineIntel ~2594 Mhz

[02]: Intel64 Family 6 Model 63 Stepping 2 GenuineIntel ~2594 Mhz

SQL Server Error Log

2024-01-20 21:29:02.16 Server Node configuration: node 0: CPU mask: 0x0000000000000fff:0 Active CPU mask: 0x0000000000000fff:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

2024-01-20 21:29:02.16 Server Node configuration: node 1: CPU mask: 0x0000000000fff000:0 Active CPU mask: 0x0000000000fff000:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

2024-01-06 19:39:03.14 Server SQL Server detected 2 sockets with 12 cores per socket and 12 logical processors per socket, 24 total logical processors; using 24 logical processors based on SQL Server licensing. This is an informational message; no user action is required.

如上述的資訊，有二個 CPU 插座 (socket)，12 核心，24個邏輯核心，所以在 SQL Server 會識別為 2個 NUMA 節點，每個節點有12個核心，總共有 24 個核心可用。

簡單的說，透過 NUMA 節點，彼此間有各自的通道與 IO，記憶體等進行溝通，將有助於流量的隔離與區分，有助於減少彼此間的影響，架構上如下圖所示。

問題排除建議：

1. 首先需要確認此主機是否有其他的程式影響造成 CPU 不平衡的情況發生，或許兇手不完全是 SQL Server，簡單的方式，可以透過工作管理員 (Task Manager)，但如果要長期監控，可以透過效能監視器中的 Processor Time 來判斷是否真的就是 SQL Server 所造成。

由上圖的監控上來看，可以知道幾乎就是 SQL Server 的程式所造成的。

2. 由於 SQL Server 上的 NUMA 節點預設是自動設定，強烈的建議千萬不要自已去異動，千萬不用動設定，千萬不用動設定，千萬不用動設定，很重要，所以說三次，我將相關的設定整理如下。

max degree of parallelism -> 1

Resource_governor_configuration = 0

affinity mask -> 0

affinity I/O mask -> 0

affinity64 mask -> 0

affinity64 I/O mask -> 0

3. 透過 DMV 查詢是否有系統有修改過 Soft NUMA 的設定，下列的值為 0，所以代表也是沒有修改過。

select softnuma_configuration,* from sys.dm_os_sys_info

參考連結：
https://learn.microsoft.com/zh-tw/sql/database-engine/configure-windows/soft-numa-sql-server?view=sql-server-ver16

4. 預設的情況下，SQL Server 在工作的分配上，都是透過輪詢 (Round-Robin) 的方式進行分派，所以如果上一步沒有進行手動配置就要進一步的看 SQL Server 分配的情況，我們可以透過下列的二個 DMV 來進行查詢。

4-1 確認目前各 NUMA 節點的連線數配置。

select node_affinity,count(*) num from sys.dm_exec_connections
group by node_affinity

4-2 確認在排程上，load_factor 的值是否有異動，通常如果有排程的話，此值會因為排序的關係值會減少，所以你可以持續查詢確認此值會不斷的變化。

select load_factor, * from sys.dm_os_schedulers
where status = 'VISIBLE ONLINE'

4-3 透過 4-1 的值可以看到，二個節點在分配的數量上，會差一點，這是因為連線時的工作可能有很快就執行完，有的要執行很久，所以基於上述的確認，可以看到還是有輪詢分派，另外平行處理的部份，也可能會影響單邊跑的情況過久，進一步的資訊，也可以透過下列的查詢，看有那些連線執行過久。

select node_affinity,* from sys.dm_exec_connections

4-4 如果需要查詢 Session ID 執行的語法為何，也可以透過下列的語法進行查詢。

select wait_type, * from sys.dm_exec_requests
where session_id > 50

5. 透過上述的方式，可以確認分配的情況，但另一方面，也有可能因為效能問題造成壓力集中在 NUMA 節點 0上，此部份可以參考我的另一篇文章進行收集與確認。

如何進行 SQL Server 效能資料收集與分析 - 以 LogScout, PSSDiag, SQL Nexus 工具為例https://caryhsu.blogspot.com/2023/12/sql-server-logscout-pssdiag-sql-nexus.html
How It Works: Soft NUMA, I/O Completion Thread, Lazy Writer Workers and Memory Nodes
https://techcommunity.microsoft.com/t5/sql-server-support-blog/how-it-works-soft-numa-i-o-completion-thread-lazy-writer-workers/ba-p/316044

經過上述的檢查，最後確認客戶的問題主要在於效能問題上，所以進一步的透過 LogScount 收集並再分析提供給客戶，但基於上述的資訊，可以完整的證明分配的情況，當然如果真的有調整過 Affinity 的相關設定值，也確認一下是否真的有調整的需要，或是也可以開案件給 Microsoft Support 進行討論與確認。

如何在 SQL SERVER 標準版上架設 AlwaysOn 的唯讀複本-超級省錢架構

2023-12-29T23:19:00.000+08:00

在 SQL SERVER 的標準版，AlwaysOn 基本上只有支援二個節點，只有 SQL SERVER 企業版才有與作業系統的節點數量有相同的支援，而且標準版的第二個節點，無法設定為唯讀複本，所以如果要設定唯讀複本時，只能升級到企業版。

Editions and supported features of SQL Server 2019
https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2019?view=sql-server-ver16

許多公司由於在標準版與企業版在價格上的差異，許多企業版的功能上並沒有使用到，所以仍會先採用 SQL SERVER 標準版進行，但問題來了，如果希望增加一個節點來進行唯讀分攤負載時，除了升級到企業版之外，本篇就來提供一個不同的方式給大家參考。

SQL Server 2022 pricing and licensing
https://www.microsoft.com/en-us/sql-server/sql-server-2022-pricing

本篇我會透過 SQL SERVER AlwasyOn 標準版與交易式複寫(Transaction Replication)來達到上述的需求，在這個架構下，重點就是當 AlwaysOn 進行角色轉移 (Failover) 後，如何讓原本的交易式複寫仍可以正常的運作，就是本篇介紹的重點。

在介紹之前，我還是要先說一下這個架構的缺點，大家可以再考慮後是否合適，再進行考慮。

無法透過 AlwaysOn Listener 進行讀寫/唯讀的路由。
唯讀複本由於是透過交易式複寫進行，所以該節點不是真的唯讀，當用戶端不小心將資料寫入至該節點的話，造成資料衝突時，只能重新進行交易式複寫的設定(打掉重作)。
如果在後續資料傳輸過慢的問題需進行排除時，由於多重的架構，所以比較不容易查出問題瓶頸，但大神們可以忽略此問題。
管理時，需同時確認 AlwaysOn 與交易式複寫的情況，無法單純的透過 AlwaysOn 的 Dashboard 進行確認。

環境說明：

SQLAGNode1: Windows 2019 + SQL Server 2019 Standard
SQLAGNode2: Windows 2019 + SQL Server 2019 Standard
SQLRep1: Windows 2019 + SQL Server 2019 Standard

複寫角色對應：

散發 (Distribution): SQLRep1
發行 (Publishing): SQLAGNode1 + SQLAGNode2
訂閱 (Subscription): SQLRep1

開始安裝設定：

1. 一開始請先依照先前的文件，將 SQLAGNode1 + SQLAGNode2 設定安裝 SQL SERVER，並啟用 AlwaysOn 的功能，最後也建議進行 Failover 測試確認可以正常的完成節點的切換。

SQL Server 2012 新功能 - AlwaysOn安裝與設定
https://caryhsu.blogspot.com/2012/04/sql-server-2012-alwayson.html

2. 設定散發主機 (SQLRep1)

散發的部份一樣可以設定在不同的主機上，此部份是與訂閱者設定在同一台的機器上。

2-1 請將第三台主機(SQLRep1)安裝好 SQL SERVER 單機環境，而且安裝時需增加勾選 SQL Server Replication 的功能。

2-2 安裝完成後，加入同網域，不用加入叢集，也不用加入 AlwaysOn 中(也無法加入，因為 SQL Server 標準版的限制)。

2-3 設定散發初使設定。

2-4 由於二台發行的資料，都要透過散發來進行，所以在此主機上，要手動設定散發並指定二台發行可以透過此台主機進行散發。

3. 設定第一台發行集

這個需確認 AlwaysOn 的主要節點在那一台上，目前假設在 SQLAGNode1，所以我們就以此台進行。

3-1 新增發行集，基本上動作都相同，只是在散發主機的部份，必須指定到 SQLRep1。

3-2 第二台的發行集不用特別設定，當進行 Failover 時，你就會發現該發行會切換到第二台上。

4. 設定訂閱者 (SQLRep1)

這部份只需注意先指定到主要節點的部份，目前假設在 SQLAGNode1上。

5. 這時候已完成初步的可讀複本的部份，請先嘗試新增一筆資料至主要節點中，目前假設在 SQLAGNode1上，然後確認可以正確的將資料複寫至 SQLRep1 的節點中。

6. 設定重新導向發行端於散發主機上 (SQLRep1)

在散發主機上，執行下列的語法，主要就是將目前指定到第一台的發行，重新導向，指定到 SQL Server Listener上。

USE distribution;  
GO  
EXEC sp_redirect_publisher   
@original_publisher = 'SQLAGSTD1',  
@publisher_db = 'carytestdb',  
@redirected_publisher = 'sqlstdaglst';

7. 測試 Failover 後，確認是否可以正常的透過複寫將資料寫到訂閱端。

此時，我們第二台其實都還沒有設定發行端，但其實你在主要節點進行 Failover 切換後，您就會發現訂閱就會顯示在第二台的節點上，所以您可以嘗試在第二台也新增資料，確認資料也是有複寫到訂閱端，這樣就都完成了。

最後補充，許多文章提到可以透過 sys.sp_validate_replica_hosts_as_publishers 進行驗證，但因為驗證時，會同時到主節點與備援節點進行確認，而由於 SQL SERVER 標準版上的第二個節點是無法設定為可讀取的，所以如果有遇到下列的錯誤，其實是可以忽略的。

USE distribution;  
GO
EXEC sys.sp_validate_replica_hosts_as_publishers  
    @original_publisher = 'SQLAGSTD1',  
    @publisher_db = 'carytestdb',  
    @redirected_publisher = 'sqlstdaglst';

錯誤訊息：

OLE DB provider "MSOLEDBSQL" for linked server "[F9C2EDD8-1050-488F-97D3-BD64AFE91CCA]" returned message "Deferred prepare could not be completed.".

Msg 21899, Level 11, State 1, Procedure sys.sp_hadr_verify_subscribers_at_publisher, Line 109 [Batch Start Line 2]

The query at the redirected publisher 'SQLAGSTD1' to determine whether there were sysserver entries for the subscribers of the original publisher 'SQLAGSTD1' failed with error '976', error message 'Error 976, Level 14, State 1, Message: The target database, 'carytestdb', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.'.

One or more publisher validation errors were encountered for replica host 'SQLAGSTD1'.

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

2023-12-27T23:03:00.000+08:00

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

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

如何進行 SQL Server 效能資料收集與分析 - 以 LogScout, PSSDiag, SQL Nexus 工具為例

2023-12-11T22:45:00.000+08:00

前面的文章，有介紹過該如何透過 TroubleShootingScript toolset (TSS) 收集系統資訊，這個通常主要用於分析如資料庫為何 Failover，意外重啟等情況，但如果要進行效能分析時，該如何進行，因為很多時候效能的問題無法立即重現與立即觀察，所以本篇要來特別介紹該如何來收集效能資訊與收集到的資訊後，該如何進行工具幫忙進行分析並給出建議。

Windows 資料收集工具介紹 - TroubleShootingScript toolset (TSS)
https://caryhsu.blogspot.com/2023/11/windows-troubleshootingscript-toolset.html

這三套工具都是由 Microsoft 所推出與維護的，而且也是 Microsoft 的工程師在分析時的工具之一，所以可以安心的使用。

一般在效能分析上，我們簡單的分成長時間的收集與短時間的收集，這個取決於問題多久重現一次，如客戶反應每天的凌晨的排程與相關作業都會變慢，這種有特定的時間點，而且很快的會重現的，我們就可以透過短時間的收集，而如果是很久才會重現一次，而且不確認時間的話，就需考慮透過長時間的觀察來找到問題，長時間與短時間最重要的是在收集時對系統的效能影響，一般來說可能會有5-10%的影響(也有可能更高，取決於系統的使用情況)，所以如果系統一直透過長時間的進行，反而會影響到系統，也會造成因為收集而空間耗用的情況，所以需要簡單先區分類型。

在收集的工具上分成 LogScout 與 PSSDiag 二種，二種都是都用來進行 SQL Server 的效能收集，而且收集後的資訊也都可以透過 SQL Nexus 進行分析。

二者在對最後的分析上沒有一定要使用上那一個工具才可以，但在收集的使用上，坦白說我比較推薦選擇 LogScout，一來此工具比較新，二來 LogScout 不需要像 PSSDiag 要先配制好相對的SQL Server 版本，而且可以在執行時才設定收集的項目等，所以在使用上比較彈性，但我在本篇中也會對二個工具都逐一的介紹。

PS: PSSDiag 可以額外的設定要收集多久的資訊，如1個小時或2個小時，算是一個比較方便的部份。

收集方式：
==============

1. LogScout:

最小需求：

Windows 2012 or later (including Windows Server Core)
Powershell version 4.0, 5.0, or 6.0

說明網址：https://github.com/microsoft/SQL_LogScout

此工具已有內建在 SQL Server on Windows VM 上，所以不需要額外的進行下載。

檔案在下載解壓縮後，只需執行該目錄下的 SQL_LogScout 即可，當下可以選擇透過 GUI 的介面或文字模式進行收集的設定，如下圖，如果透過文字的方式進行時，可以透過數字來選擇需要進行的項目，如進行效能收集，我就會選擇 0+1+4+6+11 進行。

圖型介面

文字介面

如果看到下列的文字，即代表目前已在收集中，所以可以請客戶進行問題的重現，此時請不要關閉視窗，待完成後，再輸入 STOP 就可以完成收集。

收集完成後，您就可以看到指定的目錄中會產生一個 output 的目錄，裡面就是收集的相關檔案。

2. PSSDiag:

最小需求：

Diag Manager

Windows 7 or Windows 10 (32 or 64 bit)
.NET Framework 4.5

說明網址：
https://github.com/microsoft/DiagManager

此工具分成二個部份，第一是需要先確認要收集的 SQL Server 版本，與需要收集那些資料，而且如果是叢集的話，收集方式也會不同，當如果配置錯誤，在執行時就會發生錯誤，如下列所示。

此配置的部份，可以找一台符合最小需求的電腦，不需安裝 SQL Server 即可進行，下載壓縮檔並解開後，執行 DiagManager 的執行檔後，即可進行配置，如下圖，待配置好後，即可點選 Save 進行儲存，儲存後即會在指定的目錄下產生一個檔案，此為主要進行的程式。

將配置好的檔案複制到 SQL Server 上，然後進行解壓縮，並執行 .\pssdiag.ps1 即可進行收集，此時也是請不要關閉視窗，待完成後，輸入 CTRL+C 的組合鍵進行停止即可。

上述的二個收集方式，建議要收集最少15分鐘以上，以免最後在透過 SQL Nexus 進行分析時，無法進行。

分析方式：
==============

SQL Nexus :

在使用 SQL Nexus 前，可以找隨意一台進行，只需符合下列的條件即可，在使用時，需要安裝多個不同的組件，還需要找一台 SQL Server 進行中介處理，所以通常我會找一台測試機進行即可，另外在安裝上，可以直接下載官方的 PowerShell 進行自動判斷安裝與檢查，不用逐一下載，省下很多的步驟。

最小需求：

.NET framework 4.8 (runtime is sufficient). Windows 11 has version already.
Download and install SQLSysClrTypes
Download and install ReportViewer control (ReportViewer.msi)
Download and install RML Utilities (RMLSetup_AMD64.msi)
An instance of SQL Server (2012 or above) to connect to and process data
Optional: PowerBI Desktop

自動判斷安裝與下載 (直接下載此檔案進行即可自動判斷)
https://github.com/microsoft/SqlNexus/blob/master/Setup-Related/SetupSQLNexusPrereq.ps1

在每個版本下，都會有原始碼與執行檔可以下載，直接進行下載即可，此篇寫作時，版本為 7.23.06.06, 所以下載 SQLNexus_7.23.06.06_Signed.zip 並進行解壓縮。

下載網址：
https://github.com/microsoft/SqlNexus/releases

執行 sqlnexus.exe 的程式後，即可出現主畫面，一開始會讓你選擇要透過那一台 SQL Server 要進行中介處理的動作，此時也請將收集到的檔案放到此主機上進行分析。

選擇主畫面中左下角的 Import，將所有的分析資訊進行匯入，另外路徑的部份，請不要使用中文，以免發生解析錯誤。

匯入的過程中，可能會需要一些時間，匯入完成後，會在中介處理的 SQL Server 主機上也會出現一個 sqlnexus 的資料庫。

最後處理完成後，你就會看到一個完整的分析說明，你可以從中看到各個不同的項目，你也可以逐一點開來看是否有建議改善等資訊可以參考，其實如果有建立案件到 Microsoft 時，這也是 Microsoft 的工程師們，會進行的一個方式，但其中的數據解讀才是真正的大學問，其中的寶藏，就等著大家去發現了。

如何設定無網域與無叢集架構的 SQLServer AlwaysOn - 簡易可讀取性複本

2023-12-07T22:06:00.002+08:00

之前多篇文章皆有介紹多種不同高可用性的 AlwaysOn 架構，但通常標準的 AlwaysOn 需要加入網域與架設在叢集服務上，但如果用戶端只是希望可以將資料抄寫到另一個節點，增加可讀副本，也不想用網域與叢集的服務時，此篇的方式，就是一個很節省成本的作法。

在介紹前，我也要先說明一下這個作法的缺點，由於是沒有架設在叢集上，所以沒有自動 Failover 的功能，只能手動進行切換，而且 Listener 也無法使用，所以請在使用前先參考環境需求，再進行使用。

安裝說明：
============

1. 請在二台主機依照單機的方式直接安裝 SQL Server。

2. 在二台主機上，分別啟用 AlwaysOn 可用性群組。

3. 請在二台主機上新增登入與使用者，此帳號只用於 AlwaysON 端點(endpoint)溝通使用，只需要給連線到端點(endpoint)的權限即可。

CREATE LOGIN dbm_login WITH PASSWORD = '1234qwer!@#$';
CREATE USER dbm_user FOR LOGIN dbm_login;

PS: 權限稍後會設定

4. 通常二台主機在沒有加入網域的情況下，可以透過相同的帳號密碼或憑證進行溝通，但此部份我們選擇透過憑證來進行，這也是比較推薦的作法。在第一台主機上設定憑證。

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'caryhsu@0316';
CREATE CERTIFICATE dba_certificate WITH SUBJECT = 'carytestclusterless';
BACKUP CERTIFICATE dba_certificate
  TO FILE = 'C:\cary-backup\dba_certificate.cer'
  WITH PRIVATE KEY (
          FILE = 'C:\cary-backup\dbm_certificate.pvk',
          ENCRYPTION BY PASSWORD = 'caryhsu@0316'
      );

PS: 上述的憑證很重要，請要小心保存，如果不見的話，會無法進行還原，其他的功能，如 TDE 也會用到此憑證來進行啟用。

5. 將上述的二個憑證檔案複制到第二台主機上，然後透過下列的指令進行建立憑證。

CREATE CERTIFICATE dba_certificate
AUTHORIZATION dbm_user
FROM FILE = 'C:\cary-backup\dba_certificate.cer'
WITH PRIVATE KEY (
FILE = 'C:\cary-backup\dbm_certificate.pvk',
DECRYPTION BY PASSWORD = 'caryhsu@19810316');
PS: 上述的 dbm_user 是在步驟三建立的使用者。

6. 在二台主機上分別建立端點 (endpoint)，並且賦予連接至端點的權限。

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = 5022)
    FOR DATABASE_MIRRORING (
	    ROLE = ALL,
	    AUTHENTICATION = CERTIFICATE dba_certificate,
		ENCRYPTION = REQUIRED ALGORITHM AES
		);
		
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [dbm_login];

PS: 由於我們是設定透過憑證進行驗證，所以此部份一定要透過指令的方式進行

一切設定完成後，我們就可以來設定啟用 AlwaysOn。

7. 請在第一台主機上執行下列的指令進行建立可用性群組.

CREATE AVAILABILITY GROUP [ClusterlessAG]
WITH (CLUSTER_TYPE = NONE)
FOR REPLICA ON
	N'ClusterlessAG1' WITH (
		ENDPOINT_URL = N'tcp://ClusterLessAG1:5022',
		AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
		FAILOVER_MODE = MANUAL,
		SEEDING_MODE = AUTOMATIC,
				SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
		),
	N'ClusterlessAG2' WITH (
		ENDPOINT_URL = N'tcp://ClusterLessAG2:5022',
		AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
		FAILOVER_MODE = MANUAL,
		SEEDING_MODE = AUTOMATIC,
		SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
		);

ALTER AVAILABILITY GROUP [ClusterlessAG] GRANT CREATE ANY DATABASE;

上述中的 SEEDING_MODE = AUTOMATIC 是代表建立後，如果資料庫加入時，會自動同步至另一個節點，如果資料庫過大時，建議設定成 SEEDING_MODE = MANUAL，代表建立後自行還原，藉以減少初使同步的時間。
上述中的 CLUSTER_TYPE = NONE 代表就是沒有使用任何的叢集服務

8. 請在第二台主機上執行，將第二台主機加入可用性群組中。

ALTER AVAILABILITY GROUP [ClusterlessAG] JOIN WITH (CLUSTER_TYPE = NONE);
ALTER AVAILABILITY GROUP [ClusterlessAG] GRANT CREATE ANY DATABASE;

9. 請在第一台主機上執行，將資料庫加入到可用性群組中。

ALTER AVAILABILITY GROUP [ClusterlessAG] ADD DATABASE [testdb];

PS: 加入的資料庫請確認設定為完整的複原模式 (Full Recovery)，而且已有進行一次完全備份( Full Backup)。

10. 嘗試要加入 Listener，是可以加入成功，但實際上是無法作用的，一來因為沒有網域，所以此 Listener 是無法進行註冊，而且也沒有自動 Failover，都是手動的方式，所以在連線時，需要特別指定連線到主要節點，還是連線到次要節點，所以在前端的部份，也是需要特別的注意。

如何建立 SQL Server AlwasyOn 分散式可用性群組 - 進階高可用性

2023-12-04T21:33:00.001+08:00

在之前陸續介紹多種不同方式 SQL Server AlwaysOn 的設定與安裝的方式，可以有效的提高資料庫的高可用性，這次我們再來介紹 AlwaysOn 進階的部份分散式可用性群組 (Distributed availability groups) 的功能。

相關連結參考：
在混合雲的架構上建立完整的AlwaysOn架構
http://caryhsu.blogspot.com/2015/12/alwayson.html
SQL Server 2012 新功能 - AlwaysOn安裝與設定
http://caryhsu.blogspot.com/2012/04/sql-server-2012-alwayson.html
如何在Amazon上透過EC2，架設完整的AlwaysOn架構。
https://caryhsu.blogspot.com/2015/10/amazonec2alwayson.html

一般的情況下，我們在 AlwaysOn 的設定上以2個節點為主，進一步的設定，可以將第三個節點放在不同的地區或雲端，藉以當問題發生時，可以將 AlwaysOn 的節點轉移至雲端上，而且也可以當作可讀副本，藉以減輕主節點的壓力。

但在發生問題時，可能由於地端的網域或叢集等問題，所以轉移後，可能仍需及時的確認等問題，所以如果在預算允許的情況下，其實可以建立相同的第二組藉以問題發生時進行移轉，而 AlwaysOn 可以透過分散式可用性群組 (Distributed availability groups)進行管理，當發生問題時，可以進行轉移的動作。

常見的方式，直接將第三個或第四個節點放在不同區或雲端上。

可用性群組 (Distributed availability groups) 的方式，也是本篇介紹的方式。

在使用分散式可用性群組 (Distributed availability groups) 的部份，有下列的幾個優點：

將叢集節點建立在不同的地區，而且真正作到多叢集多節點的情境。
資料中心(DataCenter) 轉移。
延伸可讀副本至不同的地區上。

環境說明：
==============

AD - Windows Server 2022
SQL Server Nodes, 這裡主要有四台 SQL Server，一個叢集中有二個SQL Server的節點

安裝方式：
==============

1. 請依照下列的文章將二組叢集建立好.

SQL Server 2012 新功能 - AlwaysOn安裝與設定
http://caryhsu.blogspot.com/2012/04/sql-server-2012-alwayson.html

2. 請在第一組叢集的部份，建立第一組的 AlwaysOn，然後確認轉移等功能皆正常。

3. 在第二組叢集時，AlwaysOn 可以透過自動同步的方式進行建立，但通常如果資料庫較大，會透過備分的方式進行還原，而且這樣在建立上，問題也會比較單純。

4. 目前請確認二組的叢集在 Failover 上皆正常，可以 Listener 也可以正常的連接，最後在 Endpoint 的部份，也都確認有建立完成。

透過 SQL 語法進行查詢

SELECT * FROM sys.endpoints
where type = 4

5. 請透過下列的語法，建立分散式可用性群組 (Distributed availability groups)

CREATE AVAILABILITY GROUP [distributedag]  
   WITH (DISTRIBUTED)   
   AVAILABILITY GROUP ON  
      'sqlag2019-1' WITH    
      (   
         LISTENER_URL = 'tcp://sqlag2019ag1lt.mscaryhsu.com:5022',    
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
         FAILOVER_MODE = MANUAL,   
         SEEDING_MODE = MANUAL   
      ),   
      'sqlag2019-2' WITH    
      (   
         LISTENER_URL = 'tcp://sqlag2019ag2lt.mscaryhsu.com:5022',   
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
         FAILOVER_MODE = MANUAL,   
         SEEDING_MODE = MANUAL   
      );    
GO

上述中可以看到分散式可用性群組 (Distributed availability groups) 主要是將二個 AlwaysOn 透過 Listener 進行綁定，其中在 AVAILABILITY_MODE 一定要設定為 ASYNCHRONOUS_COMMIT 模式，因為如果設定為 SYNCHRONOUS_COMMIT 的模式，因為要等於第一個叢集完成後，也要同時寫到第二個叢集才算一個交易的完成，一來會造成交易的時間過長，二來也會因為跨區的網路延遲造成問題，由於是 ASYNCHRONOUS_COMMIT 模式，所以在 FAILOVER_MODE 也會是手動的方式(MANUAL)，最後在 SEEDING_MODE 也就是在同步時，如果資料庫不存在，進行初使化為同步傳送，還是透過手動進行，因為我們上一個步驟是透過手動進行還原，所以這個步驟就設定成 SEEDING_MODE = MANUAL。

6. 最後建立完成後，如果要進行手動的 Failover，可以透過下列的方式進行轉移。

ALTER AVAILABILITY GROUP [distributedag] FORCE_FAILOVER_ALLOW_DATA_LOSS;

設定完上述的部份即已完成，但其實用性上來說，個人感覺使用第三個節點於雲端上已算是很好的部份，你要地端與雲端都同時發生問題，我想可能真的是發生了很嚴重的事情了，大家也別太擔心，最後也希望這篇有幫助到大家。

Windows 資料收集工具介紹 - TroubleShootingScript toolset (TSS)

2023-11-21T21:57:00.000+08:00

在協助客戶進行問題排除時，往往我們要針對不同的情境進行不同資料的收集，因為有許多的資料收集，可能會造成系統上的效能影響，所以需依情況而定。

以往我們可能會寫好收集的步驟，請客戶逐一進行收集，有時候步驟較多怕客戶漏掉或客戶怕麻煩就直接跳過，所以只好手把手的一步一步的帶著客戶，而許多時候，有許多的收集工具，可能需要透過執行檔或安裝進行，但由於許多客戶端的政策，所以無法進行。

在此我們介紹一個好用，也是目前 Microsoft 主推的收集工具，相信有許多建立案件給微軟時，MS工程師也會透過此工具請客戶進行資料的收集，所以本篇我們來介紹如何透過 TroubleShootingScript toolset (TSS) 進行 Windows 主機資訊的收集。

從下列的文件上來看，此工具可是集結許多 Microsoft Internal 的工具大成，其中包含 ProcDump，ProcMon，Xperf等工具，而且此工具不需安裝，而是透過 PowerShell 進行收集，所以非常的適合收集主機的資訊進行收集並再進行分析。

Introduction to TroubleShootingScript toolset (TSS)
Introduction to TroubleShootingScript toolset (TSS) - Windows Client | Microsoft Learn

接下來我們就說明如何透過此工具收集 SQL Server 主機的資訊。

1. 從下列的位址下載 TSS。

http://aka.ms/getTSS

2. 下載後，請解壓縮檔案。

3. 解壓縮後，請開啟一個 PowerShell 視窗，然後切換到相對應的視窗，並輸入下列的指令。

.\TSS.ps1 -SDP SQLbase -noPSR -AcceptEula

PS:
SDP: Collect Support Diagnostic Package (SDP) for the specified specialty
noPSR: do not run PSR, used to override setting in preconfigured TS scenarios
SQLbase: 收集時資料夾的名稱

收集的過程約過5分鐘左右，但由於我的 VM 分配 1 Core 與 4GB 的記憶體，所以收集的過程中CPU是接近滿載的情況。

4. 收集完成後，會自動壓縮成一個檔案，將此檔案提供給原廠即可。

5. 進一步的解開此檔案，其實可以用來分析如系統的組態、防火牆，網路配置、系統日誌，同時在系統上有裝的服務，也會一同的收集，如 SQL Server 等相關的資訊。

6. 在這裡介紹相關 TSS 工具的初步說明，但其實仍可以再進一步的收集如效能監視器，TTT (Time Travel Debugging)，Process Monitor等長期收集的工具，後續有機會再來逐一介紹，也希望大家如果要協助客戶或朋友進行分析時，強力推薦透過這個工具來進行。

設定 Kerberos 驗證與 SQL Server 連線與問題排除

2023-11-17T23:19:00.000+08:00

最近遇到許多關於 Kerberos 的問題，所以我也自學習並整理相關 Kerberos 的問題，希望對大家會有幫助，也提供給自已記錄。

在 SQL Server 的驗證模式，可以分成 SQL Server 驗證與 Windows 的整合驗證，而在網域的環境下，Windows 整合驗證主要透過 Kerberos 進行，但在 Kerberos 無法啟用成功時，就會透過 NTLM 的方式進行，簡單的說，NTLM 是一種舊的認證方式，而且本身在證驗過程會帶著使用者輸入的密碼，而且加密方式也被證實是可以被反計算出的，所以微軟建議透過 Kerberos 的方式進行連線驗證，而二者之間的差異，也可以透過下列的文章進行了解。

NTLM 驗證模式：

Kerberos 驗證模式：

NTLM vs KERBEROS
https://answers.microsoft.com/en-us/msoffice/forum/all/ntlm-vs-kerberos/d8b139bf-6b5a-4a53-9a00-bb75d4e219eb

使用 Kerberos 的好處：

More secure: No password stored locally or sent over the net.
Best performance: improved performance over NTLM authentication.
Delegation support: Servers can impersonate clients and use the client's security context to access a resource.
Simpler trust management: Avoids the need to have p2p trust relationships on multiple domains environment.
Supports MFA (Multi Factor Authentication)

而 SQL Server 如果要使用 Kerberos 的驗證模式，必須符合下列二個條件。

用戶端與主機都在相同的網域或在信任的網域中。
Service principal Name(SPN) 又可稱為服務主機名稱，必預註冊於網域中。

Register a Service Principal Name for Kerberos connections
https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/register-a-service-principal-name-for-kerberos-connections?view=sql-server-ver16

針對上述的說明，如果在網域中，預設的情況下會使用 Kerberos 的驗證模式進行，但如何確認目前 Kerberos 是否有啟用，可以透過下列的方式進行確認。

1. 從 SQL Server 的 Error Log 中，確認主機是否有正確的註冊 SPN。

成功的註冊 SPN:

2023-11-16 09:37:30.94 Server The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/SQL2019-AG1.mscaryhsu.com ] for the SQL Server service.

2023-11-16 09:37:30.94 Server The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/SQL2019-AG1.mscaryhsu.com:1433 ] for the SQL Server service.

SPN 註冊失敗：

2023-11-16 10:34:40.80 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/SQL2019-AG2.mscaryhsu.com ] for the SQL Server service. Windows return code: 0x21c7, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

2023-11-16 10:34:40.80 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/SQL2019-AG2.mscaryhsu.com:1433 ] for the SQL Server service. Windows return code: 0x21c7, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

2. 透過語法確認目前的連線是 NTLM or Kerberos.

SELECT net_transport, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid

如何進行自動註冊 SPN 的動作。

在 SQL Server 端，預設在啟動時，會進行自動註冊 SPN 的動作，如同上述的日誌檔顯示，即可確認成功與否，而這部份也要注意你的啟動帳號必須要有下列的權限，關於啟動帳號的部份，也可以參考我另一篇的文章說明。

Read servicePrincipalName
Write servicePrincipalName

SQL Server 啟動帳號最佳實踐與最小化權限設定
http://caryhsu.blogspot.com/2023/11/sql-server.html

當無法進行自動註冊時，就需要進行手動註冊與問題排除，在此篇，我就透過上述 SPN 註冊失敗的情況為例，說明該如何進行問題排除。

1。從錯誤訊息上看 Windows return code: 0x21c7，如同下列的說明，主要是疑似 SPN 重覆，造成註冊 SPN 失敗。

SPN: 0x21c7
ERROR_DS_SPN_VALUE_NOT_UNIQUE_IN_FOREST The operation failed because SPN value provided for addition/modification isn't unique forest-wide.

2. 透過 setspn 的指令進行確認，確認該啟動帳號已註冊的 SPN 有那些。

setspn -L mscaryhsu\gmsasql

Registered ServicePrincipalNames for CN=gMSAsql,CN=Managed Service Accounts,DC=mscaryhsu,DC=com:

MSSQLSvc/SQL2019-AG1.mscaryhsu.com:1433
MSSQLSvc/SQL2019-AG1.mscaryhsu.com

3. 從上述來看，只有第一個節點 (AG1) 有註冊成功，但第二個節點 (AG2) 最沒有註冊，所以我嘗試手動註冊第二個節點。

在註冊前，簡單的說一下格式，在預設的情況下，SQL Server 預設的 port 為 1433，所以需同時註冊二個，一個是沒有 port 的連線方式，而另一個則是預設的 port

格式：
setspn -S MSSQLSvc/Server FQDN domain\username

手動進行註冊：

setspn -S MSSQLSvc/SQL2019-AG2.mscaryhsu.com mscaryhsu\gmsasql

Checking domain DC=mscaryhsu,DC=com
CN=cary hsu,CN=Users,DC=mscaryhsu,DC=com
MSSQLSvc/SQL2019-AG2.mscaryhsu.com

Duplicate SPN found, aborting operation!

setspn -S MSSQLSvc/SQL2019-AG2.mscaryhsu.com:1433 mscaryhsu\gmsasql
Checking domain DC=mscaryhsu,DC=com

Registering ServicePrincipalNames for CN=gMSAsql,CN=Managed Service Accounts,DC=mscaryhsu,DC=com
MSSQLSvc/SQL2019-AG2.mscaryhsu.com:1433

Updated object

從上述來看沒有 port 的那一個是註冊失敗的，所以也就是為何 SQL Server 自動註冊失敗的情況。

4。透過 setspn -x 的方式來進行找出重覆的 SPN，但可惜是找不出來的，原因在後面會描述。

setspn -x
Checking domain DC=mscaryhsu,DC=com
Processing entry 0

found 0 group of duplicate SPNs.

5. 透過網域主機上的 Directory Service 日誌，發現一個明顯的錯誤，其實認真的看，你可以看出，主要是有另一個使用者 "caryhsu" 已註冊此台主機所造成，所以當你透過新帳號進行註冊時，就會出現帳號重覆註冊的情況。

Log Name: Directory Service
Source: Microsoft-Windows-ActiveDirectory_DomainService
Date: 2023/11/16 上午 10:34:40
Event ID: 2974
Task Category: Global Catalog
Level: Error
User: MSCARYHSU\gMSAsql$
Computer: Win-CaryDC.mscaryhsu.com
Description:
The attribute value provided is not unique in the forest or partition. Attribute: servicePrincipalName Value=MSSQLSvc/SQL2019-AG2.mscaryhsu.com
CN=cary hsu,CN=Users,DC=mscaryhsu,DC=com Winerror: 8647

6. 透過下列的指令，你可以發現原來兇手就是自已的另一個帳號，因為我換了啟動帳號後，原先的帳號沒有刪除，造成無法註冊更換後的新帳號。

setspn -Q MSSQLSvc/SQL2019-AG2.mscaryhsu.com6.
Checking domain DC=mscaryhsu,DC=com
CN=cary hsu,CN=Users,DC=mscaryhsu,DC=com
MSSQLSvc/SQL2019-AG2.mscaryhsu.com

Existing SPN found!

7。最後，將此舊的 SPN 刪除，並再進行一次註冊後，問題就解決了。

setspn -D MSSQLSvc/SQL2019-AG2.mscaryhsu.com mscaryhsu\caryhsu
Unregistering ServicePrincipalNames for CN=cary hsu,CN=Users,DC=mscaryhsu,DC=com
MSSQLSvc/SQL2019-AG2.mscaryhsu.com

Updated object

setspn -S MSSQLSvc/SQL2019-AG2.mscaryhsu.com mscaryhsu\gmsasql
Checking domain DC=mscaryhsu,DC=com
Registering ServicePrincipalNames for CN=gMSAsql,CN=Managed Service Accounts,DC=mscaryhsu,DC=com
MSSQLSvc/SQL2019-AG2.mscaryhsu.com

Updated object

SQL Server 2019 容錯移轉叢集環境架設 - 整合 Windows 2022 iSCSI Target Server 的功能

2023-11-15T22:00:00.002+08:00

我在之前有介紹關於許多 SQL Server 容錯移轉叢集環境架設的部份，而且用了許多的篇幅詳細的介紹，但隨著版本的更新，我們來介紹不同的安裝方式，但目前此篇的方法，比較適合測試環境，我是為了有一個對照的環境可以測試，但在正式機上，可能還是要考量一下，目前仍不建議透過此篇的方式進行，如果真的要選擇的話，其實透過 AlwaysOn，反而是一個比較推薦的作法，大家也可以參考下列的文章。

前篇介紹：

AlwaysOn相關設定文章：

架構說明：

此架構上，目前有一台 AD + 二台 SQL Server Nodes + Windows 2022 iSCSI Target Server，總共有四台主機，如下列所示。

AD: Windows Server 2022
SQL Server: Windows Server 2022 + SQL Server 2019
iSCSI Target Server: Windows 2022 iSCSI Target Server

安裝開始：

1. 請先至 iSCSI Target Server 主機，新增 iSCSI Target Server 的功能

2. 功能啟用完成後，開始設定 iSCSI disk

3. 選擇 iSCSI disk 建立在那一個實體磁碟上

4. 設定 iSCSI disk 磁碟名稱，在這邊我會建立二顆磁碟，一顆為仲裁磁碟 (Quorum Disk)，另一顆為資料庫資料磁碟

5. 設定磁碟的大小，這邊設定的是最大的可用容量，實際上一開始會很小，但最大可使用到此設定空間。

6. 指定 iSCSI target，由於目前系統不存在，所以進行新增

7. 指定 iSCSI target 的名稱

8. 設定有那些主機可以連線到此 iSCSI target server，所以要將二台 SQL Server 加入，其實後續的設定也可以隨時進行調整

9. 設定用戶端連線到 iSCSI target server 時，是否需要額外的帳號密碼 (CHAP)，這邊設定我就沒有特別啟用。

10. 等待系統設定啟用完成

11. 設定完成後，即可以在上方看到有二個 iSCSI 磁碟

12. iSCSI target server 設定完成後，即可設定用戶端，也就是二台 SQL Server，我們先設定第一台，登入後，點選 iSCSI initiator 即可，這個不需額外的安裝即可使用

13. 執行 iSCSI initiator 時，由於預設服務是沒有啟用的，所以會跳出下列的訊息，點選 "Yes" 後即會啟動服務

14. 在下列的 Target 區域，輸入 iSCSI target server 的 IP 位置，然後點選 Quick Connect

15. 連結成功後，即可在 Disk Management 中看到之前設定的 iSCSI disk，在此設定 online 與格式化後，即可以使用。

16. 設定好磁碟後，回到 iSCSI initiator 的 Volume and Devices 點選 Auto Configure，設定完第一台之後，也同時設定第二台，這樣即可

17. 二台都設定完成後，即可設定容錯移轉叢集，完成後，即可看到叢集中有二個節點與二個磁碟，此時也可以嘗試進行容錯轉移，確認一切都正常。

18. 設定完成容錯移轉叢集之後，即可進行 SQL Server 的安裝，在安裝第一個節點時，請選擇 "新的 SQL Server 容錯移轉叢集安裝" 進行。

19. 完成後，即可看到容錯移轉叢集中的 Roles 出現 SQL Server，此時即完成第一台，然後再進行第二台的設定。

20. 在進行第二台 SQL Server 的安裝時，請選擇用 "將節點加入到 SQL Server 容錯移轉叢集" 的方式進行，而由於第一台的 Python 與 R 安裝失敗，雖然不影響後續的使用，但在第二台安裝時，會遇到下列的錯誤，雖然我有確認第一台的服務有啟動成功，但一樣會遇到下列的錯誤。

SQL Server Database Services feature state "failed"

21. 這個問題，主要是由於第一台安裝沒有完整的完成，所以才會有這個訊息，解決方法上，可以除了確認第一台的安裝問題為何外，也可以透過下列的方式進行解決。

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.MSSQLSERVER\ConfigurationState

這個機碼下有多個服務的狀態值，如同下列的圖示，將 2 的部份改成 1，然後再點選一次 "Re-Run" 即可通過。

22. 透過 Process Monitor 觀察，其實安裝時，系統是透過 remote registry 的方式存取此區判斷

23. 第二台安裝完成後，即可嘗試進行容錯轉移的動作，確認是否都安裝完成

如何透過 Partition Table 進行資料分散與維護作業並以年度為例

2023-11-10T21:14:00.018+08:00

先前一篇針對 Partition Table 進行詳細的介紹，近日再重新的檢示，其實我覺得自已當初提出的資料平均分配的方式很好，而且其實到現在還是很適用，但由於有很多的客戶需求，希望可以透過日期的方式來進行資料分散與切割，所以今天我就透過這篇來特別介紹一下。

如何在 AlwaysOn 建立 Partition Table 與自動進行資料平均分配http://caryhsu.blogspot.com/2016/01/alwaysonpartition-table.html

此篇我們透過 AdventureWorks 的資料庫來進行，並且也會說明在建立後，如果遇到新年度時，大佬與預算較緊的使用者們，針對透過年月的方式進行資料分散的方式，該如何維護，這篇也會詳細的介紹。

本篇在 Partition Table 建立後，其實維護是最麻煩的，由其在 Merge 與 Split 的部份，我其實也是在測試機上測試了好多次，才摸清最好與最佳的方式，希望二篇加起來後，可以更完整的幫助到大家。

1。加入 file group 與 files，這邊建議將 file 分散至不同的卷上面，最好的情況是有四台實體磁碟，各自放一個 file。

USE [master]
GO
ALTER DATABASE [AdventureWorks2019] ADD FILEGROUP [ag_group_1]
GO
ALTER DATABASE [AdventureWorks2019] ADD FILEGROUP [ag_group_2]
GO
ALTER DATABASE [AdventureWorks2019] ADD FILEGROUP [ag_group_3]
GO
ALTER DATABASE [AdventureWorks2019] ADD FILEGROUP [ag_group_4]
GO
ALTER DATABASE [AdventureWorks2019] ADD FILE ( NAME = N'fg_1', FILENAME = N'C:\sql_data\fg_1.ndf' , SIZE = 51200KB , FILEGROWTH = 1024KB ) TO FILEGROUP [ag_group_1]
GO
ALTER DATABASE [AdventureWorks2019] ADD FILE ( NAME = N'fg_2', FILENAME = N'C:\sql_data\fg_2.ndf' , SIZE = 51200KB , FILEGROWTH = 1024KB ) TO FILEGROUP [ag_group_2]
GO
ALTER DATABASE [AdventureWorks2019] ADD FILE ( NAME = N'fg_3', FILENAME = N'C:\sql_data\fg_3.ndf' , SIZE = 51200KB , FILEGROWTH = 1024KB ) TO FILEGROUP [ag_group_3]
GO
ALTER DATABASE [AdventureWorks2019] ADD FILE ( NAME = N'fg_4', FILENAME = N'C:\sql_data\fg_4.ndf' , SIZE = 51200KB , FILEGROWTH = 1024KB ) TO FILEGROUP [ag_group_4]
GO

2. 由於目前有四個資料群組，所以我們先統計一下資料量，然後再來規畫一下如何分散資料，目前如下圖，主要有2013年7月至2014年8月的資料。

--統計資料分佈

SELECT left(convert(varchar,TransactionDate,112),6) [tdhyear], count(*) nums
FROM [AdventureWorks2019].[Production].[TransactionHistory]
group by  left(convert(varchar,TransactionDate,112),6)
order by 1,2

3. 建立 Partition Function 與 Partition Schema，這邊會建立三個區域與對應至四個 File Group，如下列所示。

1900-01-01 00:00:00.000 ~ 2013-10-01 00:00:00.000 -> 儲存至 ag-group_1
2013-10-01 00:00:00.000 ~ 2014-02-01 00:00:00.000 -> 儲存至 ag-group_2
2014-02-01 00:00:00.000 ~ 2014-04-01 00:00:00.000 -> 儲存至 ag-group_3
2014-04-01 00:00:00.000 ~ 日後所有 -> 儲存至 ag-group_4

--將資料平均分散在多個不同區間

use [AdventureWorks2019];
CREATE PARTITION FUNCTION [PF-cary-test](datetime) AS RANGE RIGHT FOR VALUES (N'2013-10-01T00:00:00', N'2014-02-01T00:00:00', N'2014-04-01T00:00:00')

PS: 這裡建議採用 RANGE RIGHT 的方式，而不是用 RANGE Left, 因為我在 SQL Server 2019 與 2022 上測試，當使用 RANGE Left 的方式時，如果進行 SPLIT RANGE時，你會發現 ALTER PARTITION SCHEME 中的 NEXT USED 當合併資料，並將新加入的 file 與前一個 file 互換，雖然這樣也是沒問題，但還是覺得怪怪的，所以這邊也再請注意一下。

CREATE PARTITION SCHEME [PS-cary-test] AS PARTITION [PF-cary-test] TO ([ag_group_1], [ag_group_2], [ag_group_3], [ag_group_4])

4. 建立完上述的動作後，資料並不會立即的進行資料的搬移，需透過移除 Clustered Index 後，再將索引重新加入後即可

此處原本 TransactionID 是 PK 與 CLUSTERED INDEX，但由於此處的 Partition Table 是根據 TransactionDate 進行分配，所以要將 CLUSTERED INDEX 換成 TransactionDate 之後，資料才會重新進行排序。

ALTER TABLE [Production].[TransactionHistory] DROP CONSTRAINT [PK_TransactionHistory_TransactionID] WITH ( ONLINE = OFF )
ALTER TABLE [Production].[TransactionHistory] ADD  CONSTRAINT [PK_TransactionHistory_TransactionID] PRIMARY KEY NONCLUSTERED 
(
	[TransactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
CREATE CLUSTERED INDEX [ClusteredIndex_on_PS-cary-test_638351445508081513] ON [Production].[TransactionHistory]
(
	[TransactionDate]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PS-cary-test]([TransactionDate])
DROP INDEX [ClusteredIndex_on_PS-cary-test_638351445508081513] ON [Production].[TransactionHistory]

5. 透過下列的語法進行查詢，並確認資料在各群組分配的情況

SELECT OBJECT_NAME(p.object_id) AS ObjectName ,
i.name AS IndexName ,p.index_id AS IndexID ,
ds.name AS PartitionScheme ,
p.partition_number AS PartitionNumber ,
fg.name AS FileGroupName ,
prv_left.value AS LowerBoundaryValue ,
prv_right.value AS UpperBoundaryValue ,
CASE pf.boundary_value_on_right
WHEN 1 THEN 'RIGHT'
ELSE 'LEFT'
END AS PartitionFunctionRange ,
p.rows AS Rows
FROM sys.partitions AS p
INNER JOIN sys.indexes AS i ON i.object_id = p.object_id
AND i.index_id = p.index_id
INNER JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id
INNER JOIN sys.partition_schemes AS ps ON ps.data_space_id = ds.data_space_id
INNER JOIN sys.partition_functions AS pf ON pf.function_id = ps.function_id
INNER JOIN sys.destination_data_spaces AS dds ON dds.partition_scheme_id = ps.data_space_id
AND dds.destination_id = p.partition_number
INNER JOIN sys.filegroups AS fg ON fg.data_space_id = dds.data_space_id
LEFT OUTER JOIN sys.partition_range_values AS prv_left ON ps.function_id = prv_left.function_id AND prv_left.boundary_id = p.partition_number- 1
LEFT OUTER JOIN sys.partition_range_values AS prv_right ON ps.function_id = prv_right.function_id AND prv_right.boundary_id = p.partition_number
WHERE p.object_id = OBJECT_ID('Production.TransactionHistory');

6。從查詢上來看，後續未規畫的資料年度就會放置到最後一個 file group，所以當新年度到達時，通常我們會採取二種不同的作法，一時加入一個 file group，然後將後續的資料加入，另一種就是合併資料，並將新年度的資料轉向最後一個 file group中，所以也在這分別說明二種方式。

7。課長級作法。也就是加入一個 file 並將新年度的資料轉至此新的 file 中

7-1. 加入新的 file group

use [master];
ALTER DATABASE [AdventureWorks2019] ADD FILEGROUP [ag_group_5]
GO
ALTER DATABASE [AdventureWorks2019] ADD FILE ( NAME = N'fg_5', FILENAME = N'C:\sql_data\fg_5.ndf' , SIZE = 51200KB , FILEGROWTH = 1024KB ) TO FILEGROUP [ag_group_5]
GO

7.2 將新的 file group 加入至 schema 中，並分割出新的資料區間時區，就是將 '2015-01-01' 後的時間，儲存至新的 file 中

use [AdventureWorks2019];
ALTER PARTITION SCHEME [PS-cary-test]
NEXT USED ag_group_5;
ALTER PARTITION FUNCTION  [PF-cary-test] ()  
SPLIT RANGE ('2015-01-01T00:00:00.000')

7-3 查詢資料分佈的情況

8. 微課長級的作法，如果無法增加新的 file 用來分散資料的話，可以透過資料合併的方式，然後將新的資料儲存至最後一個群組中

8-1 依照資料上的分佈，我先將 file 3 與 4 的資料先進行合併，所以這邊我將 2014-02-01 00:00:00.000 之後的資料先合併至 ag-group3

ALTER PARTITION FUNCTION [PF-cary-test] ()  
MERGE RANGE ('2014-04-01 00:00:00.000');

8-2 將 2015-01-01 之後的資料存到 ag-group4 上面

ALTER PARTITION SCHEME [PS-cary-test]
NEXT USED ag_group_4;
ALTER PARTITION FUNCTION  [PF-cary-test] ()  
SPLIT RANGE ('2015-01-01T00:00:00.000');

8-3 由於有作過資料合併的動作，所以最好也是手動更新一次統計資訊

UPDATE STATISTICS [Production].[TransactionHistory]

8-4 查詢資料分佈的情況

SQL Server 啟動帳號最佳實踐與最小化權限設定

2023-11-08T23:23:00.003+08:00

此篇文章主要是最近經過二位大師的指導，修正一些以前的概念，自已嘗試作過一次後，發現其中其實有許多的坑，沒有作過一次，真的很難說有 100% 的懂 (我自已)，而且作完後有更深的體驗，所以整理成此篇，相信這也是企業內日後主推的部份，也希望大家可以參考並套用在企業中。

預設的情況下，SQL Server 的啟動帳號為 NT Service\MSSQLSERVER，而在網域的架構上，許多人會設定為網域帳號，如常見的 Domain User + Local Admin，但這樣的設定在安全性與稽核上，存在著許多的問題。

在許多的公司，常見的情況，通常會規定不能使用 Local Admin，而且規定每一個帳號 60 or 90 天後要重新設定密碼，所以以前常遇到客戶提出SQL重啟後，無法正常啟動，常見遇到的就是密碼過期等情況，所以造成無法啟動。

另外如果 SQL Server 的啟動帳號權限設定的太大，一但遇到如 SQL Injection 等的攻擊時，容易會造成更大的問題，因為會被當作跳板，再進行其他主機的攻擊，所以最小化的權限設定，將可以有效的防止等問題的發生。

最後許多的系統管理員要維護自已的帳號與密碼於 Excel or 筆記本中，而且在固定的時間就要進行更新，這種明碼存在的方式，也是另一個安全性的隱憂。

講了這麼多，我們就講主題，在啟動帳號的部份，微軟 (Microsoft) 強烈的推薦透過受控服務帳戶 (Managed Service Accounts) 與群組受控服務帳戶 (Group Managed Service Accounts) 來進行，如同我上述的介紹，這二個帳號類型就符合我提到的部份，在使用這二個帳號類型前，我們也來說看一下最低限制。

Group Managed Service Account Prerequisites:

Domain Functional Level of 2012 or higher
SQL Server 2014 or higher
Window Server 2012 R2 Operating System

另外受控服務帳戶 (Managed Service Accounts) 與群組受控服務帳戶 (Group Managed Service Accounts) 在設定上，也不需要手動輸入密碼，不需要再透過 Excel 等筆記本來記錄帳號密碼等，而密碼的部份也是每30天自動更新，二個帳號一個應用於單機的環境，而另一個則是應用於 HA 等的架構，如 WSFC、AlwaysOn 等群組節點的情境，所以本篇將透過群組受控服務帳戶 (Group Managed Service Accounts) 與 AlwaysOn 的環境來進行設定。

1. 登入主要網域進行下列的設定

2. 首先檢查 KDS (Key Distribution Service) 是否有 Root Key 的存在。

Test-KdsRootKey -KeyId (Get-KdsRootKey).KeyId

如果存在的話，會傳回 "True"，如果沒有的話，就需要進行下列設定。

Add-KdsRootKey –EffectiveTime ((get-date).addhours(-10))
or
Add-KdsRootKey -EffectiveImmediately

3. 也可以透過下列的指令查詢目前所有的 KDS KEY

Get-KdsRootKey

4. 建立一個群組，並將二個 SQL 節點加入群組中。

New-ADGroup -Name gmsa-sql-alwayson -Description "Security group for gMSAsql computers" -GroupCategory Security -GroupScope Global

4-1 將 SQL 節點加入群組中。

Add-ADGroupMember -Identity gmsa-sql-alwayson -Members SQL2019-AG1$,SQL2019-AG2$

PS: 上述的 SQL2019-AG1$ 是第一台節點，SQL2019-AG2$ 為第二個節點。

4-2 透過下列的語法查詢目前此群組中的成員有那些。

Get-ADGroupMember -Identity gmsa-sql-alwayson

4-3 也可以透過 Active Directory Users and Computers 介面的方式進行檢示。

5. 建立群組受控服務帳戶 (Group Managed Service Accounts)

New-ADServiceAccount -name gMSAsql -DNSHostName gMSAsql.mscaryhsu.com -PrincipalsAllowedToRetrieveManagedPassword gmsa-sql-alwayson

Get-ADServiceAccount gMSAsql -Property PasswordLastSet

PS: 上述你也可以查詢到此帳號密碼最後更新的時間

6. 設定此群組受控服務帳戶 (Group Managed Service Accounts) 可以擁有註冊 SPN 的權限，這個非常的重要，後面我們也會確認是否在更換帳號後可以正確的註冊 SPN.

dsacls (Get-ADServiceAccount -Identity gMSAsql).DistinguishedName /G "SELF:RPWP;servicePrincipalName"

7. 一切設定完成後，必須到各自的節點進行套用，我們先登入到第一台節點，再登入到第二個節點進行即可。

8. 啟用 Windows 的功能

8-1 檢查功能是否已啟用

PS C:\> Get-WindowsFeature AD-Domain-Services

Display Name Name Install State
------------ ---- -------------
[ ] Active Directory Domain Services AD-Domain-Services Available

8-2 啟用此功能

PS C:\Users\Administrator> Add-WindowsFeature AD-Domain-Services

Display Name Name Install State
------------ ---- -------------
[X] Active Directory Domain Services AD-Domain-Services Installed

9. 啟用群組受控服務帳戶 (Group Managed Service Accounts)

Install-ADServiceAccount -Identity gMSAsql
Test-ADServiceAccount -Identity gMSAsql

10. 請至另一個節點也是完成 8與9 的步驟

11. 完成上述的步驟後，就已完成群組受控服務帳戶 (Group Managed Service Accounts) 的設定，完成設定後，請至 SQL Server Configuration Manager 更換 SQL Server 的啟動帳號，請注意千萬不要透過系統中的服務列表進行更換，否則會有問題。

12. 更換完成後，目前已是最小權限，但仍建議進行作業系統上的系統原則，有二個是一定要設定的，要不然也是會因為權限不夠，而造成問題，一個是啟用鎖定記憶體分頁選項 (Lock pages in memory) 與執行磁碟區維護工作(Perform volume maintenance tasks)，這二個設定也是在二個節點都需要進行。

執行磁碟區維護工作(Perform volume maintenance tasks)
執行磁碟區維護工作 - Windows Security | Microsoft Learn

啟用鎖定記憶體分頁選項 (Windows)
啟用鎖定記憶體分頁選項 (Windows) - SQL Server | Microsoft Learn

13. 確認上述的二個設定是否有啟用成功

13-1 透過 SQL Server Error Log

2023-11-08 15:10:34.63 Server Using locked pages in the memory manager.

13-2 透過 DMV 查詢是否有啟用成功

SELECT sql_memory_model, sql_memory_model_desc
FROM sys.dm_os_sys_info;

沒有啟用

啟用 Lock pages in memory

14. 另外在 SQL 各個服務的部份，下列的文章也有詳細的介紹所需的權限，這樣即可達到最小化權限的部份，目前我並沒有透過下列的文章進行，確認是沒有問題的，但仍建議在相關的目錄上，尤其是 SQL Server 的主要引擎的部份仍是需要進行設定。

Configure Windows service accounts and permissions
https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver16#GMSA

15. 最後我們也確認在 AlwaysOn 的部份也是一切正常，

16. 在權限的部份，網域帳號的群組，不是歸屬在 Domain Admin 中，而且也不歸屬在任何的群組中，而在 SQL Server 中，只有自動加入的電腦帳號，其中也只有 Connect 的權限而已，所以這部份絕對是最小化的權限。

啟用即時初始化檔案 (Instant file initialization) 的功能於 SQL Server 上

2023-11-06T20:51:00.003+08:00

即時初始化檔案 (Instant file initialization) 其實在 SQL Server 2005 就有提出，但隨著 SQL Server 版本的更新，此功能也不斷的更新與強化，所以此篇也特別介紹一下這個功能。

即時初始化檔案 (Instant file initialization) 主要說穿了，就是在建立資料庫 (Database) ，擴展空間或是還原資料庫等動作時，不進行磁碟初始化寫 zero 0 的動作，藉以加快初始化的動作，這樣一來在要求空間等動作時，可以加快申請的初始化動作，而且在資料頁面(Data File) 的空間要求越大時，效果更是明顯，因為可以略過初始化填寫 zero 0 的動作。

通常有利也是有弊，我們在啟用這個功能時，也在深入的探討這個功能是否真的這麼強悍，但是否真的有適合不同的資料庫環境，我們分成下列的幾個方向進行討論。

1. 啟用此功能後是否會造成資料的錯寫或是錯亂等問題。

此功能持續觀察與使用已久，坦白說沒有遇過客戶回饋等問題，而且在相關的修正上，也沒有發現，所以這部份我覺得不是主要的問題。

2. 啟用後對效能上的影響，大約會有多少，是否有其他的負面影嚮。

此功能在啟用後，主要是進行相關動作時，不會針對資料頁面(Data Page) 進行初始化寫 0 的動作，所以可以大大的加速擴展空間、建立資料庫、還原資料庫等動作，但是在負面影嚮的部份，我覺得主要是在安全性的部份，由於資料頁面沒有進一步的初始化，所以當有人透過此磁碟進行資料還原等動作時，是有可能可以查到之磁碟上原先存放的資料為何，這在有些管理上，可能是不允許的，也是有一定的風險存在的。

此功能在支援上，地端與雲端會有不同的支援程度，在雲端上，部份是沒有支援的，如 Azure 原本也是沒有支援的，但後來在 transaction log 的部份，是有 "部份" 支援的，但相對的資料頁面(Data Page) 的部份，也是沒有支援的。

Database instant file initialization
Database instant file initialization - SQL Server | Microsoft Learn

另外在 AWS 上，很多人會透過 Amazon FSx for Windows File Server 來當作 SQL Disk 的部份，這部份也是沒有支援的，因為這個以前也有遇過客戶來討論過效能的問題，後來發現是不支援此功能，所以印像特別的深刻。

Using FSx for Windows File Server with Microsoft SQL Server
Using FSx for Windows File Server with Microsoft SQL Server - Amazon FSx for Windows File Server

說了這麼多，我們就來開始介紹如何進行啟用的方式。

在啟用的部份，你可以在一開始安裝時，在 Server Configuration 的部份，勾選 "Grant Perform Volme Maintenance Task privilege to SQL Server Database Engine Service" 的選項。

或是安裝完成後，從 SQL Server Configuration Manager -> SQL Server Service -> SQL Server(Instance name) -> Properties -> Advanced -> Instant File Initialization

另外在啟用時也請確認在作業系統層級，是否有權限進行套用，從 Local Security Policy -> Local Policies -> User Rights Assignment -> Perform volume maintenance tasks，請將你的 SQL Server 的啟動帳號加入此區的設定中即可。

設定完成後，你可以透過二個方式確認 SQL Server 是否有套用到 Instant file initialization 的功能。

1. 透過 SQL Server 的 Error Log 確認啟動時是否有套用此功能。

2023-11-03 18:02:37.12 Server Database Instant File Initialization: enabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.

2. 透過 T-SQL 指令查詢是否有啟用 Instant file initialization。

select servicename, status_desc, service_account, instant_file_initialization_enabled from sys.dm_server_services

一切準備就緒就後，我們可以來透過建立資料庫的方式，來比較二者間的差異，藉以了解在啟用後之間的差異。

--啟用 Trace Flag 將建立資料庫等資訊寫入至 SQL LOG 中。
DBCC TRACEON(3004 ,3605 ,-1);
GO

CREATE DATABASE [testIFI]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'testIFI', 
  FILENAME = N'C:\tmp\testIFI.mdf' , 
  SIZE = 40GB)
 LOG ON 
( NAME = N'testIFI_log', 
  FILENAME = N'C:\tmp\testIFI_log.ldf' , 
  SIZE = 20GB)
GO

輸出比對：

2023-11-03 17:52:14.76 Server Database Instant File Initialization: disabled.

2023-11-03 17:54:39.78 spid64 Zeroing C:\tmp\testIFI.mdf from page 0 to 5242880 (0x0 to 0xa00000000)

2023-11-03 17:54:44.13 spid64 Zeroing completed on C:\tmp\testIFI.mdf (elapsed = 4347 ms)

2023-11-03 17:55:33.57 spid64 Zeroing C:\tmp\testIFI_log.ldf from page 0 to 2621440 (0x0 to 0x500000000)

2023-11-03 17:56:25.14 spid64 Zeroing completed on C:\tmp\testIFI_log.ldf (elapsed = 51562 ms)

2023-11-03 17:56:25.23 spid64 Starting up database 'testIFI'.

2023-11-03 17:56:25.26 spid64 Parallel redo is started for database 'testIFI' with worker pool size [1].

2023-11-03 17:56:25.26 spid64 FixupLogTail(progress) zeroing 2 from 0x5000 to 0x6000.

2023-11-03 17:56:25.26 spid64 Zeroing C:\tmp\testIFI_log.ldf from page 3 to 483 (0x6000 to 0x3c6000)

2023-11-03 17:56:25.27 spid64 Zeroing completed on C:\tmp\testIFI_log.ldf (elapsed = 7 ms)

2023-11-03 17:56:25.28 spid64 Parallel redo is shutdown for database 'testIFI' with worker pool size [1].

2023-11-03 18:04:43.74 spid63 Zeroing C:\tmp\testIFI2_log.ldf from page 0 to 2621440 (0x0 to 0x500000000)

2023-11-03 18:06:14.82 spid63 Zeroing completed on C:\tmp\testIFI2_log.ldf (elapsed = 91083 ms)

2023-11-03 18:06:14.90 spid63 Starting up database 'testIFI2'.

2023-11-03 18:06:14.94 spid63 Parallel redo is started for database 'testIFI2' with worker pool size [1].

2023-11-03 18:06:14.94 spid63 FixupLogTail(progress) zeroing 2 from 0x5000 to 0x6000.

2023-11-03 18:06:14.94 spid63 Zeroing C:\tmp\testIFI2_log.ldf from page 3 to 483 (0x6000 to 0x3c6000)

2023-11-03 18:06:14.95 spid63 Zeroing completed on C:\tmp\testIFI2_log.ldf (elapsed = 8 ms)

2023-11-03 18:06:14.96 spid63 Parallel redo is shutdown for database 'testIFI2' with worker pool size [1].

從上述的結果，你就可以看到，當 Instant File Initialization 啟用時，資料頁面 (Data Page) 是完全不作初始化的動作 (zero)，如同上述的說明，最後仍提醒在啟用此功能其實是真的可以大幅減少相關擴展的動作，但其相對的安全性，只能說可能要依不同的行業別在進行評估，如金融業等，當然存放資料的不同，可能也是要考慮，最後也希望此篇可以幫助到大家。

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

2022-04-06T12:02:00.001+08:00

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.

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

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

2022-03-10T23:28:00.006+08:00

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

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

2022-03-08T22:16:00.000+08:00

近日接到客戶需求，想要進行地端的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上進行設定即可。

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

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

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

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

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

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

2021-11-27T16:17:00.001+08:00

上一篇 (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

2021-11-27T12:01:00.006+08:00

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

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

磁碟的選擇有許多種，但在選擇磁碟種類時，通常可以選擇可用容量、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 為案例分享該如何進行。

下一篇：SQL Server 備份效能與磁碟效能分析 - II

參考連結：

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

[1]. Amazon EC2 Instance Types

https://aws.amazon.com/ec2/instance-types/

[2]. Benchmark EBS volumes

https://docs.aws.amazon.com/AWSEC2/latest/WindowsGuide/benchmark_procedures.html

雲端上動態延展的架構下，如何不影響運作的情況下，持續的進行作業系統的更新，以AWS平台架構為例

2020-02-13T02:19:00.000+08:00

在以往的運作下，如果以負載平衡的情況下進行，假設有8台電腦進行網站的負載，可以將流量導向其他台後，再將沒有流量的電腦先進行下線，然後逐一進行更新後，再將電腦重新加入進行服務。

但在雲端上，為了動態的調整所需的資源，我們可以透過 AWS Auto Scaling 的服務，自動的依據負據來動態的增加或減少伺服器的數量，但這時候問題就來了，該如何在這樣的架構下進行平日的作業系統維護與底層映像檔的更換。

在與許多人討論過，常見的方法都是將目前自行維護的映像檔再建立一台新的伺服器，然後再安裝作業系統的更新後，再作成映像檔，然後再抽換 Auto Scaling 服務中對應的映像檔。但是這樣一來通常都需要安排特定停機時間進行，而且需要花費多人來進行相關的作業。

上述的動作如果我們希望可以全部自動進行時，並儘可能的減少人工的作業，因為這種持續性的動作，人為的進行總是有疏忽的時候，讓自動的進行，才可以讓整個系統的可靠性更高。

本篇主要介紹到，希望可以透過AWS提供的眾多服務進行整合，藉以達到此需求，其實我介紹的這個方法在AWS的官網中也有介紹到，但本篇加以進行整合，然後我也特別將官網未提到的方法進行整合，並且加入排程的部份，所以我會在此文章中完整的說明並介紹給大家，也讓大家可以嘗試將這個方法導入您的環境中。

在建立前我先初步的介紹，基本上，主要是透過 SSM Automation 的服務，將指定的映像檔建立成一台新的伺服器，然後透過 SSM Run Command 進行作業系統的更新，最後再關閉伺服器，然後再進行新的映像檔的制作，最後再進行 SSM Parameter Store 參數的更新與 Auto Scaling Group 的底層映像檔的更新，基本上你可以透過下列的步驟逐一的進行即可完成初步的建置。

步驟一：建立一個Auto Scaling Group

這部份，由於後面的 AWS Lambda 程式是以 Launch Configuration 為主，所以我們也是先建立一個 Launch Configuration，後續上程式也會進行這部份的新增與抽換。

選擇預設的作業系統，由於我預期是一個全新的作業系統，所以我以 AWS 上預設的 Windows Server 2016 當成是基底進行，當然如果你有自已管理的映像檔的話，你也可以自行選擇。

建立完成範本後，再進行 Auto Scaling group 的新增，在子網路的部份，建議將目前所有的子網路區段都加入。

另外在自動成長的規則部份，我這這邊是設定為當 CPU 的使用率超過50%的時候並且超過300秒，就進行自動成長的動作。

步驟二：請先建立一個參數於 Parameter Store 中，藉以記錄目前最後更新映像檔編號 (AMI ID).

登入 Systems Manager 中心，並建立下列的參數，如下圖所示，另外名稱的部份請確認為 "latestAmi" 因為後續會使用到。

而參數中的值，請填入你目前最新維護的映像檔編號，如我的測試，我是以目前此區域中預設最新的 Windows Server 2016為此值的設定。

步驟三：建立一個 AWS Lambda執行時進行的IAM角色

登入 AWS IAM -> Roles，建立一個新的角色，並且依照下列的圖示加入相對應的權限。


AutoScalingFullAccess

AmazonSSMFullAccess

AWSLambdaExecute

此步驟中，名稱設定為：SSMAutoUpdateRoleForLambda

步驟四：建立一個 Lambda Function 藉以進行更新 Auto Scaling Group 和步驟一中建立的參數。

登入 AWS Lambda -> Create Function，如下列的圖示加入一個相對應的 Lambda Function

其中在 Execution role的部份，請選擇在步驟二中建立的 IAM 角色，建立完成後，請將下列的程式碼貼入 Function Code 的區域中存檔即可。


from __future__ import print_function



import json

import datetime

import time

import boto3



print('Loading function')





def lambda_handler(event, context):

    print("Received event: " + json.dumps(event, indent=2))



    # get autoscaling client

    client = boto3.client('autoscaling')



    # get object for the ASG we're going to update, filter by name of target ASG
    response = client.describe_auto_scaling_groups(AutoScalingGroupNames=[event['targetASG']])



    if not response['AutoScalingGroups']:

        return 'No such ASG'



    # get name of InstanceID in current ASG that we'll use to model new Launch Configuration after
    sourceInstanceId = response.get('AutoScalingGroups')[0]['Instances'][0]['InstanceId']



    # create LC using instance from target ASG as a template, only diff is the name of the new LC and new AMI

    timeStamp = time.time()

    timeStampString = datetime.datetime.fromtimestamp(timeStamp).strftime('%Y-%m-%d  %H-%M-%S')

    newLaunchConfigName = 'LC '+ event['newAmiID'] + ' ' + timeStampString

    client.create_launch_configuration(

        InstanceId = sourceInstanceId,

        LaunchConfigurationName=newLaunchConfigName,

        ImageId= event['newAmiID'] )

    

    #Updates an SSM parameter

    # get SSM client

    client2 = boto3.client('ssm')

    parameterName = 'latestAmi'



    #confirm  parameter exists before updating it

    response2 = client2.describe_parameters(

       Filters=[

          {

           'Key': 'Name',

           'Values': [ parameterName ]

          },

        ]

    )

    

    if not response2['Parameters']:

        print('No such parameter')

        return 'SSM parameter not found.'



    #if parameter has a Description field, update it PLUS the Value

    if 'Description' in response2['Parameters'][0]:

        description = response2['Parameters'][0]['Description']

        

        response2 = client2.put_parameter(

          Name=parameterName,

          Value=event['newAmiID'],

          Description=description,

          Type='String',

          Overwrite=True

        )

    

    #otherwise just update Value

    else:

        response2 = client2.put_parameter(

          Name=parameterName,

          Value=event['newAmiID'],

          Type='String',

          Overwrite=True

        )

    

    # update ASG to use new LC

    response = client.update_auto_scaling_group(AutoScalingGroupName = event['targetASG'],LaunchConfigurationName = newLaunchConfigName)



    return 'Updated ASG `%s` with new launch configuration `%s` which includes AMI `%s`.' % (event['targetASG'], newLaunchConfigName, event['newAmiID'])

另外，你也可以在右上角的部份點選測試，確認是否執行正確，測試時，名稱就隨意輸入即可，另外在下列的格式中，請取代輸入你的參考值。

如同我的範例如下。


{

  "newAmiID": "ami-02c8625672a43c5e0", 

  "targetASG": "SSM-ASG"

}

步驟五：建立一個 IAM 角色設定給當執行 SSM Automation 時，所需要的權限。

登入 AWS IAM -> Roles，建立一個新的角色，並且依照下列的圖示加入相對應的權限。


AmazonSSMAutomationRole

另外再請加入一個inline policy，並允許進行AssuleRole的進行


{

    "Effect": "Allow",

    "Action": "sts:AssumeRole",

    "Resource": "*"

}

此步驟中，名稱設定為：SSMAutomationServiceRole

步驟六：建立 SSM Automation Document 藉以進行作業系統的更新與進行製作新的映像檔

打開 AWS Systems Manager console -> Document -> Create automation.

請將下列的程式碼貼入後即可，但其中第三行的assuleRole請指定為在步驟四中新增的帳號，而28行的部份則是指定一個IAM role設定給伺服器使用，這個角把的新增方法，可以參考下列的連結。

About Policies for a Systems Manager Instance Profile
https://docs.aws.amazon.com/systems-manager/latest/userguide/setup-instance-profile.html


description: Systems Manager Automation Demo - Patch AMI and Update ASG

schemaVersion: '0.3'

assumeRole: 'arn:aws:iam::accountid:role/SSMAutomationServiceRole'

outputs:

  - createImage.ImageId

parameters:

  sourceAMIid:

    type: String

    description: AMI to patch

  targetAMIname:

    type: String

    default: 'patchedAMI-{{global:DATE_TIME}}'

    description: Name of new AMI

  targetASG:

    type: String

    description: Auto Scaling group to Update

mainSteps:

  - name: startInstances

    action: 'aws:runInstances'

    timeoutSeconds: 1200

    maxAttempts: 1

    onFailure: Abort

    inputs:

      ImageId: '{{ sourceAMIid }}'

      InstanceType: m3.large

      MinInstanceCount: 1

      MaxInstanceCount: 1

      IamInstanceProfileName: AmazonSSMManagedInstanceCore

  - name: installMissingWindowsUpdates

    action: 'aws:runCommand'

    maxAttempts: 1

    onFailure: Continue

    inputs:

      DocumentName: AWS-InstallWindowsUpdates

      InstanceIds:

        - '{{ startInstances.InstanceIds }}'

      Parameters:

        SeverityLevels: Important

  - name: stopInstance

    action: 'aws:changeInstanceState'

    maxAttempts: 1

    onFailure: Continue

    inputs:

      InstanceIds:

        - '{{ startInstances.InstanceIds }}'

      DesiredState: stopped

  - name: createImage

    action: 'aws:createImage'

    maxAttempts: 1

    onFailure: Continue

    inputs:

      InstanceId: '{{ startInstances.InstanceIds }}'

      ImageName: '{{ targetAMIname }}'

      NoReboot: true

      ImageDescription: AMI created by EC2 Automation

  - name: terminateInstance

    action: 'aws:changeInstanceState'

    maxAttempts: 1

    onFailure: Continue

    inputs:

      InstanceIds:

        - '{{ startInstances.InstanceIds }}'

      DesiredState: terminated

  - name: updateASG

    action: 'aws:invokeLambdaFunction'

    timeoutSeconds: 1200

    maxAttempts: 1

    onFailure: Abort

    inputs:

      FunctionName: Automation-UpdateAsg

      Payload: '{"targetASG":"{{targetASG}}", "newAmiID":"{{createImage.ImageId}}"}'

建立完成後，你可以透過右上角的 Execute automation進行執行，參數的部份，可以參考下列的圖示進行。

上述的動作，單次的進行後，即可完成我們想要的動作，但是當如果需要進行排程讓上述的SSM Automation Document在固定的時間進行時，我們就需要再進行下列的動作。

在排程的部份，其實大家可以找一台固定的主機進行排程進行即可，但是我希望資訊可以完整的呈現在 AWS Conole 中，所以我這邊透過 AWS Maintenance Windows進行。

步驟七：建立 Maintenance Windows 進行排程，自動進行上述的維護動作。

登入 AWS Console -> Systems Manager -> Maintenance Windows

先輸入名稱，並設定排程時間，我這邊是設定每週日的凌晨1:00進行

設定完成後，在 Tasks 的部份，請加入一個工作排程，也就是進行執行 SSM Automation 的動作，輸入的參數如下列圖示顯示。

另外關於 sourceAMIid的部份，我是透過之前建立的參數，自動的帶入，所以格式會以 ssm 開頭，這部份再請特別的注意。

由於上述的動作整合到許多 AWS 的服務，如 AWS Auto Scaling，AWS Systems Manager，AWS Lambda等功能，所以算是比較複雜的架構，但架設完成後，這樣的架構即可自動而且不斷的完成作業系統的更新並保有雲端上的彈性，所以個人非常的推薦，當然在延伸的方式上，你可以再進行更新的條件進行設定如Windows更新推出後幾天再進行安裝，或是只安裝重要更新的方式進行，這些設定都可以在 < b>SSM Systems Manager 中進行，當然如有任何問題，也歡迎提出進行討論。

參考連結：
Walkthrough: Simplify AMI Patching Using Automation, AWS Lambda, and Parameter Store
https://docs.aws.amazon.com/systems-manager/latest/userguide/automation-walk-patch-windows-ami-simplify.html

如何在AWS上進行平衡負載、網域名稱與SSL憑證(免費)的設定

2018-01-26T11:31:00.001+08:00

在前面的文章有介紹到，該如何在Azure進行WebApp的自訂網域名稱與SSL憑證購買的方式，此次我們來介紹該如何在Amazon AWS上進行相同的動作。

關於費用的部份，在AWS中申請憑證是完全免費的，而在自訂網域名稱的部份會依照你選定的網域類型而定，如.com、.org等。

https://aws.amazon.com/tw/certificate-manager/pricing/

前導文章介紹：
如何進行自訂網域與SSL憑證購買，並綁定於App Service for Web中。
http://caryhsu.blogspot.jp/2017/02/azuressl.html

開始安裝設定：
1、Web Server安裝與設定
在這邊我準備了二台Windows on EC2進行，進行前請確認各Instance可以透過IP or DNS Name正常的存取到網站。

2、Custom Domain Name申請

2-1 請先登入Route 53，並選擇 Domain Registration

2-2 選擇 Register Domain

2-3 輸入你要註冊的Domain Name先進行檢查，確認名稱是否已有被申請。

2-4 填寫個人的資訊，這部份請特別注意在聯絡資訊的部份，由其是E-Mail與電話，後續上會進行認證的動作，所以請確認填寫無誤。

2-5 最後訊息確認

2-6 訊息確認的同時下方會有E-Mail確認的資訊，此時請到你的E-Mail收件匣中進行確認。

2-7 你會收到一封如下的E-Mail確認信件，請點連結後進行回覆。

2-8 回覆後，確認E-Mail驗證的程序已完成。

2-9 回到剛剛在2-6步驟中，你會看到狀態即會進行改變。

2-10 完成註冊，此時大約仍要等一陣子，新的域名才會進行生效。

2-11 回到Route 53的Dashboard頁面，你即會看到域名的狀態，如下列所示，申請的域名即完成。

3、憑證申請
3-1 開啟ACM(AWS Certificate Manager)畫面，點選 Get started。

3-2 請在下列輸入你要申請的網域名稱，可以輸入多個。

3-3 驗證方式有二種，此步驟，我們選擇Email Validation進行。

3-4 再次確認訊息。

3-5 此時憑證申請的畫面會停在等往網域名稱確認的狀態中。

3-6 此時系統會寄出一封確認信到網域名稱的擁有者信箱中進行確認，格式如下，請點選下方 "Amazon Certificate Approvals"。

3-7 開啟網頁後，再次進行同意確認。

3-8 確認畫面。

3-9 回到ACM(AWS Certificate Manager)，你就會看到憑證完成。

4、ALB(Application Load Balancer)設定
4-1 進入Load Balancers的畫面，選擇建立Application Load Balancer。

4-2 選擇Listeners的Protocol類型與AZ(Availability Zones)區域，由於我的二台主機放在us-east-1c與 us-east-1d，所以我就先選擇這二個區域。

4-3 憑證的部份，我們直接透過ACM(AWS Certificate Manager)進行選擇，系統即會帶出我在步驟3所建立的憑證。

4-4 選擇Security Groups，請記得確認是否有開啟HTTP(80)與HTTPS(443)。

4-5 由於我們希望ALB(Application Load Balancer)支援HTTP與HTTPS，所以這部份選擇HTTP即可。

4-5 ALB的Group主機請將你的主機加入，請記得不是只有勾選，要選擇Add to Registered加入後才算是。

4-6 最後訊息確認

4-7 建立完成後，我們同時透過ALB的DNS進行確認，看是否可以正確的導向二台主機。

5、Custom Domain Name與ALB(Application Load Balancer)的綁定。
5-1 點選網域名稱

5-2 點選Create Record Set

5-3 此時我們將ALB的DNS輸入在下方的區域，然後點選完成。

5-4 最後，我們透過申請的網址名稱與HTTPS的方式進行存取，確認皆可看到連線都正常。

參考連結：
Request a Certificate
https://docs.aws.amazon.com/acm/latest/userguide/gs-acm-request.html
Create an Application Load Balancer
https://docs.aws.amazon.com/elasticloadbalancing/latest/application/create-application-load-balancer.html
HTTPS Listeners for Your Application Load Balancer
https://docs.aws.amazon.com/elasticloadbalancing/latest/application/create-https-listener.html
Routing Traffic to an ELB Load Balancer
https://docs.aws.amazon.com/Route53/latest/DeveloperGuide/routing-to-elb-load-balancer.html

如何在AWS EC2上以Windows Server 2016建立Microsoft Storage Spaces Direct(S2D)的架構

2018-01-12T15:37:00.000+08:00

相信大家對S2D的架構或多或少都知道，其實在Windows 2012 R2的時候就已經有推出支援，但在Windows 2016上又有許多的更新，相關的安裝方式其實在網路上已有許多的參考，所以這篇我要特別整理如何在AWS EC2進行建立，當然安裝上沒有太大的落差，但在一些細部的設定上需要特別的注意，我在此整理出來，也提供給大家參考。

S2D系統架構圖

1、環境準備
AD Server -> Windows 2012 R2
Cluster Node1 -> Windows 2016
Cluster Node2 -> Windows 2016

另外在上述的二個節點上增加各二顆Disk，然後請注意在容量上請選擇相同的大小。

2、環境安裝
2-1 Instance Type選擇
在一開始的的Instance Type的部份，請注意目前測試C5可以進行，因為根據下列的S2D的需求項目一定要符合，要不然在最後建立完Cluster後，當進行Enable-ClusterS2D會出現下列的錯誤訊息，這部份需要再進行注意。

錯誤訊息：
Enable-ClusterS2D:No disks with supported bus types found to be used for S2D.
FullyQualifiedErrorId : HRESULT 0x80070490, Enable-ClusterStorageSpacesDirect

Driver:
Use local-attached SATA, SAS, or NVMe drives.

2-2 Private IP設定

在節點的部份，請特別注意最好設定Private IP在下列的位置上。

3、節點設定與確認
開機登入後，從系統 -> 裝置管理員 -> 確認目前的Disk Drives是否皆為NVMe的類型，如下圖所示。

在建立完成後，請將二個節點分別加入到Cluster中，並確認狀態皆為UP。

4、啟用與設定S2D
4-1 透過Administrator開啟一個PowerShell視窗，並輸入 Enable-ClusterS2D 的功能。

執行完成後，你就可以從 Failover Cluster Manager -> Storage -> Pools中看到已建立一個Cluster Pool。

4-2 透過下列的語法確認目前可能的磁碟。

get-StorageSubSystem Clus* | get-PhysicalDisk

4-3 透過下列的指令進行Volume的建立

New-Volume -StoragePoolFriendlyName S2D* -FriendlyName SQLDataDisk1 -FileSystem CSVFS_NTFS -AllocationUnitSize 65536 -Size 50GB -ProvisioningType Fixed -ResiliencySettingName Mirror

執行過程

4-4 最後建立完成，你就可以到Failover Cluster Manager -> Storage -> Disks的部份看到。

參考連結：
Microsoft SDS 軟體定義儲存技術(2) - S2D 運作架構
https://technet.microsoft.com/zh-tw/mt684933.aspx
Storage Spaces Direct hardware requirements
https://docs.microsoft.com/en-us/windows-server/storage/storage-spaces/storage-spaces-direct-hardware-requirements

如何快速在AWS EC2與AWS Microsoft AD上建立SQL Server AlwaysOn。

2018-01-02T13:55:00.001+08:00

在之前的文章，我介紹過許多安裝SQL Server AlwaysOn的方式，但現在我來介紹一個快速安裝的方式，可以在AWS EC2上快速的透過範本(CloudFormation)進行建立所需要的SQL Server AlwaysOn的架構。

另外在安裝上由於透過AWS Microsoft AD與SQL Server AlwaysOn這二個選項安裝時，在建立Availability Group Listener會遇到一些問題，所以本篇也特別說明如何排除，再請參考。

建立步驟：
一、環境安裝與建立
1、請先點選下列的網址 -> Launch Quick Start (for New VPC)，進行建立。

SQL Server on the AWS Cloud: Quick Start Reference Deployment
https://docs.aws.amazon.com/quickstart/latest/sql/welcome.html

2、透過範本的方式快速的建立，在此請先確定你要建立的Regions為何，另外可以在下方看到範本的位址，當然如果你有興趣，也可以自行下載進行修改與調整。

3、在此由於我以簡單的二個節點進行安裝，所以在Availability Zones我只選擇二個，另外關於每個節點的劃分，如沒有特別的規劃，可以使用預設值即可。

4、選擇自已的Key Pair，此處可以選擇已存在此Regions的Key Pair，如果沒有新增的話，請記得先行設定。

5、在此處，我們選擇使用AWS Directory Service for Microsoft AD，當然你也可以選擇Microsoft AD on Amazon EC2的方式進行，此時系統會自行建立二台EC2 for AD進行。

6、選擇Microsoft SQL Server的版本，目前可以選擇的有2014、2016、2017的版本可以選擇。

7、最後勾選確認後，即可開始進行。

8、切換到CloudFormation，確認是否已建立完成，約不到30分鐘的時間，一個AD服務與四台EC2都已建立完成。

9、切換到EC2的頁面，你也可以看到4台EC2也確認都建立完成了。另外RDGW是主要負責對外連線的電腦，所以我們要透過此電腦進行連線。

10、透過RDGW，然後再連到Node1進行確認，你可以看到此時二台的SQL Server皆已安裝完成。

二、AlwaysOn建立與測試
1、請先手動建立一個測試用的資料庫，並請確認複原模式為Full，建立完成後也請手動進行一次完整備份。

2、此時如果你直接進行AlwaysOn安裝的話，由其是在建立Listener，你會遇到下列的錯誤，所以我們要先作一些設定。

錯誤訊息：
Create failed for Availability Group Listener 'carylistener'.

The WSFC cluster could not bring the Network Name resource with DNS name 'carylistener' online. The DNS name may have been taken or have a conflict with existing name services, or the WSFC cluster service may not be running or may be inaccessible. Use a different DNS name to resolve name conflicts, or check the WSFC cluster log for more information.

PS:此問題我原先是懷疑是否是已有存在相同的名稱，但是一來這是新建的AD，二來透過不同的名稱建立也是相同的情況，所以排除此情況的可能性。

3、此時我先透過RDGW的機器安裝AD的管理工具，藉以進行管理AWS Microsoft AD。

4、安裝完成後，請開啟Active Directory Users and Computers，並連線到AWS Microsoft AD -> AD的OU名稱 -> Computer。

5、選擇新增Computer物件。

5、啟用進階設定。

6、選擇建立好的Listener -> Properties -> Security -> Add -> Cluster Name -> 並請將下列的權限進行賦予，或是你也可以直接給予Full Control的權限。

建議權限：
Read
Allowed To Authenticate
Change Password
Receive As
Reset Password
Send As
Validate write To DNS Host Name
Validate Write To Service Principle Name
Read Account Restrictions
Write Account Restrictions
Read DNS Host Name Attributes
Read MS-TS-GatewayAccess
Read Personal Information
Read Public Information

7、連線到主要的節點上，選擇Availability Groups -> New Availability Group Wizard

8、在Replicas的部份請記得將二個節點都加入，然後切換到Listener的部份，名稱請使用在第5個步驟中設定的名稱，而IP請選擇各節點中的Secondary Proviate IPs中的第二個加入。

第一個節點

第二個節點

9、確定建立完成。

10、檢查並確認Listener建立完成。

上述的方式提供給大家不同安裝AlwaysOn的方式，依照上述的設定，其實真的不用10分鐘就可以建立完成(不包含等待的30分鐘)，而且跟自已手動建立的，真的沒有其他的不同，推鑑下次大家可以參考看看。

參考連結：
SQL Server on the AWS Cloud: Quick Start Reference Deployment
https://docs.aws.amazon.com/quickstart/latest/sql/welcome.html
Create Listener Fails with Message ‘The WSFC cluster could not bring the Network Name resource online’
https://blogs.msdn.microsoft.com/alwaysonpro/2014/03/25/create-listener-fails-with-message-the-wsfc-cluster-could-not-bring-the-network-name-resource-online/
Create Availability Group Listener Fails with Message 19471, ‘The WSFC cluster could not bring the Network Name resource online’
https://blogs.msdn.microsoft.com/alwaysonpro/2013/10/30/create-availability-group-listener-fails-with-message-19471-the-wsfc-cluster-could-not-bring-the-network-name-resource-online/

如何在AWS透過EC2建立自動彈性延伸的網站

2017-12-22T13:53:00.001+08:00

在傳統的網站部份，如果針對特定的節日或進行行銷時，可能網站會有較多的流量，此時原先的主機可能無法承受負載，所以通常會進行網站設備上的擴充，但是在時間過了之後，可能就沒有這麼多的使用者與流量時，就會造成設備的閒置與浪費，所以此時雲端是一個很好的選擇。

在各家的雲端上建置IaaS的部份VM皆可以選擇不同的Instance Type，當然我們可以在不同的時間點進行切換，比如當有節日或活動要進行時，我們可以彈性的調整Instance Type，但目前在AWS EC2上有不同的選擇，您可以透過Auto Scaling與Load Balance的功能整合，藉以達到快速的Scale Out，來更合適的完成公司的商業需求。

建置方式：
1、建立環境所需的影像檔

1-1 先針對你的網站設計好需要的網頁與功能，然後制作成一個Image.
1-2 方法很簡單，請到EC2 -> Instances -> 選擇你要制作的Instance -> Image -> Create Image

輸入Image的名稱與預設的Disk

送出Create Image的請求，需至下一步驟進行確認是否已建立完成。

1-3 建立完成後，請到Images -> AMIS -> 確認Image是否已建立完成。

2、建立Load Balancing

由於在後續上會有多個Instance Type自動進行建立，而且也需要進行負載平衡，所以此步驟我們建立一個Load Balanc er進行。

2-1 撰擇Load Balancing -> Load Balancers -> Create Load Balancer

2-2 在選擇Load Balancer時有三種項目可能選擇，這三種的選擇上看你的應用程式類型選擇Application Load Balancer或Network Load Balancer，基本上不建議再選擇第三種傳統模式，此部份我們選擇第二種高效能的Network Load Balancer進行。

2-3 決定NLB的名稱與開放的Port、如果您的網站有使用HTTPS的話，請將443也一同加入。

2-4 指定在Region中特定的AZ(Availability Zones)區域。

2-5 定義Target Group去指定特定的Instance。

2-6 指定特定的Instance到此Target Group中，但由於我們後續會由Auto Scaling自行啟動，所以此處不進行手動加入。

2-7 建立完成。

3、建立Auto Scaling

3-1 建立 Auto Scaling Lunch Configuration
3-1-1 選擇Auto Scaling -> Launch Configuration -> 建立Auto Scaling

3-1-2 進入後，頁面說明會有二個步驗，分別是建立設定檔與群組的部份，首先我們就先建立設定檔的動作。

3-1-3 選擇我們在步驟1-3之中所建立的Image。

3-1-4 選擇後續自動延伸時所建立的Instance Type

3-1-5 輸入Launch Configuration的名稱，建議也可以勾選透過CloudWatch進行監控。

PS:由於啟用CloudWatch進行監控時可能會有額外的費用產生，所以再請注意。

3-1-6 選擇啟動後的Instance上的Disk大小。

3-1-7 設定Security Group，此部份我只有開啟80(HTTP)與3389(RDP)二個，當然你也可以將443(HTTPS)加入，由於我之前已有設定的Security Group符合，所以我就將之前的加入。

3-1-8 再次檢示Launch Configuration的設定並確認建立。

3-1-9 設定你key pair的檔案，此處由於之前也設定過了，所以可以使用相同的檔案即可。

3-2 建立Auto Scaling Group

3-2-1 輸入Group Name與預計進行Scaling的網路區段，你可以將多個區段加入，藉以避免因單一區段發生問題時，整體無法運作，此區請至少加入一個Network與Subnet。

在進階設定的部份，請將第2步驟中建立的NLB加入，並將請將Health Check Type設定為預設的EC2，請為這樣我們就可以透過CPU與或網路流量來設定條件，藉以達到自動延伸的功能。

3-2-2 此部份由於我們要模擬當使用者過多時，CPU使用率過高，造成網站回應過慢，所以我先設定為1-5個Instance，當使用率超過50%的時候，就自動進行Scal Out。

3-2-3 設定警告通知，當遇到勾選的事件產生時，即會自動寄出通知。

3-2-4 最後檢示Auto Scaling Group的設定

3-2-5 建立完成

3-3 測試網站連結

3-3-1 建立完成後，我們可以到Auto Scaling -> Auto Scaling Groups 確認目前已自動啟動的Instance為何。

3-3-2 接下來我們同樣的透過Load Balancer的DNS來連線到網站，確認是否可以正常的連線。
請到Load Balancing -> Load Balancers -> 已建立的NLB -> Description中，即可看到。

3-3-3 測過此網址進行確認是否可以正常的連線。

4、Auto Scaling測試

4-1 由於我們在3-2-2之中，設定Auto Scaling的規則為CPU使用率超過50%，所以最快的方式我使用了測試軟體(Intel Brun Test)去直接的快速提高CPU的使用率。

4-2 經過一段時間，系統就會自動再產生一個Instance，然後自動加入NLB中。

4-3 最後，由於CPU 使用率持續不斷的增加，所以Instance數量成長到我設定的最大值。

4-4 Instance 的成長區間圖

4-5 我透過NLB的DNS網址進行連結測試，你會發現五個Instance皆會輪序的進行訪問，當然如果你要確認他有連結到那一台Instance的話，你可以逐一連到Instance中，然後修改頁面即可確認。

上述的介紹，你可以透過Auto Scaling與Elastic Load Balancing的整合，藉以快速的達到Scale Out的需求，也可以達到效益的最佳化，當然此部份後續仍有其他可以研究的部份，如程式的修改與部署的部份，我們將會在後續的章節繼續再跟大家介紹，謝謝。

關鍵字：
Amazon EC2、Elastic Load Balancing、Auto Scaling、AWS

參考連結：
Getting Started with Auto Scaling
http://docs.aws.amazon.com/autoscaling/latest/userguide/GettingStartedTutorial.html
Elastic Load Balancing
https://aws.amazon.com/tw/elasticloadbalancing/
What Is Elastic Load Balancing?
http://docs.aws.amazon.com/elasticloadbalancing/latest/userguide/what-is-load-balancing.html

如何透過Web Deploy工具進行網站的轉移 - 舊版網站轉換(IIS6 -> IIS7 Up)

2017-11-28T18:42:00.001+08:00

常見的當我們要進行網站的昇級、搬移或同步時，常需要將原先的網站同步到其他台的電腦上，所以本篇透過Web Deploy的工具整理一些常見的情況來說明如下，後續上如有遇到的話，也可以藉以參考，直接使用。

由於章節過多，所以我將此Web Deploy的部份分成幾個章節進行說明。在進行說明前，由於需要進行網站ID的設定，所以先說明在IIS上，該如何找到自已所需的ID編號，如下圖，點選站台後，即可看到。

識別方式：

舊版網站轉移：
目前在Windows 2003上，其實有下列的工具(IEMT)可以進行轉換，工具的使用方法很簡單，所以就不特別介紹，但有時候可能會發生無法轉換的情況，所以這時候就是將Web Deploy拿出來用的時候了。

轉換工具：
IIS Easy Migration Tool (IEMT)
https://www.iis.net/downloads/community/2013/04/iis-easy-migration-tool-iemt

在安裝上，由於Win2003只能安裝舊版本，在測試上只能安裝Web Deploy 1.1的版本。

工具下載：
1、Web Deploy 1.1(for Win2003)：

https://www.microsoft.com/en-us/download/details.aspx?id=24709
2、Web Deploy 3.6(Windows 2012 R2)：

https://www.iis.net/downloads/microsoft/web-deploy

在安裝後，請先確認下列的事項：
1、Web Deployment Agent Service是否有啟動。
2、由於彼此是透過Port 80進行，所以請確認是否有開啟。
3、如果要透過二台電腦直接進行同步時，請確認執行的身份，建議可以透過Domain Account進行。

執行同步方式：
1、登入目的端電腦。
2、開啟Dos Command視窗，切換到 C:\Program Files\IIS\Microsoft Web Deploy
3、輸入下列的指令：


msdeploy -verb:sync -source:metakey=lm/w3svc/1,computername=cary-srcweb -dest:metakey=lm/w3svc/1 -enableLink:AppPoolExtension -verbose

上述的指令在目的端電腦執行時，會將來源端的電腦(cary-srcweb)IIS站台中的ID編號1的站台，同步到目的端的ID編號1的站台上，-enableLink:AppPoolExtension會確認當AppPool不存在時，會自動建立，最後-verbose主要是指定執行過程中會輸出詳細的執行過程。

另外你也可以透過封存的方式進行，先將來源端上的網站封存成一個壓縮檔，然後再到目的地端上進行還原的動作。

執行封存的方式：
1、登入來源端電腦。
2、開啟Dos Command視窗，切換到 C:\Program Files\IIS\Microsoft Web Deploy
3、輸入下列的指令：


msdeploy -verb:sync -source:metakey=lm/w3svc/1 -dest:package=c:\share\20170524weblist.zip,encryptPassword=12345 -enableLink:AppPoolExtension -verbose

還原封存的方式：
1、登入目的端電腦。
2、開啟Dos Command視窗，切換到 C:\Program Files\IIS\Microsoft Web Deploy
3、輸入下列的指令：


msdeploy -verb:sync -source:package=c:\share\20170524weblist.zip -dest:
metakey=lm/w3svc/1,encryptPassword=12345 -enableLink:AppPoolExtension -verbose

參考網址：
Synchronize IIS 6.0 Web Sites
https://docs.microsoft.com/en-us/iis/publish/using-web-deploy/synchronize-iis-60-web-sites
Migrate a Web Site from IIS 6.0 to IIS 7 or above
https://docs.microsoft.com/en-us/iis/publish/using-web-deploy/migrate-a-web-site-from-iis-60-to-iis-7-or-above

CaryHsu - 學無止盡

SQL Server 叢集版本升級問題與相關注意檢查事項

SQL Server 載入防毒軟體模組的處理

SQL Server CPU 在 NUMA 節點上，CPU 負載不平衡的處理

如何在 SQL SERVER 標準版上架設 AlwaysOn 的唯讀複本-超級省錢架構

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

如何進行 SQL Server 效能資料收集與分析 - 以 LogScout, PSSDiag, SQL Nexus 工具為例

如何設定無網域與無叢集架構的 SQLServer AlwaysOn - 簡易可讀取性複本

如何建立 SQL Server AlwasyOn 分散式可用性群組 - 進階高可用性

Windows 資料收集工具介紹 - TroubleShootingScript toolset (TSS)

設定 Kerberos 驗證與 SQL Server 連線與問題排除

SQL Server 2019 容錯移轉叢集環境架設 - 整合 Windows 2022 iSCSI Target Server 的功能

如何透過 Partition Table 進行資料分散與維護作業並以年度為例

SQL Server 啟動帳號最佳實踐與最小化權限設定

啟用即時初始化檔案 (Instant file initialization) 的功能於 SQL Server 上

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

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

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

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

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

雲端上動態延展的架構下，如何不影響運作的情況下，持續的進行作業系統的更新，以AWS平台架構為例

步驟一：建立一個Auto Scaling Group

步驟二：請先建立一個參數於 Parameter Store 中，藉以記錄目前最後更新映像檔編號 (AMI ID).

步驟三：建立一個 AWS Lambda執行時進行的IAM角色

步驟四：建立一個 Lambda Function 藉以進行更新 Auto Scaling Group 和步驟一中建立的參數。

步驟五： 建立一個 IAM 角色設定給當執行 SSM Automation 時，所需要的權限。

步驟六： 建立 SSM Automation Document 藉以進行作業系統的更新與進行製作新的映像檔

步驟七：建立 Maintenance Windows 進行排程，自動進行上述的維護動作。

如何在AWS上進行平衡負載、網域名稱與SSL憑證(免費)的設定

如何在AWS EC2上以Windows Server 2016建立Microsoft Storage Spaces Direct(S2D)的架構

如何快速在AWS EC2與AWS Microsoft AD上建立SQL Server AlwaysOn。

如何在AWS透過EC2建立自動彈性延伸的網站

如何透過Web Deploy工具進行網站的轉移 - 舊版網站轉換(IIS6 -> IIS7 Up)

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

步驟五：建立一個 IAM 角色設定給當執行 SSM Automation 時，所需要的權限。

步驟六：建立 SSM Automation Document 藉以進行作業系統的更新與進行製作新的映像檔