SQL2005下使用发布订阅异地Internet同步
公司有个网站,为了保证安全性,需要对数据库进行本地备份,查了下网上资料,发现 的资料还是蛮多的,但是要进行Internet上的异地实时同步备份,发现还是蛮少的,SQL的发布订阅,文章也很多,但是基本上都是基于同一个局域网的。
经过多次试验,找到了基于Internet的异地同步方案,在此文中记录一下配置过程,以备后用。要解决跨Internet的问题,主要解决SQL Server的连接权限问题、SQL 代理服务的权限问题以及快照文件夹的权限问题。
一、发布服务器端准备工作
- 在发布服务端建立Windows账户,假设publishtest,并设置好密码,然后设置到Administrators组。
- 开放数据库1433端口,当然为了安全性,可以更改默认的1433端口,或者设置IP策略,只允许订阅客户端的IP能访问1433端口,如何更改端口和设置IP策略,这里就不详述了。
- 将SQL Server的身份认证设置为“SQL Server和WINDOWS”。步骤为:对象资源管理器----右击SQL实例-----属性----安全性----服务器身份验证------选“SQL Server和WINDOWS“,然后点确定 。
- 把步骤1中建立的用户名,分配该用户访问需要同步的数据库的权限,如下图:
- 在Windows服务中,找到SQL Server Agent服务,在登录选项卡中,使用步骤1中建立的用户,如下图:
- 在服务器上建立好FTP服务器,可以用Serv-U,并开放ftp访问端口21和ftp数据传输端口。
- 新建一个SQL 账号,分配该用户访问需要同步的数据库的dbower权限。
二、订阅客户端准备工作
- 建立与服务器端同样的用户名publishtest,并设置同样的密码,设置到Administrators组。
- 同服务器端设置一样,设置publishtest访问本地备份数据库的权限。
- 同服务器端设置一样,设置publishtest登录SQL Server Agent权限。
三、建立发布端
- 选择 “复制” –> “本地发布”,新建发布;
- 选择发布数据库
- 选择发布类型,这里选择事务发布
- 选择需要发布的对象,注意事务发布中的所有表都需要主键列。
- 一路点下一步。
- 快照代理的安全设置处,选择“在SQL Server代理服务帐户下运行”,连接到发布服务器,填入上面创建的SQL帐户信息。
- 输入发布的名称,完成创建。
- 在创建好的发布服务器上面,点右键,属性,在ftp快照中,填入上面创建的ftp帐户信息,FTP名称,填入服务器的IP或者域名,如下图:
四、建立订阅端
- 建立服务器端别名。在SQL Server配置管理中,点“SQL Native Client配置”-> “别名”,新建别名,名称填写服务器的名称,端口和服务器地址根据实际情况填写,如下图:
- 打开SQL管理器,“复制”-> “本地订阅”,新建订阅。
- 发布服务器,选择“查找SQL Server发布服务器”,在“连接到服务器”对话框中,名称填入上面建立的别名,账号填写上面服务器端设置的SQL账号用户名和密码。
- 在“分发地理位置”中,选择“请求订阅”。
- 在“分发代理安全性”中,选择“在SQL Server代理服务帐户下运行”,连接到发布服务器,填入上面创建的SQL帐户信息。
- 输入订阅名称,完成订阅。
五、查看日志
- 在服务器端,在已经建立好的发布服务器名称上,点右键,选择“启动复制监视器”。
- 在“订阅监视列表”中,可以看到订阅状态。
- 双击“正在运行”,可以查看到同步日志记录,通过查看日志,可以很方便的看到错误信息以及每一步操作。
2014-12-11补记:
关于防火墙设置
使用ftp时,SQL 不支持被动模式(Passive),只能采取主动模式(Active),因此公网服务器上需要开放2个端口,一个是命令端口,一个是数据端口,默认的是21和20.
但是为了服务器的安全性,很多时候我们会把默认的端口改掉,比如改成8821,那么数据端口就是8820,数据端口=命令端口-1
所以,防火墙上需要开放这2个端口。
另外,我用IIS自带的ftp 服务,使用主动模式没有问题,但是使用Serv-U的时候,主动模式死活不能列表目录,不知道Serv-U使用的是什么数据端口。