人人為我 我為人人

說到 Service Broker (SB) 是微軟從 SQL 2005 就開始賦予的好功能

其間的異動，在 SQL 2008 強化後，直到 2012 再追加一些更厲害的功能就維持到現在 2017

而大多數人也都認為這是給開發人員使用的功能，因此鮮少 DB 人員拿來運用 (也或許感覺難用而捨棄)

但這功能又對開發人員來說有著一定的學習門檻，且設定過程不友善
(國內可參考的相關應用資源不多...)

近些年 EF 盛行、SignalR... 等 Realtime 技術搭配運用

開發人員才讓 Service Broker 漸漸廣為人知
(不過真的建議撰寫 EF 少用 Default 避免日後的煩惱)

但其實 Service Broker 對於 DB 管理人員來說，也能夠發揮非常大的貢獻唷～
(自從深入了解認識它後，懂得避開一些雷區，我越來越愛用這個功能了)

而且其實 M$SQL 本身也有很多技術也是運用 SB 的架構在執行
(...還有太多太多的廢話可以聊，但就先到此為止 XD)

接下來的重頭戲「 How to create realtime notifications for SQL」

情境說明：如果有人想偷偷對 DB 動手腳，而你希望當下立刻察覺。
(能夠做到這個目的有很多方法，但這裡要介紹如何使用 SB 的 magic 來達成)

-- enable service broker
ALTER DATABASE {your_db} SET ENABLE_BROKER;

-- check service broker enable
SELECT is_broker_enabled FROM sys.databases WHERE name = '{your_db}';

接下來我們在 {your_db} 建立 queue 以及 service 來處理佇列堆疊

並設定 event notification 來攔截 server level events

CREATE QUEUE EventNotificationQueue
GO

CREATE SERVICE EventNotificationService
    ON QUEUE EventNotificationQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO

CREATE EVENT NOTIFICATION CaptureActionEvents
    ON SERVER
    WITH FAN_IN
    FOR ALTER_DATABASE, CREATE_DATABASE, DROP_DATABASE
    TO SERVICE 'EventNotificationService', 'current database';
GO

接下來建立一個預存來針對 queue 進來的時候做需處理的動作
(為了驗證結果，所以我把接到的訊息 insert into table and write to log)

CREATE PROCEDURE [dbo].[USP_EventNotifications]
WITH EXECUTE AS OWNER
AS 
SET NOCOUNT ON
DECLARE @message_body xml 
DECLARE @RAISERROR varchar(300)
WHILE (1 = 1)
BEGIN
   BEGIN TRANSACTION
	WAITFOR (RECEIVE TOP(1) @message_body=message_body
			 FROM dbo.EventNotificationQueue
	), TIMEOUT 1000
	IF (@@ROWCOUNT = 0)
	BEGIN
		ROLLBACK TRANSACTION
		BREAK
	END 
	SET @RAISERROR =  (SELECT @message_body.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')) + ' : ' + (SELECT @message_body.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'varchar(250)'))
	INSERT INTO [MonitorDB].[dbo].[Message] (Msg) VALUES (@message_body);
	RAISERROR (@RAISERROR, 16, 1) WITH LOG;
   COMMIT TRANSACTION
END
GO

再把 queue 跟 stored procedure 聯結並啟動

ALTER QUEUE EventNotificationQueue
WITH ACTIVATION 
(STATUS=ON,
 PROCEDURE_NAME = [USP_EventNotifications],
 MAX_QUEUE_READERS = 1,
 EXECUTE AS OWNER)
GO

然後就來見證這神奇的一刻吧

最後調整一下，混搭 楊志強 老師 的秘技就可以將 Events 即時送到手機簡訊或通訊軟體，再也不用擔心太晚發現問題

have fun ʕ•͡ᴥ•ʔ

Note : 真心推薦 楊志強 老師 的新書「SQL Server 與 R 開發實戰講堂」& 簽書會 
********************************************************************************
*2017/09/26上架，簽書會 9/29, 10/6 暨 SQL Server 2017上市發表會 *
9/26 https://www.tenlong.com.tw/products/9789864766031 
9/29 https://goo.gl/r1QiGZ 
10/6 https://goo.gl/fYSasm
********************************************************************************