SQLServer 2005数据库镜像搭建完全手册

2013年05月09日 测试工具, 测试技术, 测试综合, 软件测试 ⁄ 共 5180字 ⁄ 字号 暂无评论 ⁄ 阅读 2,377 次

本文主要介绍SQLServer搭建数据库镜像实现双备的配置方法。

一、配置主备

1、软件准备

请注意:只有SQL Server 2005 标准版、企业版和开发版才可以建立数据镜像,Express版本只能作为见证服务器。

安装操作系统时,服务器名必须不同,服务器超级账号密码保持一致;安装数据库时,sa密码保持一致

服务器上需要关闭防火墙(Windows 2008防火墙关闭位置:控制面板\系统和安全\Windows 防火墙\自定义设置)。

2、物理连接

将主服务器(建议机器名HOST_A),镜像服务器(建议机器名HOST_B),见证服务器(建议机器名HOST_C),在一个网段中互联。

二、主备实例互通

实现互通可以使用域或证书来实现,考虑实现的简单,以下选取证书的方式实现。注意:实现"主备数据库实例互通"的操作只需要做一次,例如为了将两个SQL Server 2005的实例中的5个数据库建成镜像关系,则只需要做一次以下操作就可以了;或者这样理解:每一对主备实例(不是数据库)做一次互通。

1、创建证书

注意:以下语句中的STATR_DATE必须为当前日期,请根据部署时的日期修改。

--主体服务器执行:

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = [email protected]';

CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate', START_DATE='2012-11-28';

--镜像服务执行:

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = [email protected]';

CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate', START_DATE='2012-11-28';

--见证服务器执行:

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = [email protected]';

CREATE CERTIFICATE HOST_C_cert WITH SUBJECT = 'HOST_C certificate', START_DATE='2012-11-28';

这个MASTER KEY对于每个sql server实例唯一的,如果数据库中已经创建了MASTER KEY,可以用如下的sql删除之后重新执行:DROP MASTER KEY

2、创建连接的端点

--主体服务器执行:

CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

AS

TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )

FOR

DATABASE_MIRRORING

( AUTHENTICATION = CERTIFICATE HOST_A_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = PARTNER );

ALTER ENDPOINT Endpoint_Mirroring STATE = STARTED;

--镜像服务器执行:

CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

AS

TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )

FOR

DATABASE_MIRRORING

( AUTHENTICATION = CERTIFICATE HOST_B_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = PARTNER );

ALTER ENDPOINT Endpoint_Mirroring STATE = STARTED;

 --见证服务器执行:

CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

AS

TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )

FOR

DATABASE_MIRRORING

( AUTHENTICATION = CERTIFICATE HOST_C_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = WITNESS );

ALTER ENDPOINT Endpoint_Mirroring STATE = STARTED;

3、备份证书以备建立互联

--主体服务器执行:

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

--镜像服务器执行:

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

--见证服务器执行:

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

4、互换证书

将备份到C:的证书进行互换:

HOST_A_cert.cer、HOST_C_cert.cer复制到镜像服务器的C:

HOST_B_cert.cer、HOST_C_cert.cer复制到主体服务器的C:

HOST_A_cert.cer、HOST_B_cert.cer复制到见证服务器的C:.

5、添加登陆名、用户

以下操作只能通过命令行运行,通过图形界面无法完成。

--主体服务器执行:

CREATE LOGIN HOST_B_login WITH PASSWORD = [email protected]';

CREATE USER HOST_B_user FOR LOGIN HOST_B_login;

CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'C:\HOST_B_cert.cer';

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

CREATE LOGIN HOST_C_login WITH PASSWORD = [email protected]';

CREATE USER HOST_C_user FOR LOGIN HOST_C_login;

CREATE CERTIFICATE HOST_C_cert AUTHORIZATION HOST_C_user FROM FILE = 'C:\HOST_C_cert.cer';

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

--镜像服务器执行:

CREATE LOGIN HOST_A_login WITH PASSWORD = [email protected]';

CREATE USER HOST_A_user FOR LOGIN HOST_A_login;

CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'C:\HOST_A_cert.cer';

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

CREATE LOGIN HOST_C_login WITH PASSWORD = [email protected]';

CREATE USER HOST_C_user FOR LOGIN HOST_C_login;

CREATE CERTIFICATE HOST_C_cert AUTHORIZATION HOST_C_user FROM FILE = 'C:\HOST_C_cert.cer';

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

--见证服务器执行:

CREATE LOGIN HOST_A_login WITH PASSWORD = [email protected]';

CREATE USER HOST_A_user FOR LOGIN HOST_A_login;

CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'C:\HOST_A_cert.cer';

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

CREATE LOGIN HOST_B_login WITH PASSWORD = [email protected]';

CREATE USER HOST_B_user FOR LOGIN HOST_B_login;

CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'C:\HOST_B_cert.cer';

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

三、建立镜像关系

1、准备主数据库

在主机上新建数据库时,必须将它更改为使用完整恢复模式。

如采用在ManagementStudio中新建,则在数据库属性页中,设置其恢复模式为"完整"。

具体SQL如下:

USE master;

ALTER DATABASE <DatabaeName>

SET RECOVERY FULL;

随后,对数据库进行全量备份。选择数据库后,右键单击,备份:

2、同步登录名和密码

通常来说数据库都将会有若干个用户作为访问数据库的用户,并且数据库会有相应的登录名,但是在备机中缺少与之相对应的登录名,例如iAvp平台使用'iavp'、 'nuance'作为登录名访问数据库,但是在备机中没有'iavp' 、 'nuance'这些登录名,因此一旦主备切换,业务系统就无法登录数据库了,这种情况称为"孤立用户",因此需要对这两个用户进行同步。

在主数据库中执行如下语句:

USE master;

select sid,name from syslogins;

查找出相应的用户名和sid,例如:上述的'iavp'

在备数据库中执行如下语句:

USE master;

exec sp_addlogin

@loginame = 'iavp',

@passwd = 'iavp',

@sid = sid ;

这里的sid即是上述通过SQL语句查找出的sid。

3、准备备机数据库

使用上文的全备备份文件进行还原,在还原数据的时候需要使用选上"with non recover"。如图所示:

如果执行成功数据库将会变成这个样子:

4、准备备机数据库作业

在镜像数据库上执行iavp建库脚本中的iavp.job.sql,建立数据库转储作业

5、建立镜像

假设

主体服务器IP地址:192.168.172.101

镜像服务器IP地址:192.168.172.102

见证服务器IP地址:192.168.172.103

现在为MirrorTest数据库做镜像:

--在备机中执行如下语句成功后,:

USE master;

ALTER DATABASE MirrorTest SET PARTNER = 'TCP://192.168.172.101:5022';

--再在主体上执行下述语句:

USE master;

ALTER DATABASE MirrorTest SET PARTNER = 'TCP://192.168.172.102:5022';

ALTER DATABASE MirrorTest SET witness = 'TCP://192.168.172.103:5022';

如果不成功,则分别重启三台服务器后,再重复以上过程。

如果执行成功,则主备数据库将会呈现如下图所示的图标:


建立镜像关系成功后,手工将主体服务器的SQL Server服务停止,查看镜像服务器的数据库是否变为主体,如果没有变为主体则将见证服务器的SQL Server服务重启,启动主体服务器的SQL Server。再次关闭主体服务器的SQL服务查看镜像服务器数据库是否变为主体。

如果建立失败,提示类似数据库事务日志未同步,则说主备数据库的数据(日志)未同步,为保证主备数据库内的数据一致,应在主数据库中实施一次"事务日志"备份,并还原到备数据库上。备份"事务日志"如图所示:

还原事务日志时需在选项中选择"restore with norecovery",如图所示:



成功还原以后再执行建立镜像的SQL语句。

注意:如为多个数据库做镜像,可以重复以上过程。

四、测试操作

在主服务器上选择数据库,右键点击,选择属性,再弹出的对话框中,选择"镜像":

注意:运行模式是"带自动故障转移功能的高安全(同步)",且状态为"已同步:数据库已完全同步"。可以点击"故障转移"来尝试镜像是否成功。如果成功后,主机数据库的状态会变为"主体/已同步",镜像服务器的状态会变为"镜像,已同步/正在还原"。