SQL Server 複製：事務發布（讀寫分離）

標籤：ros   產生   timestamp   win   use   模式   err   資料庫服務   串連   

一、背景

　　在複製的運用情境中，事務發布是使用最為廣泛的，我遇到這樣一個情境：在YangJiaLeClub資料庫中有表、預存程序、視圖、使用者定義函數，需要提供給其它程式讀取放入緩衝，程式需要比較及時的擷取到這些資料，需要從許可權和效能控制的角度出發，我採用了SQL Server的事務複製技術和timestamp，下面只講述事務複製的搭建過程；

 

二、實現過程

(一) 環境資訊

	作業系統	IP	伺服器名稱	資料庫版本	資料庫名稱	資料庫帳號資訊
發行伺服器	Windows 10 企業版	192.168.2.100	DESKTOP-SDIK64J	SQL Server 2008 R2	YangJiaLeClub	sa/bwl reg/bwl
散發者	與發行伺服器同一台機器
訂閱伺服器1	Windows Server 2008r2 Enterprise	192.168.2.117	Win-DB-Slave1	SQL Server 2008 R2	YangJiaLeClub	sa/bwl
訂閱伺服器2	Windows Server 2008r2 Enterprise	192.168.2.118	Win-DB-Slave2	SQL Server 2008 R2	YangJiaLeClub	sa/bwl

1.發行伺服器：

 1.1 在防火牆中設定入棧規則，開放1433和1434連接埠

      

 

2.訂閱伺服器：

2.1.Sql Native Client 10配置（32位）建立別名：

 

 2.Host 伺服器名與IP映射（由於發布訂閱伺服器名稱必須是：伺服器+“\”+執行個體名訪問）

 目錄： C:\Windows\System32\drivers\etc

 

3.控制台\使用者帳戶\認證管理員

    建立憑據

 

 

 

(二) 搭建步驟

A. 發行伺服器配置

　　首先在發行集資料庫和訂閱資料庫上建立相同的帳號和密碼（reg/bwl），並且設定YangJiaLeClub資料庫的安全性實體，設定這樣的帳號的目的就是為了和程式串連到資料庫的帳號區分開，可以做許可權上的控制，方便問題的排查；

--更改安全性實體的所有權

ALTER AUTHORIZATION ON DATABASE::[YangJiaLeClub] TO [reg]

在F盤目錄下建立檔案夾：F:\DB\Net\ReplData，並設定這個檔案夾為共用目錄，共用使用者為biwl

 

（Figure1_1：檔案夾許可權）

這裡需要設定SQL Server Agent登陸帳號為上面檔案夾訪問使用者biwl（發行伺服器和訂閱伺服器都需要建立biwl相同帳號和密碼設定SQLServer代理服務）

 

（Figure1_2：SQL Server Agent登陸帳號）

 

（Figure2：配置分發-散發者）

　　如果你設定快照檔案夾路徑為：F:\DB\Net\ReplData，即使你的發行伺服器本身就是散發者，如果訂閱伺服器是另外一台機器，那麼在請求（Pull）訂閱（如果是推送（Push）訂閱就沒有這個限制）模式下訂閱代理是無法訪問到這個快照檔案的；除非你發行伺服器、散發者和訂閱伺服器都是同一台機器；你應該設定快照檔案夾路徑為：\\DESKTOP-SDIK64J\ReplData；

 

（Figure3：快照檔案夾）

（Figure4：資料庫）

• 快照發布：隔一段時間會覆蓋訂閱伺服器的資料庫，在訂閱伺服器上做的修改同樣被覆蓋；
• 事務發布：是一種接近即時地從源到目標分發資料的方法；
• 具有可更新訂閱的事務發布：訂閱伺服器可更新發行伺服器的資料；
• 合并發布：發行伺服器和訂閱伺服器的更新都會同步到對方，注意ID在合并發布上的衝突

（Figure5：事務發布）

注意表必須有主鍵才能進行複製，選擇你必要的欄位，這樣可以減輕快照檔案的大小和傳輸時間，而且在商務邏輯上更加安全，如果有需要，你還可以對記錄進行過濾；

（Figure6_1：表欄位）

 

（Figure6_2：預存程序）

 

（Figure6_3：視圖）

 

（Figure6_4：使用者定義函數）

（Figure7：快照代理）

（Figure8：安全設定）

使用上面建立好的ReplicationUser帳號作為串連到發行伺服器的帳號和密碼；

（Figure9：使用剛剛建立的帳號密碼）

（Figure10：建立發布）

（Figure11：發布名稱）

 

（Figure12：查看複製情況）

 

B. 訂閱伺服器配置

建立完發行伺服器（散發者也一起建立了），接下來就可以建立訂閱伺服器了，下面是具體的步驟：

 

（Figure13：尋找發行伺服器）

（Figure14：尋找發行伺服器）

（Figure15：選擇發布）

（Figure16：提取訂閱）

（Figure17：訂閱資料庫）

（Figure18：訂閱串連）

（Figure19：帳號密碼）

（Figure20：代理計劃）

（Figure21：初始化訂閱）

（Figure22：建立訂閱）

（Figure23：訂閱）

（Figure24：發行伺服器上的訂閱）

（Figure25：複製監視器）

（Figure26：訂閱資料庫新增的表）

（Figure27：表資料）

 

三、注意事項

1. 在SQL SERVER下實現發行伺服器和訂閱伺服器的通訊正常（即可以互訪），開啟1433連接埠，在防火牆中設定入站規則；

2. 發行伺服器與訂閱伺服器的SQL Server Agent代理帳號必須設定的一樣，否則不能互訪；

3. 如果你希望在複製的過程中一併複製非叢集索引，可以對發布屬性-項目進行如下設定，修改完之後需要重建快照；

（Figure28：非叢集索引複製）

4. 複製代理：快照代理（snapshot agent） 分布式代理（Distribution agent）日誌讀代理（log Reader agent） 合并代理（Merge agent） 隊列讀代理（Queue Reader Agent）

5. 適合使用複製的一些情境包括：

1) 負載平衡：通過將資料複製到其它資料庫伺服器來減少當前伺服器的負載，比如說最典型的應用就是分發資料來分離OLTP和OLAP環境；

2) 分區：將經常使用的資料和曆史資料隔離，將曆史資料複製到其它資料庫中；

3) 授權：將一部分資料提供給需要使用資料的人，以供其使用；

4) 資料合併：每個地區都有其各自的資料，將其資料進行合并。比如一個大公司，每個地區都有其各自的銷售資料，總部需要匯總這些資料；

5) 容錯移轉：複製所有資料，以便故障時進行轉移；

6. 快照式複寫或事務複製產生快照檔案的類型有：

架構 (.sch)、資料 (.bcp)、約束和索引 (.dri)、約束 (.idx)、觸發器 (.trg)（只用於更新訂閱伺服器）、壓縮的快照檔案 (.cab)。

 

四、疑問

1. SQL Server 只有在完整記錄模式下才能使用複製嘛？

解惑：在簡單模式下一樣可以使用複製；

2. 如果是跨網段（跨機房）的發布與訂閱，有沒辦法實現？需要注意什嗎？

解惑：可以通過修改host檔案的方式搭建複製，請參考：SQL Server跨網段（跨機房）複製

3. 如果說上面的情況可以在host設定，但是如果有連接埠映射的，host也無法設定吧？

解惑：在SQL Server組態管理員裡建立別名，同樣可參考：SQL Server跨網段（跨機房）複製

4. 訂閱的形式可以選擇發送訂閱或者提取訂閱，提取訂閱降低散發者處理工作的開銷，這個開銷有多大呢？怎麼計算影響？

解惑：只有在有很多訂閱伺服器的時候才比較明顯，發送訂閱與提取訂閱更大的區別是在管理方面的不同；

 

五、參考文獻

快照式複寫

SQL Server 複製

timestamp (Transact-SQL)

SQL Server 複製訂閱與發布

SQL Server複製入門(一)----複製簡介

SQL Server複製入門(二)----複製的幾種模式

使用快照初始化訂閱

複製

SQL Server 複製：事務發布（讀寫分離）