利用sqlcmd实现SQLServerServiceBroker的自动部署

因为工作关系，我们项目用到了Service Broker这项技术。它在功能和性能上真的是没的说，唯一让人郁闷的就是部署。要知道，在加密情况下部署一台服务器和一台客户端我们需要以下步骤： 1. 在服务器端清理环境， 2. 在客户端清理环境， 3. 在服务器端创建数据

因为工作关系，我们项目用到了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这类企业级工具，利用类似的方法相信就能做出一个产品级的专业的部署工具来的。