SQL Server資料庫鏡像基於可用性群組容錯移轉

標籤：database mirroring   failover   wmi event   

SQL Server資料庫鏡像基於可用性群組容錯移轉

 

微軟從SQL Server 2005開始引入資料庫鏡像，很快成為一個流行的容錯移轉解決方案。資料庫鏡像的一個大的問題是容錯移轉是基於資料庫層級的，因此，如果某個資料庫故障，鏡像只會針對這個資料庫切換，但是，其他資料庫都仍然在主伺服器上。缺點是越來越多的應用程式是基於多個資料庫來構建，所以，如果某一個資料庫容錯移轉而其他資料庫仍然在主伺服器上，那應用程式將無法工作。當這種情況發生的時候，我如何知曉？並執行該應用程式調用的所有資料庫一起容錯移轉呢？

 

在SQL Server的所有功能中，有一種方式可以在資料庫鏡像故障發生時得到警示或者檢查發生的事件。用於資料庫鏡像的事件提醒並不如你想象的那樣直接，但它可以實現該功能。

 

對於資料庫鏡像，你可以選擇使用跟蹤事件，或者配置SQL Server警示來檢查對於資料庫鏡像狀態的改變的WMI（Windows Management Instrumentation）事件。

 

在開始之前，我們需要一些準備工作：

 

鏡像資料庫和msdb資料庫必需啟用service broker。可以使用如下查詢來檢查：

SELECT name, is_broker_enabledFROM sys.databases

 

如果service broker的值不為1，你可以對每個資料庫使用以下命令開啟。

ALTER DATABASE msdb SET ENABLE_BROKER

 

如果SQL Server代理正在運行，那麼這個命令將不會完成。你需要先停止SQL Server代理，運行以上命令，然後再次啟動SQL Server代理。

 

最後，如果SQL Server代理沒有運行，你需要啟動它。

 

建立警示

 

首先，我們來建立警示，與其他警示不同的是，我們會選擇”WMI event alert“類型。

 

使用SSMS串連到執行個體，展開SQL Server Agent，在Alerts上點擊右鍵，選擇“New Alert“。

650) this.width=650;" title="clip_image001" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px;" border="0" alt="clip_image001" src="http://s3.51cto.com/wyfs02/M01/8E/A3/wKiom1jHlPrThJNDAAAf2S9mxqE042.png" height="227" />

 

彈出”New Alert“介面，選擇“WMI event alert”。需要注意一下查詢的Namespace。預設，SQL Server會根據你操作的執行個體選擇正確的名稱空間。

650) this.width=650;" title="clip_image003" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px;" border="0" alt="clip_image003" src="http://s3.51cto.com/wyfs02/M00/8E/A2/wKioL1jHlPuih0R4AAB6arTpFrw515.jpg" height="227" />

 

對於Query，使用以下查詢：

SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE State = 7 OR State = 8

 

該資料從WMI擷取，當資料庫鏡像狀態變為7（手動容錯移轉）或8（自動容錯移轉）時，將會觸發作業或者提醒。

 

此外，你可以進一步對於每一個特定的資料庫定義查詢：

SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE State = 8 AND DatabaseName = ‘Test‘

 

可以閱讀下線上說明中DATABASE_MIRRORING_STATE_CHANGE的內容。

以下是可以被監控到的不同狀態改變的列表。更多內容，可以從Database Mirroring State Change Event Class裡找到。

• 0 = Null Notification

• 1 = Synchronized Principal with Witness

• 2 = Synchronized Principal without Witness

• 3 = Synchronized Mirror with Witness

• 4 = Synchronized Mirror without Witness

• 5 = Connection with Principal Lost

• 6 = Connection with Mirror Lost

• 7 = Manual Failover

• 8 = Automatic Failover

• 9 = Mirroring Suspended

• 10 = No Quorum

• 11 = Synchronizing Mirror

• 12 = Principal Running Exposed

• 13 = Synchronizing Principal

 

在Response介面，可以配置當事件發生時如何處理。你可以配置當警示觸發時執行一個作業，或者給操作者發送一個提醒。

650) this.width=650;" title="clip_image005" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px;" border="0" alt="clip_image005" src="http://s3.51cto.com/wyfs02/M01/8E/A2/wKioL1jHlPzS62NsAABoTkK81x4539.jpg" height="194" />

 

最後，如下所示可以配置額外的選項。

650) this.width=650;" title="clip_image007" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px;" border="0" alt="clip_image007" src="http://s3.51cto.com/wyfs02/M02/8E/A2/wKioL1jHlP2iGB3mAABnxx7AjAk157.jpg" height="234" />

 

配置樣本

 

例如，一個應用程式有調用3個資料庫（Customer、Orders和Log），如果其中一個資料庫自動切換，你也想要兩外兩個資料庫也一起容錯移轉。此外，這個鏡像配置了一個見證伺服器，如果發生故障，會自動容錯移轉。

 

以下展示了如何配置。

 

首先，我們只針對這3個資料庫配置警示。

650) this.width=650;" title="clip_image009" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px;" border="0" alt="clip_image009" src="http://s3.51cto.com/wyfs02/M02/8E/A3/wKiom1jHlP6Sq7qkAACKxopoKks836.jpg" height="226" />

 

然後配置警示觸發後運行哪個作業。

650) this.width=650;" title="clip_image011" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px;" border="0" alt="clip_image011" src="http://s3.51cto.com/wyfs02/M00/8E/A2/wKioL1jHlP_BwEEQAABoPQrka0w830.jpg" height="193" />

 

我們需要建立“Failover Databases”作業，用於當警示觸發的時候運行。

 

對於SQL Server代理的“Failover Databases”作業，作業步驟如下：

IF EXISTS (SELECT 1 FROM sys.database_mirroring WHERE db_name(database_id) = N‘Customer‘ AND mirroring_role_desc = ‘PRINCIPAL‘)ALTER DATABASE Customer SET PARTNER FAILOVERGOIF EXISTS (SELECT 1 FROM sys.database_mirroring WHERE db_name(database_id) = N‘Orders‘ AND mirroring_role_desc = ‘PRINCIPAL‘)ALTER DATABASE Orders SET PARTNER FAILOVERGOIF EXISTS (SELECT 1 FROM sys.database_mirroring WHERE db_name(database_id) = N‘Log‘ AND mirroring_role_desc = ‘PRINCIPAL‘)ALTER DATABASE Log SET PARTNER FAILOVERGO

 

以上的ALTER DATABASE命令對其他沒有自動轉移的資料庫強制容錯移轉。這跟你再GUI介面上點擊“Failover”是一樣的。

參考：

https://msdn.microsoft.com/en-us/library/ms191502.aspx

https://msdn.microsoft.com/en-us/library/ms186449.aspx

本文出自 “SQL Server Deep Dive” 部落格，請務必保留此出處http://ultrasql.blog.51cto.com/9591438/1906335

SQL Server資料庫鏡像基於可用性群組容錯移轉