sqlserver双机热备份⽅案之数据库镜像（实测sqlserver2016）
⼀、先简单介绍下sql server ⾃带的双机的热备的⼏种⽅案
1，发布--订阅
利⽤sql server 复制功能实现主机发布数据库，备机订阅数据库，做到数据热备
2，⽇志传送
SQLServer数据库引擎中，使⽤⽇志传送将事务⽇志不间断地从⼀个数据库（主数据库）发送到另⼀个数据库（辅助数据库）。不间断地备份主数据库中的事务⽇志，然后将它们复制并还原到辅助数据库，这将使辅助数据库与主数据库基本保持同步。⽬标服务器充当备份服务器，并可以将查询处理从主服务器重新分配到⼀个或多个只读的辅助服务器。⽇志传送可与使⽤完整或⼤容量⽇志恢复模式的数据库⼀起使⽤。
3，数据库镜像
利⽤sql server 镜像功能在备机建⽴镜像后，实现主机和备机数据热备。数据库镜像是⽤于提⾼数据库可⽤性的主要软件解决⽅案。镜像基于每个数据库实现，并且只适⽤于使⽤完整恢复模式的数据库。数据库镜像维护⼀个数据库的两个副本，这两个副本必须驻留在不同的SQL Server数据库引擎实例（服务器实例）
上。通常，这些服务器实例驻留在不同位置的计算机上。其中⼀个服务器实例使数据库服务于客户端（“主体服务器”），⽽另⼀个服务器实例则充当热备⽤或备⽤服务器（“镜像服务器”），具体取决于镜像会话的配置和状态。同步数据库镜像会话时，数据库镜像提供了热备⽤服务器，可⽀持在已提交事务不丢失数据的情况下进⾏快速故障转移。
⼆、数据库镜像热备⽅法
注意点：
1.数据库的模式要是完整模式。
2.要对数据库完整备份和事务⽇志备份，分别还原到镜像库上，使⽤NORECOVERY模式。
3.镜像数据库是不允许删除和操作，即便查看属性也不⾏。
4.先删除端点，再删除证书，再删除主密钥。
5.只有是同步模式的时候，才能⼿动故障转移，异步模式不能⼿动故障转移。
主机：192.168.11.253
备机：192.168.11.251
server 2016（1），先创建密匙，主机备机都要下⾯执⾏代码
use master  --创建密匙
go
create master key encryption by password='888888'
go
select * from sys.key_encryptions  --查询密匙
（2），创建证书，
主机执⾏
use master  --主机证书为：DBA
go
create certificate DBA_cert with subject='DBA certificate',expiry_date='2099-1-1'
go
备机执⾏
use master --主机证书为：DBB
go
create certificate DBB_cert with subject='DBB certificate',expiry_date='2099-1-1'
go
select * ificates  --查看证书
（3），创建主库镜像和端点
主机执⾏
use master
go
create endpoint Ticket_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
备机执⾏
create endpoint Ticket_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\' --密匙路径
go
备机执⾏
use master --备份密匙
go
backup certificate DBB_cert to file = 'D:\cert\' --密匙路径
go
(5)，复制交换密匙，保证在主机和备机的D:\cer下路径都有DBA_cert和DBB_cert⽂件
（6）创建登录名，和证书关联，主机创建备机，备机创建主机
主机执⾏
use master
go
create login DBB_login with password='888888'
go
备机执⾏
use master
go
create login DBA_login with password='888888'
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\'
go
备机执⾏
use master
go
create certificate DBA_cert
authorization DBA
from file='D:\cert\'
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 Ticket SET PARTNER = 'TCP://192.168.11.253:5022';
go
use master  --连接镜像
go
ALTER DATABASE Ticket SET PARTNER = 'TCP://192.168.11.251:5022';
go
（12），这个时候我们就已经完成镜像备份啦，可以打开数据库镜像监视器查看同步情况，状态不对的话，先刷新试试，完成状态如下图：主机图
备机图
（13），主库宕机后，怎么恢复镜像库为可⽤状态。
--第⼀步：脱离镜像
alter database test set partner off
--第⼆步；修改数据库的状态为recovery
restore database test with recovery
（14），脱离镜像后还要建⽴镜像的话，可以重新备份数据库和事务⽇志⽤NORECOVERY模式还原，然后连接镜像
use master  --连接镜像，先在备机库上操作，然后在主机库上操作
go
ALTER DATABASE Ticket SET PARTNER = 'TCP://192.168.11.253:5022';
go
三、常⽤命令
--切换主备
use master;
alter database testdb set partner failover;
--备机强制切换
use master;
alter database testdb set partner force_service_allow_data_loss;
--恢复镜像
use master;
alter database testdb set partner resume;
--取消见证服务器
ALTER DATABASE testdb SET WITNESS OFF ;
ALTER DATABASE testdb SET PARTNER OFF; --设置镜像数据库还原为正常
RESTORE DATABASE testdb WITH RECOVERY;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。