如何在AlwaysOn建立Partition Table與自動進行資料平均分配

自從SQL Server 2005，本身就有加入Partition Table的功能，也就是可以將一個資料表的資料分散到其他的磁碟上，藉以加快讀寫速度，但可惜的事，由於SQL Server只有支援Range Partition的功能，也就是將特定資料範圍的部份放在特定的磁上，但使用起來感覺還像只有封存的作用，而且特定時間後就要再進行一次封存，不像Oracle本身就有支援四種分割的方法好用，所以為了解決這個問題我整理了這篇分享出來，也希望大家可以參考。

另外說明我的測試是在AlwaysOn上的主要節點進行的，其實在AlwaysOn上只需特別注意在建立Data File時的路徑每一個節點都需要相同，其他的部份與在單機上進行皆相同。


1、加入Data File and File Group
在建立上由於我的主要是四核心，所以我就建立四個File Group，而其中各File Group也各自有一個Data File。

PS：如需進一步了解核心數與Data File的關係，可以參考我的另一篇文章。
SQL Server 儲存設備 (Storage) 最佳調整作業
http://caryhsu.blogspot.tw/2011/02/sql-server-io.html

1-1 建立的方式可以透過GUI進行設定，或是透過T-SQL進行。

1-2 透過T-SQL進行
USE [master]
GO
ALTER DATABASE [AdventureWorks2012] ADD FILEGROUP [ag_group_1]
GO
ALTER DATABASE [AdventureWorks2012] ADD FILEGROUP [ag_group_2]
GO
ALTER DATABASE [AdventureWorks2012] ADD FILEGROUP [ag_group_3]
GO
ALTER DATABASE [AdventureWorks2012] ADD FILEGROUP [ag_group_4]
GO
ALTER DATABASE [AdventureWorks2012] 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 [AdventureWorks2012] 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 [AdventureWorks2012] 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 [AdventureWorks2012] 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、加入平均計算函數
前面有說過，由於Partition Table目前只有Range Partition，所以內建的方法並無法達到平均分配，所以我在這邊提供了一個方式，就是在表格中加入一個實體的計算欄位(Persisted)，然後透過此欄位來進行分配。

因為我是透過 AdventureWorks2012 中的TransactionHistory進行，而因為此表是透過流水號當主鍵，所以我就以此TransactionID當成計算參數之一，設定如下。

PS：
a. 當然如果沒有流水號可以選擇的話，你也可以透過 row_number 的參數進行。
b. 上述的計算欄位的(Is Persisted)一定要是Yes，否則會發生下列的錯誤。

Msg 7724, Level 16, State 1, Line 23
Computed column cannot be used as a partition key if it is not persisted. Partition key column 'pf_val' in table 'Production.TransactionHistory' is not persisted.

3、建立Partition Table
3-1 選擇要建立的Table Name -> Storage -> Create Partition

3-2 下一步

3-3 選擇要進行分配的欄位參考，我們這裡以新增的計算欄位進行。

3-4 設定Partition Function名稱

3-5 設定Partition Scheme名稱

3-6 這一頁要特別注意不要被騙了，這個部份我作了好多次，雖然你看到第一欄只有分配到一筆，但是做完後即可完平均分配。

另外最後一列由於雖然我們會分成四個區塊，但由於系統設計因素，所以必須提供另外一個區塊存放(但不會有資料)，所以我們就選預設的File Group。

3-7 選擇立即執行

3-8 點選完成

3-9 執行完成

3-10 執行完成後，我們可以透過DMV來查平均分配的結果，如下所示，的確如我們預期進行，後續上的資料新增後也會依照我們的規劃循序放置，這樣一來就不需要固定時間後又進行一次調整，建議大家可以參考看看。

執行語法：
--查詢所有Partition Table資訊
select     object_schema_name(i.object_id) as [schema],  
object_name(i.object_id) as [object_name],    t.name as [table_name],  
i.name as [index_name],    s.name as [partition_scheme]
from sys.indexes i    join sys.partition_schemes s on
i.data_space_id = s.data_space_id    join sys.tables t on
i.object_id = t.object_id

--查詢特定Partition Table的分配情況
SELECT
OBJECT_Name(object_id),
row_count,
(used_page_count * 8.0 / 1024) usedMB
FROM sys.dm_db_partition_stats
where OBJECT_Name(object_id) = 'TransactionHistory'
and index_id = 0



參考連結：
Computed Columns
https://technet.microsoft.com/en-us/library/ms191250(v=sql.105).aspx
Partitioning
https://technet.microsoft.com/en-us/library/ms178148(v=sql.105).aspx
Partitioned Tables and Indexes
https://docs.oracle.com/html/A96524_01/c12parti.htm


關鍵字：Partitioning Methods、Hash Partitioning、Range Partitioning、Partition Table、資料分割