SQL Server 镜像数据库搭建

SQL Server镜像

“数据库镜像”是一种提高 SQL Server 数据库的可用性的解决方案。 镜像基于每个数据库实现，并且只适用于使用完整恢复模式的数据库。类似于Oracle的DG，也有几种运行模式，根据性能/安全的不同要求，选择相应的运行模式。下面会有详细说明：

注意：这种高可用架构，目前微软官方已经不推荐使用了。并且会在未来某个版本中将其移除，不再支持。所以对于新环境来说，推荐使用AlwaysOn。

具有下列优点：

提高数据库的可用性。

发生灾难时，在具有自动故障转移功能的高安全性模式下，自动故障转移可快速使数据库的备用副本联机（而不会丢失数据）。 在其他运行模式下，数据库管理员可以选择强制服务（可能丢失数据），以替代数据库的备用副本。 有关详细信息，请参阅本主题后面的角色切换。

增强数据保护功能。

数据库镜像提供完整或接近完整的数据冗余，具体取决于运行模式是高安全性还是高性能。 有关详细信息，请参阅本主题后面的运行模式。

在 SQL Server Enterprise 或更高版本上运行的数据库镜像伙伴会自动尝试解决某些阻止读取数据页的错误。 无法读取页的伙伴会向其他伙伴请求新副本。 如果此请求成功，则将以新副本替换不可读的页，这通常会解决该错误。有关详细信息，请参阅自动页修复（可用性组/数据库镜像）。

提高生产数据库在升级期间的可用性。

为了尽量减少镜像服务器的停机时间，您可以按顺序升级承载故障转移伙伴的 SQL Server 实例。 这样只会导致一个故障转移的停机时间。 这种形式的升级称为“滚动升级”。有关详细信息，请参阅在系统上安装 Service Pack 并且尽量缩短镜像数据库停机时间。

前提条件：

相同版本的数据库

数据库必须使用完整恢复模式

如有可能，镜像数据库的路径（包括驱动器号）应该与主体数据库的路径相同。

镜像会话中的所有服务器实例都应该使用相同的主代码页和排序规则。

缺点/限制：

只能镜像用户数据库。 不能镜像master、msdb、tempdb或model数据库。镜像的数据库在数据库镜像会话过程中不能重命名。数据库镜像不支持 FILESTREAM。 不能在主体服务器上创建 FILESTREAM 文件组。 不能为包含 FILESTREAM 文件组的数据库配置数据库镜像。在 32 位系统上，由于受每个数据库镜像会话所占用的工作线程数限制，对于每个服务器实例，数据库镜像最多支持 10 个数据库。跨数据库事务和分布式事务均不支持数据库镜像。 有关详细信息，请参阅数据库镜像或 AlwaysOn 可用性组不支持跨数据库事务 (SQL Server)。

镜像运行模式

高性能模式 (High-performance mode)

数据库镜像会话异步运行并仅使用主体服务器和镜像服务器。 唯一的角色切换形式是强制服务（可能造成数据丢失）。高安全性模式 (High-safety mode)

数据库镜像会话同步运行并可以选择使用见证服务器、主体服务器和镜像服务器。事务安全 (Transaction safety)

一种镜像特定的数据库属性，用于确定数据库镜像会话是同步运行还是异步运行。 有两种安全级别：FULL 和 OFF。见证服务器 (Witness)

仅用于高安全性模式，SQL Server 的一个可选实例，它能使镜像服务器识别是否要启动自动故障转移。 与这两个故障转移伙伴不同的是，见证服务器并不能用于数据库。 见证服务器的唯一角色是支持自动故障转移。

搭建镜像数据库

1.数据库安装

这个安装过程就不赘述了，测试环境一直下一步就行了！

2.配置主数据库和镜像数据库的身份验证

有两种身份验证方式：

使用 Windows 身份验证使用证书

绝大部分情况下，都是使用证书来验证身份，下面演示如果通过证书来验证身份

2.1主数据库出站配置

创建主密钥

USE master;CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Admin@123';GO

创建加密证书

USE master;CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = '主库用于镜像', START_DATE = '19990101', EXPIRY_DATE = '99991231';GO

查看证书信息

USE master;SELECT * FROM sys.certificates;

创建镜像断点

CREATE ENDPOINT EDP_MirrorSTATE = STARTED AS TCP(LISTENER_PORT = 5022, -- 镜像端点使用的通信端口LISTENER_IP = ALL) -- 侦听的IP地址FOR DATABASE_MIRRORING(AUTHENTICATION = CERTIFICATE HOST_A_cert, -- 证书身份验证,和上面的证书名保持一致ENCRYPTION = DISABLED, -- 不对传输的数据加密,如果需要加密,可以配置为 SUPPORTED 或 REQUIRED, 并可选择加密算法ROLE = ALL)

备份证书

目的是拷贝到镜像服务器上，备库可以通过这个证书登录主数据库，然后将备份的证书复制到镜像服务器上。

BACKUP CERTIFICATE HOST_A_cert TO FILE = 'C:\HOST_A_cert.cer';GO

2.2镜像数据库出战配置

步骤和主库配置一样，重复以上步骤即可。

创建主密钥

USE master;CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Admin@123';GO

创建加密证书

USE master;CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = '用于镜像', START_DATE = '19990101', EXPIRY_DATE = '99991231';GO

查看证书信息

USE master;SELECT * FROM sys.certificates;

创建镜像断点

CREATE ENDPOINT EDP_MirrorSTATE = STARTED AS TCP(LISTENER_PORT = 5022, -- 镜像端点使用的通信端口LISTENER_IP = ALL) -- 侦听的IP地址FOR DATABASE_MIRRORING(AUTHENTICATION = CERTIFICATE HOST_B_cert, -- 证书身份验证,和上面的证书名保持一致ENCRYPTION = DISABLED, -- 不对传输的数据加密,如果需要加密,可以配置为 SUPPORTED 或 REQUIRED, 并可选择加密算法ROLE = ALL)

备份证书

目的是拷贝到镜像服务器上，备库可以通过这个证书登录主数据库，然后将备份的证书复制到镜像服务器上。

BACKUP CERTIFICATE HOST_B_cert TO FILE = 'C:\HOST_B_cert.cer';GO

2.3主库入站配置

创建登录名

USE master;CREATE LOGIN mirroradmin WITH PASSWORD = 'Admin!@#';GO

创建使用该登录名的用户

USE master;CREATE USER mirror_user FOR LOGIN mirroradmin;GO

将镜像库的证书和该用户关联

USE master;CREATE CERTIFICATE mirror_certAUTHORIZATION mirror_userFROM FILE = 'C:\HOST_B_cert.cer'GO

授予对远程镜像端点的登录名的 CONNECT 权限。

mirroradmin：登录名

EDP_Mirror：是断点

USE master;GRANT CONNECT ON ENDPOINT::EDP_Mirror TO [mirroradmin];GO

2.4 镜像库入站配置

创建登录名

USE master;CREATE LOGIN masteradmin WITH PASSWORD = 'Admin!@#';GO

创建使用该登录名的用户

USE master;CREATE USER master_user FOR LOGIN masteradmin;GO

将镜像库的证书和该用户关联

USE master;CREATE CERTIFICATE master_certAUTHORIZATION master_userFROM FILE = 'C:\HOST_A_cert.cer'GO

授予对远程镜像端点的登录名的 CONNECT 权限。

masteradmin：登录名

EDP_Mirror：是断点

USE master;GRANT CONNECT ON ENDPOINT::EDP_Mirror TO [masteradmin];GO

3.镜像库的配置

设置镜像关系，并将主库数据恢复到镜像库。

3.1 配置新的镜像库

将数据库设置为完全恢复模式

USE master;GOALTER DATABASE ebap SET RECOVERY FULL;GO

在主库上备份需要镜像的数据库

BACKUP DATABASE ebap TO DISK = 'C:\ebap.bak' WITH FORMATGO

将备份文件拷贝到镜像服务器上

在镜像数据库上恢复数据

如果和主数据库数据文件路径相同

RESTORE DATABASE ebap FROM DISK = 'C:\ebap.bak' WITH NORECOVERYGO

如果和主数据库数据文件路径不同：数据文件名要查看主库的文件名

RESTORE DATABASE ebapFROM DISK='C:\ebap.bak'WITH NORECOVERY, MOVE 'ebap' TO 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLSTANDBY\MSSQL\DATA\ebap.mdf', MOVE 'ebap_log' TO'C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLSTANDBY\MSSQL\DATA\ebap_log.ldf';GO

【必须】创建日志备份

BACKUP LOG ebap TO DISK = 'C:\ebap_log.bak' GO

镜像服务器应用日志

RESTORE LOG ebap FROM DISK = 'C:\ebap_log.bak' WITH FILE=1, NORECOVERYGO

如果还有其他日志备份，则一次恢复即可，如：

RESTORE LOG ebap FROM DISK = 'C:\ebap.bak' WITH FILE=2, NORECOVERYGO

3.2镜像服务器的实例上，将主库设置为镜像关系

ALTER DATABASE ebap SET PARTNER = 'TCP://60.60.60.215:5022';GO

3.3在主服务器上启用镜像

默认为高安全性模式

ALTER DATABASE ebap SET PARTNER = 'TCP://60.60.60.94:5022';GO

至此基本上数据库镜像已配置完成

附录

查看镜像数据库相关信息

select * from sys.database_mirroring

查看镜像会话连接信息

select * from sys.dm_db_mirroring_connections

查看见证服务器信息

sys.database_mirroring_witnesses