事務複製中的分區表

標籤：style   blog   http   color   使用   os   

背景

  事務複製中發行資料表有分區表，如何配置發布項，使分區結構傳播到訂閱庫？有何限制？

測試環境

CodeUSE [master]GOCREATE DATABASE [OMS_Test] ON  PRIMARY ( NAME = N‘OMS_Test_data1‘, FILENAME = N‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\OMS_Test.mdf‘ ,   SIZE = 34816KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),  FILEGROUP [FG_OMSTest_1] ( NAME = N‘OMS_Test_data2‘, FILENAME = N‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\OMS_Test_data2.ndf‘ ,   SIZE = 475136KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),  FILEGROUP [FG_OMSTest_2] ( NAME = N‘OMS_Test_data3‘, FILENAME = N‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\OMS_Test_data3.ndf‘ ,   SIZE = 611328KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),  FILEGROUP [FG_OMSTest_3] ( NAME = N‘OMS_Test_data4‘, FILENAME = N‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\OMS_Test_data4.ndf‘ ,   SIZE = 245760KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N‘OMS_Test_log‘, FILENAME = N‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\OMS_Test_log.ldf‘ ,   SIZE = 5093952KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)GOuse OMS_Testgocreate  partition function pf_orders_createon(datetime2)as range  right forvalues(‘20120101‘,‘20130101‘,‘20140101‘)gocreate   partition scheme ps_orders_createonas partition pf_orders_createonto ([primary],[FG_OMSTest_1],[FG_OMSTest_2],[FG_OMSTest_3])gocreate  table orders (ID INT IDENTITY(1,1),OrderNumber varchar(20) not null,CreateOn datetime2 not null)on [ps_orders_createon](CreateOn)goalter table orders add constraint PK_Orders_ID_CreateON primary key nonclustered (ID,CREATEON)gocreate clustered index CIX_Orders_CreateOn on Orders(CreateOn)on [ps_orders_createon](CreateOn)go

載入測試資料之後的資料分布如下：

 

 

前置條件

• 如果資料分割配置使用的不只是PRIMARY檔案組，首先要保證分發和訂閱具有同樣的檔案組。所以我的測試中訂閱庫會添加同樣三個檔案組。
• 在添加分區表為發布項時，需要指定Copy table partitioning schemes和Copy index partitioning schemes為True,Copy file group associations也設定為True。三者的含義就是字面意思。

             

 

流量分析

    1. 滿足“前置條件”建立的事務複製，分區表(包括資料、分區函數和分區架構)成功發布並傳播到訂閱庫。但是下列命令不會從發布庫傳播到訂閱庫：

         ALTER INDEX 的 ALTER PARTITION FUNCTION、ALTER PARTITION SCHEME 或 REBUILD WITH PARTITION。

       在訂閱庫執行如下語句，新增的資料可以傳播到訂閱庫，但是Partition Function & Scheme的修改沒有傳播到訂閱庫：

Codealter partition scheme ps_orders_createonnext used [PRIMARY]GOalter partition function pf_orders_createon()split range(‘20140201‘)GOinsert into dbo.ordersvalues (‘140714001074904685‘,‘2014-07-15 04:06:19.6600000‘)GO

      需要在訂閱庫執行上面的兩個Alter，才能與發布庫一致。如果使用是非PRIMARY檔案組，則還需要在訂閱庫上先增加同樣的檔案組。

分割分區後，發布和訂閱的資料分布是一樣，如下：

 

2. 對事務複製中的分區表啟用PARTITION SWITCH，使得發布庫PARTITION SWITCH傳播到訂閱庫，需要滿足如下條件：

• 在對訂閱端執行Switch前，需要保證訂閱端面也存在需要移入或者移出的表和資料。比如將分區表中的某個分區切到中間表，而兩者需要存在同樣結構的中間表。
• 發布和訂閱庫的分區表，需要具有同樣的分區表定義。因為訂閱庫的分區表可以與發布庫的定義不一致，但如果需要發布庫PARTITION SWITCH傳播到訂閱庫，則要一致。
• 發布屬性中@allow_partition_switch和@replicate_partition_switch設定為TRUE。

      前者允許對發行的資料庫執行 ALTER TABLE…SWITCH 語句，後者使得對發行的資料庫執行的 ALTER TABLE…SWITCH 語句傳播到訂閱伺服器。

  執行如下語句修改發布屬性並將Partition 4切到暫存資料表：

CodeUSE [OMS_Test]GO--Set Publication properties for partition switchexec sp_changepublication ‘OMSTEST_PUB‘,N‘allow_partition_switch‘,N‘true‘;exec sp_changepublication ‘OMSTEST_PUB‘,N‘replicate_partition_switch‘,‘true‘;go--create table for partition switch outCREATE TABLE [dbo].[orders_Temp]([ID] [int] NOT NULL,[OrderNumber] [varchar](20) NOT NULL,[CreateOn] [datetime2](7) NOT NULL, CONSTRAINT [PK_OrdersTemp_ID_CreateON] PRIMARY KEY NONCLUSTERED ([ID] ASC,[CreateOn] ASC))on [FG_OMSTest_3]GOCREATE CLUSTERED INDEX CIX_OrdersTemp_CreateON on dbo.orders_temp(createon)goALTER TABLE dbo.orders_Temp ADD CONSTRAINTCK_orders_Temp CHECK (CreateOn>=‘2014-01-01‘ and CreateOn<‘2014-02-01‘)go--Switch partition 4 to orders_tempALTER TABLE dbo.orders   switch partition 4 to dbo.orders_tempgo

執行後，訂閱和發布的資料都成功切出。現在再嘗試載入分區，也就是切回來，結果也是成功的：

CodeALTER TABLE dbo.orders_temp   switch  to dbo.orders partition 4go

3. 對2.的一點小變通，也是我最想要的一個功能：發布可以利用Switch移出過時分區進行曆史歸檔，而訂閱不受影響仍然保留過時的曆史資料用於查詢或者備份，且事務複製正常。

   發布屬性中@allow_partition_switch和@replicate_partition_switch，將前者設定為true,後者設定為false。這樣允許對發行的資料庫執行 ALTER TABLE…SWITCH ，但不傳播到訂閱庫。

   現在我假設Partiton 4到期了，需要移出到暫存資料表，然後再進行歸檔處理，但我希望訂閱庫的Partition 4不被切出，且事務複製不受影響。

CodeUSE [OMS_Test]GO--Set Publication properties for partition switchexec sp_changepublication ‘OMSTEST_PUB‘,N‘allow_partition_switch‘,N‘true‘;exec sp_changepublication ‘OMSTEST_PUB‘,N‘replicate_partition_switch‘,‘false‘;goALTER TABLE dbo.orders   switch partition 4 to dbo.orders_tempgoinsert into dbo.ordersvalues (‘140714001074904686‘,‘2014-07-16 04:06:19.6600000‘)GO

執行完後，一切都如預期

發布的分區表結構: Partition 4成功切出去了

 

訂閱的分區表結構：發布的Switch並為對它造成影響

 

新增的記錄 ‘140714001074904686‘ 也成功的從發布端傳播到了訂閱端。事務複製運行也正常。

 

總結

1. 事務複製中的分區表，對發布和項目屬性做幾個小的更改，然後就可以把它當作普通分區表來管理了。

2. 事務複製實現讀寫分離的架構中，發布庫中的分區表需要將非活躍資料做歸檔用以提升效能，而某些應用又需要查詢全部的資料，配置一下發布屬性也能符合使用。