SQL Server 2005 数据库镜像的实现


一、配置主备机

1、 物理连接

将主备数据库按照如图所示连接:

2、 检查SQL Server 2005数据库

只有SQL Server 2005 标准版、企业版和开发版才可以建立数据镜像。其他版本即Express只能作为见证服务器。如果实在不清楚什么版本,执行如下语句查看:

select @@version;

若要对此数据库进行数据库镜像,必须将它更改为使用完整恢复模式。若要用 Transact-SQL 实现此目的,请使用 ALTER DATABASE 语句:

USE master;
 ALTER DATABASE <DatabaeName> 
 SET RECOVERY FULL;

 

 

二、主备实例互通

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

1、创建证书(主备可并行执行)

–主机执行:

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'killkill';
CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate' , 
START_DATE = '01/01/2010';

–备机执行:

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'killkill';
CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate', 
START_DATE = '01/01/2008';

 

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 = ALL );

–备机执行:

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 = ALL );

 

3、备份证书以备建立互联(主备可并行执行)

–主机执行:

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

 –备机执行:

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

 

4、互换证书

将备份到D:\SQLBackup\的证书进行互换,即HOST_A_cert.cer复制到备机的D:\SQLBackup\。HOST_B_cert.cer复制到主机的D:\SQLBackup\

5、添加登陆名、用户(主备可并行执行)

以下操作只能通过命令行运行,通过图形界面无法完成。(截至文档编写结束,SQL Server2005的不定号为SP2)

–主机执行:

CREATE LOGIN HOST_B_login WITH PASSWORD = 'killkill';
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'D:\SQLBackup\HOST_B_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];

–备机执行:

CREATE LOGIN HOST_A_login WITH PASSWORD = 'killkill';
CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'D:\SQLBackup\HOST_A_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];

 

三、建立镜像关系

以下步骤是针对每个数据库进行的,例如:现有主机中有5个数据库以下过程就要执行5次。

1、 手工同步登录名和密码

在第一章中提到数据库镜像的缺点之一是无法维护登录名,所以需要我们手工维护登录。

通常来说数据库都将会有若干个用户作为访问数据库的用户,并且数据库会有相应的登录名,但是在备机中缺少与之相对应的登录名,例如某业务系统使用’myuser’作为登录名访问数据库,但是在备机中没有’myuser’这个登录名,因此一旦主备切换,业务系统就无法登录数据库了,这种情况称为”孤立用户”

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

USE master;
select sid,name from syslogins;

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

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

USE master;
exec sp_addlogin 
@loginame = '<LoginName>', 
@passwd = '<Password>', 
@sid = <sid> ;

这里的’LoginName’即主数据库中的登录名,sid即是上述通过SQL语句查找出的sid。

例如,查询得到的sid和name如下所示。

sid name
---------------------------------- -----------------
0x074477739DCA0E499C29394FFFC4ADE4 cz_account

则建立登录名的SQL语句:

USE master;
exec sp_addlogin 
@loginame = 'cz_account', 
@passwd = 'password', 
@sid = 0x074477739DCA0E499C29394FFFC4ADE4;

到此为止可以认为备机数据库的环境已经与主机同步了,还差数据库内的数据未同步。

2、 准备备机数据库

承接上文,该节是描述如何同步主备数据库内的数据。

可以尝试从刚刚使用的全备文件进行还原,在还原数据的时候需要使用选上“with non recover”。如图所示:

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

3、 建立镜像

由于是实验,没有为服务器配置双网卡,IP地址与图有点不一样,但是原理一样。

–主机执行:

ALTER DATABASE shishan SET PARTNER = 'TCP://10.168.6.45:5022';

–如果主体执行不成功,尝试在备机中执行如下语句:

ALTER DATABASE shishan SET PARTNER = 'TCP://10.168.6.49:5022';

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

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

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

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

四、测试操作

1、主备互换

–主机执行:

USE master;
ALTER DATABASE <DatabaseName> SET PARTNER FAILOVER;

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

–备机执行:

USE master;
ALTER DATABASE <DatabaseName> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;

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

--备机执行:
USE master;
ALTER DATABASE <DatabaseName> SET PARTNER RESUME; --恢复镜像
ALTER DATABASE <DatabaseName> SET PARTNER FAILOVER; --切换主备

4、原来的主服务器恢复,可以继续工作

–默认情况下,事务安全级别的设置为 FULL,即同步运行模式,而且SQL Server 2005 标准版只支持同步模式。

–关闭事务安全可将会话切换到异步运行模式,该模式可使性能达到最佳。

USE master;
ALTER DATABASE <DatabaseName> SET PARTNER SAFETY FULL; --事务安全,同步模式
ALTER DATABASE <DatabaseName> SET PARTNER SAFETY OFF; --事务不安全,异步模式

  

clip_image015

 

clip_image013

 

clip_image011

 

clip_image009

 

clip_image006

 

clip_image004

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

验证码 * Time limit is exhausted. Please reload CAPTCHA.