復制的應用：
初級應用：讀寫分離、數據庫備份
高級應用：搬遷大型數據庫（跨機房）、變更數據類型、未分區表轉為分區表
京東的復制專家 菠蘿 曾經寫過文章、在數據庫大會上也做過演講，但是我相信真正按照菠蘿兄的文章自己去做一次實驗的人應該不多
京東的復制專家 菠蘿 的文章地址：Replication的犄角旮旯（一）--變更訂閱端表名的應用場景
為什麼要玩轉復制，大家想象一下：變更數據類型、未分區表轉為分區表 這些業務場景經常都會發生，特別在數據量特別大的公司
變更數據類型：沒有其他特別好的辦法，數據量大，鎖表時間會比較長
未分區表轉為分區表：有時候一張表的數據量已經很多了，比如體積已經達到100G，那么這時候需要做表分區，方法是重建聚集索引或者導數據
上面的方法不多不少都有一些缺陷，對于數據量特別大的情況下，如果超出業務的預期停機時間……菊花殘，滿地傷，被領導認為辦事不力
常見場景：
1、變更其中的自增列主鍵，int-》bigint ，將表改為表分區
2、100G+的大表
3、單次最長停機時間：為1小時
復制回路，一次搞定
下面介紹一下，如何在一個實例下，通過三個數據庫，建立一個復制回路，完成上面的需求
實驗環境：一臺電腦，一個SQL Server實例，SQL Server2012， Windows7
復制類型為事務復制結構圖
從上圖可以看出，由于都是在同一個實例，同一臺機器下，所以機器磁盤需要有足夠的磁盤空間?。?/strong>
因為[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%)GOView Code
下面分區方案和分區函數都在三個庫上執行
--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]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]GOSELECT * FROM [dbo].[testAltertype_new]SELECT * FROM [dbo].[testChangepartition_new]
新聞熱點
疑難解答
