非域环境下利用证书设置数据库镜像

数据库镜像是将数据库事务处理从一个SQL Server数据库移动到不同SQL
Server环境中的另一个SQL
Server数据库中。镜像不能够直接访问;它只用在错误恢复生机的图景下才可以被访问。

要拓展数据库镜像所需的细微需求包括了六个不同的SQL
Server运行条件。主服务器被叫作“主机”,第二个服务器被称作“备机”。主机数据库就是您其实用着的数据库,镜像数据库就是你的数据库的备用拷贝。当工作写入你的骨干服务器的时候,他们也同等被传送到并写入你的镜像数据库中。

而外要旨和镜像之外,你还是可以引入另一个可选的机件,名为“见证”。见证服务器是第两个SQL
Server
2005/2008运作实例,它是在认清什么日期举办不当复苏的时候,用于着力和镜像之间内部沟通。只有当你想实现机关错误恢复生机的时候用到那些选项。它实现了2比1投票的力量,当我的一个零件不可达,并就此需要开展不当恢复生机的时候。见证服务器唯有在您想实现活动错误复苏的时候才需要用到。具体内容参见《SQL
SERVER
2005数据库镜像》

兑现互通可以使用域或证件来贯彻,非域环境下使用证书配置数据库镜像。

1、 环境

主机名称

主机操作系统

主机SQL版本

IP配置

初始镜像角色

oa-anquan

Windows Server 2003

Microsoft SQL Server 2008

10.6.11.84

主体服务器

oa-canoe

Windows Server 2003

Microsoft SQL Server 2008

10.6.11.224

镜像服务器

oa-tjcfw

Windows Server 2003

Microsoft SQL Server 2008

10.6.11.74

见证服务器

2、 配置出站连接

着重办事是为服务器实例制作证书:

(一)为主机配置出站连接:

/********************************************************

此脚本在中央服务器执行

********************************************************/

USE master;

–DROP MASTER KEY

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘OA@asp.net’;

GO

–为此服务器实例制作一个证书。

–DROP CERTIFICATE oa-anquan_cert

CREATE CERTIFICATE oa-anquan_cert

WITH SUBJECT = ‘oa_anquan certificate’,START_DATE = ’05/01/2010

,EXPIRY_DATE = ’01/01/2099′;

GO

–使用该证件为服务器实例创制一个镜像端点。

–DROP ENDPOINT Endpoint_Mirroring

CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

AS TCP (

LISTENER_PORT=5022

, LISTENER_IP = ALL

)

FOR DATABASE_MIRRORING (

AUTHENTICATION = CERTIFICATE oa-anquan_cert

, ENCRYPTION = REQUIRED ALGORITHM AES

, ROLE = PARTNER

);

GO

–备份oa-anquan 证书,并将其复制到其他机器,将
D:\dbmirror\oa-anquan_cert.cer 复制到 oa-canoe\oa-tjcfw。

BACKUP CERTIFICATE oa-anquan_cert TO FILE =
‘D:\dbmirror\oa-anquan_cert.cer’;

GO

(二)为镜像服务器配置出站连接

/***********************************************

在镜像服务器执行此脚本

***********************************************/

USE master;

–DROP MASTER KEY

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘OA@asp.net’;

GO

–为 oa-canoe 服务器实例制作一个证件。

–DROP CERTIFICATE oa-canoe_cert

CREATE CERTIFICATE oa-canoe_cert

WITH SUBJECT = ‘oa-canoe certificate for database mirroring’,START_DATE
= ’05/01/2010′

,EXPIRY_DATE = ’01/01/2099′;

GO

–在 oa-canoe 中为服务器实例创设一个镜像端点。

–DROP ENDPOINT Endpoint_Mirroring

CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

AS TCP (

LISTENER_PORT=5022

, LISTENER_IP = ALL

)

FOR DATABASE_MIRRORING (

AUTHENTICATION = CERTIFICATE oa-canoe_cert

, ENCRYPTION = REQUIRED ALGORITHM AES

, ROLE = PARTNER

);

GO

–备份 oa-canoe 证书,将 D:\dbmirror\oa-canoe_cert.cer
复制到oa-anquan\oa-tjcfw。

BACKUP CERTIFICATE oa-canoe_cert TO FILE =
‘D:\dbmirror\oa-canoe_cert.cer’;

GO

(三)为见证服务器配置出站连接

/****************************

知情者服务器执行

*****************************/

–ALTER DATABASE MirrorDB SET PARTNER OFF

USE master;

–DROP MASTER KEY

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘OA@asp.net’;

GO

–为此服务器实例制作一个注明。

–DROP CERTIFICATE oa-tjcfw_cert

CREATE CERTIFICATE oa-tjcfw_cert

WITH SUBJECT = ‘oa-tjcfw certificate’,START_DATE = ’05/01/2010′

,EXPIRY_DATE = ’01/01/2099′;

GO

–使用该证件为服务器实例创立一个镜像端点。

–DROP ENDPOINT Endpoint_Mirroring

CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

AS TCP (

LISTENER_PORT=5022

, LISTENER_IP = ALL

)

FOR DATABASE_MIRRORING (

AUTHENTICATION = CERTIFICATE oa-tjcfw_cert

, ENCRYPTION = REQUIRED ALGORITHM AES

, ROLE = WITNESS

);

GO

–备份 oa-tjcfw 证书,并将其复制到其他系统,即 oa-canoe\oa-anquan。

BACKUP CERTIFICATE oa-tjcfw_cert TO FILE =
‘D:\dbmirror\oa-tjcfw_cert.cer’;

GO

注意:证书必须指定过期时间,倘使未指定,则将 EXPIRY_DATE 设置为
START_DATE
一年之后的日子。

3、 配置入站连接

(一)为主导服务器配置入站连接:

–为入站连接配置oa-anquan

–在oa-anquan 上为 oa-canoe 创立一个登录名。

USE master;

–DROP LOGIN oa-canoe_login

CREATE LOGIN oa-canoe_login WITH PASSWORD = ‘OA@asp.net’;

GO

–创立一个使用该登录名的用户。

–DROP USER oa-canoe_user

CREATE USER oa-canoe_user FOR LOGIN oa-canoe_login;

GO

–使证书与该用户关联。

–DROP CERTIFICATE oa-canoe_cert

CREATE CERTIFICATE oa-canoe_cert

AUTHORIZATION oa-canoe_user

FROM FILE = ‘D:\dbmirror\oa-canoe_cert.cer’

GO

–授予对长途镜像端点的登录名的 CONNECT 权限。

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [oa-canoe_login];

GO

–在oa-anquan 上为 oa-tjcfw 创设一个登录名。

USE master;

–DROP LOGIN oa-tjcfw_login

CREATE LOGIN oa-tjcfw_login WITH PASSWORD = ‘OA@asp.net’;

GO

–成立一个拔取该登录名的用户。

–DROP USER oa-tjcfw_user

CREATE USER oa-tjcfw_user FOR LOGIN oa-tjcfw_login;

GO

–使证书与该用户关联。

–DROP CERTIFICATE oa-tjcfw_cert

CREATE CERTIFICATE oa-tjcfw_cert

AUTHORIZATION oa-tjcfw_user

FROM FILE = ‘D:\dbmirror\oa-tjcfw_cert.cer’

GO

–授予对长途镜像端点的登录名的 CONNECT 权限。

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [SQL Server,oa-tjcfw_login];

GO

USE master;

–DROP LOGINoa-anquan_login

CREATE LOGINoa-anquan_login WITH PASSWORD= ‘OA@asp.net’;

GO

–创立一个施用该登录名的用户。

–DROP USERoa-anquan_user

CREATE USERoa-anquan_user FOR CERTIFICATEoa-anquan_cert;

GO

–授予对长距离镜像端点的登录名的 CONNECT 权限。

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [oa-anquan_login];

GO

(二)为镜像服务器配置入站连结:

–为入站连接配置 oa-canoe

–在 oa-canoe 上为oa-anquan 创造一个登录名。

USE master;

–DROP LOGINoa-anquan_login

CREATE LOGINoa-anquan_login WITH PASSWORD = ‘OA@asp.net’;

GO

–成立一个施用该登录名的用户。

–DROP USERoa-anquan_user

CREATE USERoa-anquan_user FOR LOGINoa-anquan_login;

GO

–使证书与该用户关联。

–DROP CERTIFICATEoa-anquan_cert

CREATE CERTIFICATEoa-anquan_cert

AUTHORIZATIONoa-anquan_user

FROM FILE = ‘D:\dbmirror\oa-anquan_cert.cer’

GO

–授予对长途镜像端点的登录名的 CONNECT 权限。

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [oa-anquan_login];

GO

–在 oa-canoe 上为 oa-tjcfw 创立一个登录名。

USE master;

–DROP LOGIN oa-tjcfw_login

CREATE LOGIN oa-tjcfw_login WITH PASSWORD = ‘OA@asp.net’;

GO

–创造一个利用该登录名的用户。

–DROP USER oa-tjcfw_user

CREATE USER oa-tjcfw_user FOR LOGIN oa-tjcfw_login;

GO

–使证书与该用户关联。

–DROP CERTIFICATE oa-tjcfw_cert

CREATE CERTIFICATE oa-tjcfw_cert

AUTHORIZATION oa-tjcfw_user

FROM FILE = ‘D:\dbmirror\oa-tjcfw_cert.cer’

GO

–授予对长距离镜像端点的登录名的 CONNECT 权限。

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [oa-tjcfw_login];

GO

–在 oa-canoe 上为 oa-canoe 创造一个登录名。

USE master;

–DROP LOGIN oa-canoe_login

CREATE LOGIN oa-canoe_login WITH PASSWORD = ‘OA@asp.net’;

GO

–创设一个拔取该登录名的用户。

–DROP USER oa-canoe_user

CREATE USER oa-canoe_user FOR CERTIFICATE oa-canoe_cert;

GO

–授予对长距离镜像端点的登录名的 CONNECT 权限。

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [oa-canoe_login];

GO

(三)为见证服务器配置入站连接:

–为入站连接配置 oa-tjcfw

–在 oa-tjcfw 上为 oa-canoe 成立一个登录名。

USE master;

–DROP LOGIN oa-canoe_login

CREATE LOGIN oa-canoe_login WITH PASSWORD = ‘OA@asp.net’;

GO

–创设一个采取该登录名的用户。

–DROP USER oa-canoe_user

CREATE USER oa-canoe_user FOR LOGIN oa-canoe_login;

GO

–使证书与该用户关联。

–DROP CERTIFICATE oa-canoe_cert

CREATE CERTIFICATE oa-canoe_cert

AUTHORIZATION oa-canoe_user

FROM FILE = ‘D:\dbmirror\oa-canoe_cert.cer’

GO

–授予对长途镜像端点的登录名的 CONNECT 权限。

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [oa-canoe_login];

GO

–在 oa-tjcfw 上为oa-anquan 创制一个登录名。

USE master;

–DROP LOGIN oa-anquan_login

CREATE LOGIN oa-anquan_login WITH PASSWORD = ‘OA@asp.net’;

GO

–创立一个使用该登录名的用户。

–DROP USER oa-anquan_user

CREATE USER oa-anquan_user FOR LOGIN oa-anquan_login;

GO

–使证书与该用户关联。

–DROP CERTIFICATE oa-anquan_cert

CREATE CERTIFICATE oa-anquan_cert

AUTHORIZATION oa-anquan_user

FROM FILE = ‘D:\dbmirror\oa-anquan_cert.cer’

GO

–授予对长途镜像端点的登录名的 CONNECT 权限。

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [oa-anquan_login];

GO

4、 配置镜像数据库

通常来说数据库都将会有多少个用户作为走访数据库的用户,并且数据库会有照应的登录名,但是在备机中缺失与之相对应的登录名,例如某工作系统使用’oateam’作为登录名访问数据库,但是在备机中从不’oateam’那么些登录名,因而只要主备切换,业务系统就不可能登录数据库了,这种景象称为”孤立用户”

在主数据库中实践如下语句:

USE master;

select sid,name from syslogins;

摸索出相应的用户名和sid,例如:上述的’oateam’

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

USE master;

exec sp_addlogin

@loginame = ‘<LoginName>’,

@passwd = ‘<Password>’,

@sid = <sid> ;

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

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

sid name


50x074477739DCA0E499C29394FFFC4ADE4 oateam

则树立登录名的SQL语句:

USE master;

exec sp_addlogin

@loginame = ‘oateam’,

@passwd = ‘OA@asp.net’,

@sid = 0x074477739DCA0E499C29394FFFC4ADE4;

到此结束可以认为备机数据库的条件已经与主机同步了

在主旨服务器上备份数据库,镜像只扶助完全苏醒形式,在备份数据库此前检查恢复生机的格局,需要同时完整备份数据库和工作日志。

USE MASTER;
GO
BACKUP DATABASE Credit
TO DISK = ‘D:\dbmirror\Credit.bak’
WITH INIT
GO
BACKUP LOG Credit
TO DISK = ‘D:\dbmirror\Credit_log.bak’
WITH INIT
GO

将Credit.bak、Credit_log.bak复制到oa-canoe上,复制到oa-canoe的镜像服务器实例上以NORECOVERNY选项举办復苏.

留神:这里数据库必须备份一遍,一遍全备份和两回事情日志备份,在Mirror机上回复数据库必须运用NORECOVERNY选项举办復苏.

5、 配置镜像伙伴

(一)在镜像服务器上配置

–在 oa-canoe 的镜像服务器实例上,将oa-anquan
上的服务器实例设置为小伙伴(使其变成先河主体服务器实例)。

ALTER DATABASE Credit

SET PARTNER = ‘TCP://10.6.11.84:5022’;

GO

(二)在主题服务器上配备

–必须要在镜像数据库中先安装好伙伴后,才能在关键性服务器执行

–在oa-anquan 的侧重点服务器实例上,将 oa-canoe
上的服务器实例设置为小伙伴(使其变为开始镜像服务器实例)。

ALTER DATABASE Credit

SET PARTNER = ‘TCP://10.6.11.224:5022’;

GO

(三)在主体服务器上配置见证服务器

–设置见证服务器,在oa-anquan 的基点服务器实例上,将 oa-tjcfw
上的服务器实例设置为见证(使其变成开端见证服务器实例)

ALTER DATABASE Credit SET WITNESS = N’TCP://10.6.11.74:5022′;

GO

l 数据库运营

1、 数据库镜像要求完全苏醒形式,有大气的事体日志,推荐这样做:

a) 数据库配置好机关裁减。

b) 数据库配置好机关备份(全备)

c) 数据库配置好工作日志备份(事务日志备份)

d) 遵照自己的磁盘空间设置好备份窗口。

2、 监控,参看msdn
http://msdn.microsoft.com/zh-cn/library/ms365781.aspx,
通过事件通报形式举办:

状态

名称

描述

0

Null Notification

This state occurs briefly when a mirroring session is started.

1

Synchronized Principal with Witness

This state occurs on the principal when the principal and mirror are connected and synchronized and the principal and witness are connected. For a mirroring configuration with a witness, this is the normal operating state.

2

Synchronized Principal without Witness

This state occurs on the principal when the principal and mirror are connected and synchronized but the principal does not have a connection to the witness. For a mirroring configuration without a witness, this is the normal operating state.

3

Synchronized Mirror with Witness

This state occurs on the mirror when the principal and mirror are connected and synchronized and the mirror and witness are connected. For a mirroring configuration with a witness, this is the normal operating state.

4

Synchronized Mirror without Witness

This state occurs on the mirror when the principal and mirror are connected and synchronized but the mirror does not have a connection to the witness. For a mirroring configuration without a witness, this is the normal operating state.

5

Connection with Principal Lost

This state occurs on the mirror server instance when it cannot connect to the principal.

6

Connection with Mirror Lost

This state occurs on the principal server instance when it cannot connect to the mirror.

7

Manual Failover

This state occurs on the principal server instance when the user fails over manually from the principal, or on the mirror server instance when a force service is executed at the mirror.

8

Automatic Failover

This state occurs on the mirror server instance when the operating mode is high safety with automatic failover (synchronous) and the mirror and witness server instances cannot connect to the principal server instance.

9

Mirroring Suspended

This state occurs on both partner instances when the user suspends (pauses) the mirroring session or when the mirror server instance encounters an error. It also occurs on the mirror server instance following a force service command. When the mirror comes online as the principal, mirroring is automatically suspended.

10

No Quorum

If a witness is configured, this state occurs on the principal or mirror server instance when it cannot connect to its partner or to the witness server instance.

11

Synchronizing Mirror

This state occurs on the mirror server instance when there is a backlog of unsent log. The status of the session is Synchronizing.

12

Principal Running Exposed

This state occurs on the principal server instance when the operating mode is high protection (synchronous) and the principal cannot connect to the mirror server instance.

13

Synchronizing Principal

This state occurs on the principal server instance when there is a backlog of unsent log. The status of the

领悟了上述事态,大家就足以因而数据库引擎或者WMI的事件展开监察:

USE [msdb]

GO

EXEC msdb.dbo.SP_ADD_ALERT @name=N’DBM State Change’,

        @message_id=0,

        @severity=0,

        @enabled=1,

        @delay_between_responses=0,

        @include_event_description_in=1,

        @category_name=N'[Uncategorized]’,

       
@wmi_namespace=N’\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER’,

        @wmi_query=N’SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE
WHERE State = 6 ‘,

        @job_id=N’00000000-0000-0000-0000-000000000000′

GO

上述ALERT在中央服务器上开创,状态为6,同样在镜像服务器上创意一个相类似的ALERT,状态为5。这五个告警会布告本人,主体和镜像之间由于网络或任何故障的总是丢失。可以通过Operators举行报警通告。

Database Mirroring Monitor warning

Event name

Event ID

Warn if the unsent log exceeds the threshold

Unsent log

32042

Warn if the unrestored log exceeds the threshold

Unrestored log

32043

Warn if the age of the oldest unsent transaction exceeds the threshold

Oldest unsent transaction

32044

Warn if the mirror commit overhead exceeds the threshold

Mirror commit overhead

32045

USE [msdb]

GO

EXEC msdb.dbo.SP_ADD_ALERT @name=N’DB Mirroring Unsent Log Warning’,

@message_id=32042,

@severity=0,

@enabled=0,

@delay_between_responses=0,

@include_event_description_in=1,

@category_name=N'[Uncategorized]’,

@job_id=N’00000000-0000-0000-0000-000000000000′

GO

http://sqlcat.com/technicalnotes/comments/1102.aspx

http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/how-to-monitor-database-mirroring

镜像的利用

运用新的ADO.NET 2.0+或者SQL Native Access
Client驱动程序,应用程序还足以从自己的服务器上举办自动的故障转移。使用ADO.NET,连接字符串如下所示:

data source=10.6.11.84\SQLSERVER08;failover partner=10.6.11.224;Initial
Catalog=OA_0184N;User ID=oateam;Password=OA@asp.net;

l 注意事项

(一)镜像相关服务器上的SQL SERVER 版本必须一律。

(二)在推行镜像此前,一定要将主数据库完整备份到镜像数据库,这里一定要备份两回,两次数据库备份和五次工作日志备份。

(三)数据帐号的孤立帐号处理,具体参见配置镜像数据库。

(四)假设不是在域帐户间实施镜像,则必定需要为服务器制作证书,证书一定要指定过期时间,尽管没有点名,默认的逾期时间是发端时间伊始估量,一年的有效期。

(五)推荐使用64位版本的SQL Server
2008,64位的服务器可以荣升服务器的性质和利用率,在32位的系统中,每个服务器实例,数据库最多辅助10个数据库。

(六)系统数据库,如master,msdb等不能履行镜像。

(七)一旦确立镜像,镜像数据库就只看做主数据库的冗余备份使用。

相关文章