玩转SQL Server复制回路の变更数据类型、未分区表转为分区表

原文:玩转SQL Server复制回路の变更数据类型、未分区表转为分区表

玩转SQL Server复制回路の变更数据类型、未分区表转为分区表

 

复制的应用:

初级应用:读写分离、数据库备份

高级应用:搬迁大型数据库(跨机房)、变更数据类型、未分区表转为分区表

 

京东的复制专家 菠萝 曾经写过文章、在数据库大会上也做过演讲,但是我相信真正按照菠萝兄的文章自己去做一次实验的人应该不多

京东的复制专家 菠萝 的文章地址:Replication的犄角旮旯(一)--变更订阅端表名的应用场景

 

为什麽要玩转复制,大家想象一下:变更数据类型、未分区表转为分区表 这些业务场景经常都会发生,特别在数据量特别大的公司

 

变更数据类型:没有其他特别好的办法,数据量大,锁表时间会比较长

未分区表转为分区表:有时候一张表的数据量已经很多了,比如体积已经达到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、建库脚本

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

View Code

 

下面分区方案和分区函数都在三个库上执行

--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])

 

建表脚本

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]

View Code

 

 

 

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]库

 

 

启动快照初始化

然后需要对[testChangepartition_new]表设置不用于复制 为“是”

[testAltertype_new]表不需要设置

 

9、测试

[testloopbackA]库插入的记录,[testloopbackC]库马上能看到

 

 

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

View Code

数据入到相应分区

 

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]库

 

启动快照初始化

 

然后需要对[testChangepartition_new]表设置不用于复制 为“是”

[testAltertype_new]表不需要设置

 

 

13、测试

[testloopbackA]库插入的记录,[testloopbackA]库马上能看到

USE [testloopbackA]
GO
SELECT * FROM [dbo].[testAltertype_new]
SELECT * FROM [dbo].[testChangepartition_new]

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

View Code

数据进入到相应分区

 

接下来就是找个适当的时间,比如凌晨, 停写, 拆复制, 改表名    打完 收工!!

 

 

 



总结

 

在搭建复制回路的过程当中,本人发现加字段是不行的,比如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

 

对于大表加字段的情况,可以考虑在停机维护窗口添加或者考虑升级到SQL Server2012 ,SQL Server2012 对加字段已经作了一些修改

对阻塞减少到最低

相关文章:Sql Server 2012新特性 Online添加非空栏位.

 

 

如有不对的地方,欢迎大家拍砖o(∩_∩)o 

时间: 2015-06-05

玩转SQL Server复制回路の变更数据类型、未分区表转为分区表的相关文章

SQL Server复制原理及注意事项

以两个复制服务器A,B双向复制为模型 SQL Server的复制分为三种: 1. 快照复制 A每隔一段时间将B中的相应表中的数据全部删除,然后将自己相应表中的 全部插到B中.此种方式显然不适合我们的应用,不予考虑. 2. 事务复制 对A中的每一个满足复制条件的事务,每隔一定时间A都应用到B上,反之亦然.SQL Server 通过把数据传给三个存储过程(插入,删除,修改)进行数据的修改,我们可以根据自己的应用改写此存储过程. 存在缺陷:如果从A到B的复制条件和从B到A复制条件相同的话,会产生"踢皮

SQL Server 2005中的数据类型总结

SQL Server 2005 中的数据类型归纳为下列类别: 精确数字 bigint decimal int numeric smallint money tinyint smallmoney bit   近似数字 float real 日期和时间 datetime smalldatetime 字符串 char text varchar   Unicode字符串 nchar ntext nvarchar   二进制字符串 binary image varbinary  

SQL Server中的Image数据类型的操作

原文:SQL Server中的Image数据类型的操作 准备工作,在库Im_Test中建立一张表Im_Info,此表中有两个字段,分别为Pr_Id (INT),Pr_Info (IMAGE),用来存储图形编号及图形信息.其语法如下: CREATE TEALE Im_Info ( Pr_Id INT NULL , Pr_Info IMAGE NULL )  第一步: 往表中插入一条记录,并初始化PR_INFO字段.其语法如下: INSERT INTO Im_Info VALUES (1 ,0xFF

SQL Server 复制需要有实际的服务器名称才能连接到服务器

机子由于在捣鼓DAC连接方式时,计算机名被修改了,被改成了kerry,直到最近几天由于计算机改名了,这个潜在的问题(本机数据库只是偶尔那里 做实验)终于爆发了:我用连接服务器从其它服务器拷贝数据时报错,错误如下:SQL Server 复制需要有实际的服务器名称才能连接到服务器.不支持通过服务器别名.IP 地址或任何其他备用名称进行连接.请指定实际的服务器名称"xxxx". (Replication.Utilities):具体情况如图所示       解决方法:从sys.sysserve

关于 sql server 2005 的 datetime数据类型

问题描述 关于 sql server 2005 的 datetime数据类型 我在执行 insert into department(pdeptid,deptname,compid,createdate,leaderid) values(0,'11111111',13,'2015-01-11 21:22:36',0) 时,数据库中新增数据 它,请问问什么会主动添加 周 部分的内容呢? 其中 createdate 的数据类型是 datetime. 解决方案 这个跟区域设置的日期格式有关. 显示在数

(初稿)SQL Server 复制(Replication)系列(2)——事务复制搭建

原文:(初稿)SQL Server 复制(Replication)系列(2)--事务复制搭建 本文演示如何搭建最基本的事务复制. 环境准备: 虚拟机2台: 服务器名分别为RepA和RepB,RepA为发布服务器,RepB为订阅服务器.均安装WindowsServer 2008R2英文版(在外企工作的原因).并确保两台服务器能互访. SQL Server: 在上面的虚拟机中分别安装SQLServer 2008 R2 x64 英文企业版. 演示数据库: 微软示例数据库AdventureWorks20

SQL Server 复制订阅

原文:SQL Server 复制订阅 标签:SQL SERVER/MSSQL SERVER/数据库/DBA/高性能解决方案/高可用 概述   配置复制就没有数据库镜像和AlwaysOn的要求那么高,只需要两台服务器能通过TCP进行通讯即可,两台服务器操作系统和SQL版本都可以不完全一致,而且两台服务器也不需要加入域,所以配置复制订阅就简单多了,但是复制订阅主要是针对数据表而不能像镜像和AlwaysOn那样配置整个数据库,这也是它的缺点吧. 接下来主要除了前面介绍配置复制订阅后面还会解释一些需要注

SQL Server 复制 - 发布订阅(SQL Server 数据同步)

原文:SQL Server 复制 - 发布订阅(SQL Server 数据同步) SQL Server的同步是通过SQL Server自带的复制工具来实现的,分发布和订阅2大步. A,复制-发布 发布之前,需要设置好几个前置条件,发布属性和快照位置.发布主要是设置发布数据库,如未设置,所有的发布,订阅可正常进行,也可通过快照同步,但是却无法在后面的修改中实时同步. 其次,设置快照位置.快照位置设置是在"分发服务器属性"中的发布服务器设置.如果设置的位置不能被订阅机访问,订阅是最好采用发

SQL Server复制需要有实际的服务器名称才能连接到服务器

SQL Server 2005/2008 配置 Transactional  Replication 过程中,出现如下异常信息. 异常信息: Replication.Utilities---------- SQL Server 复制需要有实际的服务器名称才能连接到服务器.不支持通过服务器别名.IP 地址或任何其他备用名称进行连接.请指定实际的服务器名称"xxxxxxx". SQL Server replication requires the actual server name to