SqlServer主从技术

bigcucumber

已于 2024-01-16 16:47:29 修改

阅读量1.8k

点赞数 19

分类专栏： sqlserver 文章标签：数据库 sqlserver windows

于 2024-01-16 11:43:03 首次发布

本文链接：https://blog.csdn.net/bigcucumber/article/details/135616144

版权

sqlserver 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、介绍

sqlserver主从技术包含：复制、日志传送、镜像、AlwaysOn这四种方式。

1、复制

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

　　复制方式是以前很常见的一种主备，速度快，延迟小，可以支持部分同步等优点，但是也有一个很明显的缺点，因为是部分同步，如果是表修改，可以主动同步，但是如果是新增表、视图等操作，必须在发布属性中，将新加的表或者视图添加到同步配置中，否则对这个表做的任何操作都不会同步。

　　复制模式同步，要求数据库名称和主机名称必须一致，否则查找不到数据库主机

主要分为以下4种发布方式：

1.1 快照复制

1.1.1 概念

将发布服务器的所有表做成一个镜像，然后一次性复制到订阅服务器。后续的更新不会自动传送到订阅服务器，即只记录某一时刻的完整数据。

1.1.2 使用场景

1）订阅服务器只读

2）数据不频繁更新

3）允许一段时间内具有发布服务器已过时的数据副本

1.2 事务复制

1.2.1 概念

初次将发布服务器的初始快照数据发送到订阅服务器（事务复制中的初始快照不会对主数据库进行加锁，其采用的是并发快照技术），之后每次对于发布服务器所做的改动都会以日志的方式发送到订阅服务器，使得发布服务器和订阅服务器的数据保持同步。

1.2.2 复制方式

1）日志推送：发布服务器有数据变更，会主动将改动发送给订阅服务器。发布服务器和订阅服务器的网络连接需要保持畅通，适用于及时性的环境。

2）请求日志：订阅服务器周期性请求发布服务器，获取发布服务器的数据变更日志。发布服务器和订阅服务器可以不用保持长连接，适用于实时性的环境。

1.2.3 使用场景

1）主从服务器的数据高度一致

2）发布服务器有大量的增删改操作

3）订阅服务器的改动不会传播回发布服务器

1.3 具有可更新订阅的事务发布

1.3.1 使用场景

在订阅服务器上的改动同样会同步到主库

1.4 合并复制

1.4.1 概念

允许发布服务器和多个订阅服务器的数据都更新，定期将这些数据改动进行合并，使得所有节点的数据保持一致。

2、日志传送

2.1 概念

将主数据库日志备份，发送到从服务器上，然后从服务器还原日志，更新数据。

2.2 优点

无论是新增表、视图等等，都会通过日志同步给从服务器，而复制模式不行。

2.3 缺点

通过日志备份传输，在还原时会有较大的时间延迟。而且无法自动转移故障，只能手动转移。

3、镜像

3.1 概念

维护两个副本：主体服务器和镜像服务器，两者之间建立TCP长连接，在主体服务器发生数据变更时，会将活动事务日志记录的流发送到镜像服务器上（sqlserver 2008版本之后，在发送日志之前，主体服务器将其先进行压缩）。镜像数据库提供热备用服务器，支持在已提交事务不丢失数据的情况下进行快速故障转移，即手动主从切换；若支持自动主从切换，则需要新增见证服务器。

3.2 使用场景

1）镜像服务器不可读，只支持故障转移，若需要读，则需要对镜像数据库做快照，读取快照

2）提高数据库的高可用性。

4、AlwaysOn

4.1 概念

sqlserver 2012版本之后才支持，其集中了故障转移群集、数据库镜像和日志传送三者的优点。具体暂未研究。

二、背景

本次使用sqlserver2008，是为了解决主服务器所在Windows服务器的压力骤增问题，采用数据库主从技术，实现主体服务器的写，从服务器的读的架构方案，因此，选择事务复制来实现。本文对于事务复制和镜像数据库两种方案都做了实现。

三、事务复制实现方式

3.1 连接发布数据库

通过主机名称连接数据库服务器，并在本机hosts文件中加入目标主机名称和主机IP的映射

3.2 查询主机名称是否一致

3.3 修改主机名称一致

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

3.4 新建发布

在发布服务器上，点击复制，右键本地发布--新建发布

3.5 选择要同步的数据库

3.6 选择同步类型--事务发布

3.7 选择同步的对象

同步对象包括表、存储过程、用户函数等

注：没有主键的表是不支持复制的

3.8 选择初始快照复制

3.9 代理连接发布服务器

3.10 创建发布

3.11 声明发布名称

3.12 等待初始快照复制

3.13 新建订阅

在订阅服务器上，点击复制，右键本地订阅--新建订阅

3.14 查找发布服务器

使用主机名称连接发布服务器

3.15 选择要订阅的发布任务

3.16 选择推送订阅模式

3.17 新建同步数据库

3.18 配置订阅服务器的连接

3.20 代理连接订阅服务器

3.21 代理计划选择连续运行

3.22 初始化时间选择立即

3.23 创建订阅

3.24 点击完成订阅

3.25 等待订阅完成

3.26 查看发布订阅任务

3.27 测试

在主库上插入数据，从库也会同步进来，全部完成。

四、镜像数据库实现方式

4.1 备份主数据库

右键主数据库，选择任务---备份

4.2 还原镜像数据库

在镜像数据库上新建和主数据库同名的数据库，右键该数据库，选择任务---还原---数据库，将步骤一的备份文件复制到镜像数据库所在服务器上，下图中的源设备指定路径为备份文件的路径，然后点击左上角的选项，选择覆盖现有数据库和RESTORE WITH NORECOVERY，点击确定。刷新镜像数据库，即可看到正在还原的状态。

4.3 配置镜像属性

右键主数据库，点击属性---镜像，点击配置安全性。

4.4 配置见证服务器

不使用见证服务器

4.5 配置主服务器属性

配置主服务器的开放端口

4.6 配置镜像服务器属性

选择镜像服务器地址，并配置开放端口

4.7 配置账户

可直接跳过

4.8 完成镜像配置

4.9 镜像配置成功

4.10 开始镜像

4.11 更新数据库状态

4.12 配置镜像数据库快照

镜像数据库不支持访问，因此如果要访问镜像数据库，需要对镜像数据库建立快照，我们使用定时作业对数据库建立快照

4.12.1 配置作业名称

4.12.2 配置步骤

4.12.2.1 创建镜像数据库快照脚本

if exists(select * From sys.databases where name ='lz3_snapshot') 
drop database lz3_snapshot
create database lz3_snapshot on
(name=N'lz3', filename='D:\snapshot\lz3_snapshot.ss')as snapshot of lz3;

--  name=N'lz3'  lz3--代表的是镜像数据库的日志文件名称
--  filename='D:\snapshot\lz3_snapshot.ss' 该路径为镜像快照的日志文件，自定义路径即可