玩转SQL Server复制回路の变更数据类型、未分区表转为分区表
玩转SQL Server复制回路の变更数据类型、未分区表转为分区表
复制的应用:
初级应用:读写分离、数据库备份
高级应用:搬迁大型数据库(跨机房)、变更数据类型、未分区表转为分区表
为什麽要玩转复制,大家想象一下:变更数据类型、未分区表转为分区表 这些业务场景经常都会发生,特别在数据量特别大的公司
变更数据类型:没有其他特别好的办法,数据量大,锁表时间会比较长
未分区表转为分区表:有时候一张表的数据量已经很多了,比如体积已经达到100G,那么这时候需要做表分区,方法是重建聚集索引或者导数据
上面的方法不多不少都有一些缺陷,对于数据量特别大的情况下,如果超出业务的预期停机时间……菊花残,满地伤,被领导认为办事不力
常见场景:
1、变更其中的自增列主键,int-》bigint ,将表改为表分区
2、100G+的大表
3、单次最长停机时间:1小时
复制回路,一次搞定
下面介绍一下,如何在一个实例下,通过三个数据库,建立一个复制回路,完成上面的需求
实验环境:一个SQL Server实例,SQL Server2012, Windows7
结构图
从上图可以看出,由于都是在同一个实例,同一台机器下,所以机器磁盘需要有足够的磁盘空间!!
因为[testloopbackA]库有一个[testAltertype]表100G,复制到[testloopbackB]库[testAltertype]表100G
复制到[testloopbackC]库[testAltertype]表100G,最后复制回去[testloopbackA]库[testAltertype]表100G
加上生成的快照文件,当然快照文件可能会压缩,但是一定要保证有足够的磁盘空间
下面是具体演示
1、建库脚本
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
USE [master] GO /****** Object: Database [testloopbackA] Script Date: 2015/6/3 8:21:01 ******/ CREATE DATABASE [testloopbackA] CONTAINMENT = NONE ON PRIMARY ( NAME = N'testloopbackA', FILENAME = N'D:\DataBase\testloopbackA.mdf' , SIZE = 30720KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), FILEGROUP [FG_testChangepartition_Id_01] ( NAME = N'FG_testChangepartition_Id_01_data', FILENAME = N'D:\DataBase\testloopbackA\FG_testChangepartition_Id_01_data.ndf' , SIZE = 24576KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ), FILEGROUP [FG_testChangepartition_Id_02] ( NAME = N'FG_testChangepartition_Id_02_data', FILENAME = N'D:\DataBase\testloopbackA\FG_testChangepartition_Id_02_data.ndf' , SIZE = 24576KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ) LOG ON ( NAME = N'testloopbackA_log', FILENAME = N'D:\DataBase\testloopbackA_log.ldf' , SIZE = 2432KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO USE [master] GO /****** Object: Database [testloopbackB] Script Date: 2015/6/3 8:22:11 ******/ CREATE DATABASE [testloopbackB] CONTAINMENT = NONE ON PRIMARY ( NAME = N'testloopbackB', FILENAME = N'D:\DataBase\testloopbackB.mdf' , SIZE = 30720KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), FILEGROUP [FG_testChangepartition_Id_01] ( NAME = N'FG_testChangepartition_Id_01_data', FILENAME = N'D:\DataBase\testloopbackB\FG_testChangepartition_Id_01_data.ndf' , SIZE = 24576KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ), FILEGROUP [FG_testChangepartition_Id_02] ( NAME = N'FG_testChangepartition_Id_02_data', FILENAME = N'D:\DataBase\testloopbackB\FG_testChangepartition_Id_02_data.ndf' , SIZE = 24576KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ) LOG ON ( NAME = N'testloopbackB_log', FILENAME = N'D:\DataBase\testloopbackB_log.ldf' , SIZE = 2432KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO USE [master] GO /****** Object: Database [testloopbackC] Script Date: 2015/6/3 8:22:14 ******/ CREATE DATABASE [testloopbackC] CONTAINMENT = NONE ON PRIMARY ( NAME = N'testloopbackC', FILENAME = N'D:\DataBase\testloopbackC.mdf' , SIZE = 30720KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), FILEGROUP [FG_testChangepartition_Id_01] ( NAME = N'FG_testChangepartition_Id_01_data', FILENAME = N'D:\DataBase\testloopbackC\FG_testChangepartition_Id_01_data.ndf' , SIZE = 24576KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ), FILEGROUP [FG_testChangepartition_Id_02] ( NAME = N'FG_testChangepartition_Id_02_data', FILENAME = N'D:\DataBase\testloopbackC\FG_testChangepartition_Id_02_data.ndf' , SIZE = 24576KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ) LOG ON ( NAME = N'testloopbackC_log', FILENAME = N'D:\DataBase\testloopbackC_log.ldf' , SIZE = 2432KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO
下面分区方案和分区函数都在三个库上执行
--1.创建分区函数 CREATE PARTITION FUNCTION Fun_testChangepartition_Id(INT) AS RANGE LEFT FOR VALUES(2) --2.创建分区方案 CREATE PARTITION SCHEME [Sch_testChangepartition_Id] AS PARTITION [Fun_testChangepartition_Id] TO([FG_testChangepartition_Id_01],[FG_testChangepartition_Id_02])
建表脚本
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
USE [testloopbackA] GO --更改数据类型 CREATE TABLE [testAltertype](id INT IDENTITY(1,1) PRIMARY KEY,name NVARCHAR(100)) GO --变分区表 CREATE TABLE [testChangepartition](id INT IDENTITY(1,1) PRIMARY KEY,name NVARCHAR(100)) GO --插入测试数据 INSERT INTO [dbo].[testAltertype] ( [name] ) VALUES ( N'nihao' -- name - nvarchar(100) ) INSERT INTO [dbo].[testChangepartition] ( [name] ) VALUES ( N'nihao' -- name - nvarchar(100) ) SELECT * FROM [testAltertype] SELECT * FROM [testChangepartition]
2、在[testloopbackB]库先建好2个表
USE [testloopbackB] GO --更改数据类型 CREATE TABLE testAltertype_new(id BIGINT IDENTITY(1,1) PRIMARY KEY,name NVARCHAR(100)) GO --变分区表 CREATE TABLE testChangepartition_new(id INT IDENTITY(1,1) PRIMARY KEY,name NVARCHAR(100)) ON [Sch_testChangepartition_Id](id) GO
3、创建[testloopbackA]库到[testloopbackB]库的发布,这一步很关键,因为在发布的时候需要修改项目属性,在发布属性里,还需要选择快照为字符类型
testChangepartition_new表
testAltertype_new表
[testloopbackA]库到[testloopbackB]库的复制
4、建立[pub_testloopbackAtotestloopbackB]发布的订阅
5、在[testloopbackB]库里, 将[testAltertype_new]表和[testChangepartition_new]表里的id字段属性,不用于复制设置为"是"
[testAltertype_new]表
[testChangepartition_new]表
6、测试
在[testloopbackA]库的[testAltertype]表和[testChangepartition]表各插入一些记录
USE [testloopbackA] GO --插入测试数据 INSERT INTO [dbo].[testAltertype] ( [name] ) VALUES ( N'nihao2' -- name - nvarchar(100) ) INSERT INTO [dbo].[testChangepartition] ( [name] ) VALUES ( N'nihao2' -- name - nvarchar(100) ) SELECT * FROM [testAltertype] SELECT * FROM [testChangepartition]
在[testloopbackB]库就能看到新插入的记录
USE [testloopbackB] GO SELECT * FROM [dbo].[testAltertype_new] SELECT * FROM [dbo].[testChangepartition_new]
在[testloopbackB]库里执行
USE [testloopbackB] GO --查看分区架构文件组分布 SELECT CONVERT(VARCHAR(MAX), ps.name) AS partition_scheme , p.partition_number , CONVERT(VARCHAR(MAX), ds2.name) AS filegroup , CONVERT(VARCHAR(MAX), ISNULL(v.value, ''), 120) AS range_boundary , STR(p.rows, 9) AS rows FROM sys.indexes i JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id JOIN sys.destination_data_spaces dds ON ps.data_space_id = dds.partition_scheme_id JOIN sys.data_spaces ds2 ON dds.data_space_id = ds2.data_space_id JOIN sys.partitions p ON dds.destination_id = p.partition_number AND p.object_id = i.object_id AND p.index_id = i.index_id JOIN sys.partition_functions pf ON ps.function_id = pf.function_id LEFT JOIN sys.Partition_Range_values v ON pf.function_id = v.function_id AND v.boundary_id = p.partition_number - pf.boundary_value_on_right WHERE i.object_id = OBJECT_ID('testChangepartition_new') AND i.index_id IN ( 0, 1 ) ORDER BY p.partition_number
数据已经入到相应分区
7、继续将[testloopbackB]库的[testAltertype_new]表和[testChangepartition_new]表复制到[testloopbackC]
这一步需要注意:[testAltertype_new]表不需要再跟[testloopbackA]库到[testloopbackB]库的复制那样设置项目属性->XX_new,只需要保持默认就行了
[testChangepartition_new]表跟刚才一样,需要设置项目属性->XX_new
先在[testloopbackC]库建好 [testChangepartition_new]表,[testAltertype_new]表不需要预先建立
USE [testloopbackC] GO --变分区表 CREATE TABLE testChangepartition_new(id INT IDENTITY(1,1) PRIMARY KEY,name NVARCHAR(100)) ON [Sch_testChangepartition_Id](id) GO
8、建[testloopbackB]库到[testloopbackC]库的发布
8、在[testloopbackC]库建立订阅
启动快照初始化
然后需要对[testloopbackC]库的[testChangepartition_new]表设置, id字段的属性,不用于复制 为“是”
[testloopbackC]库的[testAltertype_new]表不需要设置 id字段的属性
9、测试
[testloopbackA]库插入的记录,[testloopbackC]库马上能看到
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
USE [testloopbackC] go --查看分区架构文件组分布 SELECT CONVERT(VARCHAR(MAX), ps.name) AS partition_scheme , p.partition_number , CONVERT(VARCHAR(MAX), ds2.name) AS filegroup , CONVERT(VARCHAR(MAX), ISNULL(v.value, ''), 120) AS range_boundary , STR(p.rows, 9) AS rows FROM sys.indexes i JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id JOIN sys.destination_data_spaces dds ON ps.data_space_id = dds.partition_scheme_id JOIN sys.data_spaces ds2 ON dds.data_space_id = ds2.data_space_id JOIN sys.partitions p ON dds.destination_id = p.partition_number AND p.object_id = i.object_id AND p.index_id = i.index_id JOIN sys.partition_functions pf ON ps.function_id = pf.function_id LEFT JOIN sys.Partition_Range_values v ON pf.function_id = v.function_id AND v.boundary_id = p.partition_number - pf.boundary_value_on_right WHERE i.object_id = OBJECT_ID('testChangepartition_new') AND i.index_id IN ( 0, 1 ) ORDER BY p.partition_number --分区区间 --SELECT * FROM sys.partition_range_values
数据入到相应分区
10、跟第7步一样,但是这一次是[testloopbackC]库到[testloopbackA]库
先在[testloopbackA]库建好 [testChangepartition_new]表,[testAltertype_new]表不需要预先建立
USE [testloopbackA] GO --变分区表 CREATE TABLE testChangepartition_new(id INT IDENTITY(1,1) PRIMARY KEY,name NVARCHAR(100)) ON [Sch_testChangepartition_Id](id) GO
11、建[testloopbackC]库到[testloopbackA]库的发布
12、在[testloopbackA]库建立订阅
启动快照初始化
然后需要对[testloopbackA]库的[testChangepartition_new]表设置id字段属性,不用于复制 为“是”
[testloopbackA]库的[testAltertype_new]表不需要设置设置id字段属性
13、测试
[testloopbackA]库插入的记录,[testloopbackA]库马上能看到
USE [testloopbackA] GO SELECT * FROM [dbo].[testAltertype_new] SELECT * FROM [dbo].[testChangepartition_new]
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
USE [testloopbackA] go --查看分区架构文件组分布 SELECT CONVERT(VARCHAR(MAX), ps.name) AS partition_scheme , p.partition_number , CONVERT(VARCHAR(MAX), ds2.name) AS filegroup , CONVERT(VARCHAR(MAX), ISNULL(v.value, ''), 120) AS range_boundary , STR(p.rows, 9) AS rows FROM sys.indexes i JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id JOIN sys.destination_data_spaces dds ON ps.data_space_id = dds.partition_scheme_id JOIN sys.data_spaces ds2 ON dds.data_space_id = ds2.data_space_id JOIN sys.partitions p ON dds.destination_id = p.partition_number AND p.object_id = i.object_id AND p.index_id = i.index_id JOIN sys.partition_functions pf ON ps.function_id = pf.function_id LEFT JOIN sys.Partition_Range_values v ON pf.function_id = v.function_id AND v.boundary_id = p.partition_number - pf.boundary_value_on_right WHERE i.object_id = OBJECT_ID('testChangepartition_new') AND i.index_id IN ( 0, 1 ) ORDER BY p.partition_number --分区区间 --SELECT * FROM sys.partition_range_values
数据进入到相应分区
接下来就是找个适当的时间,制定好停机计划plan,比如凌晨业务低峰时期,业务停写,DBA拆复制,DBA改表名 , 打完 收工!!
总结
有人会问C库主要起什么作用呢? A--B--A 架构不行吗,为什么一定要 A--B--C--A 架构
回答:其实这个复制回路有点类似MySQL的gh-ost做DDL原理
这里以数据库为单位,以数据库为粒度,不是实例级和表级
如果是 A--B--A 架构,那么就是双主复制,互为主节点,这样会导致在事务复制的过程中,直接在两个库之间往复复制导致数据冲突和一致性问题。
也就是事务插入到A->B->A->B->A->B->A,很容易冲突
如果C库没有,SQL Server允许你可以正常创建链路,但事务复制的增量不会同步回A库,也就是说,SQL Server 不允许两个数据库建立互为复制关系,所以需要增加一个C库
在搭建复制回路的过程当中,本人发现,添加字段这个场景无法实现,比如testloopbackA库testAddcolumn_new表有四个字段
然后预先在testloopbackB库建立testAddcolumn_new表,并增加一个字段,在快照初始化的时候,订阅端会报错
错误消息: 进程无法向表“"dbo"."testAddcolumn_new"”进行大容量复制。 (源: MSSQL_REPL,错误号: MSSQL_REPL20037) 获取帮助: http://help/MSSQL_REPL20037 已达到文件末尾,缺少结束符或字段数据不完整 若要获取详细说明初始化订阅表时所遇到的错误的错误文件,请执行在下面显示的 bcp 命令。有关该 bcp 实用工具及其支持的选项的详细信息,请参阅 BOL。 (源: MSSQLServer,错误号: 20253) 获取帮助: http://help/20253 bcp "testloopbackB"."dbo"."testAddcolumn_new" in "E:\DataBase\ReplData\unc\NAME-PC_TESTLOOPBACKA_PUB_TESTLOOPBACKATOTES1cf75016\20150604115556\testAddcolumn_2.bcp" -e "errorfile" -t"\n<x$3>\n" -r"\n<,@g>\n" -m10000 -SNAME-PC -T -w (源: MSSQLServer,错误号: 20253) 获取帮助: http://help/20253
添加字段这个场景方法一
在这种情况下,我们可以采取跟MySQL主从一样的策略,建立好发布订阅之后,在订阅上添加字段,这时候订阅表会应用发布端发送过来的DML和加字段DDL,加字段DDL在订阅端执行可能会报错,这时候忽略错误即可
但是,基本上影响不是很大,当添加字段DDL执行完毕之后,就可以切表
添加字段这个场景方法二
考虑升级到SQL Server2012或以上版本 ,SQL Server2012 对可变长度的字符型字段已经作了一些修改,只修改元数据只有在实际update的时候才会实际修改数据,对阻塞减少到最低
如果是添加数值型字段,只能选择停机维护进行添加
相关文章:Sql Server 2012新特性 Online添加非空栏位.
京东SQL Server DBA 肖磊 的文章:Replication的犄角旮旯(一)--变更订阅端表名的应用场景
如有不对的地方,欢迎大家拍砖o(∩_∩)o