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

数据库镜像是将数据库事务处理从一个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 [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等无法实施镜像。

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

诊断案例:从实例挂起到归档失败和内存管理的蝴蝶效应

杨廷琨(yangtingkun) 云和恩墨 CTO 高级咨询顾问,Oracle ACE 总监,ITPUB Oracle 数据库管理版版主 编辑手记:在很多数据...

3219
来自专栏杨建荣的学习笔记

数据迁移部分问题总结(r2第3天)

按照计划在周二开始了数据迁移,本来之前也做了不少的准备工作。但是还是在迁移的过程中出现了一些问题。简单做一个总结。 1.constraint导致的数据rej...

2917
来自专栏杨建荣的学习笔记

通过shell定制dbms_advisor.quick_tune(r4笔记第15天)

在平时的调优工作中,在11g中的新特性sql monitor可以极大的简化性能监控的工作,对于执行时间超过5秒的sql语句都会记入v$sql_monitor中...

3254
来自专栏乐沙弥的世界

Oracle 联机重做日志文件(ONLINE LOG FILE)

--=========================================

1132
来自专栏杨建荣的学习笔记

关于sysdba,sysoper,dba的区别(r3笔记第62天)

关于sysdba,sysoper,dba这些名词在工作中可能接触的比较多,如果接触的环境是服务器端的,sysdba可能是经常用到的。如果是数据库的维护工作,db...

2726
来自专栏杨建荣的学习笔记

使用impdp不当导致的数据丢失问题(r5笔记第1天)

今天有个朋友问我们一个问题,说他在使用了impdp导入数据的时候,使用了TABLE_EXISTS_ACTION=REPLACE这个选项,结果现在数据都给覆盖了。...

3625
来自专栏乐沙弥的世界

Oracle 控制文件(CONTROLFILE)

为二进制文件,初始化大小由CREATE DATABASE指定,可以使用RMAN备份

1212
来自专栏杨建荣的学习笔记

关于数据库中的一些name(r3笔记第64天)

如果接触数据库有些时间了,可能会碰到很多关于数据库相关的名字,比如ORACLE_SID,db_name,instance_name,db_unique_name...

34212
来自专栏杨建荣的学习笔记

session跟踪失效的问题和分析(57天)

最近碰到一个奇怪的问题,在生产和其他比较正式的环境中进行sql trace都没问题,但就是测试环境的数据库不知道怎么的, 设置sql_trace,开启诊断事件,...

37210
来自专栏cloudskyme

使用oracle的大数据工具ODCH访问HDFS数据文件

软件下载 Oracle Big Data Connectors:ODCH 下载地址: http://www.oracle.com/technetwork/bdc...

3798

扫码关注云+社区

领取腾讯云代金券