[SQL] 2008 Transactional Replication

本篇是說明MSSQL 2008 該進行哪些步驟,完成此資料同步覆寫的機制。

 

簡單的利用上圖來說,當 Publisher 與 Subscriber 一致後,若有 User 或 Application 修改了來源(Publisher)資料庫,便會引發上述六個步驟同步(Replication)到目的地(Subscriber)的資料庫中。

  1. User/ Application 更動資料
  2. Log reader Agent 讀取異動的 Transaction Log 
  3. 修改 Distributor 的 Snapshot
  4. Distribution Agent 發現異動
  5. 將此 Log 傳 Push/Pull 給 Subscriber
  6. 修改 Subscriber 的資料庫內容

本篇就是要來說明該進行哪些步驟,完成此資料同步覆寫的機制。

---------------------------------------------

準備段~由於此同步覆寫機制是利用 SQL Agent 來完成,並且會需要將一些檔案寫到分享的資料夾,並提供給 subscriber 使用,所以權限的設定將會影響正常執行與否的因素。底下列出一些基本的需求或是檢查項目,供各位實作前先做檢查一下

  • 使用 SSMS 登入主機時,請使用主機名稱,而不要用「.」來表示 Local
  • 為了避免此實驗執行失敗,我先以本機的 Administrator 身分來執行 SQL Agent
  • 以「SELECT @@servername」檢查是否與本機名稱一致。若安裝SQL前與後有變更伺服器名稱,將會導致不一致,也會造成 replication 失敗。
  • 再進一步檢查 \\SSMS\Security\Login 的名稱,要為正確的「Servername\Administrator」
  • 建立分享資料夾(Share Folder),並授與足夠的權限給執行 SQL Agent 身分來做檔案寫入的動作。
  • 確認要做資料覆寫的資料庫,其 Recovery Mode = Full

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

簡單的說只要三個步驟,就完成了資料覆寫的機制

  • 設定散發者(設定散發分享資料夾)
  • 建立發行集New Publication)
  • 觀察 Share Folder 是否已經建立 Snapshot 檔案
  • 設定訂閱者

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

設定散發者(設定散發分享資料夾)

↓ 設定散發者 Distributor

↓ Snapshot Folder 就是之前提的分享資料夾(Share Folder),雖然他預設出現的資料夾目錄是 C:\xxooxx... ,但是這邊是必須使用UNC(Universal Naming Convention)格式的目錄,也就是 \\ServerName\Path 網路芳鄰的路徑方式 

↓ 設定名稱與資料庫檔案的位置

↓ 設定資料來源 Publisher,這時可順便看一下 Snapshot Folder 是否設定正確

↓ 勾選精靈結束時,要做哪些事

↓ 也可以將整個動作的 Script 產生出來

/*** Distributor. Script Date: 2010/12/28 上午01:10:13 ******/
use master
exec
 sp_adddistributor @distributor = N'SNAKE', @password = N''
GO
exec
 sp_adddistributiondb @database = N'distribution', @data_folder = N'D:\SQL2008\DB_data',@log_folder = N'D:\SQL2008\DB_data', @log_file_size = 2, @min_distretention = 0,@max_distretention = 72, @history_retention = 48, @security_mode = 1
GO
use
 [distribution] 
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U ')) 
      create table UIProperties(id int) 
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties',null, null))) 
      EXEC sp_updateextendedproperty N'SnapshotFolder', N'\\snake\Replicate', 'user', dbo, 'table','UIProperties' 
else 
     EXEC sp_addextendedproperty N'SnapshotFolder', N'\\snake\Replicate', 'user', dbo, 'table','UIProperties'
GO
exec
 sp_adddistpublisher @publisher = N'SNAKe', @distribution_db = N'distribution',@security_mode = 1, @working_directory = N'\\snake\Replicate', @trusted = N'false',@thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO

↓ 建立 Distributor 散發目錄設定,這時候 Share Folder 當然還是空的囉~因為還沒設定 publication

--- 建立發行集New Publication) ---

↓ 在 SSMS 下執行 New Publication...

↓ 選取想要做覆寫的資料庫

↓ 選擇散佈形式,本篇採用「Transactional publication」所以選第二個囉

↓ Transactional Publication 是 Table-Level,分享資料庫物件可以任選你想要覆寫的標的

↓ 如果想進一步做 Filter ,也就是以 where 條件來過濾,可以在此「Filter Table Rows」來做

↓ 決定要不要等設定完成後,立即做 Snapshot

↓ 決定執行此  Snapshot Agent 的身分,我們可以直接選用跟 SQL Agent 一樣就可以了

↓ 完成發行集了,給的容易辨認的名稱吧

↓ 成功~


↓ --- 觀察 Share Folder 是否已經建立 Snapshot 檔案 ---


--- 設定訂閱者 --------

↓ 發現在 Snapshot Folder (Share Folder) 產生 Snapshot 檔案後,就可以在目的地主機,開始進行訂閱的動作了

↓ 選擇發行者(Publisher)

↓ 選擇發行集

↓ 這邊選擇 Distributor 是以 push 或 pull 方式傳送給訂閱者

  • Push 是由 Distributor 主動送給 subscriber
  • Pull 是由 Subscriber 來向 Distributor 索取

↓ 選擇訂閱者

↓ 訂閱者目的地的資料庫名稱

↓ 設定 Distributor 的執行身分,跟 SQL Agent 用相同身分去執行就ok了

↓ 選擇「持續執行 Run Continuously」或「有需要在執行 Run on demand oly」

↓ 初始化訂閱者

↓ 成功

↓經過上一個步驟後,就會在目的地主機上建立設定的 subscriber 的資料庫,當然你也可以對此資料庫進行查詢的動作。

QQ:那 replication 過來的資料庫能不能修改?

Ans:可以囉~

-----但是 ------

請回頭看第一張圖,注意看箭頭方向,都是由 Publisher 經 Distributor 到 Subscriber 的方向

那意思就是說

你在 Subscriber 修改資料是不會影響到原始資料(publisher 的 data)

Reference:http://msdn.microsoft.com/en-us/library/ms151198.aspx 

~ End