【SQL Server高可用性】数据库镜像：同一台机器的两个不同实例上配置数据库镜像 + 另一台见证服务器

在上一篇文章中：

【SQL Server高可用性】数据库镜像：在SQL Server 2008R2上的配置数据库镜像 

讲到了用3台笔记本电脑来配置数据库镜像，这个主要是用来模拟生产环境中，在多台服务器上的配置情况。

这次主要考虑到大家都没那么多的笔记本电脑，所以下面要讲的方法，只需要2台笔记本就可以了。

1、基本的信息

本文主要是在2台笔记本上配置数据库镜像，通过证书来实现验证，而不是用域账户来实现。另外，本文配置的是高安全性的数据库镜像，能实现自动秒级切换，需要2台机器。

注意：主体、镜像实在同一台服务器上的两个实例上的。

主体服务器ip：192.168.1.101 实例：MSSQLSERVER   文件目录：c:\share

镜像服务器ip：192.168.1.101 实例：SQL2008R2          文件目录：e:\share

见证服务器ip：192.168.1.102                                          文件目录：c:\

注意：下面所有的代码，都需要按照(编号)的顺序，在相应的服务器上执行。

比如：(1)是在主体服务器上执行的，(2)是在镜像服务器上执行的，(3)又是在主体服务器执行的，所有的操作必须要按照(编号)中的编号的顺序来执行。

2、主体服务器的配置

需要把数据库备份文件c:\share\DB_Mirror.bak 复制到到e:\share\DB_Mirror.bak：

-- ===========================================-- 无论是主体服务器、镜像服务器, 还是见证服务器-- 除特别说明外，均需要保证下面的操作在master库中执行USE masterGO-- ===========================================--（1）  建立镜像主体数据库-- 此操作主体服务器上执行-- a. 建立测试数据库CREATE DATABASE DB_MirrorON(NAME = DB_Mirror_DATA,FILENAME = N'c:\share\DB_Mirror.mdf')LOG ON(NAME = DB_Mirror_LOG,FILENAME = N'c:\share\DB_Mirror.ldf')--设置数据库的恢复模式是完全模式ALTER DATABASE DB_Mirror SETRECOVERY FULLGO-- b. 完全备份,需要把这个完全备份文件，传输到镜像服务器上BACKUP DATABASE DB_MirrorTO DISK = N'c:\share\DB_Mirror.bak'WITH FORMATGO

执行代码后，需要把c:\share\CT_Mirror_SrvA.cer证书文件，复制到e:\share\CT_Mirror_SrvA.cer：

-- ===========================================--（3）  主体服务器上的数据库镜像端点及身份验证用的证书-- 此操作主体服务器上执行-- a. 用于数据库镜像端点身份验证的证书IF NOT EXISTS(  -- 使用数据库主密钥加密证书SELECT * FROM sys.symmetric_keysWHERE name = N'##MS_DatabaseMasterKey##')CREATE MASTER KEYENCRYPTION BY PASSWORD = N'wcis123'--drop certificate ct_mirror_srvaCREATE CERTIFICATE CT_Mirror_SrvAWITHSUBJECT = N'certificate for database mirror',START_DATE = '19990101',EXPIRY_DATE = '99991231'GO-- b. 备份证书, 以便在与此端点通信的另一端建立此证书BACKUP CERTIFICATE CT_Mirror_SrvATO FILE = 'c:\share\CT_Mirror_SrvA.cer'GO--drop endpoint edp_mirror-- c. 数据库镜像端点CREATE ENDPOINT EDP_MirrorSTATE = STARTED AS TCP(LISTENER_PORT = 5022,  -- 镜像端点使用的通信端口LISTENER_IP = ALL)     -- 侦听的IP地址    FOR DATABASE_MIRRORING(AUTHENTICATION = CERTIFICATE CT_Mirror_SrvA, -- 证书身份验证ENCRYPTION = DISABLED,                       -- 不对传输的数据加密,如果需要加密,可以配置为 SUPPORTED 或 REQUIRED, 并可选择加密算法ROLE = ALL)                                  -- 端点支持所有的数据库镜像角色, 也可以设置为 WITNESS(仅见证服务器),或 PARTNER(仅镜像伙伴)GO

把镜像创建的证书文件e:\share\CT_Mirror_SrvB.cer复制到到c:\share\CT_Mirror_SrvB.cer后，运行如下代码：

-- ===========================================--（6）  在主体服务器上完成镜像服务器数据库镜像端点的传输安全模式配置-- 此操作主体服务器上执行-- a. 建立主体服务器上的证书(假设镜像服务器上备份的证书已经复制到 c:\share\CT_Mirror_SrvB.cer)CREATE CERTIFICATE CT_Mirror_SrvBFROM FILE = 'c:\share\CT_Mirror_SrvB.cer'-- b. 建立登录，用这个login来登录到镜像服务器上CREATE LOGIN LOGIN_Mirror_SrvBFROM CERTIFICATE CT_Mirror_SrvB-- c. 授予对数据库镜像端点的 connect 权限GRANT CONNECT ON ENDPOINT::EDP_MirrorTO LOGIN_Mirror_SrvBGO

注意，主体和镜像是在同一台服务器上的，我就把连接字符串写成了这样：'TCP://192.168.1.101,14335:5023'

这里的14335是SQL2008R2这个实例的端口，但是这么写提示找不到镜像服务器。

我在网上搜索了一下，没找到什么有用的资料，于是我想通过图形化界面来设置，当设置到镜像服务器时，提示：如果主体、镜像或者见证，在同一个机器上时，那么必须使用不同的数据库镜像连接端口，也就是比如：

主体的监听端口是：5022

镜像的监听端口是：5023

见证的监听端口是：5022

所以，需要通过如下的连接字符串来设置数据库镜像，意思就是连接到5023端口，因为数据库镜像在这个端口进行监听：

-- ===========================================
--（8）  在主体服务器上启用数据库镜像(默认为高安全性模式,所以不用进行模式设置)
-- 此操作主体服务器上执行
ALTER DATABASE DB_Mirror SET
PARTNER = 'TCP://192.168.1.101:5023'
GO

把在见证服务器上创建的证书，复制到主体服务器上的c:\share\CT_Mirror_SrvWitness.cer，然后执行如下代码：

-- ===========================================--（11）  在主体服务器上完成见证服务器数据库镜像端点的传输安全模式配置-- 此操作主体服务器上执行-- a. 建立见证服务器上的证书(假设见证服务器上备份的证书已经复制到 c:\share\CT_Mirror_SrvWitness.cer)CREATE CERTIFICATE CT_Mirror_SrvWitnessFROM FILE = 'c:\share\CT_Mirror_SrvWitness.cer'-- b. 建立登录CREATE LOGIN LOGIN_Mirror_SrvWitnessFROM CERTIFICATE CT_Mirror_SrvWitness-- c. 授予对数据库镜像端点的 connect 权限GRANT CONNECT ON ENDPOINT::EDP_MirrorTO LOGIN_Mirror_SrvWitnessGO

-- ===========================================--（12）  在主体服务器上为数据库镜像启用见证服务器-- 此操作主体服务器上执行ALTER DATABASE DB_Mirror SETWITNESS = 'TCP://192.168.1.102:5022'GO

3、镜像服务器的配置

把数据库备份文件c:\share\DB_Mirror.bak 复制到到e:\share\DB_Mirror.bak后，再执行下面的代码：

-- ===========================================-- 无论是主体服务器、镜像服务器, 还是见证服务器-- 除特别说明外，均需要保证下面的操作在master库中执行USE masterGO-- ===========================================--（2）  初始化镜像主体数据库-- 此操作镜像服务器上执行-- 假设主体数据库的完全备份已经复制到 e:\share\DB_Mirror.bak-- SQL Server必须使用相同的账户名来启动RESTORE DATABASE DB_MirrorFROM DISK = 'e:\share\DB_Mirror.bak'WITH REPLACE, NORECOVERY-- 如果镜像数据库文件要放在指定位置, 则启用下面的 Move 选项, MOVE 'DB_Mirror_DATA' TO N'e:\DB_Mirror.mdf', MOVE 'DB_Mirror_LOG' TO N'e:\DB_Mirror.ldf'GO

-- ===========================================--（4）  镜像服务器上的数据库镜像端点及身份验证用的证书-- 此操作镜像服务器上执行-- a. 用于数据库镜像端点身份验证的证书IF NOT EXISTS(  -- 使用数据库主密钥加密证书SELECT * FROM sys.symmetric_keysWHERE name = N'##MS_DatabaseMasterKey##')CREATE MASTER KEYENCRYPTION BY PASSWORD = N'wwwwc123'CREATE CERTIFICATE CT_Mirror_SrvBWITHSUBJECT = N'certificate for database mirror',START_DATE = '19990101',EXPIRY_DATE = '99991231'GO-- b. 备份证书, 以便在与此端点通信的另一端建立此证书BACKUP CERTIFICATE CT_Mirror_SrvBTO FILE = 'e:\share\CT_Mirror_SrvB.cer'GO

注意，数据库镜像端点的监听端口是 5023

-- c. 数据库镜像端点CREATE ENDPOINT EDP_MirrorSTATE = STARTED AS TCP(LISTENER_PORT = 5023,  -- 镜像端点使用的通信端口LISTENER_IP = ALL)     -- 侦听的IP地址    FOR DATABASE_MIRRORING(AUTHENTICATION = CERTIFICATE CT_Mirror_SrvB, -- 证书身份验证ENCRYPTION = DISABLED,                       -- 不对传输的数据加密,如果需要加密,可以配置为 SUPPORTED 或 REQUIRED, 并可选择加密算法ROLE = ALL)                                  -- 端点支持所有的数据库镜像角色, 也可以设置为 WITNESS(仅见证服务器),或 PARTNER(仅镜像伙伴)GO

把镜像创建的证书e:\share\CT_Mirror_SrvB.cer复制到c:\share\CT_Mirror_SrvB.cer后，再执行下面的代码：

-- ===========================================--（5）  在镜像服务器上完成主体服务器数据库镜像端点的传输安全模式配置-- 此操作镜像服务器上执行-- a. 建立主体服务器上的证书(假设主体服务器上备份的证书已经复制到 C:\CT_Mirror_SrvA.cer)CREATE CERTIFICATE CT_Mirror_SrvAFROM FILE = 'e:\share\CT_Mirror_SrvA.cer'-- b. 建立登录CREATE LOGIN LOGIN_Mirror_SrvAFROM CERTIFICATE CT_Mirror_SrvA-- c. 授予对数据库镜像端点的 connect 权限GRANT CONNECT ON ENDPOINT::EDP_MirrorTO LOGIN_Mirror_SrvAGO

-- ===========================================--（7）  在镜像服务器上启用数据库镜像-- 此操作镜像服务器上执行ALTER DATABASE DB_Mirror SETPARTNER = 'TCP://192.168.1.101:5022'GO

把见证服务器上的证书，拷贝到镜像服务器上后，再执行下面的代码：

-- ===========================================--（10）  在镜像服务器上完成见证服务器数据库镜像端点的传输安全模式配置-- 此操作镜像服务器上执行-- a. 建立见证服务器上的证书(假设见证服务器上备份的证书已经复制到 c:\CT_Mirror_SrvWitness.cer)CREATE CERTIFICATE CT_Mirror_SrvWitnessFROM FILE = 'e:\share\CT_Mirror_SrvWitness.cer'-- b. 建立登录CREATE LOGIN LOGIN_Mirror_SrvWitnessFROM CERTIFICATE CT_Mirror_SrvWitness-- c. 授予对数据库镜像端点的 connect 权限GRANT CONNECT ON ENDPOINT::EDP_MirrorTO LOGIN_Mirror_SrvWitnessGO

4、见证服务器的配置

把主体服务器和镜像服务器上的证书，拷贝到见证服务器上后，再执行下面的代码：

-- ===========================================-- 无论是主体服务器、镜像服务器, 还是见证服务器-- 除特别说明外，均需要保证下面的操作在master库中执行USE masterGO-- ===========================================--（9）  配置见证服务器-- 此操作在见证服务器上执行-- a. 完成见证服务器上数据库镜像端点的传输安全模式配置-- (a). 用于数据库镜像端点身份验证的证书IF NOT EXISTS(  -- 使用数据库主密钥加密证书SELECT * FROM sys.symmetric_keysWHERE name = N'##MS_DatabaseMasterKey##')CREATE MASTER KEYENCRYPTION BY PASSWORD = N'abc.123'CREATE CERTIFICATE CT_Mirror_SrvWitnessWITHSUBJECT = N'certificate for database mirror',START_DATE = '19990101',EXPIRY_DATE = '99991231'GO-- (b). 备份证书, 以便在与此端点通信的另一端建立此证书BACKUP CERTIFICATE CT_Mirror_SrvWitnessTO FILE = 'c:\CT_Mirror_SrvWitness.cer'GO-- (c). 数据库镜像端点CREATE ENDPOINT EDP_MirrorSTATE = STARTED AS TCP(LISTENER_PORT = 5022,  -- 镜像端点使用的通信端口LISTENER_IP = ALL)     -- 侦听的IP地址    FOR DATABASE_MIRRORING(AUTHENTICATION = CERTIFICATE CT_Mirror_SrvWitness, -- 证书身份验证ENCRYPTION = DISABLED,                             -- 不对传输的数据加密,如果需要加密,可以配置为 SUPPORTED 或 REQUIRED, 并可选择加密算法ROLE = ALL)                                        -- 端点支持所有的数据库镜像角色, 也可以设置为 WITNESS(仅见证服务器),或 PARTNER(仅镜像伙伴)GO-- b. 完成主体服务器上数据库镜像端点的传输安全模式配置-- (a). 建立主体服务器上的证书(假设主体服务器上备份的证书已经复制到 C:\CT_Mirror_SrvA.cer)CREATE CERTIFICATE CT_Mirror_SrvAFROM FILE = 'C:\CT_Mirror_SrvA.cer'-- (b). 建立登录CREATE LOGIN LOGIN_Mirror_SrvAFROM CERTIFICATE CT_Mirror_SrvA-- (c). 授予对数据库镜像端点的 connect 权限GRANT CONNECT ON ENDPOINT::EDP_MirrorTO LOGIN_Mirror_SrvAGO-- c. 完成镜像服务器上数据库镜像端点的传输安全模式配置-- (a). 建立镜像服务器上的证书(假设镜像服务器上备份的证书已经复制到 C:\CT_Mirror_SrvB.cer)CREATE CERTIFICATE CT_Mirror_SrvBFROM FILE = 'C:\CT_Mirror_SrvB.cer'-- (b). 建立登录CREATE LOGIN LOGIN_Mirror_SrvBFROM CERTIFICATE CT_Mirror_SrvB-- (c). 授予对数据库镜像端点的 connect 权限GRANT CONNECT ON ENDPOINT::EDP_MirrorTO LOGIN_Mirror_SrvBGO

5、配置完成后的图

主体：

镜像：

如果这个时候，主体服务器挂了会怎么样呢？

这里，通过关闭服务来模拟主体服务器故障的情况：

我们会发现，镜像自动变为主体：

再次启动MSSQLSERVER实例的服务器，也就是启动原主体，我们发现原来的主体，自动变为镜像：

当然，这里也可以进行手工故障转移，在SQL2008R2实例的主体上执行如下的代码，可以实现手工故障转移到镜像服务器，也就是当主体挂掉后，自动把镜像转为主体，继续提供数据库服务：

alter database db_mirrorset partner failover

MSSQLSERVER 从镜像变为主体：

SQL2008R2 从主体变为镜像

之所以能自动切换的原因是，本文配置的是高安全模式 ，再加上见证服务器，就可以实现秒级的自动故障转移，这个可以设置为高安全模式：

alter database db_mirrorset safety full

如果配置的是高可用性模式，可以使用强制故障转移：

--设置为高可用性模式alter database db_mirrorset safety off--当主体数据库未出现故障时,不能故障转移到镜像数据库--当主体数据库出现故障时,在镜像数据库中可以强制故障转移到镜像服务器alter database db_mirrorset partner force_service_allow_data_loss--当原来的主体数据库重新进入服务状态,并且能和新的主体服务器连接时--可以恢复数据库镜像,但是会导致所有未发送的日志记录、对应的更新丢失alter database db_mirrorset partner resume