开发者社区> 寒凝雪> 正文

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

简介:
+关注继续查看

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

要进行数据库镜像所需的最小需求包括了两个不同的SQL Server运行环境。主服务器被称为“主机”,第二个服务器被称作“备机”。主机数据库就是你实际用着的数据库,镜像数据库就是你的数据库的备用拷贝。当事务写入你的基本服务器的时候,他们也同样被传送到并写入你的镜像数据库中。

除了基本和镜像之外,你还可以引入另一个可选的组件,名为“见证”。见证服务器是第三个SQL Server 2005/2008运行实例,它是在判断什么时候进行错误恢复的时候,用于基本和镜像之间内部交流。只有当你想实现自动错误恢复的时候用到这个选项。它实现了2比1投票的能力,当我的一个组件不可达,并因此需要进行错误恢复的时候。见证服务器只有在你想实现自动错误恢复的时候才需要用到。具体内容参见《SQL SERVER 2005数据库镜像》。

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

1、 环境


cc57a5836af7c8ed78f9ce26c95d2e6203e9ebeb

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 [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, 通过事件通知方式进行:


1ff9ecff1739abbdebbca92cb4d49a520bb28068


7d0b7d83f26954fafdaf68120731403101427935

知道了上述状态,我们就可以通过数据库引擎或者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进行告警通知。


d612c51ee87afd2a56f37914227fc0bea7c6d00c
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等无法实施镜像。

(七)一旦建立镜像,镜像数据库就只作为主数据库的冗余备份使用。

本文来自云栖社区合作伙伴“doNET跨平台”,了解相关信息可以关注“opendotnet”微信公众号

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
邮件服务别名数据库设置指南
使用 Linux 服务器的时候,我们经常需要转发邮件,最简单的方案就是使用别名。以下简单介绍 sendmail 的别名数据库配置。这些配置也同样适用于兼容 sendmail 的其他邮件服务,例如 postfix。
99 0
使用镜像市场的oracle11g镜像搭建云数据库
阿里云的镜像市场提供了丰富的镜像供首次使用ECS或忙于工作没有时间配置环境的人选择。在镜像市场选择Oracle11g,通过本地电脑的SQL连接工具就可以连上数据库并进行调试。
1288 0
阿里云RDS-MYSQL数据库参数设置
阿里云RDS-MYSQL数据库参数设置
24645 0
+关注
5854
文章
223
问答
文章排行榜
最热
最新
相关电子书
更多
OceanBase 入门到实战教程
立即下载
阿里云图数据库GDB,加速开启“图智”未来.ppt
立即下载
实时数仓Hologres技术实战一本通2.0版(下)
立即下载