非域环境下使用证书设置数据库镜像
数据库镜像是将数据库事务处理从一个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];
GO4、 配置镜像数据库
通常来说数据库都将会有若干个用户作为访问数据库的用户，并且数据库会有相应的登录名，但是在备机中缺少与之相对应的登录名，例如某业务系统使用’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';
GOl 数据库运营
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'
GOhttp://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等无法实施镜像。
（七）一旦建立镜像，镜像数据库就只作为主数据库的冗余备份使用。
腾讯云开发者
