文章出處

玩轉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

 

方法一

在這種情況下，我們可以采取跟MySQL主從一樣的策略，建立好發布訂閱之后，在訂閱上添加字段，這時候訂閱表會應用發布端發過來的DML和加字段DDL

但是，基本上影響不是很大，當添加字段DDL執行完畢之后，就可以切表了

 

 

方法二

可以考慮在停機維護窗口添加或者考慮升級到SQL Server2012 ，SQL Server2012 對加字段已經作了一些修改

對阻塞減少到最低

相關文章：Sql Server 2012新特性 Online添加非空欄位.

 

 

如有不對的地方，歡迎大家拍磚o(∩_∩)o 


文章列表


不含病毒。www.avast.com
arrow
arrow
    全站熱搜
    創作者介紹
    創作者 大師兄 的頭像
    大師兄

    IT工程師數位筆記本

    大師兄 發表在 痞客邦 留言(0) 人氣()

    E-mail轉寄