sqlserver关于mirror镜像的总结

官方文档https://docs.microsoft.com/zh-cn/sql/database-engine/database-mirroring/database-mirroring-sql-server?view=sql-server-2017

mirror镜像的基本原理：主服务器上的主体数据库执行的增、删、改、查操作以日志的方法在镜像服务器的镜像数据库中重做。主体数据库创建镜像后，会启动一个单独的事务日志发送线程，维护一个虚拟的发送队列，然后读取事务日志，将其进行压缩，然后发送给 mirror 节点，mirror 节点接收到以后，会将其写入本地在磁盘上的一个重做队列文件中，然后再通过另外的一个线程异步的方式，从重做队列中获取事务日志，然后分发给应用线程（process unit）进行回放。

数据镜像的两种模式
　　同步镜像操作：在事务传送中，主服务器必须等待镜像服务器返回成功接收日志的消息后，主服务器才继续下一事务日志到磁盘的写入与提交到镜像服务器。这种镜像不会造成数据丢失，但是存在镜像操作的事务延迟。
　　异步镜像操作：在事务传送中，主服务器不等待镜像服务器返回日志的接收情况，继续写下一事务日志到物理磁盘并提交给镜像服务器，这种镜像操作性能较高，但是主服务器宕机后可能造成镜像服务器数据丢失。

1、搭建mirror，必须先对主库进行全备和日志备份，并且要以norecovery方式把全备和日志恢复到从库，之后再在主库右键数据库–>properties–>mirror，参考图形界面一步步来搭建，如下是主库搭建mirror的报错,因为从库恢复的时候只恢复了数据库没有恢复日志导致
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.
2、图形界面搭建镜像过程中，跳出的端点名称会自动创建好，不需要手工先去创建，跳出的端点名称和端口可以自己定义，默认的是Mirroring和5022
3、搭建好后，监控mirror的工具，右键数据库–>Tasks–>Launch Database Mirroring Monitor,这个工具主库从库都有，效果一样，这点和log shipping的Transaction Log Shipping Status主库只负责主库，从库只负责从库不一样。
4、搭建好后，主库后面状态显示(Principal,Synchronized)，从库后面状态显示(Mirror,Synchronized/Restoring…)
5、搭建好后，主库和从库都有一个job名字是“Database Mirroring Monitor Job”，就算拆掉数据库的mirror，主从上该job还在，新增一个数据库的mirror，主备还是该job，没新增job，该job删除后，就算还有数据库的mirror，该job也不会自动重建，但是下次新增数据库的mirror时，该job会重建
6、因为从库的镜像数据库无法读，所以可以在从库创建快照数据库来读，来确定mirror是否真正的同步
7、如果想实现主从自动切换，即自动故障转移功能，必须要有见证服务器
8、如果主库故障了，从库状态变成(Mirror,Disconnected/In Recovery)，执行如下语句，恢复从库的读写状态(必须先执行第二条语句删除从库的快照，否则第三条语句无法执行)
ALTER DATABASE testdb SET PARTNER OFF;
drop database testdb_snapshot;
RESTORE DATABASE testdb WITH RECOVERY;
9、如果主库执行了移除remove镜像操作后需要删除从库再重新搭建主库从库的镜像，但是从库仍然显示(Mirror,Disconnected/In Recovery)，导致从库无法删除，且从库执行ALTER DATABASE testdb SET PARTNER OFF后状态仍是(Mirror,Disconnected/In Recovery)，则需要先在主库配置一下镜像，然后会报错镜像搭建不成功，这个时候从库状态显示(In Recovery),从库这时候可以直接删除。如果从库还是无法删除，就先在主库配置一下镜像，然后会报错镜像搭建不成功，再重启从库实例，从库状态一般会显示为(suspect),这时从库也可以直接删除了。
10、手动故障转移需要将事务安全设置为 FULL，且当伙伴连接在一起并且数据库已同步时即数据库处于 SYNCHRONIZED 状态时，才支持手动故障转移，登录主库执行如下语句，上面8非手动故障转移，因为数据库不是同步状态而是Disconnected/In Recovery
USE master;
ALTER DATABASE testdb SET PARTNER FAILOVER;
11、如果从库使用主库的全备和日志备份进行restore norecovery后，开始搭建mirror，但是这个过程中，mirror还没有搭建好，主库又备份了日志，mirror无法成功搭建，会有如下报错，只能把主库备份的日志再restore norecovery到从库，才可以正常搭建mirror
The remote copy of database has not been rolled forward to a point in time that is encompassed in the local copy of the database log
12、从库无法直接delete删除，这点和logshipping不一样
13、镜像故障查找，可以从主库和从库的日志中找相关信息
14、mirror数据库升级后，无法修改数据库版本COMPATIBILITY_LEVEL，因为mirror是只读的，暂停mirror也无法修改，因为数据库是restoring状态
15、mirror相关信息，可以参考视图sys.database_mirroring
16、mirror的从库，不能执行backup
17、主库升级后可以修改level，从库升级后无法修改level，如果主库修改了level一旦主库的日志同步到从库后，从库对应的数据库的level会和主库一样
18、主库新增datafile后，从库也会新增datafile，并且路径和主库的路径一样
19、mirror主库增加了文件，但是从库没有相应的目录，则同步会suspend挂起，就算从库有默认的datafile和logfile路径。在从库的数据库日志里面可以看到报错信息:CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘E:\XX\YY.ndf’.
20、FILETABLE的数据库无法搭建mirror，会报错A database cannot be enabled for both Database Mirroring and FILESTREAM or for both Database Mirroring and MEMORY_OPTIMIZED_DATA storage
21、搭建mirror时遇到一个怪异报错Database ‘mirror_1’ cannot be opened. It is in the middle of a restore。最后发现是因为数据库实例的版本是2016，而搭建mirror时使用的SSMS版本是2017，把SSMS换成2016就没有再报这个错误了，这个算是sqlserver的bug了
22、在从库创建镜像数据库mirror1的快照数据库mirror_snapshot，NAME必须等于等于主库里面的数据文件相同的逻辑名称，filename自己随意定义，指快照数据库的filename
create database mirror_snapshot on
(NAME=mirror1,filename=‘G:\DEFAULT.DATA\mirror_snapshot’)
as snapshot of mirror1;
23、监控mirror数据库Db1最近的同步情况，可以参考如下语句(2表示最后4小时的行，如果把2改成1表示最后2小时的行)
USE msdb;
EXEC sp_dbmmonitorresults Db1,2, 0;
24、主库的状态一直是(Principal,Suspend),右键主库–属性–Mirroring–Resume后，主库的状态是(Principal,Synchronizing),过一会主库状态还是(Principal,Suspend),查看从库实例的日志有如下内容
The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.
解决方法：右键主库–属性–Mirroring–Remove Mirroring，从库状态变成(Mirror,Disconnected/In Recovery),再在主库创建Mirror，这时会报错，从库状态变成(restoring),这个时候，把主库备份的日志拿到从库去手工restore，所有日志都restore完了后，再在主库创建Mirror就正常了内存。
25、8核CPU，数据库最大内存16GB环境，发现Database1在15分钟内产生的日志达到500MB以上时，mirror很容易出现suspend的状态，查看日志发现信息如下，就算你重启数据库，还是解决不了这个问题，只能移除mirror，在从库上再手工restore这些日志，再搭建mirror
The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.
Database mirroring will be suspended. Server instance ‘Instance1’ encountered error 1204, state 4, severity 19 when it was acting as a mirroring partner for database ‘Database1’. The database mirroring partners might try to recover automatically from the error and resume the mirroring session. For more information, view the error log for additional error messages.
26、关于mirror和logshipping的选择，遇到数据库在短时间内产生的日志很大，比如15分钟内产生了500MB，那么mirror不如logshipping，因为mirror需要消耗更多的内存，mirror很容易出现suspend的状态
27、搭建mirror时遇到错误：服务器网络地址 “TCP://dbalias:5022″ 无法访问或不存在。请检查网络地址名称，并检查本地和远程端点的端口是否正常运行。 (Microsoft SQL Server，错误: 1418)。
解决思路
27.1、telnet dbalias:5022 是否通
27.2、检查主备机器的sqlserver启动账号是否有权限访问对方实例
27.3、以上两点都正常的情况下，重启备机
27.4、以上步骤3也做过还是不行的话，重启主机和备机的mirror endpoint端点，ALTER ENDPOINT endpoint_name STATE = STOPPED；ALTER ENDPOINT endpoint_name STATE = STARTED
27.5、以上步骤4也做过还是不行的话，重启主机(自己就遇到一个这样的问题，直到这第五步做完才能正常搭建mirror)
28、监控mirror同步更新状态可以结合使用存储过程msdb.sys.sp_dbmmonitorupdate和系统表msdb.dbo.dbm_monitor_data
29、在一台服务器上的实例搭建mirror报错：The Database Mirroring endpoint cannot listen on port 5022 because it is in use by another process
解决思路
29.1、查看发现该主机和备机服务器上都有两个实例1、2，通过以下语句查询到主备机服务器的实例1已经使用了5022，主机服务器实例2使用了5023，备机服务器实例2还是使用5022，所以报错
select * from sys.endpoints where type_desc=‘DATABASE_MIRRORING’
select name,port from sys.tcp_endpoints where type_desc=‘DATABASE_MIRRORING’
29.2、对备机服务器实例2修改endpoint端点的端口5022为5023，通过以下语句修改，sys.tcp_endpoints.name表示查询sys.tcp_endpoints得出的name结果
ALTER ENDPOINT sys.tcp_endpoints.name AS TCP (LISTENER_PORT=5023) FOR DATABASE_MIRRORING()
30、mirror主备通过遇到问题而发生中断supsend，执行resume后还是不行，可以重启端点再resume
30.1主备顺序执行以下语句试试
ALTER ENDPOINT sys.tcp_endpoints.name STATE = STOPPED
ALTER ENDPOINT sys.tcp_endpoints.name STATE = STARTED
30.2主节点执行以下语句
ALTER DATABASE DBNAME SET PARTNER RESUME
31、mirror主从环境，遇到过主库sql service服务器重启，重启是因为遇到大事务在回滚导致这个数据库一些表被锁死，引发一堆的堵塞，想通过重启来加快回滚，重启后主库的某个数据库一直处于recovery状态，mirror对应的从库也是recovery not sysncring状态，重启后10几个小时都还是这种状态的情况，尝试把该库的mirror移除，主库马上就恢复到了open状态。所以个人得出结论：mirror情况下，如果主库在recovery，那么这些recovery在主库完成了并完全同步到了从库，这个时候主库才会从recovery状态恢复到open状态，所以遇到主库资源足，从库资源不足，假设主库本身recovery需要1小时，这些recovery的更改完全同步到从库需要2小时，那么mirror情况下主库recovery的完成就需要1+2=3小时。
32、查询mirror镜像信息包含主从服务器名称，数据库的同步模式和同步状态
select mirroring_partner_name,mirroring_partner_instance,mirroring_witness_name,db_name(database_id) db_name,mirroring_role_desc,mirroring_safety_level_desc,mirroring_state_desc from sys.database_mirroring
33、如果mirror的模式选择High safety without automatic failover(synchronous)，那么一些大事务期间如果遇到主从节点资源瓶颈(网络不稳定、从节点io压力大，从节点内存不够）等，主从节点之间很容易出现DISCONNECTED的故障，把模式修改为High performance(asynchronous)会大大减少此类故障，也可以增加mirroring_connection_timeout(ALTER DATABASE DBName SET PARTNER TIMEOUT 30，参见数据字典sys.database_mirroring).