透過備份及還原資料庫來初始化交易式複寫的訂閱(續) -- 新增發行項

在前一篇文章【透過備份及還原資料庫來初始化交易式複寫的訂閱】中,談到利用資料庫備份檔來處理交易式複寫的訂閱初始化,緊接著來說明當發行者(Publisher)新增了一個資料表,且欲加入發行集(Publication)時,我們該如何來進行。

我們繼續沿用資料庫RplTest1,主要複寫角色如下:

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

前置作業

在發行者(Publisher)手動新增兩張資料表資料表Table_3及Table_4,做為要加入發行集的對象,請注意,其中資料表Table_4有使用識別值(identity)欄位,當資料表Table_4被加入發行集時,其使用識別值的欄位將會被加上NOT FOR REPLICATION關鍵字,其功用我們稍後會再提到。

use [RplTest1]
go

CREATE TABLE [dbo].[Table_3](
	[id] [smallint] NOT NULL,
	[name] [nvarchar](20) NULL,
	[memo] [nvarchar](50) NULL,
 CONSTRAINT [PK_Table_3] 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

CREATE TABLE [dbo].[Table_4](
	[id] [smallint] IDENTITY(1,1) NOT NULL,  --使用識別值
	[name] [nvarchar](20) NULL,
	[memo] [nvarchar](50) NULL,
 CONSTRAINT [PK_Table_5] 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

寫入並查對測試資料

--寫入資料
:connect USER-PC\SQL2012STD
insert [RplTest1].[dbo].[Table_3] values(1,'num01','test')
insert [RplTest1].[dbo].[Table_3] values(2,'num02','test')
insert [RplTest1].[dbo].[Table_3] values(3,'num03','test')
insert [RplTest1].[dbo].[Table_4] values('identity01','test')
insert [RplTest1].[dbo].[Table_4] values('identity02','test')
insert [RplTest1].[dbo].[Table_4] values('identity03','test')
go
--查資料
select * from [RplTest1].[dbo].[Table_3]
select * from [RplTest1].[dbo].[Table_4]

以下為加入發行項的建議步驟:

1.停止Log Reader

為避免我們正在手動同步發行者與訂閱者兩邊的資料時,散發代理程式(Distribution Agent)就因訂閱者資料尚未完成同步緣故,而發生套用複寫指令失敗的錯誤,所以先將Log Reader停止,如此,在發行者被標記為要複寫的交易記錄就暫時不會被持續寫入distribution資料庫中,也就不會被散發代理程式讀取及套用指令(commands)到訂閱者。

2.停掉AP服務(此為option)

主要是避免欲發行的資料表被持續異動,而造成手動同步資料有落差,或者,該資料表已被加入發行集,但尚未完成資料同步時,因為停止Log Reader而造成發行資料庫的Transaction Log持續膨脹。

3.將資料表加入發行集

在發行集上按滑鼠右鍵,點選【屬性】,開啟【發行集屬性】視窗

勾選資料表Table_3及Table_4,將其設定為新發行的物件,再按下【確定】,以完成發行項的新增

4.在訂閱者手動初始化資料表

接著,從發行者(Publisher)手動轉出資料表Table_3及Table_4的table schema,會發現資料表Table_4中,有使用identity的欄位會被加入NOT FOR REPLICATION關鍵字,其類似於設定 SET IDENTITY_INSERT table_name ON 的效果,故當複寫代理程式執行insert作業時,訂閱者端的資料表識別欄位值不會自動遞增,而是會以發行者傳遞過來的資料為主,避免發行者與訂閱者兩邊的流水號不一致。

【補充】:可使用EXEC sys.sp_identitycolumnforreplication將有使用識別值的複寫欄位標記成NOT FOR REPLICATION,此部分有機會的話,擬在【透過attach/detach database來初始化交易式複寫的訂閱】文章中再提及其應用。

--以下指令碼是從發行者轉出,請在訂閱者(Subscriber)執行
use [RplTest1]
go

CREATE TABLE [dbo].[Table_3](
	[id] [smallint] NOT NULL,
	[name] [nvarchar](20) NULL,
	[memo] [nvarchar](50) NULL,
 CONSTRAINT [PK_Table_3] 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

CREATE TABLE [dbo].[Table_4](
	[id] [smallint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, --有使用識別值(identity)
	[name] [nvarchar](20) NULL,
	[memo] [nvarchar](50) NULL,
 CONSTRAINT [PK_Table_5] 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

檢查兩資料表是否已確實在訂閱者新增

--手動匯入資料至訂閱者(亦利用[SQL Server 匯出和匯入精靈]或其他方式)
:connect USER-PC\SQL2012STD_2
insert [RplTest1].[dbo].[Table_3] values(1,'num01','test')
insert [RplTest1].[dbo].[Table_3] values(2,'num02','test')
insert [RplTest1].[dbo].[Table_3] values(3,'num03','test')
insert [RplTest1].[dbo].[Table_4] values('identity01','test')
insert [RplTest1].[dbo].[Table_4] values('identity02','test')
insert [RplTest1].[dbo].[Table_4] values('identity03','test')
go
--核對兩邊資料筆數是否相同
:connect USER-PC\SQL2012STD
select @@servername,count(*) as '資料筆數' from [RplTest1].[dbo].[Table_3]
select @@servername,count(*) as '資料筆數' from [RplTest1].[dbo].[Table_4]
go
:connect USER-PC\SQL2012STD_2
select @@servername,count(*) as '資料筆數' from [RplTest1].[dbo].[Table_3]
select @@servername,count(*) as '資料筆數' from [RplTest1].[dbo].[Table_4]
go

5.啟用Log Reader

重新將Log Reader Agent啟動(若前面有停用AP服務,此時亦可一併啟用)

6.測試後續資料異動能否正常同步

--於發行者寫入測試資料
:connect USER-PC\SQL2012STD
insert [RplTest1].[dbo].[Table_3] values(4,'num04','test')
insert [RplTest1].[dbo].[Table_3] values(5,'num05','test')
insert [RplTest1].[dbo].[Table_3] values(6,'num06','test')
insert [RplTest1].[dbo].[Table_4] values('identity04','test')
insert [RplTest1].[dbo].[Table_4] values('identity05','test')
go

--核對發行者及訂閱者資料筆數
:connect USER-PC\SQL2012STD
select @@servername,count(*) as '資料筆數' from [RplTest1].[dbo].[Table_3]
select @@servername,count(*) as '資料筆數' from [RplTest1].[dbo].[Table_4]
go
:connect USER-PC\SQL2012STD_2
select @@servername,count(*) as '資料筆數' from [RplTest1].[dbo].[Table_3]
select @@servername,count(*) as '資料筆數' from [RplTest1].[dbo].[Table_4]
go

資料已同步,完成發行項的新增。

 

參考資料:

(1) 使用 NOT FOR REPLICATION 控制條件約束、識別和觸發程序

(2) Marking Identity Columns as “Not For Replication” in existing Publication

Jay Huang