CaryHsu - 學無止盡

2024年4月2日星期二

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

最近協助客戶進行升級的動作，但客戶表示已有升級多台 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$

2024年2月13日星期二

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

防毒軟體相信在很多的主機上都會安裝，在 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 進行分析，找出此問題，所以再次強烈的推薦給大家。

2024年1月31日星期三

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

最近接到客戶反應，為何 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 進行討論與確認。

2023年12月29日星期五

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

在 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'.

訂閱：文章 (Atom)

2024年4月2日 星期二