利用sqlcmd實現SQLServerServiceBroker的自動部署

       因為工作關系，我們項目用到了Service Broker這項技術。它在功能和性能上真的是沒的說，唯一讓人郁悶的就是部署。要知道，在加密情況下部署一台服務器和一台客戶端我們需要以下步驟：

1. 在服務器端清理環境，

2. 在客戶端清理環境，

3. 在服務器端創建數據庫，並且創建Service Broker相關對象（Message Type，Contract，Queue，Service，Routing甚至Binding）

4. 在客戶端創建數據庫，並且創建Service Broker相關對象（Message Type，Contract，Queue，Service，Routing甚至Binding）

5. 服務器端生成認證，拷貝給客戶端，客戶端導入

6. 客戶端生成認證，拷貝給服務器，服務器導入。

7. 服務器端授權，建立相關存儲過程；

8. 客戶端授權，建立相關存儲過程；

如果沒有自動部署工具，手動部署一遍大概要40分鐘時間。因為我們項目現在處於開發階段，數據庫結構經常變，每周至少要重新部署3-4次。這麼多時間花在重復性的勞動上明顯是浪費。況且上面的例子還僅僅是一台服務器和一台客戶端的情況，後面有多台服務器和多台客戶端的話，我們花在部署上的時間就沒法估算了。

於是我就在考慮能不能用最簡單的bat批處理腳本實現自動部署。研究發現sqlcmd是一個很好的選擇，再借助一些簡單的dos命令基本就滿足要求。在這裡和大家分享一下（本例子是基於局域網的）。

測試連接

要實現自動部署，至少要保證用sqlcmd能訪問到本機（服務器端）和目標機器（客戶端）的數據庫，測試方法如下：

測試本機：

? 1 sqlcmd -U [username] -P [password] -S localhost

測試目標機器：

? 1 sqlcmd -U [username] -P [password] -S [IP address]SQLEXPRESS

這裡和用Management Studio連接數據庫是所用的Server Name，Login和Password是相同的。 注意：如果目標機器安裝的是SQLExpress或打開防火牆，有可能連接不通。如遇到類似如下錯誤，可以參照這裡和這裡解決。

“Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : SQL Server Network Interfaces: Server doesn't support requested protocol [xFFFFFFFF]. . Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired. Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..”

執行SQL文件

因為sqlcmd是可以在本機的數據庫上執行一個sql文件裡的語句的，有用的是也可以在其他機器上執行本地文件系統中的sql。

? 1 sqlcmd -U [username] -P [password] -S [IP address]SQLEXPRESS -i c:Clear.sql

有了它，我就可以把需要執行的sql語句都組織在一起，在命令行裡調用。如果是多台客戶端，甚至可以循環調用。

? 1 2 3 4 Set IpList=10.32.0.1 10.32.0.2 for %%a in (%IpListt%) do {  sqlcmd -U [username] -P [password] -S %%aSQLEXPRESS -i C:Clear.sql }

操作共享文件夾

了解service broker的朋友們應該知道，service broker的安全機制是很嚴格的。因為我們利用了認證文件（certificate file）做認證，所以在部署過程中需要創建認證文件，復制到共享文件夾，再由目標機器導入。於是，在部署過程中是需要訪問共享文件夾的，是要有刪除，創建和讀取的訪問權限的。還有一個前提：我們需要把需要訪問的文件夾共享給所有人（Everyone），這樣命令行才能即通過slqcmd正常的生成認證文件，又能拷貝和刪除認證文件。

? 1 2 3 for %%a in (%IpList%) do (   copy C:share*.* %%ashare ) ? 1 2 3 Set IpList=10.32.0.1 10.32.0.2 net use %IpList%share [password] "/USER:[domain][username]" del /s /q %IpList%share*.*

循環執行SQL文件和傳參

因為部署的機器IP和其他一些信息是有區別的，但我們又希望一次部署多台機器，所以就需要在批處理文件中循環執行命令把不同的地方通過參數傳進去。下面的例子裡，首先定義了兩個數組變量。然後，因為我需要在下面的循環了賦值，所以執行了命令SETLOCAL ENABLEDELAYEDEXPANSION。最後，在雙重循環裡，分別讀出IP和序列號信息，當循環序號相同時，執行sqlcmd命令並通過-v傳遞參數。在這裡我截取IP的後6位（因為用了SETLOCAL ENABLEDELAYEDEXPANSION命令，所以循環內賦值的變量都需要用！！分隔）。

? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 Set IpList=10.32.0.1 10.32.0.2 Set SerialNumber=PC1001 PC1002   SETLOCAL ENABLEDELAYEDEXPANSION set num1=0 for %%a in (%IpList%) do (   set /a num2=0   for %%b in (%SerialNumberList%) do (     if "!num1!" == "!num2!" (       set temp=%%a       sqlcmd -U [username] -P [password] -S %%a -i c:setup.sql -v IP=!temp:~-6! -v SerialNumber=%%b     )     set /a num2+=1   )   set /a num1+=1 )

而在setup.sql裡，我就可以輕松讀到變量，並用來生成相應的對象。下面這段代碼裡，實現了從變量中讀出剛傳過來的IP後6位，並把本機的認證文件保存在指定目錄下，為建立service broker的安全機制做好准備。

? 1 2 3 4 5 6 7 8 declare @cmd nvarchar(max) = null declare @IP nvarchar(20); set @IP=$(IP); select @cmd = isnull(@cmd,'')+ ' BACKUP CERTIFICATE [TestCert] TO FILE = ''c:backupTestCert'+@hubIP+'.cer''' exec (@cmd) go

總結

通過利用上面提到的這些技術，我們得到了以下好處：

1. 部署時間從40分鐘左右縮短到現在的5分鐘，從原來的14個手工步驟縮短到4個；

2. 部署步驟簡單，沒有技術背景的測試人員也能部署；

3. 實現了一台服務器對多台客戶端的自動部署，並且部署多台客戶端和部署一台的時間相差不大。

當然，它還不是產品級的，只能是作為內部提高效率的一個工具。這裡只是拋磚引玉，如果有installshield這類企業級工具，利用類似的方法相信就能做出一個產品級的專業的部署工具來的。