SQL Server Service Broker 自動化部署實戰：從 40 分鐘到 5 分鐘的效率躍升

痛點：手動部署的地獄

Service Broker 在加密模式下部署一組 Server-Client 需 8 大步驟、耗時 40 分鐘：

1. 雙端環境清理
2. 雙端建庫與 Broker 物件建立
3. 雙向憑證生成、複製、匯入
4. 雙端授權與存儲過程部署

開發階段每週重新部署 3-4 次，多實例擴展更是噩夢。

解決方案：sqlcmd + 批次腳本自動化

第一關：連通性驗證

REM 測試本機
sqlcmd -U sa -P P@ssw0rd -S localhost -Q "SELECT @@VERSION"

REM 測試遠端（含具名實例）
sqlcmd -U sa -P P@ssw0rd -S 10.32.0.1\SQLEXPRESS -Q "SELECT DB_NAME()"

常見阻礙：SQL Express 預設關閉 TCP/IP、防火牆阻擋 1433、Browser 服務未啟動。需在 Configuration Manager 啟用協定、開放埠、啟動 Browser。

第二關：SQL 腳本遠端執行

REM 單機執行
sqlcmd -U sa -P P@ssw0rd -S 10.32.0.1\SQLEXPRESS -i C:\Deploy\Clear.sql

REM 多機迴圈部署
SET IP_LIST=10.32.0.1 10.32.0.2 10.32.0.3
FOR %%A IN (%IP_LIST%) DO (
    sqlcmd -U sa -P P@ssw0rd -S %%A\SQLEXPRESS -i C:\Deploy\Clear.sql
)

第三關：共享資料夾與憑證流轉

REM 建立共享並設定 Everyone 完整控制
net share DeployShare=C:\Deploy /GRANT:Everyone,FULL

REM 遠端複製憑證檔
FOR %%A IN (%IP_LIST%) DO (
    copy C:\Deploy\*.cer \\%%A\DeployShare\
)

REM 遠端清理舊檔
FOR %%A IN (%IP_LIST%) DO (
    del /s /q \\%%A\DeployShare\*.cer
)

關鍵：sqlcmd 執行 BACKUP CERTIFICATE 生成檔案需寫入共享路徑，故 SQL 服務帳號需有共享寫入權限。

第四關：參數化腳本與雙重迴圈

SET IP_LIST=10.32.0.1 10.32.0.2
SET SERIAL_LIST=PC1001 PC1002

SETLOCAL ENABLEDELAYEDEXPANSION
SET IDX=0
FOR %%A IN (%IP_LIST%) DO (
    SET /A IDX2=0
    FOR %%B IN (%SERIAL_LIST%) DO (
        IF "!IDX!"=="!IDX2!" (
            SET TARGET_IP=%%A
            REM 取 IP 後 6 碼作為憑證檔名後綴
            sqlcmd -U sa -P P@ssw0rd -S %%A -i C:\Deploy\Setup.sql -v IP=!TARGET_IP:~-6! -v SerialNumber=%%B
        )
        SET /A IDX2+=1
    )
    SET /A IDX+=1
)

Setup.sql 參數化範例

:setvar IP ""
:setvar SerialNumber ""

DECLARE @cmd NVARCHAR(MAX), @IP NVARCHAR(20) = '$(IP)';
SET @cmd = 'BACKUP CERTIFICATE [BrokerCert] TO FILE = ''\\Fileserver\Certs\BrokerCert_' + @IP + '.cer''';
EXEC (@cmd);
GO

成果驗證

避坑指南

• sqlcmd 變數傳遞：-v var=value 中值含空格需雙引號，腳本內用 $(var) 引用。
• 延遲環境變數：批次檔迴圈內修改變數必須 SETLOCAL ENABLEDELAYEDEXPANSION 並用 !var! 讀取。
• 錯誤處理：sqlcmd 加參數 -b 遇錯返回 ERRORLEVEL，批次檔檢查 IF %ERRORLEVEL% NEQ 0 中斷。
• 冪等性：所有 SQL 腳本需可重複執行（IF EXISTS DROP、MERGE 模式）。

擴展建議

若需產品級部署工具，建議採用 PowerShell + dbatools 或 InstallShield 封裝，提供 GUI、回滾、日誌審計、跨平台支援。但對內部專案，sqlcmd + 批次腳本 已是 ROI 最高的輕量方案。