阿洪之聲

2016年9月13日星期二

【-Ma の筆記本-】: [MS SQL] MERGE 同步處理兩個資料表

【-Ma の筆記本-】: [MS SQL] MERGE 同步處理兩個資料表: 今天研究了一下MS SQL SERVER 2008才新增的MERGE功能，主要簡化了比對兩個表格差異處時的新增、修改、刪除動作，以前需要分開撰寫INSERT、UPDATE、DELETE語法，透過MERGE比對資料，根據設定更新資料！

MS SQL HA的4種做法

資料來源

資料庫High Availability (HA)，微軟SQL Server共有4種做法。

Failover Clustering
Database Mirroring
Log Shipping
Replication

Failover Clustering
把兩台以上SQL Server組成Cluster，讓SQL Server個體(Instance)隨時保持同步的狀態。
每一台SQL Server稱為一個node，這些nodes連結在一個storage，例如san或是iscsi。

同一時間只有一個node是active，
當active node掛掉了，會failover到另一個node，以繼續提供服務。

在SQL Server個體的授權上，由於同一時間只有一個node是active，
所以只有active那個需要授權，passive的node是不需要授權的。

由於是Cluster架構，所以發生問題時，切換速度最快！
但因要綁storage，所以硬體價格最貴。

Database Mirroring
透過SQL的Mirror鏡像機制，將主Server(Principal)的資料庫，覆寫於目標Server(Mirror)中。

Mirror機制是針對資料庫進行覆寫，
不同於Cluster機制是將個SQL Server Instance進行同步。

有High-Safety Mode/High-Performance Mode兩種模式，
其中High-Safety Mode需再搭配一台Server(Witness)來進行auto failover的切換。
系統切換時，active的Server IP會變成目標Server IP，
所以在程式開發上，必需將Connection String中加入Failover_Partner的關鍵字，
以自動切換到active主機。

於Mirror機制下，目標資料庫是處理還原狀態，無法提供使用，
所以目標資料庫是不需要授權的。

參考文章http://caryhsu.blogspot.tw/2011/12/sql-s...-mirroring.html

Log Shipping
透過SQL的Log Shipping交易記錄傳送機制。
利用排程，於固定時間內，將主Server的資料庫，覆寫到目標Server中。

Log Shipping與Mirror相同，都是針對資料庫進行覆寫，而不是SQL Server Instance。
與Mirror不同的是，它只能進行資料庫覆寫，無法進行auto failover切換。

當主Server掛掉時，可藉由手動還原log方式，將資料庫上線供AP使用。
由於是透過排程於固定時間內覆寫資料，所以會資料遺失上的風險。

於Log Shipping機制下，目標資料庫是處理還原狀態，無法提供使用，
所以目標資料庫是不需要授權的。

參考文章http://caryhsu.blogspot.tw/2012/02/log-shipping.html

Replication
Replication複寫是透過SQL Agent將主Server的資料庫複製後，再發行到另一台Server上。

Replication與Log Shipping及與Mirror相同，都是針對資料庫進行覆寫，
而不是SQL Server Instance。

在Replication機制中，主Server與目標Server均可讀寫。
主Server上的異動會立即反映到目標Sever，
但目標Server的異動則不會影響主Sever。

當主Server掛掉時，必需手動修改AP對應到目標Server。

於Replication機制下，主Server與目標Server均是獨立且完整讀寫之個體；
所以不論主資料庫或是目標資料庫，都是需要授權的。

參考文章http://blog.xuite.net/tolarku/blog/41423198

SQL Server 2008 複寫實作

資料來源

你不可不知的複寫常識
複寫用來複製資料和資料庫物件的一項強大的功能，是大型資料庫或資料庫同步維持資料一致性的功能
透過各種網路、撥接連線，將資料散佈到不同地點上，即然是複寫了顧名思義，就是將資料同步的寫到各個不同的資料庫伺服器上

你不可不知的複寫常識複寫用來複製資料和資料庫物件的一項強大的功能，是大型資料庫或資料庫同步維持資料一致性的功能
透過各種網路、撥接連線，將資料散佈到不同地點上，即然是複寫了顧名思義，就是將資料同步的寫到各個不同的資料庫伺服器上
當然在整個資料庫的複寫中，也有考量到效能處理及資料庫結構問題，又分為三種複寫架構：交易式、合併式、快照式。

快照式複寫：資料變更數量大，但次數不用太頻繁的同步複寫時，最適合使用快照式複寫。
在所有複寫功能中快照式在「發行者」端負擔較小，因為他不用追踨累加變更的資料，只需要將資料庫中的資料做快照即可。
例如，在整個整批交易中，每天共有三十萬筆資料，一天只需要傳回總部一次，那麼快照式複寫就可以較有效率的將資料複寫回去。
※此種複寫需要每一張表都有Identify欄位。

交易式複寫：當資料有任何改變時，會主動的傳遞到訂閱者，預設交易式複寫是唯讀存取，因此在訂閱者端有任何資料的改變
是不會傳遞回到發行者端的。不過交易式複寫是可以設定成訂閱者亦可更新資料。
這種複寫狀態會有較大的彈性並且於資料端有大量的更新或插入、刪除的動作，適合非MS SQL Server資料庫做同步的複寫方式。
※此種複寫在發行資料庫下會主動的配置交易記錄，透過交易記錄來執行複寫轉送，當使用這種複寫方式在資料尚未完全移動到散發資料庫前，記錄檔無法被截斷。
※所有交易式複寫的資料表上，必須包含主索引鍵，否則無法被利用來發送。

合併式複寫：與交易式複寫很類似，是在發行者上發佈後，訂閱者存資料時，之主動的交換最後一次同步處理所變更過的資料。
通常這種複寫是較適合在多個訂閱者可能會在不同時間之下更新相同的資料，較容易產生資料衝突，當資料產生衝突時，必須由DBA進行排解。
※此種複寫會自動建立一組GUID資料行，且支援Timestamp資料行，在訂閱者套用快照集時會重新產生timestamp，驗證timestamp是否為可用快照。

環境限制和需求不論在那一種複寫狀況之下，建立快照集資料夾的安全問題都是需要被重視的，因此在資料庫複寫的過程中，快照夾實體目錄權限就必須考慮進來，
使用何種複寫則必須視你所貼近的環境來選用，若資料庫設計時，並無使用identify，或並無使用索引欄位，那麼於快照及交易複寫就不適用，除非改變資料庫內容。

實作注意事項在實作時，必須先選擇何者為發行者、散發者、訂閱者…等等的角色定義。(總是要知道誰負責發資料、誰是來接受同步資料的角色吧)
多數的工作是著在發行者身上，其它的資料處理同步，就可以透過代理程式來決定運作在訂閱者發起同步令命，還是由訂閱者發起呢?
若是遠端的資料庫同步(跨WAN)則必須透過VPN達成複寫或是Web同步處理方式，畢竟複寫是必須透過網芳、或XML訊息傳遞來處理。
在實作的過程中，有一個問題產生了，因SQL是建置在WINDOWS之上，又有使用網芳傳遞，那麼如何確認每個資料庫的同步是沒有問題的呢?
因此在複寫的安全架構下，必須將SQL Server Agent的啟動帳戶設定為相同的實體本機帳號，另外網路上存取網芳亦必須開通由這幾台db可存取。

SQL Server 2008 數據庫同步的兩種方式（發布、訂閱）

資料來源
時間:2012-07-30 18:03來源:Internet 作者:Internet
1、找到數據庫服務器下的【复制】--【本地發布】，選擇【新建發布】。如下圖：　　2、選擇待發布的數據庫。如下圖：　　3、選擇發布類型。這裏選擇的默認類型【快照發布】。幾種發布類型的區別，SQL
1、找到數據庫服務器下的【复制】--【本地發布】，選擇【新建發布】。如下圖：