利用sqlcmd实现SQL Server Service Broker的自动部署

        因为工作关系，我们项目用到了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能访问到本机（服务器端）和目标机器（客户端）的数据库，测试方法如下：

         测试本机：       

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

         测试目标机器：

sqlcmd -U [username] -P [password] -S [IP address]\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。

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

        有了它，我就可以把需要执行的sql语句都组织在一起，在命令行里调用。如果是多台客户端，甚至可以循环调用。

Set IpList=10.32.0.1 10.32.0.2
for %%a in (%IpListt%) do { 
 sqlcmd -U [username] -P [password] -S %%a\SQLEXPRESS -i C:\Clear.sql
}

操作共享文件夹

for %%a in (%IpList%) do (
  copy C:\share\*.* \\%%a\share)

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命令，所以循环内赋值的变量都需要用！！分隔）。

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
)

declare @cmd nvarchar(max) = null
declare @IP nvarchar(20);
set @IP=$(IP);
select @cmd = isnull(@cmd,‘‘)+ ‘
BACKUP CERTIFICATE [TestCert]
TO FILE = ‘‘c:\backup\TestCert‘+@hubIP+‘.cer‘‘‘
exec (@cmd)
go

总结

        通过利用上面提到的这些技术，我们得到了以下好处：

1. 部署时间从40分钟左右缩短到现在的5分钟，从原来的14个手工步骤缩短到4个；

2. 部署步骤简单，没有技术背景的测试人员也能部署；

3. 实现了一台服务器对多台客户端的自动部署，并且部署多台客户端和部署一台的时间相差不大。

        当然，它还不是产品级的，只能是作为内部提高效率的一个工具。这里只是抛砖引玉，如果有installshield这类企业级工具，利用类似的方法相信就能做出一个产品级的专业的部署工具来的。