MsSQL 2005 数据库镜像的搭建与维护

数据库镜像大大提高了可用性,并为故障转移群集或日志传送提供了一种易于管理的替代方案或补充方案。同步数据库镜像会话时,数据库镜像提供了热备用服务器,可支持在已提交事务不丢失数据的情况下进行快速故障转移。在一般的镜像会话期间,如果生产服务器出现故障,客户端应用程序可以通过重新连接到备用服务器来快速进行恢复。

一、准备工作

(一) 安装三个实例,分别为:
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

八、镜像数据库之间的用户问题
假设有镜像的服务器是MIR-A和MI-B,要访问镜像数据库的登录账号是tom,使用下面这个方法就可以在MIR-A上创建登录名tom,然后在镜像数据库中创建数据库用户tom并映射到登录名tom
执行下列语句取回tom的sid

select [name], sid from syslogins where [name] = 'tom'

—————————————————–
tom 0xD6AABCC8F83E3243A6C3C97F28A4CB55

然后在MIR-B上用下面的语句创建登录名tom

sp_addlogin @loginame = 'tom', @passwd = 'password', @sid = 0xD6AABCC8F83E3243A6C3C97F28A4CB55