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

数据库镜像是将数据库事务处理从一个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 = '[e-mail]';

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 = '[e-mail]';

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 = '[e-mail]';

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 = '[e-mail]';

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 = '[e-mail]';

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

USE master;

--DROP LOGINoa-anquan_login

CREATE LOGINoa-anquan_login WITH PASSWORD= '[e-mail]';

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 = '[e-mail]';

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 = '[e-mail]';

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 = '[e-mail]';

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 = '[e-mail]';

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 = '[e-mail]';

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 = ‘[e-mail]’,

@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