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

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
日志服务 SLS,月写入数据量 50GB 1个月
简介:

数据库镜像是将数据库事务处理从一个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”微信公众号

相关实践学习
日志服务之使用Nginx模式采集日志
本文介绍如何通过日志服务控制台创建Nginx模式的Logtail配置快速采集Nginx日志并进行多维度分析。
目录
相关文章
|
5天前
|
SQL 关系型数据库 MySQL
SQL命令行退出操作指南:轻松掌握不同数据库环境下的退出技巧
在数据库管理与开发过程中,经常需要通过SQL命令行工具(如MySQL的mysql客户端、PostgreSQL的psql、SQL Server的sqlcmd等)与数据库进行交互
122 59
|
5天前
|
存储 分布式计算 数据库
阿里云国际版设置数据库云分析工作负载的 ClickHouse 版
阿里云国际版设置数据库云分析工作负载的 ClickHouse 版
|
7天前
|
应用服务中间件 数据库
Tomcat 的数据库连接池设置与应用
Tomcat 的数据库连接池设置与应用
17 3
|
6天前
|
应用服务中间件 PHP Apache
PbootCMS提示错误信息“未检测到您服务器环境的sqlite3数据库扩展...”
PbootCMS提示错误信息“未检测到您服务器环境的sqlite3数据库扩展...”
|
6天前
|
关系型数据库 MySQL 数据库
使用Docker部署的MySQL数据库如何设置忽略表名大小写?
【10月更文挑战第1天】使用Docker部署的MySQL数据库如何设置忽略表名大小写?
28 1
|
1月前
|
SQL 关系型数据库 MySQL
MySQL数据库中给表添加字段并设置备注的脚本编写
通过上述步骤,你可以在MySQL数据库中给表成功添加新字段并为其设置备注。这样的操作对于保持数据库结构的清晰和最新非常重要,同时也帮助团队成员理解数据模型的变化和字段的具体含义。在实际操作中,记得调整脚本以适应具体的数据库和表名称,以及字段的详细规范。
38 8
|
5天前
|
数据可视化 关系型数据库 MySQL
【IDEA】配置mysql环境并创建mysql数据库
【IDEA】配置mysql环境并创建mysql数据库
29 0
|
1月前
|
关系型数据库 数据库 网络虚拟化
Docker环境下重启PostgreSQL数据库服务的全面指南与代码示例
由于时间和空间限制,我将在后续的回答中分别涉及到“Python中采用lasso、SCAD、LARS技术分析棒球运动员薪资的案例集锦”以及“Docker环境下重启PostgreSQL数据库服务的全面指南与代码示例”。如果你有任何一个问题的优先顺序或需要立即回答的,请告知。
51 0
|
4天前
|
缓存 算法 关系型数据库
Mysql(3)—数据库相关概念及工作原理
数据库是一个以某种有组织的方式存储的数据集合。它通常包括一个或多个不同的主题领域或用途的数据表。
23 5
Mysql(3)—数据库相关概念及工作原理
|
8天前
|
SQL 关系型数据库 MySQL