一、概念简介

1.1、基本概念

1）读写分离概念：是把对数据库的读操作和写操作分离开。在一定程度上，读写分离可以缓解读写操作并发时产生锁的问题。

2）读写分离原理：是让主数据库处理事务性增、删、改操作（INSERT、DELETE、UPDATE），而从数据库处理查询操作（SELECT）。

1.2、技术简介

SQL Server 提供了三种技术来实现读写分离，分别是：日志传送、事务复制、Always On。以下是三种技术的比较：

事务复制没有 Always On 的要求那么高，只需要主从服务器能通过 TCP 进行通讯即可，主从服务器操作系统和 SQL Server 版本可以不完全一致（生产环境建议一致），同时，主从服务器也不需要加入域。

注：本文主从同步实现方式采用事务复制方式。

二、实战准备

2.1、网络环境

1）主从服务器最好在同一个局域网内，而且要互相 ping 得通，可以是不同网段。

2）以下是本文的测试环境：

2.2、数据库主机名

1）SQL Server 数据库实例主机名需与本地服务器名称一致：

--本地服务器名称
SELECT @@SERVERNAME
--数据库实例主机名
SELECT SERVERPROPERTY('ServerName')

2）若出现 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

更改完成后请重启 SQL Server 服务：

2.3、同步账号

主从服务器都需要建立一个账号及密码都相同的本地管理员用户如 sync：

2.4、SQL Server 代理

主从服务器都需要启动 SQL Server 代理，另外登录账号都设为同步账号如 sync：

 

2.5、同步说明

1）以数据库 AdventureWorks 为例。

2）在 AdventureWorks 上执行以下 SQL 语句，否则后续会出现【进程无法在“IT01”上执行“sp_replcmds”】报错。

sys.sp_changedbowner 'sa'

3）主从搭建，实际是发布->分发->订阅的过程。本文发布与分发使用的是同一台服务器 IT01。

2.6、同步规则

1）新增的表一定要有主键，否则不能进行同步。

2）从库上一定不能有任何的数据修改，这个原则一定要遵守。

三、实战操作

3.1、分发配置

1）在 IT01 主服务器上，对着 SQL Server 的”复制”右键->点击”配置分发”。

2）点击”下一步”。

3）默认选择，点击”下一步”。

4）快照文件夹应使用网络路径，因此要先设置文件夹共享。

5）打开”D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL”->对着”repldata”文件夹”右键”->点击”属性”。

6）选择”共享”页签->点击”共享”。

7）添加”Everyone”用户。

8）授予”Everyone”用户”读取/写入”权限->点击”共享”。

9）共享成功后，在快照文件夹中输入网络路径”\\IT01\ReplData”，点击”下一步”。

10）默认选择，点击”下一步”。

11）默认选择，点击”下一步”。

12）默认选择，点击”下一步”。

13）点击”完成”。

14）完成后，点击”关闭”即可。

3.2、发布配置

1）在 IT01 主服务器上，点击 SQL Server 的”复制”->对着”本地发布”右键->点击”新建发布”。

2）点击”下一步”。

3）选择要发布的数据库如”AdventureWorks”->点击”下一步”。

4）选择”事务发布”->点击”下一步”。

5）选择要发布的对象如”表”（也可以选择某个具体表）->点击”下一步”。

6）默认选择，点击”下一步”。

7）勾选”立即创建快照并使快照保持可用状态，以初始化订阅”->点击”下一步”。

8）点击”安全设置”。

9）由于本测试环境为非域环境，因此只能选择”在 SQL Server 代理服务账号下运行”。同时，录入 SQL Server 登录名及密码，点击”确定”。

10）点击”下一步”。

11）默认选择，点击”下一步”。

12）起个发布名称，点击”完成”。

13）执行成功后，点击”关闭”即可。

14）对着发布名称”右键”->点击”属性”。

15）点击”快照”->取消勾选”将文件放入默认文件夹”，勾选”将文件放入下列文件夹”，并录入网络地址”\\IT01\ReplData”->点击”确定”。

3.3、订阅配置

1）在 HW01 从服务器上打开运行->输入”\\IT01″。

2）确保能正常访问主服务上的共享文件夹”repldata”。

3）对着数据库”右键”->选择”新建数据库”。

 

4）输入数据库名如”AdventureWorks”->点击”确定”。

 

5）打开”复制”，对着本地订阅”右键”->点击”新建订阅”。

 

6）默认选择，点击”下一步”。

7）在下拉框中选择”查找 SQL Server 发布服务器…”。

8）输入主服务器名称及身份验证，同时勾选”记住密码”，最后点击”连接”。

9）默认选择，点击”下一步”。

10）选择”在其订阅服务器上运行每个代理(请求订阅)”->点击”下一步”。

11）选择订阅数据库”AdventureWorks”->点击”下一步”。

12）点击”…” 。

13）选择”在 SQL Server 代理服务账户下运行”->输入连接到分发服务器的登录账号及密码->点击”确定”。

14）点击”下一步”。

15）选择”连续运行”->点击”下一步”。

16）初始化时间选择”立即”->点击”下一步”。

17)默认选择，点击”下一步”。

18)点击完成。

19）创建成功后，点击”关闭”。

20）对着订阅名称”右键”->点击”属性”。

22）在快照项中，快照位置选择”备份文件夹”->快照文件夹输入”\\IT01\repldata”->点击”确定”。

四、异常检查

1）对着发布名称”右键”->点击”启动复制监视器”。

2）记录行”右键”，可以”停止代理”再”启动代理”，这样就可以发现执行过程中的报错。也可以点击”查看详细信息”，查看执行的过程日志等。

五、新增项目内容

1）假如有新的表或其它新的项目内容需要同步，可以对着发布名称”右键”->点击”属性”。

2）选择”项目”->勾选新增的表等项目内容->点击”确定”。

3）对着发布名称”右键”->点击”查看快照代理状态”。

4）点击”启动”。

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

1）先删除发布服务器上的订阅和发布。

2）执行以下命令，删除 distribution 分发数据库。

USE master
GO
EXEC sp_dropdistributiondb @database=N'distribution'
GO
EXEC sp_dropdistributor @no_checks=1,@ignore_distributor=1
GO