SQL Server 多实例下的复制

4353

一.本文所涉及的内容（Contents）
本文所涉及的内容（Contents）
背景（Contexts）
搭建步骤（Procedure）
注意事项（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=N'ReplicationPassword', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC master..sp_addsrvrolemember @loginame = N'ReplicationUser', @rolename = N'sysadmin'
GO
USE [Task]
GO
CREATE USER [ReplicationUser] FOR LOGIN [ReplicationUser]
GO
USE [Task]
GO
ALTER USER [ReplicationUser] WITH DEFAULT_SCHEMA=[dbo]
GO

（Figure1：帐号密码）

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

（Figure2：文件夹权限）

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

（Figure3：SQL Server Agent登陆帐号）

（Figure4：测试网络共享）

（Figure5：分发服务器）

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

（Figure6：快照文件夹）

（Figure7：选择发布数据库）

（Figure8：事务发布）

（Figure9：选择对象）

（Figure10：选择对象）

（Figure11：选择对象）

（Figure12：初始化订阅）

（Figure13：安全设置）

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

（Figure14：设置帐号密码）

（Figure15：发布选项）

（Figure16：发布名称）

（Figure17：查看复制情况）

B. 订阅服务器配置

创建完发布服务器（分发服务器也一起创建了），接下来就可以创建订阅服务器了，下面是订阅服务器设置的具体步骤：

--创建订阅服务器帐号密码

USE [master]

GO

CREATE LOGIN [ReplicationUser] WITH PASSWORD=N'ReplicationPassword', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

EXEC master..sp_addsrvrolemember @loginame = N'ReplicationUser', @rolename = N'sysadmin'

GO

USE [TaskSiteInfo]

GO

CREATE USER [ReplicationUser] FOR LOGIN [ReplicationUser]

GO

USE [TaskSiteInfo]

GO

ALTER USER [ReplicationUser] WITH DEFAULT_SCHEMA=[dbo]

GO

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

（Figure18：查找发布服务器错误信息）

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

（Figure19：登录发布服务器）

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

（Figure20：查找发布服务器错误信息）

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

（Figure21：别名参数值）

（Figure22：32位别名）

（Figure23：64位别名）

（Figure24：查找发布服务器错误信息）

难道是TZR06有冲突？修改别名为：TZR06Task

（Figure25：修改32位和64位的别名）

（Figure26：查找发布服务器错误信息）

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

/*

返回有关服务器实例的属性信息

Windows 服务器和与指定的SQL Server 实例关联的实例信息

*/

SELECT CONVERT(sysname, SERVERPROPERTY('servername'));

/*

返回运行SQL Server 的本地服务器的名称

如果连接默认实例，则@@SERVERNAME仅返回servername

如果连接命名实例，则@@SERVERNAME函数返回的字符串以servername\instancename的格式标识实例名

*/

SELECT @@SERVERNAME AS 'Server Name'上面的结果为：

（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

（Figure28：修改32位和64位的别名）

（Figure29：选择发布）

（Figure30：请求订阅）

（Figure31：选择订阅数据库）

（Figure32：分发代理安全性）

（Figure33：设置帐号密码）

（Figure34：同步计划）

（Figure35：初始化）

（Figure36：创建订阅）

（Figure37：本地订阅）

四.注意事项（Attention）

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

（Figure38：系统找不到指定的路径）

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

（Figure39：默认文件夹）

（Figure40：设置文件夹）

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

（Figure41：备用文件夹）

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

（Figure42：错误信息）

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

（Figure43：订阅服务器SQL Server Agent设置）

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

（Figure44：错误信息）

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