SQL Server 多实例下的复制

2022-06-22 05:00:48

一.本文所涉及的内容（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