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

本文主要介绍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 = 'iflytek@2012';

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

--镜像服务执行：

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'iflytek@2012';

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

--见证服务器执行：

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'iflytek@2012';

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,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,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 = 'iflytek@2012';

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 = 'iflytek@2012';

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 = 'iflytek@2012';

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 = 'iflytek@2012';

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 = 'iflytek@2012';

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 = 'iflytek@2012';

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语句。

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

四、测试操作

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

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!