sql server基于镜像服务器的主从搭建+认证服务器

题目：sql server基于镜像服务器的主从搭建+认证服务器

要求：重启主库数据库服务器看镜像库是否变自动更为主库，原主库重启后是否变更为镜像库，同时与新主库保持同步状态，再把从库切换成主库看是否正常。测试数据随便在库表内建立任意数据。

环境说明：windows2016、sql server 2014

主体服务器：192.168.164.100

镜像服务器：192.168.164.101

见证服务器：192.168.164.102

前置概念

主从方案介绍

sql server 作为目前主流的数据库，用户遍布世界各地。sql server也有一些比较成熟的主备方案，目前主要有：复制模式(发布-订阅模式)、镜像传输模式、日志传输模式、故障转移集群。后面会一一介绍介绍各自的优缺点。

（一）复制模式

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

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

　　复制模式同步，要求数据库名称和主机名称必须一致，否则查找不到数据库主机；要求数据库不能使用端口，必须是可以通过ip直接访问的；

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

　　1.快照发布

　　　　快照发布，就是将所有要发布的内容，做成一个镜像文件，然后一次性复制到订阅服务器，两次快照之间的更新不会实时同步。这种方式占用带宽较多，因此比较适用内容不是很大，或者更新不需要很频繁的场景

　　2.事务发布/具有可更新订阅的事务发布

　　　　事务发布，是在第一次设置好事务复制之后，所有发布的内容都会进行镜像快照，订阅服务器收到已发布数据的初始快照后，发布服务器将事务流式传输到订阅服务器。当主服务器数据发生变更时，会通过日志传递同步给订阅服务器，数据近似于同步更新。

　　　　此方式会对主服务器性能造成很大影响（实时同步每次变更，而不是最终变更），适用于对数据及时性要求比较严格主备方案，但是目前已被微软提供的集群Always On所取代。

　　3.合并发布

　　　　合并发布是相当于两台都是主服务器，都可以对数据进行更新修改等操作，然后定时将发布服务器上的内容与订阅服务器上的内容进行合并，并根据配置保留相应内容，此种很少用。

（二）镜像传输模式

　　数据库镜像传输，严格来说不是主从架构，而是主备架构，将两台数据库服务器通过一台中间监控服务器关联起来，两台服务器通过镜像文件，实时同步数据（有延迟，延迟很短）。当主服务器宕机之后，监控服务器自动切换到备份服务器上。

　　此方案优点是可以快速的切换主备方案，相比较Always on集群，可以不用共享磁盘即可实现，避免了数据库集群存储单点故障，导致整个集群崩溃。

　　缺点也很明显，无论是主备服务器，要实现同步操作，都是依赖于性能低的那一端，因此两台服务器都要是高性能的才可以保证同步的及时性；同时备份服务器只是备份和故障转移，不能提供从服务器的只读访问，因此才说是主备服务器，而且是一对一，只能有一台备份服务器。

（三）日志传输模式

　　与镜像传输模式类似，是将主数据库日志备份，发送到从服务器上，然后从服务器还原日志，更新数据。

　　此方式优点在于从服务器可以有多台从服务器，而且当主服务器脚本操作异常后，只需要在日志同步之前，及时拦截日志传输，即可保留从服务器数据，减少灾难损失；此方式相较于“复制发布”模式，还有一个有点就是无论是新增表、视图等等，都会通过日志同步给从服务器，而复制模式不行

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

（四）故障转移集群

　　集群技术是微软提供的，可用性最高的主备方案。它是将多台服务器通过一个共享的外部存储区域（SAN），连接成一个资源共享的服务器群体，数据库文件和实例，都存放并运行在该共享区域节点上，每台服务器相当于一个节点，共同访问共享的节点实例。服务器只有一个节点处于活动状态，当活动节点出现故障，会有其他节点主动启动，取代当前故障点，整个过程只需要几秒钟，用户无法感知。

　　集群有很多优点，是目前最高效的高可用技术，但是他也有很明显的缺点，所有的节点，都依赖于共享节点实例，如果共享节点出现故障，将会导致整个集群失去作用，且很难恢复。

1.1、服务器概念

◆ 主体服务器（Principal Server）

　　其中一个实例为客户端提供服务，这个实例称为"主体服务器"。该服务器"扮演"主体角色"，其数据库副本为当前的"主体数据库"。

◆ 镜像服务器（Mirror Server）

　　另一个实例则充当备用服务器，这个实例称为"镜像服务器"（Mirror Server）。该服务器扮演"镜像角色"，其数据库副本为当前的"镜像数据库"。镜像数据库不能供客户端访问，但是可以为镜像数据库创建一个快照，让客户端访问这个快照。

◆ 见证服务器（Witness Server）

　　见证服务器并不能用于数据库，只是用来支持自动故障转移。见证服务器验证主体服务器是否保持运行，当见证服务器与主体服务器断开连接之后，如果此时镜像服务器和见证服务器保持相互连接，则镜像服务器启动自动故障转移，成为新的主体服务器。

1.2、模式概念

数据库镜像会话以同步操作或异步操作运行。

　　在同步操作下，事务将在伙伴双方处提交。由于主体数据库需要等待镜像数据库将日志写入磁盘后返回的确认消息，因此会延长事务滞后时间。在异步操作下，事务不需要等待镜像服务器将日志写入磁盘便可提交，这样可最大程度地提高性能。

在SQL SERVER 2008之后，主库和镜像库之间的日志流传送会默认使用压缩，压缩一方面降低了网络压力，另一方面增大了镜像两端的CPU压力。 可以打开 TF 1462 来关闭日志流压缩，SQL SERVER 2005 上日志传送没有使用压缩。

根据是否同步操作以及是否支持自动故障转移功能，数据库镜像有以下三种运行模式。

（1） 高安全性模式：主库把事务日志数据信息发给从库，从库返回事务日志持久化确认信息，确认同步后，事务将在主从库一起提交。

（2） 高性能模式：主库把事务日志数据信息发给从库，发完后无需等待从库返回确认信息。

（3） 自动故障转移模式（高可用模式）：在高安全模式运行时，可以添加见证服务器，从而实现自动故障转移。

前置配置

（1）实例版本：必须是2005 SP1及以上（且兼容级别也要在这个版本及以上），且主从版本一致。

（2）数据库版本：必须是标准版及更高的开发/评估/企业，且只有企业版/开发版才能实现高性能模式。且主从一致。

（3）通信：确认网络能ping通，确定端点端口（默认一般是5022）与实例端口（默认1433）可以telnet 通实现访问。

（4）磁盘：足够的磁盘空间（全备复制+事务备复制+还原空间+预留空间）。最好主从是相同的目录（不同会造成无法加文件）。

（5）限制：不支持 FILESTREAM。不能在主体上创建它。不能为包含 FILESTREAM 文件组的数据库配置数据库镜像。

（6）系统：32位系统下，单实例最多支持10个数据库做镜像。

（7）主数据库：镜像的数据库对象不能是系统数据库。主库必须是完整恢复模式。且主从库必须是相同的数据库名。

（8）从数据库：利用主数据库的相关备份进行还原，必须以norecovery模式（RESTORING 状态）

（9）权限：登录名具有实施步骤权限，最好sysadmin。可能还需要实例账户拥有一定程度的windows权限，最好是admin组；

（10）端点：镜像两端的加密算法必须保持一致，否则无法搭建。（因为不同版本默认加密算法不一样）小版本没关系，但前提是从库可以还原主库。

搭建过程

-- 1、创建主密钥（主库、镜像库、认证服务器上都执行）
use master
go
create master key encryption by password='landui@123'
go
-- 查看主密钥 select * from sys.key_encryptions

-- 2、分别在主体服务器、镜像服务器、见证服务器上创建证书
-- 主库
use master
go
create certificate sqlsrv1_cert with subject='sqlsrv1_cert',expiry_date='2099-1-1'
go

-- 镜像库
use master
go
create certificate sqlsrv2_cert with subject='sqlsrv2_cert',expiry_date='2099-1-1'
go

-- 见证服务器
use master
go
create certificate sqlsrv_witness_cert with subject='sqlsrv_witness_cert',expiry_date='2099-1-1'
go


-- 3、分别在主体服务器、镜像服务器、见证服务器上创建端点
-- 主库
Use master;
go
CREATE ENDPOINT Endpoint_Mirroring
  STATE = STARTED AS TCP (
    LISTENER_PORT=5022
     , LISTENER_IP = ALL
  )

FOR DATABASE_MIRRORING (
    AUTHENTICATION = CERTIFICATE sqlsrv1_cert
     , ENCRYPTION = REQUIRED ALGORITHM AES
     , ROLE =    ALL
  );
GO

-- 镜像库
Use master;
go
CREATE ENDPOINT Endpoint_Mirroring
  STATE = STARTED AS TCP (
    LISTENER_PORT=5022
     , LISTENER_IP = ALL
  )

FOR DATABASE_MIRRORING (
    AUTHENTICATION = CERTIFICATE sqlsrv2_cert
     , ENCRYPTION = REQUIRED ALGORITHM AES
     , ROLE =    ALL
  );
GO

-- 见证服务器
Use master;
go
CREATE ENDPOINT Endpoint_Mirroring
  STATE = STARTED AS TCP (
    LISTENER_PORT=5022
     , LISTENER_IP = ALL
  )

FOR DATABASE_MIRRORING (
    AUTHENTICATION = CERTIFICATE sqlsrv_witness_cert
     , ENCRYPTION = REQUIRED ALGORITHM AES
     , ROLE =    ALL
  );
GO

-- 4、备份证书（主体服务器、镜像服务器、见证服务器都备份，并互相拷贝过去，保证每个服务器上都有3个证书）
-- 主库
use master
go
backup certificate sqlsrv1_cert to file = 'C:\\cert\\sqlsrv1_cert.cer'
go

-- 从库
use master
go
backup certificate sqlsrv2_cert to file = 'C:\\cert\\sqlsrv2_cert.cer'
go

-- 见证服务器
use master
go
backup certificate sqlsrv_witness_cert to file = 'C:\\cert\\sqlsrv_witness_cert.cer'
go

-- 5、创建登录名（这个要和证书关联，所以主体服务器、镜像服务器、见证服务器都要创建除自己以外的另外2个用户）
-- 主库上执行：
-- 创建镜像库的证书关联用户
use master
go
create login sqlsrv2_login with password='landui@123'
go

-- 创建见证服务器的证书关联用户
use master
go
create login sqlsrv_witness_login with password='landui@123'
go

-- 从库上执行：
-- 创建主库的证书关联用户
use master
go
create login sqlsrv1_login with password='landui@123'
go

-- 创建见证服务器的证书关联用户
use master
go
create login sqlsrv_witness_login with password='landui@123'
go

-- 见证服务器上执行：
-- 创建主库的证书关联用户
use master
go
create login sqlsrv1_login with password='landui@123'
go

-- 创建镜像库的证书关联用户
use master
go
create login sqlsrv2_login with password='landui@123'
go

-- 6、创建使用该登录名的用户（主体服务器、镜像服务器、见证服务器都要创建）
-- 主库上执行

-- 创建镜像库的证书关联用户
use master
go
create user sqlsrv2_user for login sqlsrv2_login
go

-- 创建见证服务器的证书关联用户
use master
go
create user sqlsrv_witness_user for login sqlsrv_witness_login
go

-- 镜像库上执行

-- 创建主库的证书关联用户
use master
go
create user sqlsrv1_user for login sqlsrv1_login
go

-- 创建见证服务器的证书关联用户
use master
go
create user sqlsrv_witness_user for login sqlsrv_witness_login
go

-- 见证服务器上执行

-- 创建主库的证书关联用户
use master
go
create user sqlsrv1_user for login sqlsrv1_login
go

-- 创建镜像库的证书关联用户
use master
go
create user sqlsrv2_user for login sqlsrv2_login
go

-- 7、证书与用户关联
-- 主库上执行

-- 使镜像库上的证书与用户关联
use master
go
create certificate sqlsrv2_cert
authorization sqlsrv2_user
from file='C:\\cert\\sqlsrv2_cert.cer'
go

--使见证库上的证书与用户关联
use master
go
create certificate sqlsrv_witness_cert
authorization sqlsrv_witness_user
from file='C:\\cert\\sqlsrv_witness_cert.cer'
go

-- 镜像库上执行

-- 使主库上的证书与用户关联
use master
go
create certificate sqlsrv1_cert
authorization sqlsrv1_user
from file='C:\\cert\\sqlsrv1_cert.cer'
go

--使见证服务器上的证书与用户关联
use master
go
create certificate sqlsrv_witness_cert
authorization sqlsrv_witness_user
from file='C:\\cert\\sqlsrv_witness_cert.cer'
go

-- 见证服务器上执行

-- 使主库上的证书与用户关联
use master
go
create certificate sqlsrv1_cert
authorization sqlsrv1_user
from file='C:\\cert\\sqlsrv1_cert.cer'
go

--使镜像库上的证书与用户关联
use master
go
create certificate sqlsrv2_cert
authorization sqlsrv2_user
from file='C:\\cert\\sqlsrv2_cert.cer'
go

-- 8、授予对远程数据库端点的登录名的CONNECT权限(每个上面2个用户都要有端点的权限)
-- 主库上执行
use master
go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [sqlsrv2_login];
go

use master
go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [sqlsrv_witness_login];
go

-- 从库上执行
use master
go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [sqlsrv1_login];
go

use master
go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [sqlsrv_witness_login];
go

-- 见证服务器上执行
use master
go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [sqlsrv1_login];
go

use master
go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [sqlsrv2_login];
go

-- 9、对需要做镜像的数据库做完整备份和事务日志备份，然后在镜像库上做还原（先还原完整备份，再还原事务日志），使用NORECOVERY模式

-- 10.连接镜像(先在镜像库上操作,然后在主库上操作，不需要在见证数据库上操作)
-- 镜像库上执行 (做完这个操作后,数据库的状态:正在恢复)

use master
go
ALTER DATABASE testdb SET PARTNER = 'TCP://192.168.164.100:5022';
go

-- 主库上执行

-- 连接镜像库
use master
go
ALTER DATABASE testdb SET PARTNER = 'TCP://192.168.164.101:5022';
go
-- 连接见证服务器
use master
go
ALTER DATABASE testdb SET WITNESS = 'TCP://192.168.164.102:5022';
go

完成后测试

主库和镜像库的状态

主从同步和见证服务器的自动故障转移测试

新建表

CREATE  TABLE　course
( course_id   char(5)  PRIMARY  KEY,
course_name  char(20) ,
period  int,
UNIQUE(course_name)
)

INSERT INTO course VALUES('1001',' C++','100');
INSERT INTO course VALUES('1002',' java','101');
INSERT INTO course VALUES('1003',' python','102');

INSERT INTO course VALUES('1004',' 计算机组成原理','103');
INSERT INTO course VALUES('1005',' 操作系统','104');