五餅二魚工作室

SQL Server 從很早之前就開始支援「複寫」的功能，讓某些資料表的異動可以用非同步的方式，抄寫到其他台電腦的資料庫，以達到資料表同步的功能。原本這個是一個蠻不錯的功能，但在最近幾次中，就重複遇到一些客戶因為設定異常所造成的困擾，因此就針對這些同一台電腦中，不同資料庫之間的複寫，提出我的一些處理方式。

一開始的起因是這樣的，某個客戶反映他們的資料庫備份，從某一個時間開始所花的時間越來越久，檔案的成長大小也變得越來越大了，希望我們來協助查找原因。

原本這些本來不是我的守備範圍，但看在同事拿個星巴克的拿鐵誘惑之下，就來查看一下。基本上這種復原模式是「簡單」模式的資料庫，應該是不大可能會那麼大，且不論怎麼壓縮也都沒有用，因此我們透過 DMV 的指令來做查看。

select name , log_reuse_wait , log_reuse_wait_desc
from sys.databases where name = DB_NAME()

果不其然又是一個因為 REPLICATION 失敗，造成交易紀錄檔不斷地成長的一個資料庫。當知道問題發生的原因，要處理起來就很簡單了，先用 sp_removedbreplication 的指令將設定清除之後，讓交易紀錄可以恢復正常後，就可以壓縮交易紀錄檔了。然後再把資料庫複寫重新設定回去，其實也就沒有問題了。

其實上述這個案例，如果我們只看問題，基本上算是個很容易的處理，但如果我們來看這一個問題背後所存在的問題，會發現這個問題蠻好玩的。因為原本設定 Replication 的人員，是想把來源資料庫的某些資料表，類似 Master Data Services（MDS）的方式來做到主要資料管理（Master Data Management, MDM），但又不想用太複雜的方式，因此就選用複寫來達到他的目的。舉例來說可能企業中有很多系統的資料庫，會希望像是商品資料、員工資料這些散落在每個資料庫中都有的，只要更改其中一個資料庫就可以同步到其他的資料庫上，避免不同資料庫之間這些共用資料有差異，造成系統使用上的困擾。

雖然複寫是一個蠻好用的方式，但如果設定不當就會像上述案例一般，交易紀錄檔會一值成長，甚至來源資料庫的某些資料在維護的時候，會因為複寫到其他資料庫上，又造成大量的 I/O ，使得效能也不會是很好的狀況。以下我們就用 Northwind 的資料庫來做個展示，如何利用一些簡單的方式來簡化這樣的處理。

---

首先在我們的環境中會有兩個資料庫，分別是我們的來源資料庫 Northwind 和目的資料庫 TEST 在我們的 Server 上面，接下來我們用 Northwind 資料庫裡面的 Customers 的資料表來作範例

首先我們用以下語法，在 TEST 資料庫建立一個 mock 的結構描述( SCHEMA )，建立這個結構描述的主要用意是來用這個紀錄其下所有的都是假的物件

USE [TEST]
GO
CREATE SCHEMA [mock] AUTHORIZATION [dbo]
GO

當建立好之後，接著我們利用同義字( SYNONYM ) 的方式，使得 mock.Customers 這個物件對應到 [Northwind].[dbo].[Customers] 的物件上面去

USE [TEST]
GO
CREATE SYNONYM [mock].[Customers] FOR [Northwind].[dbo].[Customers]
GO

接下來我們再用 View 來封裝一層，這樣在 TEST 資料庫下面，就像是有個 Customers 的資料表，他的內容是跟 [Northwind].[dbo].[Customers] 的資料表內容是一樣的，用法也都類似，可以直接用 SQL 去進行 select , insert , update 和 delete 的相關處理。

USE [TEST]
GO
CREATE VIEW dbo.Customers
AS
SELECT [CustomerID]
      ,[CompanyName]
      ,[ContactName]
      ,[ContactTitle]
      ,[Address]
      ,[City]
      ,[Region]
      ,[PostalCode]
      ,[Country]
      ,[Phone]
      ,[Fax]
  FROM [mock].[Customers]

基本上做到這裡應該就可以了，但如果後續還想要讓在 TEST 資料庫下面，不要直接去更動 Customers 的資料表，這裡我們還可以用一個觸發( TRIGGER ) 的技巧來處理

USE [TEST]
GO
CREATE TRIGGER dbo.TRIG_Customers 
   ON  dbo.Customers 
   INSTEAD OF INSERT,DELETE,UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
    -- Insert statements for trigger here
	
END
GO

從範例中可以看到，我們利用一個 INSTEAD OF 的觸發 ( TRIGGER) 作用在 View 的上面，而這個 Trigger 並沒有做任何事情，用這樣來騙過 SQL Server。這裡我們做個測試的範例，我們下了 SELECT 和 DELETE 的語法來測試

SELECT * FROM Customers

DELETE FROM Customers WHERE CustomerID < 'B'
GO 2

從執行結果的訊息中可以看出，資料表內實際有 91 筆的資料，雖然看起來好像有成功的下了 DELETE 的指令，似乎也正常刪除掉四筆的資料。可是跑第二次的 SELECT 的時候，從筆數上看起來是沒有真正的刪除掉資料

而且也是因為這個是 INSTEAD OF 的 TRIGGER，因此在這樣的執行過程中，也不會因為要寫入交易紀錄檔，而造成增加資料異動的時間。因此我們又再次調整上述的指令，將 GO 後面的數字從 2 改成 50000，也透過活動監視器的察看，會看到在這樣刪除的狀況下，資料庫檔案都沒有任何寫入的異動會增加磁碟的負擔。

因此在這個範例中，我們同時間利用 SYNONYM , VIEW 和 INSTEAD OF TRIGGER 的搭配下，就可以很容易地做到在同一台主機上不同的資料庫，可以達到資料庫類似同步的功能了。