積沙成塔

【情境描述】

Techdays 2012 楊志強老師的一場【SQL Server 2012-跨國企業資料庫管理與效能調教實戰經驗分享】課程中，楊老師提到許多內建工具不須額外購買監控軟體就可以幫助 DBA 有效管理數量龐大的 SQL Server，其中十大老闆常問 DBA 的第一個問題就是【你的 SQL Server 現在是活的嗎?】，身為 DBA 的您該如何回答這個問題？若您管理的 SQL Server 屈指可數，或許透過 SSMS 直接連到 SQL Server，就可以判斷伺服器是不是活著，連得上就是活的。但如果像楊老師管理四五百台資料庫伺服器，或許就需要有更有效率的方式來判斷 SQL Server 是不是活著，以下筆者將楊老師於課堂上的實作方式整理如後。

【實作步驟】

SQL Server 2008 以後的版本，新增了【中央管理伺服器】功能讓 DBA 能夠建立伺服器群組來管理多台 SQL Server，您可以在 SSMS 中以【Ctrl + Alt + G】來開啟【已註冊的伺服器】視窗，於視窗中便可看到註冊於目前執行個體中的本機伺服器群組與中央管理伺服器。

有關中央管理伺服器的說明請參閱參考資料中的連結，本文將針對如何運用中央管理伺服器的功能來幫助 DBA 監控 SQL Server 是不是活的。

【註冊中央管理伺服器】

請於【已註冊的伺服器】視窗中的 Database Engine 節點下點選【中央管理伺服器 > 註冊中央管理伺服器】。

於【新增伺服器註冊】視窗中輸入您想要成為中央管理伺服器的執行個體相關資訊後按儲存，筆者示範利用 Windows 驗證連接本機的預設執行個體，然後將已註冊的伺服器名稱設定為 Local。

新增成功後，您將看到如下圖的畫面。

【新增伺服器註冊】

註冊中央管理伺服器後便是新增您想要監控是否活著的 SQL Server 執行個體，請於上一節建立的 Local 上按滑鼠右鍵，選擇【新增伺服器註冊】。

於【新增伺服器註冊】視窗輸入您要監控的伺服器名稱並輸入您的伺服器註冊後想要使用的名稱，然後按儲存。而中央管理伺服器為了可以在多部伺服器執行 T-SQL 指令碼，在註冊新伺服器時有關驗證的連接資訊並不會被儲存，因此新增伺服器註冊時只支援 Windows 驗證。

儲存成功後您將會看到如下圖的畫面，其中筆者又另外新增一名稱為 SQL2K8R2 的伺服器。

您可以由 msdb 系統資料庫的 sysmanagement_shared_registered_servers_internal 資料表來查看註冊到中央伺服器的伺服器。

use msdb
go

SELECT *
FROM sysmanagement_shared_registered_servers_internal

執行結果如下，其中 server_name 資料行為您註冊時的伺服器名稱或 IP 位址， name 資料行為您註冊之後顯示在已註冊伺服器視窗中的名稱。

【監控註冊於中央管理伺服器的伺服器】

至此前置作業都已經完成，接下來您可以利用 CURSOR 來依序利用 OPENROWSET 來連接您註冊在中央管理伺服器的 SQL Server，使用 OPENROWSET 的好處是您不需要特別建立 Linked Server，直接以分散式查詢的方式來連接遠端的執行個體，連得上表示您的 SQL Server 還活著，例如下列的程式碼：

DECLARE @server_name varchar(50)
DECLARE @timeout varchar(3) = '15'

DECLARE cur CURSOR FOR
SELECT server_name
FROM msdb.dbo.sysmanagement_shared_registered_servers_internal

OPEN cur
FETCH NEXT FROM cur INTO @server_name

WHILE @@FETCH_STATUS = 0
BEGIN
	BEGIN TRY
		EXEC (
			'SELECT * FROM OPENROWSET 
			(''SQLNCLI'',''Server=' + @server_name + ';Trusted_Connection=yes;Timeout=' + @timeout +''',					
			''select @@SERVERNAME'')'
		     )
	END TRY

	BEGIN CATCH
		DECLARE @context nvarchar(100)
		SET @context = N'【緊急】{' + @server_name + + N'} 執行個體沒有回應。'

		EXEC msdb.dbo.sp_send_dbmail @recipients = 'terry@contoso.com',
	                                     @copy_recipients = 'terryjuang@contoso.com', 
					     @importance  = 'high',
					     @subject = @context,
					     @body = '請盡速處理!!'
	END CATCH
	FETCH NEXT FROM cur INTO @server_name
END

CLOSE cur
DEALLOCATE cur

您可以依照您的企業網路架構來決定 Timeout 時間要設多長，但應避免設定過短以造成誤判的情況，若連接不上遠端伺服器時，表示 SQL Server 可能有狀況，可以在 Catch 區段中使用 sp_send_dbmail 來通知 DBA。另外，若您的執行個體未啟用分散式查詢，可以利用下列的 T-SQL 指令碼來啟用之。

--啟用進階選項
EXEC sp_configure 'show advanced options',1
RECONFIGURE WITH OVERRIDE
GO

--啟用分散式查詢
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE WITH OVERRIDE
GO

【使用 SQL Server Agent 實現自動化監控】

為了讓監控 SQL Server 是否活著的動作可以自動執行，您可以新增一個 SQL Server Agent 作業，接著建立 T-SQL 類型的步驟，然後貼上前一節的 T-SQL 指令碼。

最後指定排程來定期監控您的 SQL Server，您可以依照您的需求來自行設定多久檢查一次註冊在中央管理伺服器中的執行個體是否活著（筆者以 2 小時檢查一次為示範）。

有了這個機制後，DBA 沒收到 E-Mail 透過 SQL Server Agent Job 丟出來了警告訊息，您就可以稍微比較不用擔心您的伺服器掛掉而您渾然不知。

【參考資料】

- SQL Server 2012-跨國企業資料庫管理與效能調教實戰經驗分享

- 建立中央管理伺服器與伺服器群組(SQL Server Management Studio)

- 使用中央管理伺服器管理多部伺服器 - MSDN – Microsoft

- 如何：建立中央管理伺服器和伺服器群組

- 新手學SQL Server 2008 中央管理伺服器

- SQL Server Central Management Servers System Tables

- how to manage your SQL Servers with Powershell

- sp_configure

- Database Mail