透過備份及還原資料庫來初始化交易式複寫的訂閱

2017-04-19

2173
0
[MSSQL]Replication
2017-06-13

一般透過複寫精靈來設定交易式複寫時，都會選擇透過快照集代理程式(Snapshot Agent)來產出快照，然後藉由散發代理程式(Distribution Agent)來初始化訂閱的資料，這種方式確實是方便省事，而且當你產出快照集到訂閱被新增，這中間的資料異動，交易式複寫機制都會自動幫你補上去，然而當要複寫的資料量大到上百GB，甚至是TB時，同步資料到訂閱者所耗費的時間將會相當長，更遑論如果是跨國同步資料的情境。

有兩種屬於database層級的方式，可以不使用快照集方式來初始化交易式複寫的訂閱，用來處理大資料量的資料同步，一種是利用backup/restore database，另一種則是利用attach/detach database，這兩種方式最主要的不同是在於使用backup/restore database來設定初始化訂閱時，因為可以利用LSN(Log Sequence Number)來從特定點重新執行交易紀錄，比較不會有遺失資料的風險，而使用attach/detach database的方式來設定初始化訂閱，則必須自己確保發行端跟訂閱端兩邊的資料是一致的，因為當你在同步data及schema到訂閱端，到訂閱被建立的這一段時間，資料可能還是持續被異動，而採用attach/detach database方式將可能遺失這段時間的異動資料。

本篇將簡單地來介紹如何透過備份及還原資料庫來初始化交易式複寫的訂閱，主要角色說明如下：

發行者兼散發者: USER-PC\SQL2012STD
訂閱者: USER-PC\SQL2012STD_2

1.前置作業

首先，建一個測試資料庫，其包含兩張資料表，並寫入幾筆資料以備後續測試用

--建立資料庫
CREATE DATABASE RplTest1
go

--建立資料表Table_1
use [RplTest1]
go
CREATE TABLE [dbo].[Table_1](
	[id] [smallint] NOT NULL,
	[name] [nvarchar](20) NULL,
	[memo] [nvarchar](50) NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

--寫入資料
use [RplTest1]
go
insert [dbo].[Table_1] values(1,'Jay','aaa')
insert [dbo].[Table_1] values(2,'Fish','bbb')
insert [dbo].[Table_1] values(3,'Mary','ccc')

--建立資料表Table_2
use [RplTest1]
go
CREATE TABLE [dbo].[Table_2](
	[id] [smallint] IDENTITY(1,1) NOT NULL,  --使用識別值
	[name] [nvarchar](20) NULL,
	[memo] [nvarchar](50) NULL,
 CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

--寫入資料
use [RplTest1]
go
insert [dbo].[Table_2] values('user01','test01')
insert [dbo].[Table_2] values('user02','test02')
insert [dbo].[Table_2] values('user03','test03')

--檢查資料是否正確
select * from [dbo].[Table_1]
select * from [dbo].[Table_2]

2.建立發行集

在建立發行集的時候，我們會先在發行者(Publisher)利用[建立發行集精靈]來產出T-SQL指令碼，經過加工後再手動建立發行集。

請選擇剛剛建立的新資料庫"RplTest1"

我們要建立的是交易式發行集(transactional publication)

將資料表Table_1及Table_2勾選為發行項(Article)，請留意Table_2有一個欄位有使用identiry(識別值)欄位來自動填入資料流水號，這裡先稍微注意一下就好，後面會再提到。

不進行資料列的篩選，直接按【下一步】

不要勾選【立即產生快照集，並保留.......】，因為我們後面的步驟會使用資料庫備份檔來初始化訂閱

可針對Snapshot Agent及Log Reader Agent分別指定執行帳戶及連線到發行者的權限，下圖是讓兩者使用相同的安全性設定

前面說過我們不使用精靈來建立發行集，所以只要勾選【產生具建立發行集步驟的指令碼檔案】即可

將指令碼檔案暫時另存於電腦桌面

指定發行集名稱

使用SSMS開啟並修改指令碼，在exec sp_addpublication那一段語法中，請修改@immediate_sync = N'True'及@allow_initialize_from_backup = N'True'，修改後的完整指令碼如下，請直接在發行者(Publisher)執行以建立發行集。

use [RplTest1]
exec sp_replicationdboption @dbname = N'RplTest1', @optname = N'publish', @value = N'true'
GO
-- 正在加入交易式發行集
use [RplTest1]
exec sp_addpublication @publication = N'Rpl_Sample01_Rpl_Restore', @description = N'來自發行者 ''USER-PC\SQL2012STD'' 的資料庫 ''RplTest1'' 交易式發行集。', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'True', @allow_sync_tran = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'True', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO


exec sp_addpublication_snapshot @publication = N'Rpl_Sample01_Rpl_Restore', @frequency_type = 1, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 0, @publisher_login = N'sa', @publisher_password = N''


use [RplTest1]
exec sp_addarticle @publication = N'Rpl_Sample01_Rpl_Restore', @article = N'Table_1', @source_owner = N'dbo', @source_object = N'Table_1', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Table_1', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboTable_1', @del_cmd = N'CALL sp_MSdel_dboTable_1', @upd_cmd = N'SCALL sp_MSupd_dboTable_1'
GO




use [RplTest1]
exec sp_addarticle @publication = N'Rpl_Sample01_Rpl_Restore', @article = N'Table_2', @source_owner = N'dbo', @source_object = N'Table_2', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Table_2', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboTable_2', @del_cmd = N'CALL sp_MSdel_dboTable_2', @upd_cmd = N'SCALL sp_MSupd_dboTable_2'
GO

執行結果如下圖，已指明會用"隱含方式"建立Log Reader Agent Job(其實連Snapshot Agent Job也一併建立)，所以不會在上述的指令碼中看到如msdb.dbo.sp_add_job等建立Job的相關語法。

以隱含方式建立兩個Job

成功建立發行集

檢查一下快照集檔案的預設資料夾，確實沒有產出任何快照。

附帶說明，設定@allow_initialize_from_backup = N'True'的效果，其實等同於在發行集建立後，手動更改下圖的設定，其效果是一樣的，只是我們是直接修改指令碼，好處是以後若需要重建發行集，只需要重新跑此SQL script就好，不用額外再開啟發行集屬性視窗來修改此屬性。

另外，原本以前的做法，並沒有特別去修改@immediate_sync = N'True'，還記得前面在設定發行集時，我們選擇不勾選【立即產生快照集，並保留.......】，所以轉出的指令碼中@immediate_sync參數的值為False，但後來查閱相關文件時有看到下圖的建議，故將此參數值修改為True，但實際執行建立發行集的指令碼後，它其實還是不會自動產生快照集檔案。

3.在發行者(Publisher)備份發行資料庫

因為我們所發行的資料表中，資料表Table_2有使用identity(識別值)，所以一定要在設定完發行集後，才做備份發行資料庫的動作，否則後續等設定完訂閱並開始做資料同步時，可能會出現錯誤訊息，屆時又要做些額外的異常處理，這個順序務必要注意一下，後面會再補充說明。

--請在發行者(Publisher)執行
backup database [RplTest1] to disk='D:\temp\RplTest1.bak'

4.模擬建立訂閱之前，發行者端發生資料異動

在文章一開始有提到，採用備份/還原資料庫來初始化訂閱，會利用LSN(Log Sequence Number)來從特定點重新執行交易紀錄，比較不會有遺失資料的風險，所以在訂閱者(Subscriber)執行資料庫還原之前，我們先在發行資料庫的發行資料表上新增三筆資料，測試看看這三筆新增的資料在設定完訂閱之後，會不會自動同步到訂閱資料庫，以做到資料不漏接。

在執行後續的查詢語法前，請先將查詢視窗切換為【SQLCMD模式】，以避免無法執行

--在發行資料庫上新增資料
:connect USER-PC\SQL2012STD  --表示建立一條連接到USER-PC\SQL2012STD的connection
insert [RplTest1].[dbo].[Table_1] values(4,'John','ddd')
insert [RplTest1].[dbo].[Table_1] values(5,'Sam','eee')
insert [RplTest1].[dbo].[Table_1] values(6,'Mars','fff')
go

--查詢資料內容
:connect USER-PC\SQL2012STD
select count(*) from [RplTest1].[dbo].[Table_1]
select count(*) from [RplTest1].[dbo].[Table_2]
go

所以，目前在發行者端的資料庫"RplTest1"中，資料表Table_1、Table_2分別有資料筆數6筆及3筆(而此時，在資料庫備份檔中，資料表Table_1、Table_2的資料筆數應該都只有3筆)

5.在訂閱者(Subscriber)還原資料庫

--請在訂閱者執行以下資料庫還原語法
USE [master]
RESTORE DATABASE [RplTest1] FROM  DISK = N'D:\temp\RplTest1.bak' WITH  FILE = 1,  
MOVE N'RplTest1' TO N'D:\MSSQL2012\DATA\RplTest12.mdf',  
MOVE N'RplTest1_log' TO N'D:\MSSQL2012\DATA\RplTest12_log.ldf',  NOUNLOAD,  STATS = 5
GO

順便檢查一下資料筆數，目前兩邊的資料表Table_1的資料筆數確實是不一致的，差了前面在發行端資料庫所新增的3筆。

--查詢發行端與訂閱端資料表目前的資料筆數
:connect USER-PC\SQL2012STD
select @@servername,count(*) from [RplTest1].[dbo].[Table_1]
select @@servername,count(*) from [RplTest1].[dbo].[Table_2]
go
:connect USER-PC\SQL2012STD_2
select @@servername,count(*) from [RplTest1].[dbo].[Table_1]
select @@servername,count(*) from [RplTest1].[dbo].[Table_2]
go

6.新增訂閱(Subscription)

在建立訂閱的時候，我們會先利用[新增訂閱精靈]來產出T-SQL指令碼，經過加工後再手動建立訂閱。

選擇該訂閱所使用的發行集

這裡我是依預設值，將訂閱設定成Push(發送)類型，所以Distribution Agent Job(散發代理程式作業排程)會在散發者端建立並執行。

按下【加入訂閱者】，將"USER-PC\SQL2012STD_2"加入為訂閱者，並指定訂閱資料庫為"RplTest1"。

針對Distribution Agent指定執行帳戶及連接到散發者及訂閱者的權限

此步驟的設定都依預設就好，不用管它，因為後面我們會藉由修改指令碼的方式來更改設定。補充說明，前面在設定發行集時，由於我們要採用資料庫還原方式來初始化訂閱，所以我們並未藉由快照集代理程式產出快照集，故本來就無法利用快照集來初始化訂閱。

我們不使用精靈來建立訂閱，所以只要勾選【產生含有建立訂閱之步驟的指令碼檔案】即可

將指令碼檔案暫時另存於電腦桌面

使用SSMS開啟並修改指令碼，在exec sp_addsubscription那一段語法中，請修改@sync_type = N'initialize with backup'，並增加兩個參數@backupdevicetype = N'disk'及@backupdevicename = N'D:\Temp\RplTest1.bak'，修改後的完整指令碼如下，請直接在發行者(Publisher)執行以建立訂閱。

-----------------開始: 在發行者 'USER-PC\SQL2012STD' 端執行的指令碼-----------------
use [RplTest1]
exec sp_addsubscription @publication = N'Rpl_Sample01_Rpl_Restore', 
@subscriber = N'USER-PC\SQL2012STD_2', 
@destination_db = N'RplTest1', 
@subscription_type = N'Push', 
@sync_type = N'initialize with backup', 
@article = N'all', 
@update_mode = N'read only', 
@subscriber_type = 0,
@backupdevicetype = N'disk',
@backupdevicename = N'D:\Temp\RplTest1.bak'

exec sp_addpushsubscription_agent @publication = N'Rpl_Sample01_Rpl_Restore', @subscriber = N'USER-PC\SQL2012STD_2', @subscriber_db = N'RplTest1', @job_login = null, @job_password = null, @subscriber_security_mode = 0, @subscriber_login = N'sa', @subscriber_password = null, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20170420, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO
-----------------結束: 在發行者 'USER-PC\SQL2012STD'  端執行的指令碼-----------------

執行結果如下圖，已指明會用"隱含方式"建立Distribution Agent Job，所以不會在上述的指令碼中看到如msdb.dbo.sp_add_job等建立Job的相關語法。

多了一個Distribution Agent Job

成功建立訂閱

補充說明，使用@sync_type = N'initialize with backup'、@backupdevicetype = N'disk'及@backupdevicename = N'D:\Temp\RplTest1.bak'這三個參數，其實就是告知系統我們要使用資料庫備份檔來初始化訂閱(initialize with backup)，而且該備份檔是儲存於磁碟(disk)中，其檔案完整路徑為"D:\Temp\RplTest1.bak"。

這時，我們來比較兩邊的資料，資料居然沒有同步，資料表Table_1還是差了三筆資料。其實這是可以預期的，因為我們是使用轉出並修改參數後的指令碼來執行發行集與訂閱的新增，而轉出的指令碼並未包含密碼，所以造成連接驗證失敗，我們必須手動再重新輸入一次密碼，或乾脆將密碼直接寫在指令碼中(較不建議這種方式)。這其實跟轉出linked server指令碼的情形是類似的，為了機敏資料安全，預設轉出指令碼時都不會包含密碼。

--比較發行者與訂閱者兩邊資料
:connect USER-PC\SQL2012STD
select @@servername,count(*) from [RplTest1].[dbo].[Table_1]
select @@servername,count(*) from [RplTest1].[dbo].[Table_2]
go
:connect USER-PC\SQL2012STD_2
select @@servername,count(*) from [RplTest1].[dbo].[Table_1]
select @@servername,count(*) from [RplTest1].[dbo].[Table_2]
go

會出現下圖連接訂閱者錯誤

為確保各複寫代理程式連接能正常執行，在[發行集屬性]視窗及[訂閱屬性視窗]，將含有密碼的欄位都重新輸入一次。

查看資料同步的狀態，已恢復正常執行

--重新比較發行者與訂閱者兩邊資料
:connect USER-PC\SQL2012STD
select @@servername,count(*) from [RplTest1].[dbo].[Table_1]
select @@servername,count(*) from [RplTest1].[dbo].[Table_2]
go
:connect USER-PC\SQL2012STD_2
select @@servername,count(*) from [RplTest1].[dbo].[Table_1]
select @@servername,count(*) from [RplTest1].[dbo].[Table_2]
go

我們在[步驟4]中刻意加進去資料表Table_1的三筆資料，確實已經自動被同步到訂閱端了，所以使用backup/restore database來設定初始化訂閱，確實可以降低遺失資料的風險。

這是因為備份檔中存放了LSN(Log Sequence Number)的相關資訊，如此可以讓複寫機制用來從特定點依序執行交易紀錄

RESTORE HEADERONLY   
FROM DISK = N'D:\temp\RplTest1.bak'   
WITH NOUNLOAD;  
GO

而且，因為還原所使用的備份檔一定要在發行者端產生，所以我們其實也可以直接在發行者的msdb.dbo.backupset中查到相關的備份檔案資訊

select database_name,machine_name,server_name,first_lsn,last_lsn
from msdb.dbo.backupset 
where database_name='RplTest1'

--最後，我們在[發行者]的資料表Table_1及Table_2上同時新增資料，看看是否可以正常運作
:connect USER-PC\SQL2012STD
insert [RplTest1].[dbo].[Table_1] values(7,'Johny','ddd')
insert [RplTest1].[dbo].[Table_1] values(8,'Eason','eee')
insert [RplTest1].[dbo].[Table_1] values(9,'Mark','fff')
insert [RplTest1].[dbo].[Table_2] values('user04','test04')
insert [RplTest1].[dbo].[Table_2] values('user05','test05')
go

--檢查資料
:connect USER-PC\SQL2012STD
select @@servername,count(*) from [RplTest1].[dbo].[Table_1]
select @@servername,count(*) from [RplTest1].[dbo].[Table_2]
go
:connect USER-PC\SQL2012STD_2
select @@servername,count(*) from [RplTest1].[dbo].[Table_1]
select @@servername,count(*) from [RplTest1].[dbo].[Table_2]
go

兩邊資料同步正常，完成設定。