SQLSERVER2016 数据库镜像部署

郑全

我们知道，SQLSERVER 官方已经从2012标注镜像被放弃，2017已经剔除不再支持镜像，但在生产中，数据库镜像还比较多，并且我前面已经搭建log shipping,发布订阅，为了完整性，继续了解镜像技术。

一、SQLSERVER 数据库镜像概念

• 本质：一对一的数据库级高可用方案（一个主体库 + 一个镜像库），仅支持单个数据库，不支持跨数据库事务。
  
• 模式：
  
  
    
  • 高安全性模式：需要见证服务器（Witness）实现自动故障转移，事务同步提交，无数据丢失。
      
  • 高性能模式：异步提交，镜像库有延迟，但主体库性能不受影响，仅支持手动故障转移。
    
• 状态：镜像库处于 “恢复中” 状态，无法直接访问（只能通过快照只读访问）。
  
• 版本支持：SQL Server 2005 引入，SQL Server 2012 后被标记为弃用，SQL Server 2017/2019 已彻底移除。
  

二、本次部署环境

       WINDOWS2016非域环境+SQLSERVER 2016

主库：192.168.133.131

备库：192.168.133.132

三、安装步骤

安装注意点：

1.数据库的模式要是完整模式。

2.要对数据库完整备份和事务日志备份，分别还原到镜像库上，使用NORECOVERY模式。

3.镜像数据库是不允许删除和操作，即便查看属性也不行。

4.先删除端点，再删除证书，再删除主密钥。

5.只有是同步模式的时候，才能手动故障转移，异步模式不能手动故障转移。

使用图形界面ssms安装失败，使用命令安装成功。

1.创建密匙

先创建密匙，主机备机都要下面执行代码

use master  --创建密匙
go
create master key encryption by password='sztech_4U'
go

select * fromsys.key_encryptions  --查询密匙

2.创建证书

主机执行
use master  --主机证书为：DBA
go
create certificate DBA_cert with subject='DBAcertificate',expiry_date='2099-1-1'
go

备机执行

usemaster --主机证书为：DBB
go
create certificate DBB_cert with subject='DBBcertificate',expiry_date='2099-1-1'
go

select * fromsys.certificates  --查看证书

3.创建主库镜像和端点

主机执行

use master
go

create endpointTicket_Mirroring  --端点为Ticket_Mirroring ，端口号：5022,镜像为DBA

state=started
as tcp ( listener_port = 5022,listener_ip = all )
for database_mirroring ( authentication = certificate DBA_cert, encryption =required algorithm aes, role = all )
go

//*  ------------注意-----------------

如果前面通过图形界面安装过，需要把这个端点删除

Select * fromsys.endpoints;

Drop endpoint “存在的端点名称”;

*//  ------------------------------

备机执行

create endpointTicket_Mirroring --端点为Ticket_Mirroring ，端口号：5022,镜像为DBB
state=started
as tcp ( listener_port = 5022,listener_ip = all )
for database_mirroring ( authentication = certificate DBB_cert, encryption =required algorithm aes, role = all )
go

4.备份密匙

主机执行

use master --备份密匙
go
backup certificate DBA_cert to file = 'D:\cert\DBA_cert.cer' --密匙路径
go

备机执行

use master --备份密匙
go
backup certificate DBB_cert to file = 'D:\cert\DBB_cert.cer' --密匙路径
go

5.复制交换密匙

复制交换密匙，保证在主机和备机的D:\cert下路径都有DBA_cert和DBB_cert文件

6.建登录名和证书关联

主机创建备机，备机创建主机

主机执行

use master
go
create login DBB_login with password='sztech_4U'
go

备机执行

use master
go
create login DBA_login with password='sztech_4U'
go

7.创建使用该登录名的用户

主机创建备机，备机创建主机

主机执行

use master
go
create user DBB for login DBB_login
go

备机执行

use master
go
create user DBA for login DBA_login
go

8.证书与用户关联

主机关联备机，备机关联主机

主机执行

use master

go
create certificate DBB_cert
authorization DBB
from file='D:\cert\DBB_cert.cer'
go

备机执行

use master

go
create certificate DBA_cert
authorization DBA
from file='D:\cert\DBA_cert.cer'
go

9.授权

授予对远程数据库端点的登录名的CONNECT权限,主授权备机，备机授权主机

主机执行

use master
go
GRANT CONNECT ON ENDPOINT::Ticket_Mirroring TO [DBB_login];
go

备机执行

use master
go
GRANT CONNECT ON ENDPOINT::Ticket_Mirroring TO [DBA_login];
go

10.恢复备库

从主机上备份需要热备的数据库的数据库和事务日志，数据库一定要完整，然后把数据库和事务日志还原到备机，还原一定要使用NORECOVERY模式，

还原后备机数据库显示正在还原为正常现象。

   主库：

                               
登录/注册后可看大图

                               
登录/注册后可看大图

备库恢复：

                               
登录/注册后可看大图

                               
登录/注册后可看大图

                               
登录/注册后可看大图

                               
登录/注册后可看大图

11.连接镜像

先在备机库上操作,然后在主机库上操作

备机执行

use master --连接镜像
go
ALTER DATABASE mirrortest SET PARTNER = 'TCP://192.168.133.132:5022';
go

注意，数据库名称不能有减号

主机执行

use master --连接镜像
go
ALTER DATABASE mirrortest SET PARTNER = 'TCP://192.168.133.132:5022';
go

12.验证

这个时候我们就已经完成镜像备份啦，可以打开数据库镜像监视器查看同步情况，状态不对的话，先刷新试试，完成状态如下图：

主机图

                               
登录/注册后可看大图

       备机图

                               
登录/注册后可看大图

13.主库宕机后，怎么恢复镜像库为可用状态。

--第一步:脱离镜像
alter database mirrortest set partner off

--第二步;修改数据库的状态为recovery
restore database mirrortest with recovery

14.脱离镜像后还要建立镜像的话

可以重新备份数据库和事务日志用NORECOVERY模式还原，然后连接镜像

use master  --连接镜像，先在备机库上操作,然后在主机库上操作
go
ALTER DATABASE mirrortest SET PARTNER = 'TCP://192.168.133.131:5022';
go

四、常用命令

--切换主备,在主库上执行

use master;

alter database mirrortest set partnerfailover;

                               
登录/注册后可看大图

备库不用操作

直接在原备库上刷新，发现已经变成主库

                               
登录/注册后可看大图

--备机强制切换

use master;

alter database mirrortest set partnerforce_service_allow_data_loss;

--恢复镜像

use master;

alter database mirrottest set partnerresume;

--取消见证服务器

ALTER DATABASE mirrortest SET WITNESS OFF ;

--取消镜像

ALTER DATABASE mirrortest SET PARTNER OFF;

--设置镜像数据库还原为正常

RESTORE DATABASE mirrortest WITH RECOVERY;