玩转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%)GOUSE [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%)GOUSE [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 FUNCTIONFun_testChangepartition_Id(INT) ASRANGE LEFTFOR VALUES(2)--2.创建分区方案CREATE PARTITION SCHEME[Sch_testChangepartition_Id] aspARTITION [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]

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]GOSELECT * FROM [dbo].[testAltertype_new]SELECT * FROM [dbo].[testChangepartition_new]