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

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

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

在此我們介紹一個好用，也是目前 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 連線與問題排除

        最近遇到許多關於 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 的驗證模式，必須符合下列二個條件。

1. 用戶端與主機都在相同的網域或在信任的網域中。
2. 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 的功能

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

前篇介紹：

1. SQL Server 2008 R2 容錯移轉叢集環境架設 - 利用 VM 與 Windows Storage Server - Part I
2. SQL Server 2008 R2 容錯移轉叢集環境架設 - 利用 VM 與 Windows Storage Server - Part II
3. SQL Server 2008 R2 容錯移轉叢集環境架設 - 利用 VM 與 Windows Storage Server - Part III(終)
4. 容錯移轉叢集中多個節點切換順序的設定

AlwaysOn相關設定文章：

1. 在混合雲的架構上建立完整的AlwaysOn架構
2. SQL Server 2012 新功能 - 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 進行資料分散與維護作業並以年度為例

        先前一篇針對 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 啟動帳號最佳實踐與最小化權限設定

        此篇文章主要是最近經過二位大師的指導，修正一些以前的概念，自已嘗試作過一次後，發現其中其實有許多的坑，沒有作過一次，真的很難說有 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 上

        即時初始化檔案 (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: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.

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)，如同上述的說明，最後仍提醒在啟用此功能其實是真的可以大幅減少相關擴展的動作，但其相對的安全性，只能說可能要依不同的行業別在進行評估，如金融業等，當然存放資料的不同，可能也是要考慮，最後也希望此篇可以幫助到大家。