背景
事务复制中发布表有分区表,如何配置发布项,使分区结构传播到订阅库?有何限制?
测试环境
USE [master]
GO
CREATE 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%)
GO
use OMS_Test
go
create partition function pf_orders_createon(datetime2)
as range right for
values('20120101','20130101','20140101')
go
create partition scheme ps_orders_createon
as partition pf_orders_createon
to ([primary],[FG_OMSTest_1],[FG_OMSTest_2],[FG_OMSTest_3])
go
create table orders (ID INT IDENTITY(1,1),OrderNumber varchar(20) not null,CreateOn datetime2 not null)
on [ps_orders_createon](CreateOn)
go
alter table orders add constraint PK_Orders_ID_CreateON primary key nonclustered (ID,CREATEON)
go
create 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的修改没有传播到订阅库:
alter partition scheme ps_orders_createon
next used [PRIMARY]
GO
alter partition function pf_orders_createon()
split range('20140201')
GO
insert into dbo.orders
values ('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切到临时表:
USE [OMS_Test]
GO
--Set Publication properties for partition switch
exec 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 out
CREATE 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]
GO
CREATE CLUSTERED INDEX CIX_OrdersTemp_CreateON on dbo.orders_temp(createon)
go
ALTER TABLE dbo.orders_Temp ADD CONSTRAINT
CK_orders_Temp CHECK (CreateOn>='2014-01-01' and CreateOn<'2014-02-01')
go
--Switch partition 4 to orders_temp
ALTER TABLE dbo.orders
switch partition 4 to dbo.orders_temp
go
执行后,订阅和发布的数据都成功切出。现在再尝试加载分区,也就是切回来,结果也是成功的:
USE [OMS_Test]
GO
--Set Publication properties for partition switch
exec sp_changepublication 'OMSTEST_PUB',N'allow_partition_switch',N'true';
exec sp_changepublication 'OMSTEST_PUB',N'replicate_partition_switch','false';
go
ALTER TABLE dbo.orders
switch partition 4 to dbo.orders_temp
go
insert into dbo.orders
values ('140714001074904686','2014-07-16 04:06:19.6600000')
GO
执行完后,一切都如预期
发布的分区表结构: Partition 4成功切出去了
订阅的分区表结构:发布的Switch并未对它造成影响
新增的记录 '140714001074904686' 也成功的从发布端传播到了订阅端。事务复制运行也正常。
总结
1. 事务复制中的分区表,对发布和项目属性做几个小的更改,然后就可以把它当作普通分区表来管理了。
2. 事务复制实现读写分离的架构中,发布库中的分区表需要将非活跃数据做归档用以提升性能,而某些应用又需要查询全部的数据,配置一下发布属性也能符合使用。
-------------------------------------
作者:Joe.TJ
转载于:https://blog.51cto.com/joetang/1606326