【MSSQL】镜像维护

本文主要是介绍【MSSQL】镜像维护，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

参考

概述：https://blog.csdn.net/qq_33656602/article/details/86492019

参考：http://blog.itpub.net/30126024/viewspace-2643859/

搭建：https://blog.csdn.net/elie_yang/article/details/88024042 （注意配置镜像时按下面逻辑配置）

启用GUEST：https://jingyan.baidu.com/article/f54ae2fc57c5371e92b849a3.html

启用共享免密：https://www.cnblogs.com/ymj126/p/6106899.html

使用证书搭建镜像：https://blog.csdn.net/jojoliny/article/details/103990314

使用证书搭建镜像：https://docs.microsoft.com/zh-cn/sql/database-engine/database-mirroring/use-certificates-for-a-database-mirroring-endpoint-transact-sql?view=sql-server-ver15

清理（收缩）事务日志并恢复镜像

语法：https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-database-mirroring

-- 以下内容库名以 DB_TEST 代替
-- 1、主节点 DB_TEST 库属性停止镜像

-- 2、停止主节点 DB_TEST 库的备份类型完整模式（FULL）
alter database DB_TEST set recovery simple;

-- 3、收缩文件
dbcc shrinkdatabase(DB_TEST);

-- 4、恢复主节点 DB_TEST 库的备份类型完整模式（FULL）
alter database DB_TEST set recovery full;

-- 5、备份主节点 DB_TEST

-- 6、恢复从节点 restore DB_TEST files and filegroups，注意使用 restore with norecovery

-- 7、恢复从节点 restore DB_TEST transaction log，注意使用 restore with norecovery

-- 8、设置从节点镜像源
alter database DB_TEST set partner = 'TCP://主:5022';

-- 8.1、如果从节点已经注册一个镜像
alter database DB_TEST set partner off;

-- 9、设置主节点中的从节点
alter database DB_TEST set partner = 'TCP://从:5022';

-- 10、如果库是 restoring 状态无法删除
restore database DB_TEST with recovery;

The mirror database, “XX”, has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database.

针对上面报错，再单独备份并恢复事务日志

The remote copy of database "XX" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.

上面的报错会出现在做了一次完整备份，并把备份文件还原到镜像库中，然后还没开始镜像配置时又在主体服务器做了一次日志备份，导致事务日志中找不到位置。解决方法是再单独备份并恢复事务日志

计划内切换

ALTER DATABASE DB_TEST SET SAFETY FULL
USE master;
ALTER DATABASE DB_TEST SET PARTNER FAILOVER;

计划外切换

• 假设原 A->B，A 是 PRIMARY
• 确保 A 断电或已确认停止服务
• 接受 A 重启后部分未同步的数据可能存在丢失风险

强制切换

-- 在 B 上执行
USE master;
ALTER DATABASE DB_TEST SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;

回切

-- A 恢复后，在 B 执行
USE master;
ALTER DATABASE DB_TEST SET PARTNER RESUME;

-- 此时 A 作为镜像
-- B 作为 PRIMARY，回切需要在 B 上执行
ALTER DATABASE DB_TEST SET PARTNER FAILOVER;

使用证书配置镜像

SSMS 提示报错

The ATER DATABASE command could not be sent to the remote server instance 'TCP://:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

这个报错需要查看服务日志，找到操作不成功的具体问题。

报错1

-- 执行添加镜像节点时，例如
alter database DB_TEST set witness = 'TCP://:5022'

Database mirroring connection error5'Connection handshake faild. There is no compatible authentication protocol. State 21.' for 'TCP://:5022'.

节点间镜像同步的认证方式不同，确认 endpoint 的连接认证方式和加密方式是否相同。

select * from sys.database_mirroring_endpoints

报错2

-- 执行添加镜像节点时，例如
alter database DB_TEST set witness = 'TCP://:5022'

Database mirroring connection error4'10053(An established connection was aborted by the software in your host machine)' for 'TCP://:5022'

部分节点缺少证书，确认各个节点都有其他所有节点的证书。

select * from sys.certificates

报错3

-- 导入证书时
create certificate host_1 from file='';

-- 创建本地证书时
create certificate host_1 with subject='host_1_cert', expiry_date='2099-01-01';

Please create a master key in the database or open themaster key in the session before performing this operation.

本地没有创建 master key，所以不允许创建本地证书和导入外部证书，只需要执行：

create master key encryption by password='';

-- 然后就可以创建本地证书并导出证书
backup certificate host_w to file='';

报错4

create certificate host_1 from file='';

The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

确认文件位置，确认是否是最大权限用户。

这篇关于【MSSQL】镜像维护的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！