SQL SERVER 2005镜像配置文档

SQL SERVER 2005镜像配置文档

Contents

介绍 (1)

方法 (2)

配置步骤（证书方式） (3)

1、为主体数据库配置出站连接 (3)

2、为镜像数据库配置出站连接 (4)

3、为见证数据库配置出站连接 (5)

4、为主体数据库配置入站连接 (6)

5、为镜像数据库配置入站连接 (7)

6、为见证数据库配置入站连接 (8)

7、主机备份数据库 (9)

8、镜像机还原数据库 (9)

9、为镜像数据库配置镜像伙伴 (9)

10、为主体数据库配置镜像伙伴和见证服务器 (9)

11、配置数据库镜像事务安全级别 (10)

维护 (10)

1、查看数据库镜像状态 (10)

2、测试故障转移 (10)

常见错误 (11)

介绍

SQL SERVER 2005镜像基于日志同步，可良好实现故障转移。每个数据库镜像配置均包含一个主体服务器(包含主体数据库)、一个镜像服务器(包含镜像数据库)和一个见证服务器（可选）。

主体服务器和镜像服务器要求是独立的服务器实例。

主体服务器和镜像服务器的角色是相对的，可以自动或者手动地将主体服务器与镜像服务器的角色互换。

与主体服务器和镜像服务器不同的是，见证服务器并不能用于数据库。见证服务器监视主体服务器和镜像服务器，确保在给定的时间内这两个故障转移服务器中有且只有一个作为主体服务器，从而支持自动故障转移。

如果存在见证服务器，同步会话将以“高可用性模式”运行，如果主体服务器出现故障，可以实现故障自动转移。

如果见证服务器不存在，同步会话将以“高级别保护模式”运行，出现故障需要手动故障转移，并且有可能丢失数据。

基本模式：

同步：已提交的事务将在伙伴双方上提交

异步：事务不需要等待镜像服务器将日志写入磁盘便可提交

镜像运行模式：

高安全性模式

支持同步操作。在高安全性模式下，当会话开始时，镜像服务器将使镜像数据库尽快与主体数据库同步。在同步数据库之后，已提交的事务将在伙伴双方上提交，但会延长事务滞后时间。具有自动故障转移功能的高安全性模式要求使用第三个服务器实例，称为“见证服务器”。与这两个伙伴不同的是，见证服务器并不能用于数据库。见证服务器通过验证主体服务器是否已启用并运行来仅支持自动故障转移。只有在镜像服务器和见证服务器与主体服务器断开连接之后而保持相互连接时，镜像服务器才启动自动故障转移。

高性能模式

异步运行。将事务安全设置为 OFF 时，数据库镜像会话便会异步运行。镜像服务器尝试与主体服务器发送的日志记录保持同步。虽然镜像数据库可能稍微滞后于主体数据库，但这两个数据库之间的时间间隔通常很小。但是，如果主体服务器的工作负荷过高或镜像服务器系统的负荷过高，则时间间隔会增大。

方法

1.单机多实例

适用于单台机器的多个实例间做数据同步测试

2.同网段（域环境）

适用于域环境中，相对证书方式，配置相对简单，省去了建立密钥、证书及多次为用户授权的过程

3.同网段（非域）：证书方式

配置相对较繁琐，基本步骤：

1.各服务器生成主密钥(MASTER KEY)

2.各服务器生成服务证书(CERTIFICATE)

3.使用服务证书生成端点(ENDPOINT)

4.将证书备份为文件，并复制到其他服务器

5.各服务器对其他服务器生成访问授权

1) 生成登陆

2) 生成用户

3) 绑定证书到用户

4) 授予相关用户对ENDPOINT 的连接访问权

6.主机上备份数据库（最好加上日志备份）

7.镜像机上还原数据库（norecovery）

8.为镜像机配置镜像伙伴

9.为主机配置镜像伙伴和见证服务器

配置步骤（证书方式）

1、为主体数据库配置出站连接

--创建数据库主密钥

USE master;

GO

--DROP MASTER KEY

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '111111';

GO

--如果以前设置过，通过以下语句修改master key

--ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = '111111';

--在MIR-A上为数据库实例创建证书

CREATE CERTIFICATE MIR_A_cert

WITH SUBJECT = 'MIR_A certificate',

START_DATE = '2009-12-01',

EXPIRY_DATE = '2099-12-31'

GO

--在MIR-A上使用上面创建的证书为数据库实例创建镜像端点

CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

AS TCP (

LISTENER_PORT=5024

, LISTENER_IP = ALL

)

FOR DATABASE_MIRRORING (

AUTHENTICATION = CERTIFICATE MIR_A_cert

, ENCRYPTION = REQUIRED ALGORITHM RC4

, ROLE = ALL

);

GO

--备份MIR-A上的证书并将其复制到MIR-B（镜像机），MIR-W(见证机) BACKUP CERTIFICATE MIR_A_cert TO FILE = 'E:\yangjj\MIR_A_cert.cer';

GO

2、为镜像数据库配置出站连接

--创建数据库主密钥

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '111111';

GO

--在MIR-B上为数据库实例创建证书

CREATE CERTIFICATE MIR_B_cert

WITH SUBJECT = 'MIR_B certificate for database mirroring',

START_DATE = '2009-12-01',

EXPIRY_DATE = '2099-12-31';

GO

--在MIR-B上使用上面创建的证书为数据库实例创建镜像端点

CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

AS TCP (

LISTENER_PORT=5024,

LISTENER_IP = ALL

)

FOR DATABASE_MIRRORING (

AUTHENTICATION = CERTIFICATE MIR_B_cert,

ENCRYPTION = REQUIRED ALGORITHM RC4,

ROLE = ALL

);

GO

--备份MIR-B上的证书并复制到MIR-A（主机），MIR-W（见证机）

BACKUP CERTIFICATE MIR_B_cert TO FILE = 'E:\yangjj\MIR_B_cert.cer'; GO

3、为见证数据库配置出站连接

--创建数据库主密钥

USE master;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '111111';

GO

--在MIR-W上为数据库实例创建证书

CREATE CERTIFICATE MIR_W_cert

WITH SUBJECT = 'MIR_W certificate for database mirroring',

START_DATE = '2009-12-01',

EXPIRY_DATE = '2099-12-31';

GO

--在MIR-W上使用上面创建的证书为数据库实例创建镜像端点

CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

AS TCP (

LISTENER_PORT=5024,

LISTENER_IP = ALL

)

FOR DATABASE_MIRRORING (

AUTHENTICATION = CERTIFICATE MIR_W_cert,

ENCRYPTION = REQUIRED ALGORITHM RC4,

ROLE = ALL

);

GO

--备份MIR-W上的证书并拷贝到MIR-A（主机），MIR-B（镜像机）

BACKUP CERTIFICATE MIR_W_cert TO FILE = 'E:\yangjj\MIR_W_cert.cer'; GO

4、为主体数据库配置入站连接

--在MIR-A上为MIR-B创建登陆

USE master;

GO

CREATE LOGIN MIR_B_login WITH PASSWORD = '111111';

GO

--为以上登陆创建一个用户

CREATE USER MIR_B_user FOR LOGIN MIR_B_login;

GO

--绑定证书到用户

CREATE CERTIFICATE MIR_B_cert

AUTHORIZATION MIR_B_user

FROM FILE = 'E:\yangjj\MIR_B_cert.cer'

GO

--在镜像端点上为登陆赋予CONNECT权限

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [MIR_B_login];

GO

--在MIR_A上为MIR_W创建登陆

USE master;

GO

CREATE LOGIN MIR_W_login WITH PASSWORD = '111111';

GO

--为以上登陆创建一个用户

CREATE USER MIR_W_user FOR LOGIN MIR_W_login;

GO

--绑定证书到用户

CREATE CERTIFICATE MIR_W_cert

AUTHORIZATION MIR_W_user

FROM FILE = 'E:\yangjj\MIR_W_cert.cer'

GO

--在镜像端点上为登陆赋予CONNECT权限

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [MIR_W_login];

GO

5、为镜像数据库配置入站连接

--在MIR-B上为MIR-A创建登陆

USE master;

GO

CREATE LOGIN MIR_A_login WITH PASSWORD = '111111';

GO

--为以上登陆创建一个用户

CREATE USER MIR_A_user FOR LOGIN MIR_A_login;

GO

--绑定证书到用户

CREATE CERTIFICATE MIR_A_cert

AUTHORIZATION MIR_A_user

FROM FILE = 'E:\yangjj\MIR_A_cert.cer'

GO

--在镜像端点上为登陆赋予CONNECT权限

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [MIR_A_login]; GO

--在MIR-B上为MIR-W创建登陆

USE master;

GO

CREATE LOGIN MIR_W_login WITH PASSWORD = '111111';

GO

--为以上登陆创建一个用户

CREATE USER MIR_W_user FOR LOGIN MIR_W_login;

GO

--绑定证书到用户

CREATE CERTIFICATE MIR_W_cert

AUTHORIZATION MIR_W_user

FROM FILE = 'E:\yangjj\MIR_W_cert.cer'

GO

--在镜像端点上为登陆赋予CONNECT权限

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [MIR_W_login]; GO

6、为见证数据库配置入站连接

--在MIR-W上为MIR-A创建登陆

USE master;

GO

CREATE LOGIN MIR_A_login WITH PASSWORD = '111111';

GO

--为以上登陆创建一个用户

CREATE USER MIR_A_user FOR LOGIN MIR_A_login;

GO

--绑定证书到用户

CREATE CERTIFICATE MIR_A_cert

AUTHORIZATION MIR_A_user

FROM FILE = 'E:\yangjj\MIR_A_cert.cer'

GO

--在镜像端点上为登陆赋予CONNECT权限

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [MIR_A_login]; GO

--在MIR-W上为MIR-B创建登陆

USE master;

GO

CREATE LOGIN MIR_B_login WITH PASSWORD = '111111';

GO

--为以上登陆创建一个用户

CREATE USER MIR_B_user FOR LOGIN MIR_B_login;

GO

--绑定证书到用户

CREATE CERTIFICATE MIR_B_cert

AUTHORIZATION MIR_B_user

FROM FILE = 'E:\yangjj\MIR_B_cert.cer'

GO

--在镜像端点上为登陆赋予CONNECT权限

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [MIR_B_login];

GO

7、主机备份数据库

--检查并修改Northwind数据库使其为完整恢复模式（如果已是完整恢复模式，则省略此步）

ALTER DATABASE mirrorTest SET RECOVERY FULL;

GO

--在 MIR_A 的主体服务器实例上备份数据库并将备份文件复制到MIR_B上

BACKUP DATABASE mirrorTest TO DISK = 'E:\yangjj\mirrorTest.bak' WITH FORMAT GO

8、镜像机还原数据库

--在MIR-B的镜像服务器实例上还原数据库：

RESTORE DATABASE mirrorTest FROM DISK = 'E:\yangjj\mirrorTest.bak' WITH NORECOVERY GO

9、为镜像数据库配置镜像伙伴

--在 MIR-B 的镜像服务器实例上，将 MIR-A上的服务器实例设置为伙伴

ALTER DATABASE mirrorTest SET PARTNER = 'TCP://db04:5024';

GO

10、为主体数据库配置镜像伙伴和见证服务器

--在 MIR-A 的主体服务器实例上，将MIR-B上的服务器实例设置为伙伴

ALTER DATABASE mirrorTest SET PARTNER = 'TCP://db04:5024';

GO

--在 MIR-A 的主体服务器实例上，将 MIR-W 上的服务器实例设置为见证服务器ALTER DATABASE mirrorTest SET WITNESS = 'TCP://db03:5024';

GO

11、配置数据库镜像事务安全级别

ALTER DATABASE mirrorTest SET SAFETY FULL

GO

维护

1、查看数据库镜像状态

1.)通过Management studio 对象资源管理器，查看主体数据库、镜像数据库状态

2.)通过Management studio 对象资源管理器中的数据库属性查看状态

3.)通过系统目录视图查看数据库镜像配置情况

USE MASTER

GO

SELECT * FROM sys.database_mirroring_endpoints;

GO

SELECT * FROM sys.database_mirroring WHERE database_id =

(SELECT database_id FROM sys.databases WHERE name = 'Northwind') GO

2、测试故障转移

1.主备互换

--主机执行：

ALTER DATABASE mirrorTest SET PARTNER FAILOVER

--主服务器Down掉,备机紧急启动并且开始服务

ALTER DATABASE mirrorTest SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

--原来的主服务器恢复,可以继续工作,需要重新设定镜像

--备机执行：

USE master

--恢复镜像

ALTER DATABASE mirrorTest SET PARTNER RESUME

--切换主备

ALTER DATABASE mirrorTest SET PARTNER FAILOVER;

--停用镜像

ALTER DATABASE mirrorTest SET PARTNER OFF

--停用见证机

ALTER DATABASE mirrorTest SET WITNESS OFF

3.生成删除镜像相关对象脚本

--删除镜像端点

SELECT 'DROP ENDPOINT ' + name

FROM sys.database_mirroring_endpoints

WHERE type = 4

--删除证书

SELECT 'DROP CERTIFICATE ' + name

FROM sys.certificates

WHERE name not like '##%'

--删除用户

SELECT ' DROP USER ' + name FROM sys.sysusers WHERE name LIKE'mir%'

--删除登录

SELECT ' DROP LOGIN ' + name FROM sys.syslogins WHERE name LIKE'mir%'

--删除主密钥

if exists(SELECT * from sys.symmetric_keys where name= '##MS_DatabaseMasterKey##' ) DROP master key

常见错误

域名不正确，连接失败

主数据库，镜像，见证服务器最好都用域名，虽然有时用netbios名或ip也可以，但不稳定，服务器重启后有时镜像起不来。如果服务器都已加入域，直接填写域名作为路径基本都不会有问题；

不加入域的情况下需要编辑hosts文件。

身份认证不正确，连接失败

域环境下注意用域帐号启动SQL SERVER 服务；

非域环境下只能使用证书方式，配置步骤繁琐，注意SQL语句中用户名，证书名正确。

镜像库恢复不符合要求，开始同步时发生错误

如开始同步后报错：

The mirror database, "UserDefinedDatabase", 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. (Microsoft SQL Server, Error: 1478)

解决办法：可从主机增加事务日志备份，到镜像机恢复

---------------------------------------------------------------------------------------------------------------------- 补充说明：两台数据库做镜像，不管是哪台数据库出了问题，另外一台数据库都可以保证正常对外提供服务。