预备知识: 数据库镜像基本概念
--------一 准备工作----------
-------(一) 安装三个实例,分别为:-----
SZDXBAK\SQLSERVER(默认实例), 作为主体服务器
SZDXBAK\SQL2 作为镜像服务器
SZDXBAK\WITNESS 作为鉴证服务器
注: 所有实例服务使用SQLService账户启动
------(二) 数据库准备-------
--1 在主体服务上创建测试数据库 RecoveryDb
并创建一个完全备份
--2 在镜像服务上使用NORECOVERY选项恢复数据库,
恢复后数据库状态为'正在还原'
----- 二 创建数据库镜像端点----
--1 在主体数据库(SQLSERVER实例)上创建端点,用于伙伴通讯
CREATE ENDPOINT DbMirroring
STATE=STARTED
AS TCP(LISTENER_PORT=5022)
FOR DATABASE_MIRRORING(ROLE=PARTNER,ENCRYPTION=SUPPORTED)
go
--2 在镜像数据库(SQL2实例)上创建端点,用于伙伴通讯
CREATE ENDPOINT DbMirroring
STATE=STARTED
AS TCP(LISTENER_PORT=5033)
FOR DATABASE_MIRRORING(ROLE=PARTNER,ENCRYPTION=SUPPORTED)
go
--3 在鉴证服务器(WITNESS实例)上创建端点,用于鉴证通讯
CREATE ENDPOINT DbMirroring
STATE=STARTED
AS TCP(LISTENER_PORT=5022)
FOR DATABASE_MIRRORING(ROLE=WITNESS,ENCRYPTION=SUPPORTED)
go
ALTER ENDPOINT DbMirroring STATE=STARTED
go
--4 检查端点配置,可分别在不同服务器上运行以下查询
SELECT * FROM sys.database_mirroring_endpoints
----- 三 配置数据库镜像安全性--------
--1 主体数据库(SQLSERVER实例)
use master
go
GRANT CONNECT ON ENDPOINT::"DbMirroring" TO "SZDXBAK\SQLService"
go
--2 镜像数据库(SQL2实例)
use master
go
GRANT CONNECT ON ENDPOINT::"DbMirroring" TO "SZDXBAK\SQLService"
go
--3 鉴证服务器(WITNESS实例)
use master
go
GRANT CONNECT ON ENDPOINT::"DbMirroring" TO "SZDXBAK\SQLService"
go
----- -四 启动数据库镜像-----
--注意配置顺序
--1 在镜像数据库(SQL2实例)上,指定到主体服务器(SQLSERVER实例)的伙伴端点
ALTER DATABASE RecoveryDb
SET PARTNER=N'TCP://SZDXBAK:5022'
go
--2 在主数据库(SQLSERVER实例)上,指定到镜像服务器(SQL2实例)的伙伴端点
ALTER DATABASE RecoveryDb
SET PARTNER=N'TCP://SZDXBAK:5033'
go
--注: 以上两步完成后,已经配置为高保护级别性模式
--3 如果要创建高可用性模式:在主数据库上(SQLSERVER实例),指定鉴证服务器端点
ALTER DATABASE RecoveryDb
SET WITNESS=N'TCP://SZDXBAK:5044
go
--4 在高可用性模式下:配置数据库镜像事务安全级别
ALTER DATABASE RecoveryDb SET SAFETY FULL
go
--- 五 查看数据库镜像的配置状态
--1 通过Management Studio 对象资源管理器,查看主体数据库,镜像数据库状态
--2 通过Management Studio 对象资源管理器中的数据库属性查看状态
--3 通过系统目录视图查看数据库镜像配置情况
SELECT * FROM sys.database_mirroring_endpoints
SELECT * FROM sys.database_mirroring_endpoints
WHERE database_id = (SELECT database_id FROM sys.database
WHERE name='RecoveryDb')
以上配置完成后,主体数据库的状态为:主体,已同步
镜像数据库的状态为:镜像,已同步/正在还原..
--- 六 服务器角色切换测试----
预备知识
数据库镜像角色切换
自动故障转移
只针对高可用性模式
SAFETY=FULL
手动故障转移
针对高可用性模式和高保护级别性模式
SAFETY=FULL
强制服务
只针对高性能模式
SAFETY=OFF
---(一) 自动故障转移
在主体服务器上,断开网络连接,通过数据库资源管理器查看主体/镜像服务器上数据库的状态
镜像服务器状态: 主体,已断开连接
恢复主体服务器
此时原来主体服务器(SQLSERVER实例)变为镜像服务器状态为:镜像,已同步/正在还原..
原来镜像服务器(SQL2实例)变为主体服务器状态:主体,已同步
---(二) 手动故障转移
-- 主体服务器上执行故障转移过程
USE MASTER
go
ALTER DATABASE RecoveryDb SET PARTNER FAILOVER
go
执行完后主体服务器状态变为: 镜像,已同步/正在还原..
镜像服务器状态变为 主体,已同步
---(三) 强制服务,有可能造成数据丢失
--1) 在镜像服务器上,取消对鉴证服务器的配置
ALTER DATABASE RecoveryDb SET WINTESS OFF
--2) 在镜像服务器上,配置事务安全性为OFF
ALTER DATABASE RecoveryDb SET SAFETY OFF
--3) 断开主体服务器,在镜像服务器上,强制服务 进行角色切换
ALTER DATABASE RecoveryDb SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
完成后数据库状态为 主体,已断开连接
-----七 如何查询镜像服务器上的数据: 在镜像服务器上创建数据库快照
--1 在镜像服务器上,创建数据库快照
CREATE DATABASE ReconveryDb_snap_1
ON (NAME=RecoveryDb,FILENAM='H:\seconddb\recovery_snap1.ss')
AS SNAPSHOT OF RecoveryDb
--2 访问镜像数据库
USE ReconveryDb_snap_1
go
SELECT * FROM test
注：
1） 做镜像时，必须保证主体和镜像数据库的日志一致性，如果在镜像服务器使用NORECOVERY选项恢复数据库时，主体数据库日志有更新，需要在主体数据库备份日志然后在镜像服务器上使用NORECOVERY选项恢复主体服务器更新的日志，否则在主体数据库上指定到镜像服务器的伙伴端点时会报错
2） 镜像完成后，对主体数据库新建/修改/删除表，镜像服务器将同步更新
3） 镜像完成后，对主体数据库执行收缩文件，镜像服务器将同步更新
