首页 > 代码库 > SQL Server 多实例下的复制

SQL Server 多实例下的复制

原文:SQL Server 多实例下的复制

一.本文所涉及的内容(Contents)

  1. 本文所涉及的内容(Contents)
  2. 背景(Contexts)
  3. 搭建步骤(Procedure)
  4. 注意事项(Attention)

二.背景(Contexts)

  在服务器A有一个数据库Task,需要把部分表部分字段发布订阅到服务器B的TaskSiteInfo数据库上,但是A服务器有些特别,因为它除了有个默认的实例之外,还有一个命名实例:TZR06\SQLSERVER2008R2,如果是默认实例到不会遇到太多的问题,现在因为有命名实例在创建发布订阅的过程中出现了一些异常,所以这里做为记录;

三.搭建步骤(Procedure)

(一) 环境信息

系统环境:Windows Server 2008 R2 + SQL Server 2008 R2

发布服务器:192.168.100.6,1433,服务器名称:tzr06

发布服务器命名实例:SQLSERVER2008R2

发布数据库:Task

分发服务器:与发布服务器同一台机器

订阅服务器:192.168.100.8,1433,服务器名称:tzr08

订阅数据库:TaskSubscribe

数据库帐号:ReplicationUser/ ReplicationPassword

(二) 搭建过程

  上面提到的,发布服务器上有个默认实例和一个命名实例,本来默认实例的数据库端口为1433,后来我把它禁用了,再把命名实例的端口设置为1433,所以这个需要借助别名来实现发布订阅。

A. 发布服务器配置

首先在发布数据库和订阅数据库上创建相同的帐号和密码(ReplicationUser/ ReplicationPassword),并且设置Task数据库的安全对象,设置这样的帐号的目的就是为了和程序连接到数据库的帐号区分开,可以做权限上的控制,方便问题的排查;

--创建发布服务器帐号密码
USE [master]
GO
CREATE LOGIN [ReplicationUser] WITH PASSWORD=NReplicationPassword, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC master..sp_addsrvrolemember @loginame = NReplicationUser, @rolename = Nsysadmin
GO
USE [Task]
GO
CREATE USER [ReplicationUser] FOR LOGIN [ReplicationUser]
GO
USE [Task]
GO
ALTER USER [ReplicationUser] WITH DEFAULT_SCHEMA=[dbo]
GO

wps_clip_image-8981

(Figure1:帐号密码)

在E盘目录下创建文件夹:E:\ReplData,并设置这个文件夹为共享目录,共享用户为bfadmin;

wps_clip_image-8763

(Figure2:文件夹权限)

需要设置SQL Server Agent登陆帐号为上面文件夹访问用户bfadmin;

wps_clip_image-29477

(Figure3:SQL Server Agent登陆帐号)

wps_clip_image-2693

(Figure4:测试网络共享)

wps_clip_image-1661

(Figure5:分发服务器)

如果你设置快照文件夹路径为:E:\ReplData,即使你的发布服务器本身就是分发服务器,如果订阅服务器是另外一台机器,那么在请求(Pull)订阅(如果是推送(Push)订阅就没有这个限制)模式下订阅代理是无法访问到这个快照文件的;除非你发布服务器、分发服务器和订阅服务器都是同一台机器;你应该设置快照文件夹路径为:\\tzr06\ ReplData;

wps_clip_image-27389

(Figure6:快照文件夹)

wps_clip_image-6197

(Figure7:选择发布数据库)

wps_clip_image-5271

(Figure8:事务发布)

wps_clip_image-5826

(Figure9:选择对象)

wps_clip_image-24573

(Figure10:选择对象)

wps_clip_image-13321

(Figure11:选择对象)

wps_clip_image-3034

(Figure12:初始化订阅)

wps_clip_image-7934

(Figure13:安全设置)

使用上面创建好的ReplicationUser帐号作为连接到发布服务器的帐号和密码;

wps_clip_image-4731

(Figure14:设置帐号密码)

wps_clip_image-31543

(Figure15:发布选项)

wps_clip_image-22937

(Figure16:发布名称)

wps_clip_image-3613

(Figure17:查看复制情况)

B. 订阅服务器配置

创建完发布服务器(分发服务器也一起创建了),接下来就可以创建订阅服务器了,下面是订阅服务器设置的具体步骤:

--创建订阅服务器帐号密码
USE [master]
GO
CREATE LOGIN [ReplicationUser] WITH PASSWORD=NReplicationPassword, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC master..sp_addsrvrolemember @loginame = NReplicationUser, @rolename = Nsysadmin
GO
USE [TaskSiteInfo]
GO
CREATE USER [ReplicationUser] FOR LOGIN [ReplicationUser]
GO
USE [TaskSiteInfo]
GO
ALTER USER [ReplicationUser] WITH DEFAULT_SCHEMA=[dbo]
GO

创建完了数据库帐号,我们接着来创建订阅,按照前面提到的在发布服务器上有命名实例,所以这里是按照TZR06\SQLSERVER2008R2来设置服务器名称的,但是在连接过程中出现了下面的错误:

wps_clip_image-30021

(Figure18:查找发布服务器错误信息)

使用上面的配置在订阅服务器上使用【连接服务器】的方式同样无法登录到发布服务器,防火墙的入站规则已经加入允许1433端口了,而且在发布服务器使用netstat查看端口,也是有监听的,为什么会连接不上呢?后来在【连接服务器】加入1433是可以登录的,如下图所示:

wps_clip_image-5793

(Figure19:登录发布服务器)

使用同样的方式却无法查找到发布服务器,出现了新的错误信息,如下图所示:

wps_clip_image-16198

(Figure20:查找发布服务器错误信息)

既然需要加端口号,那我们就尝试使用别名的方式,在64位的操作系统中,需要同时设置32位和64位的网络配置,设置别名为:TZR06

wps_clip_image-11165

(Figure21:别名参数值)

wps_clip_image-20082

(Figure22:32位别名)

wps_clip_image-7762

(Figure23:64位别名)

wps_clip_image-30153

(Figure24:查找发布服务器错误信息)

难道是TZR06有冲突?修改别名为:TZR06Task

wps_clip_image-2451

(Figure25:修改32位和64位的别名)

wps_clip_image-29011

(Figure26:查找发布服务器错误信息)

在发布服务器上创建发布的时候,如果SQL Server数据库实例名与服务器名不一致,也会出现上面的错误,所以在发布服务器上执行下面的SQL语句:

/*
返回有关服务器实例的属性信息
Windows 服务器和与指定的SQL Server 实例关联的实例信息
*/
SELECT CONVERT(sysname, SERVERPROPERTY(servername));
/*
返回运行SQL Server 的本地服务器的名称
如果连接默认实例,则@@SERVERNAME仅返回servername
如果连接命名实例,则@@SERVERNAME函数返回的字符串以servername\instancename的格式标识实例名
*/
SELECT @@SERVERNAME AS Server Name

上面的结果为:

wps_clip_image-15272

(Figure27:查找发布服务器错误信息)

如果两个值不同,那到可以通过下面的方式进行修改:

/*
SQL Server数据库实例名与服务器名不一致的解决办法
*/
IF SERVERPROPERTY(SERVERNAME)<>@@SERVERNAME  
BEGIN
    DECLARE @server SYSNAME
    SET @server=@@SERVERNAME
    EXEC sp_dropserver @server=@server  
    SET @server=CAST(SERVERPROPERTY(SERVERNAME) AS SYSNAME)
    EXEC sp_addserver @server=@server,@local=LOCAL
END

后来请教高文佳,突然想到:“在分发服务器和订阅服务器上设置别名的时候,别名应该跟服务器的实例名要一致”继续做尝试,修改别名为:TZR06\SQLSERVER2008R2

wps_clip_image-22296

(Figure28:修改32位和64位的别名)

wps_clip_image-22511

(Figure29:选择发布)

wps_clip_image-21671

(Figure30:请求订阅)

wps_clip_image-14008

(Figure31:选择订阅数据库)

wps_clip_image-17928

(Figure32:分发代理安全性)

wps_clip_image-32000(Figure33:设置帐号密码)

wps_clip_image-22798

(Figure34:同步计划)

wps_clip_image-29422

(Figure35:初始化)

wps_clip_image-18531

(Figure36:创建订阅)

wps_clip_image-5947

(Figure37:本地订阅)

四.注意事项(Attention)

1. 如果一开始你在发布服务器上设置的快照文件为本地路径,比如设置成E:\ReplData,那么有可能出现下面的错误:

wps_clip_image-21441

(Figure38:系统找不到指定的路径)

这个时候你重新发布订阅是没有默认路径可以设置的,可以修改?我没找到可以设置的地方,只能通过另外一种方式进行修改,在发布属性中修改快照路径:

wps_clip_image-7924

(Figure39:默认文件夹)

wps_clip_image-1850

(Figure40:设置文件夹)

在订阅服务器上修改订阅属性的快照文件夹:

wps_clip_image-19512

(Figure41:备用文件夹)

2. 在订阅服务器上同样需要设置SQL Server Agent登陆帐号为上面文件夹访问用户bfadmin,不然会出现下面的错误:

wps_clip_image-12671

(Figure42:错误信息)

设置帐号之后需要重启SQL Server Agent服务

wps_clip_image-7125

(Figure43:订阅服务器SQL Server Agent设置)

3. 在发布服务器上无法对订阅服务器进行【重新初始化】,报下面错误信息,即使在发布服务器上设置了:

wps_clip_image-10620

(Figure44:错误信息)

上面这个错误暂时还没有解决,不过关于命名实例的复制已经成功了,虽然成功了,但是还是要建议大家尽量不要在生产环境中安装多实例,避免出现不必要的问题;