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

2017-04-21

850
0
[MSSQL]Replication
2017-04-23

在前一篇文章【透過備份及還原資料庫來初始化交易式複寫的訂閱】中，談到利用資料庫備份檔來處理交易式複寫的訂閱初始化，緊接著來說明當發行者(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

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