SQL 2005数据库镜像

 

SQL SERVER 2005数据库镜像搭建

一 概述

    数据库镜像是SQL SERVER 2005用于提高数据库可用性的新技术。数据库镜像将事务日志记录直接从一台服务器传输到另一台服务器,并且能够在出现故障时快速转移到备用服务器。可以编写客户端程序自动重定向连接信息,这样一旦出现故障转移就可以自动连接到备用服务器和数据库。

优势:数据库镜像可以在不丢失已提交数据的前提下进行快速故障转移,无须专门的硬件,并且易于配置和管理。

 环境准备

操作系统:Window 2003 enterprise sp2(至少两台,如要启用自动故障转移,必需三台)

SQL版本:MSSQL SERVER 2005 SP3

检查SQL SERVER版本:

exec xp_msver

select SERVERPROPERTY('productlevel')

 

 

数据库准备:准备一个数据库:ccerp_jzt ,备份此数据库还原到另外一台机器上,另外一台必须是with no recovery

这里我假设服务器A,B,C

A为主体服务器,B为镜像服务器,C为见证服务器

 

A服务器

use master

go

restore filelistonly from disk=N'f:\databak\ccerp_jzt_backup_200911250100.bak'

 

restore database ccerp_jzt from disk=N'f:\databak\ccerp_jzt_backup_200911250100.bak' with replace,recovery,

move 'ccerp_ydswzip_Data' to 'd:\data\ccerp_jzt.mdf',

move 'ccerp_ydswzip_Log' to 'd:\data\ccerp_jzt_log.ldf'

exec sp_helpdb 'ccerp_jzt'

 

backup database ccerp_jzt to disk =N'f:\databak\sk.bak' with init

--更改恢复模式

alter database ccerp_jzt set recovery full

B服务器:

CREATE DATABASE ccerp_jzt

ON

( NAME = Sales_dat,

   FILENAME = 'd:\data\ccerp_jzt.mdf',

   SIZE = 10

)

LOG ON

( NAME = 'ccerp_jzt_log',

   FILENAME = 'd:\data\ccerp_jzt_log.ldf',

   SIZE = 5MB

 )

GO

restore filelistonly from disk=N'f:\xxzx\data\sk.bak'

use master

go

restore database ccerp_jzt from disk=N'f:\xxzx\data\sk.bak' with replace,norecovery,

exec sp_helpdb 'ccerp_jzt'

 

C服务器只要装上SQL SERVER 2005就可以,无需其他准备

准备完成后如下图所示:

 三种模式的搭建

数据库镜像要建立必需得建立信任关系,那么在WIN环境下建立信任关系可以通过三种方式:域帐户,证书信任,windows 匿名登陆,现就前两种模式做配置说明.

3.1 域帐户模式:

3.1.1 更改mssqlserver服务的的登陆方式为域帐户登陆方式:

      进入windows服务管理控制台,更改服务登陆帐户,使域账户有更改MSSQL SERVER服务状态的权限.三台机器都做同样设置

 

将域帐户赋予sysadmin角色

 

 

3.1.2 建立端点:

通过图形界面建立端点:

启动SQLWB,按图一直下一步


  
     

用域帐户登陆

   如果成功则:

 

3.2 证书模式

3.2.1建立证书&端点

参与数据库镜像会话的服务器必须彼此信任。对于本地通信而言,例如一个域内的通信,信任意味着SQL Server实例登陆账号必须有权限连接到其他镜像服务器,也包括endpoints。首先在每个服务器上使用CREATE LOGIN命令,然后使用GRANT CONNECT ON ENDPOINT命令.非信任域之间的通信必须使用证书。如果使用CREATE CERTIFICATE语句创建自签名的证书,基本上所有数据镜像证书的要求都可以满足。确认在CREATE CERTIFICATE语句中将证书标记为ACTIVE FOR BEGIN_DIALOG

一 建立证书:

镜像服务器上执行:

USE master;

 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TEST';

 CREATE CERTIFICATE HOST_A_cert WITH SUBJECT='HOST_A certificate', START_DATE='2010-03-10';

主体服务器上执行:

USE master;

 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TEST';

 CREATE CERTIFICATE HOST_B_cert WITH SUBJECT='HOST_B certificate', START_DATE='2010-03-10';

见证服务器上执行:

USE master;

 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TEST';

 CREATE CERTIFICATE HOST_C_cert WITH SUBJECT='HOST_C certificate', START_DATE='2010-03-10';

 

 

 

二 建立端点:

镜像服务器上执行:

 

--create mirror endpoint on primary A

 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 on mirror server B

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

见证服务器上执行:

--Create endpoint on witness server C

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

SELECT * FROM sys.database_mirroring_endpoints;

 

证书互备:

镜像服务器上执行:

--backup certificate

 BACKUP CERTIFICATE HOST_A_cert TO FILE = 'e:\HOST_A_cert.cer'

主体服务器上执行

--backup certificate

 BACKUP CERTIFICATE HOST_B_cert TO FILE = 'e:\HOST_B_cert.cer'

见证服务器上执行:

BACKUP CERTIFICATE HOST_c_cert TO FILE = 'e:\HOST_C_cert.cer'

将备份到的证书进行互换,即HOST_A_cert.cer复制到B机的e:\ HOST_B_cert.cer复制到A机的E:\,也就是每台服务器有三个证书

 

三:建立登陆用户:

镜像服务器上执行:

--Create user

 CREATE LOGIN HOST_B_login WITH PASSWORD = 'test';

 CREATE USER HOST_B_user FOR LOGIN HOST_B_login;

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

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

 

CREATE LOGIN HOST_C_login WITH PASSWORD = 'test';

 CREATE USER HOST_C_user FOR LOGIN HOST_c_login;

 CREATE CERTIFICATE HOST_c_cert AUTHORIZATION HOST_c_user FROM FILE = 'e:\HOST_c_cert.cer';

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

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

--query user sid

select loginname,name,sid From syslogins

 

主体服务器上执行:

--Create user

 CREATE LOGIN HOST_A_login WITH PASSWORD = 'test';

 CREATE USER HOST_A_user FOR LOGIN HOST_A_login;

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

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

-- add witness user

 CREATE LOGIN HOST_C_login WITH PASSWORD = 'test';

 CREATE USER HOST_C_user FOR LOGIN HOST_c_login;

 CREATE CERTIFICATE HOST_c_cert AUTHORIZATION HOST_c_user FROM FILE = 'e:\HOST_c_cert.cer';

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

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

 

--query sid

select loginname,name,sid From syslogins

见证服务器上执行:

--Create user

 CREATE LOGIN HOST_A_login WITH PASSWORD = 'test';

 CREATE USER HOST_A_user FOR LOGIN HOST_A_login;

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

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

--add user host_b_login to have pemission to access witness

 CREATE LOGIN HOST_B_login WITH PASSWORD = 'test';

 CREATE USER HOST_B_user FOR LOGIN HOST_B_login;

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

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

grant connect on endpoint::endpoint_mirroring to HOST_C_login

 

 

 USE master;

 exec sp_addlogin

 @loginame = 'HOST_B_login',

 @passwd = 'test',

 @sid = 0x1A914CA3D1D00C4793EBC96E4C4F4352 ;

 

ALTER DATABASE ccerp_jzt SET PARTNER = 'TCP://192.168.137.32:5022';

 

四.建立镜像:

先在镜像服务器上执行:

ALTER DATABASE ccerp_jzt SET PARTNER = 'TCP://192.168.137.44:5022';

接着主体服务器执行:

ALTER DATABASE ccerp_jzt SET PARTNER = 'TCP://192.168.137.32:5022';

ALTER DATABASE ccerp_jzt SET witness = 'TCP://192.168.137.49:5022';

至此引证书建立完毕

 

四、测试操作

1、主备互换

--主机执行:

1 USE master;
2 ALTER DATABASE <DatabaseName> SET PARTNER FAILOVER;
3

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

--备机执行:

1 USE master;
2 ALTER DATABASE <DatabaseName> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;
3

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

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

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

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

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

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

错误说明:
消息1498,级别16,状态3,第1

默认情况下,数据库镜像是被禁用的。当前提供的数据库镜像仅供评估使用,并不应使用于生产环境中。若要以评估为目的启用数据库镜像,请在启动过程中使用跟踪标志1400。有关跟踪标志和启动选项的详细信息,请参阅SQL Server 联机丛书。

解决办法:没打SP1以上补丁.强烈建议打SP3

 

消息1475,级别16,状态2,第1

由于"ccerp_jzt" 数据库可能有尚未备份的大容量日志记录更改,所以无法启用数据库镜像。必须在镜像上还原主体数据库的上一次日志备份。

主体上:backup log ccerp_jzt to disk ='e:\log.trn' with no_truncate

 

镜像上:restore log ccerp_jzt from disk='e:\log.trn' with norecovery

 

操作系统上的:

Database Mirroring 登录尝试失败错误为: 'Connection handshake failed. The login 'HOST_A_login' does not have CONNECT permission on the endpoint. State 84.'。 [CLIENT: 192.168.137.32]

解决办法:重新grant用户权限

 

 

Database Mirroring 登录尝试失败,错误为: 'Connection handshake failed. The certificate used by the peer is invalid due to the following reason: The database principal has no mapping to a server principal. State 89.'。 [CLIENT: 192.168.137.49]

证书不对:重新导证书,重新grant用户权限

posted on 2010-03-30 11:44 snowhill 阅读(1448) 评论(2)  编辑 收藏 引用 所属分类: 数据库-oracle

评论

# re: SQL 2005数据库镜像 2012-01-31 08:54 李星辰

为啥看不到图啊  回复  更多评论   

# re: SQL 2005数据库镜像[未登录] 2012-02-17 14:27 snowhill

@李星辰
主要是从word里传出来的,这个blog贴图有点麻烦.  回复  更多评论   


只有注册用户登录后才能发表评论。
网站导航: 博客园   IT新闻   BlogJava   知识库   博问   管理


<2012年1月>
25262728293031
1234567
891011121314
15161718192021
22232425262728
2930311234

导航

公告

又一年...........

留言簿(3)

随笔分类(13)

文章分类(131)

文章档案(124)

c++

java

linux

oracle

常用软件

其他

网络配置

系统安全

音乐

搜索

最新评论

阅读排行榜