sqlserver配置分发实现主备.20240301

方案总体说明

本方案采用“发布-订阅模式”

由主服务器进行发布消息，备份服务器进行订阅
当主服务器数据发生变更时，就会发布消息，备份服务器读取消息进行同步更新，中间过程延迟比较短。

- 优点：速度快，延迟小，可以支持部分同步等优点

• 缺点：

1. 新增表、视图操作不能主动同步
2. 部分同步，如果是表修改，可以主动同步；
3. 如果是新增表、视图等操作，必须在发布属性中，将新加的表或者视图添加到同步配置中，否则对这个表做的任何操作都不会同步。

• 注意点：

1. 要求数据库名称和主机名称必须一致，否则查找不到数据库主机；
2. 要求数据库不能使用端口，必须是可以通过ip直接访问。

配置步骤

1. 检查sql server数据库实例名与服务器名是否一样

如果不一样，请把注释放开，执行SQL，之后重启服务器。

IF SERVERPROPERTY('SERVERNAME')<>@@SERVERNAME
Print '服务器名和数据库实例名不同！请修改操作后重启！'
--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

2. 建立复制账户，测试连通性

所有发布/订阅服务器都建立该登录名和密码；给予系统管理员权限；

USE [master]
GO
CREATE LOGIN [repl] WITH PASSWORD='密码'
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [repl]
GO

在两台机器上互相连接对方主机名：如下，可以连通就证明网络已经搞定了，账户名密码也是确认无误可以使用了。

3. 新建共享文件夹

• 发布/订阅服务器，新建用户repluser，密码相同；
• 发布服务器新建共享文件夹repl_share，并共享，设置repluser读写权限，设置SqlServerAgent账号读取权限。
  

4. 在主库上配置分发服务器

启动SQL Server Agent代理服务

设置代理服务登录账号为local system

配置分发

一直下一步，到配置分发向导，将步骤3的共享文件夹路径填入

一路下一步

5. 在主服务器上配置发布

6. 在主服务器上配置订阅

7. 在主服务器上监控事务