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

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
简介:

数据库镜像是将数据库事务处理从一个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日志并进行多维度分析。
目录
相关文章
|
7天前
|
SQL Oracle 关系型数据库
|
3天前
|
存储 缓存 NoSQL
Redis是一种高性能的内存数据库,常用于高并发环境下的缓存解决方案
【6月更文挑战第18天】**Redis摘要:** 高性能内存数据库,擅长高并发缓存。数据存内存,访问迅速;支持字符串、列表等多元数据类型;具备持久化防止数据丢失;丰富命令集便于操作;通过节点集群实现数据分片与负载均衡,增强可用性和扩展性。理想的缓存解决方案。
18 1
|
1月前
|
关系型数据库 MySQL 数据库
mysql 设置环境变量与未设置环境变量连接数据库的区别
设置与未设置MySQL环境变量在连接数据库时主要区别在于命令输入方式和系统便捷性。设置环境变量后,可直接使用`mysql -u 用户名 -p`命令连接,而无需指定完整路径,提升便利性和灵活性。未设置时,需输入完整路径如`C:\Program Files\MySQL\...`,操作繁琐且易错。为提高效率和减少错误,推荐安装后设置环境变量。[查看视频讲解](https://www.bilibili.com/video/BV1vH4y137HC/)。
102 3
mysql 设置环境变量与未设置环境变量连接数据库的区别
|
2天前
|
安全 BI 数据库
数据库大作业——基于qt开发的图书管理系统 (一)环境的配置与项目需求的分析
数据库大作业——基于qt开发的图书管理系统 (一)环境的配置与项目需求的分析
|
15天前
|
Web App开发 Ubuntu 安全
Linux中的certutil命令:处理证书与证书数据库的实用工具
`certutil`是Linux下的命令行工具,用于处理X.509证书和证书数据库,常与NSS库配合,服务于Firefox等应用。安装`certutil`可通过`apt-get install libnss3-tools`(Debian/Ubuntu)或`yum/dnf install nss-tools`(RHEL/Fedora/CentOS)。基本操作包括:使用`-L`列出证书数据库中的证书,`-A`添加证书,`-D`删除证书,`-x`导出证书。此外,还能用`-M`修改信任设置,`-C`列出证书链,`-V`验证证书链的有效性。了解这些功能有助于高效管理证书。
|
15天前
|
存储 缓存 测试技术
数据库在云存储环境中的配置与优化
【6月更文挑战第6天】探讨数据库在云存储的配置与优化,包括选择合适云服务、设计高效架构、查询语句优化、参数调整、数据分区索引、备份恢复策略、资源监控及性能测试。通过实践与学习,提升应对云环境中的数据库挑战能力。如有问题,欢迎讨论。
32 0
|
16天前
|
存储 SQL 缓存
Java性能优化(十)-数据库调优-数据库参数设置优化
Java性能优化(十)-数据库调优-数据库参数设置优化
22 0
|
1月前
|
DataWorks 安全 数据库
DataWorks产品使用合集之在DataWorks中,使用Power BI Desktop来连接Hologres数据库需要设置白名单如何解决
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
56 2
|
1月前
|
运维 分布式计算 DataWorks
DataWorks产品使用合集之dataworks为子账号创建DataWorks访问密钥的基本步骤如何解决
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
35 0
|
1月前
|
关系型数据库 MySQL 数据库
mysqwl 数据库 设置默认时间 datetime 和 timestamp 实测
mysqwl 数据库 设置默认时间 datetime 和 timestamp 实测
20 0

热门文章

最新文章