如何在鏡像狀態更改時創建執行作業/過程的事件通知

November 10, 2020

我按照這個問題的順序問這個問題我可以使用 T-SQL 通過 TCP 發送一個字元串嗎？
Remus Rusanu 揭露了它似乎是解決我問題的最佳解決方案，但是……我太不成熟了，無法理解和做出他所說的一切。
到目前為止，我認為我需要為 DATABASE_MIRRORING_STATE_CHANGE 創建通知事件，對嗎？
我如何創建此事件通知，以在其觸發時在表中插入一行，該行儲存時間戳和來自通知的 ID。
到目前為止，我正在為每個 ID 設置一個警報，每個警報都執行這樣的作業（此範例適用於 ID = 1）：
   DECLARE @state AS varchar(50);
   SELECT @state = mirroring_state_desc FROM SYS.database_mirroring WHERE mirroring_guid IS NOT NULL;
   IF (@state IS null) SET @state = ' ';
   INSERT INTO MirroringAlerts (DateTime, alertID, alertDesc, Sync, alertCreator) values (SYSDATETIME(), 1, 'Principal synchronized with W ', @state, @@SERVERNAME)
基本上我正在這個數據庫中創建一個內部日誌：
CREATE TABLE [dbo].[MirroringAlerts](
   [DateTime] [datetime] NOT NULL,
   [alertID] [smallint] NOT NULL,
   [alertDesc] [nchar](50) NOT NULL,
   [Sync] [nchar](12) NOT NULL,
   [alertCreator] [nchar](128) NULL
) ON [PRIMARY]
但是這樣……警報沒有足夠快地觸發……所以我失去了資訊……
您能告訴我如何通過為數據庫鏡像狀態更改事件創建事件通知來程式此行為嗎？
最好的祝福

第 1 步：創建一個服務來接收通知和一個隊列：
use msdb;
go

create queue dbm_notifications_queue;
create service dbm_notification_service
   on queue dbm_notifications_queue
   ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
go

create event notification dbm_notifications
   on server   
   for database_mirroring_state_change
   to service N'dbm_notification_service', N'current database';
go
請注意，我正在使用msdb，這不是偶然的。因為如果您也在中創建相反的對話端點（目標），則從它發送伺服器級事件通知會msdb好得多msdb，這意味著目標服務和隊列也必須部署在中msdb。
第二步：創建事件通知處理流程：
use msdb;
go

create table dbm_notifications_errors (
   incident_time datetime not null,
   session_id int not null,
   has_rolled_back bit not null,
   [error_number] int not null,
   [error_message] nvarchar(4000) not null,
   [message_body] varbinary(max));
create clustered index cdx_dbm_notifications_errors 
   on dbm_notifications_errors  (incident_time);
go

create table mirroring_alerts (
   alert_time datetime not null,
   start_time datetime not null,
   processing_time datetime not null,
   database_id smallint not null,
   database_name sysname not null,
   [state] tinyint not null,
   [text_data] nvarchar(max),
   event_data xml not null);
create clustered index cdx_mirroring_alerts
   on mirroring_alerts (alert_time);   
go      

create procedure dbm_notifications_procedure
as
begin
   declare @dh uniqueidentifier, @mt sysname, @raw_body varbinary(max), @xml_body xml; 
   
   begin transaction;
   begin try;
       receive top(1)
           @dh = conversation_handle,
           @mt = message_type_name,
           @raw_body = message_body
       from dbm_notifications_queue;
       if N'http://schemas.microsoft.com/SQL/Notifications/EventNotification' = @mt
       begin
           set @xml_body = cast(@raw_body as xml);
            -- shred the XML and process it accordingly
            -- IMPORTANT! IMPORTANT!
            -- DO NOT LOOK AT sys.database_mirroring
            -- The view represents the **CURRENT** state
            -- This message reffers to an **EVENT** that had occured
            -- the current state may or may no be relevant for this **PAST** event
           declare @alert_time datetime
               , @start_time datetime
               , @processing_time datetime = getutcdate()
               , @database_id smallint 
               , @database_name sysname
               , @state tinyint
               , @text_data nvarchar(max);
               
           set @alert_time = @xml_body.value (N'(//EVENT_INSTANCE/PostTime)[1]', 'DATETIME');
           set @start_time = @xml_body.value (N'(//EVENT_INSTANCE/StartTime)[1]', 'DATETIME');
           set @database_id = @xml_body.value (N'(//EVENT_INSTANCE/DatabaseID)[1]', 'SMALLINT');
           set @database_name = @xml_body.value (N'(//EVENT_INSTANCE/DatabaseName)[1]', 'SYSNAME');
           set @state = @xml_body.value (N'(//EVENT_INSTANCE/State)[1]', 'TINYINT');
           set @text_data = @xml_body.value (N'(//EVENT_INSTANCE/TextData)[1]', 'NVARCHAR(MAX)');
           
           insert into mirroring_alerts (
               alert_time, 
               start_time,
               processing_time,
               database_id,
               database_name,
               [state],
               text_data,
               event_data)
           values (
               @alert_time, 
               @start_time,
               @processing_time,
               @database_id,
               @database_name,
               @state,
               @text_data,
               @xml_body);
       end
       else if N'http://schemas.microsoft.com/SQL/ServiceBroker/Error' = @mt
       begin
       set @xml_body = cast(@raw_body as xml);
       DECLARE @error INT
               , @description NVARCHAR(4000);
       WITH XMLNAMESPACES ('http://schemas.microsoft.com/SQL/ServiceBroker/Error' AS ssb)
       SELECT @error = CAST(@xml_body AS XML).value('(//ssb:Error/ssb:Code)[1]', 'INT'),
           @description = CAST(@xml_body AS XML).value('(//ssb:Error/ssb:Description)[1]', 'NVARCHAR(4000)');          
       
       insert into dbm_notifications_errors(
           incident_time,
           session_id, 
           has_rolled_back,
           [error_number],
           [error_message],
           [message_body])
       values (
           getutcdate(),
           @@spid,
           0,
           @error,
           @description,
           @raw_body);
           end conversation @dh;
       end
       else if N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' = @mt
       begin
           end conversation @dh;
       end
       commit;
   end try
   begin catch
       declare @xact_state int = xact_state(), 
           @error_number int = error_number(), 
           @error_message nvarchar(4000) = error_message(),
           @has_rolled_back bit = 0;
       if @xact_state = -1
       begin
           -- Doomed transaction, it must rollback
           rollback;
           set @has_rolled_back = 1;
       end
       else if @xact_state = 0
       begin
           -- transaction was already rolled back (deadlock?)
           set @has_rolled_back = 1;
       end
       insert into dbm_notifications_errors(
           incident_time,
           session_id, 
           has_rolled_back,
           [error_number],
           [error_message],
           [message_body])
       values (
           getutcdate(),
           @@spid,
           @has_rolled_back,
           @error_number,
           @error_message,
           @raw_body);
       if (@has_rolled_back = 0)
       begin
           commit;
       end
   end catch
end
go
編寫服務代理程序不是您的普通程式碼。必須遵循一定的標準，很容易誤入流沙地帶。這段程式碼展示了一些好的做法：
將消息出列和處理包裝在事務中。不費吹灰之力，一目了然。
始終檢查收到的消息類型。一個好的服務代理程序必須通過從它的一側結束對話來適當地處理Error和消息。EndDialog不這樣做會導致句柄洩漏（sys.conversation_endpoints增長）
始終檢查消息是否已被 RECEIVE 出隊。一些樣本 check@@rowcount after RECEIVE，這是完全可以的。此範常式式碼依賴於消息名稱檢查（沒有消息意味著 NULL 消息類型名稱）並隱式處理這種情況。
創建處理錯誤表。SSB 啟動過程的背景性質使得如果消息在沒有跟踪的情況下簡單地消失，則很難對錯誤進行故障排除。
此外，此程式碼還針對手頭的任務（監控 DBM）做了一些良好的實踐程式碼：
區分post_time（何時發送通知？），start_time（觸發通知的操作何時開始？）和processing_time（何時處理通知？）。post_time並且start_time可能相同或非常接近，但processing_time可能相差幾秒、幾小時、幾天post_time。有趣的審計通常是post_time.
由於post_time和processing_time不同，很明顯，在一個甚至通知啟動過程中的 DBM 監控任務沒有業務查看sys.database_mirroringview。該視圖將顯示處理時的目前狀態，可能與事件相關，也可能不相關。如果處理髮生在事件發布後很長時間（認為維護停機時間），則問題很明顯，但如果 DBM 非常快速地更改狀態並在一個行（經常發生）：在這種情況下，如您發布的程式碼中的處理，會在事件發生時對其進行審核，但會記錄目前的最終狀態。以後閱讀這樣的審計可能會非常混亂。
始終審核原始 XML 事件。這樣，您以後可以查詢此 XML 以獲取未“分解”到審計表中的列中的任何資訊。
第 3 步：將過程附加到隊列中：
alter queue dbm_notifications_queue
with activation (
   status=on,
   procedure_name = [dbm_notifications_procedure],
   max_queue_readers = 1,
   execute as  owner);
在鏡像中涉及的所有實例上重複上述步驟可確保無論哪個實例是主體，您都會收到通知。

在閱讀第 6 章後，我不得不購買“Pro SQL Server 2008 鏡像”，我發現這樣做的步驟是：

檢查是否啟用了服務代理

SELECT CASE is_broker_enabled
WHEN 1 Then 'Enabled'
ELSE 'Disabled'
END
FROM sys.databases
WHERE name = 'DataBaseName'

如果沒有，執行

ALTER DATABASE DataBaseName set ENABLE_BROKER;

創建我們希望在通知事件到達時觸發的儲存過程：

CREATE PROCEDURE dbo.dba_MirroringStateChanged
AS
DECLARE @Message XML,
       @DBName sysname,
       @MirrorStateChange INT,
       @ServerName sysname,
       @PostTime datetime,
       @SPID INT,
       @TextData NVARCHAR(500),
       @DatabaseID INT,
       @TransactionsID INT,
       @StartTime datetime;
SET NOCOUNT ON;
-- Receive first unread message in service broker queue
RECEIVE TOP (1)
@Message = CAST(message_body AS XML)
FROM DBMirrorQueue;

BEGIN TRY
   -- Parse state change and database affected
   -- 7 or 8 = database failing over,
   --11 = synchronizing,
   --1 or 2 = synchronized
   SET @MirrorStateChange =
   @Message.value('(/EVENT_INSTANCE/State)[1]', 'int');
   SET @DBName =
   @Message.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'sysname');
   SET @ServerName =
   @Message.value('(/EVENT_INSTANCE/ServerName)[1]', 'sysname');
   SET @PostTime =
   @Message.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime');
   SET @SPID = @Message.value('(/EVENT_INSTANCE/SPID)[1]', 'int');
   SET @TextData =
   @Message.value('(/EVENT_INSTANCE/TextData)[1]', 'nvarchar(500)');
   SET @DatabaseID =
   @Message.value('(/EVENT_INSTANCE/DatabaseID)[1]', 'int');
   SET @TransactionsID =
   @Message.value('(/EVENT_INSTANCE/TransactionsID)[1]', 'int');
   SET @StartTime =
   @Message.value('(/EVENT_INSTANCE/StartTime)[1]', 'datetime');
END TRY
   BEGIN CATCH
       PRINT 'Parse of mirroring state change message failed.';
   END CATCH

IF (@MirrorStateChange IN (1,2,3,4,5,6,7,8,9,10,11,12,13))
BEGIN

   DECLARE @state AS varchar(50);
   SELECT @state = mirroring_state_desc FROM SYS.database_mirroring WHERE mirroring_guid IS NOT NULL;
   IF (@state IS null) SET @state = ' ';
   INSERT INTO MirroringAlerts (DateTime, alertID, alertDesc, Sync, alertCreator) values (SYSDATETIME(), @MirrorStateChange , @TextData , @state, @SERVERNAME);

END

創建一個隊列，作為服務和我們要觸發的儲存過程之間的某種中間人

-- Create Queue if not exists
IF NOT EXISTS (SELECT 1
   FROM sys.service_queues
   WHERE name = 'DBMirrorQueue')
BEGIN
   CREATE QUEUE DBMirrorQueue
   WITH ACTIVATION (
   PROCEDURE_NAME = dbo.dba_MirroringStateChanged,
   MAX_QUEUE_READERS = 1,
   EXECUTE AS OWNER);
END

創建將與事件關聯的服務

-- Create Service if not exists
IF NOT EXISTS (SELECT 1
   FROM sys.services
   WHERE name = 'DBMirrorService')
BEGIN
   CREATE SERVICE DBMirrorService
   ON QUEUE DBMirrorQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
END

創建路線

-- Create Route if not exists
IF NOT EXISTS (SELECT 1
   FROM sys.routes
   WHERE name = 'DBMirrorRoute')
BEGIN
   CREATE ROUTE DBMirrorRoute
   WITH SERVICE_NAME = 'DBMirrorService',
   ADDRESS = 'Local';
END

然後創建事件通知

-- Create Event Notification if not exists
IF NOT EXISTS (SELECT 1
   FROM sys.server_event_notifications
   WHERE name = 'DBMirrorStateChange')
BEGIN
   CREATE EVENT NOTIFICATION DBMirrorStateChange
   ON SERVER
   FOR DATABASE_MIRRORING_STATE_CHANGE
   TO SERVICE 'DBMirrorService', 'current database';
END

引用自：https://dba.stackexchange.com/questions/10273

如何在鏡像狀態更改時創建執行作業/過程的事件通知

相關問答

如何減少鏡像期間的網路延遲？

數據庫鏡像卡在掛起模式

在不破壞現有鏡像的情況下啟用 TDE

使用 CNAME 的數據庫鏡像故障轉移

異地複制的 SQL Server 複製

數據庫鏡像中的未發送日誌行為