MSSQL主从复制

参考

http://www.it165.net/database/html/201306/4088.html https://baijiahao.baidu.com/s?id=1669646042808142213&wfr=spider&for=pc  SQL Server读写分离神器，发布与订阅

背景：

读写分离(主从复制)，就是把对数据库的读操作和写操作分离开，读写分离初期可以一定程度上缓解读写并发时产生锁的问题，将读写压力分担到多台服务器上。基本原理让主数据库处理事务的增、改、删操作，从数据库处理查询操作

环境配置

两台虚拟机，设置NAT模式192.168.136.128，计算机名称：U813Develop192.168.136.130，计算机名称：WIN-131P4VG5H6E确保能ping通
从服务器也能ping主服务器
保证主从数据库代理正常运行,从数据库代理使用Windows管理员账号登陆这里是新建了一个账号sqladmin

主数据库新建数据库

创建数据库MasterDB，新建表和存储过程作为传输对象

配置分发主服务器

如果是第一次配置，需要右击复制，配置分发

默认的快照路径，需要设置此文件夹为共享，以便于从服务器访问
右击文件属性共享此文件夹，目的是让从服务器能读取此文件夹
可以添加everyone权限或者增加Windows管理员账号，这里设置everyone简单些确保此网络路径能访问并且在从服务器也能访问
从服务器访问主服务器的网络文件夹
更改快照文件夹
下一步
下一步
完成即可

新建数据库发布

选择数据库事务发布选择同步的表和存储过程存储过程引用了表对象，确保表对象是已经建立的
下一步
下一步
设置当前主数据库的账号密码，
下一步
输入发布名称，完成发布完成后能在本地发布看到，并且快照文件夹有对应的文件生成

从数据库订阅

新建一个空的数据库MasterDB_Slave1，用来接收数据设置代理为windows管理员账号登陆
新建订阅查找发布服务器，这里服务器名称不能用ip登陆
 选择连接到主数据库
下一步
下一步
下一步
完成
完成后能在本地订阅中看到等一会查看数据已经传递过来了从库已经有同步的数据了
在主数据库中能查看运行和订阅情况主数据库中增加、修改数据插入数据
发现数据也是同步的，传输需要一点时间
主数据库新增了表如何同步表定义加上主键，我们右击本地发布，取消右侧的勾选，选择我们刚刚新增的Class表
右击此发布查看快照
点击启动，生成快照
点击监视可以看到Class表已经分发出去了从数据库中class表已经同步过来
向主数据库class表新增数据从数据库也同步数据过来了
注意点

数据延迟

数据在发布服务器发生变化后到更新订阅服务器会耗费一定时间，理想的情景应该是，报表数据取数可以从订阅服务器获取没有为发布配置您是xxx
当前登陆账号权限问题，设置管理员权限即可

从服务器可能订阅失败

首先确保主数据库的快照文件夹能在网络中访问，从数据库代理添加windows管理员账号登陆，快照位置可以修改为网络文件夹

需要安装mssql的复制功能

安装sql server的时候必须添加此复制组件才能使用此功能

计算机名称和数据库服务名称不一致

先查询当前计算机名称和数据库名称
select @@servername;
select serverproperty('servername');

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

删除发布服务器上的主从复制

--如何删除发布服务器上的主从复制
1、先删除发布服务器上的上的订阅和发布。
2、执行下面命令，删除distribution分发数据库.
使用sp_dropdistributiondb删除数据库distribution
use master;
go
exec sp_dropdistributiondb @database = N'distribution'
go

use master; 
go
exec sp_dropdistributor @no_checks = 1, @ignore_distributor = 1 
go

从数据库中的数据能删除或修改？

不要手动删除或修改从数据库中的数据，否则会在主数据库监视中看到错误，如果不小心删除了数据，可以整个表删除，全部同步数据

同步数据的时候表和视图等的顺序

在这个发布的对象中，有表、存储过程、视图、自定义函数、自定义类型等等，需要注意的是同步的顺序，否则可能报错的，比如：表中的字段是自定义类型的，同步表结构会报错说找不到这个类型。视图所依赖的表如果缺少也是报错。同步顺序：如果是先有的数据库，先同步用户自定义类型或者用户定义表类型，然后到自定义函数、表，视图，存储过程，

timestamp数据传输

该数据类型无法传输的主库的数据跟子库的数据不一致，timestamp

附件列表