详细讲解:两台服务器实现非域环境下使用证书设置数据库镜像 (Transact-SQL)

详细讲解:两台服务器实现非域环境下使用证书设置数据库镜像 (Transact-SQL)

7224发表于2016-04-11

sql server要实现高可用、数据同步的常用解决方案之一就是数据库镜像了,虽然一般做数据库镜像都是需要3台服务器(主体、镜像、见证),但是两台也是可以的,可以实现双机热备。今天我就来总结一下只使用两台服务器,实现非域环境下使用证书设置数据库镜像的详细步骤:

一台命名为Main IP地址为:192.168.0.2

用于做镜像的数据库名为:db_mirror 并将其恢复模式设置为完整恢复模式。

一台命名为Mirror IP地址为:192.168.0.3

配置环境:

1、windows server 2008 r2

2、sql server 2008 r2


假设您已经以NORECOVERY的选项备份了Main服务器上的db_mirror,并恢复数据库到Mirror服务器,这是Mirror服务器上的 db_mirror的状态应该是正在还原且不可用。

如果您曾经试图做过镜像,那么请先删除您所有服务器上原来的端点(Endpoint),

例如:

您原来Main服务器上端点的名字为Endpoint_Mirroring,执行以下代码:

USE [master]

DROP ENDPOINT Endpoint_Mirroring

GO

注:如何确认您原来的端点名称?

打开SQL Server Management Studio,在左边的对象资源管理中展开您的sql server实例中的服务器对象->端点->数据库镜像,当然也可在这里点击右键删除。

同样在Mirror服务器上删除原来的端点。

另外您还要删除服务器之间的伙伴关系,分别在服务器Main和Mirror上执行如下代码:

/*关闭可能原来配置失败的伙伴*/

ALTER DATABASE db_mirror SET PARTNER OFF

db_mirro为你的数据库名字。


现在让我开始吧!!!!

第一步:现在Main(主体)和Mirror(镜像)服务器上创建证书和端点,并备份证书到D:\Mirror,在开始以前请确定您的MSSQLUSER对该目录的读写权限。

一、主体服务器操作

在Main服务器上执行如下代码:

USE [master]

/*创建数据库主密钥。*/

IF EXISTS(SELECT * FROM sys.databases WHERE name=’master’ and is_master_key_encrypted_by_server=1)

/*drop master key;*/

OPEN MASTER KEY DECRYPTION BY PASSWORD=’www.lanhusoft.com′;

ELSE

create MASTER KEY ENCRYPTION BY PASSWORD=’www.lanhusoft.com′;

GO

SELECT * FROM sys.key_encryptions;

go


/*向数据库中添加证书。。*/

IF EXISTS(select * from sys.certificates WHERE name=’HOST_A_cert’)

DROP CERTIFICATE HOST_A_cert;

GO

/*注意证书开始的日期*/

CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = ‘HOST_A certificate’,

START_DATE = ‘2010-02-28′;

GO

SELECT * FROM sys.certificates;

GO


/*创建数据库端点*/

IF EXISTS(SELECT * FROM sys.database_mirroring_endpoints WHERE name=’Endpoint_Mirroring’)

DROP ENDPOINT Endpoint_Mirroring;

GO

CREATE ENDPOINT Endpoint_Mirroring STATE=STARTED

AS TCP ( LISTENER_PORT=5022 ,LISTENER_IP = ALL )

FOR DATABASE_MIRRORING( AUTHENTICATION = CERTIFICATE HOST_A_cert ,

ENCRYPTION = REQUIRED ALGORITHM RC4 ,

ROLE = PARTNER );

GO

SELECT * FROM sys.database_mirroring_endpoints;

GO


/*备份Main服务器证书用于接下来拷贝到镜像服务器*/

BACKUP CERTIFICATE HOST_A_cert TO FILE = ‘D:\MIRROR\HOST_A_cert.cer’;

GO


二、镜像(备库)操作

在Mirror服务器上执行操作和在主体服务器上大体相同,如下代码,创建证书和端点,并备份证书到Mirror服务器的D:\Mirror:

USE [master]

/*创建数据库主密钥。*/

IF EXISTS(SELECT * FROM sys.databases WHERE name=’master’ and is_master_key_encrypted_by_server=1)

–drop master key;

OPEN MASTER KEY DECRYPTION BY PASSWORD=’www.lanhusoft.com′;

ELSE

create MASTER KEY ENCRYPTION BY PASSWORD=’www.lanhusoft.com′;

GO

SELECT * FROM sys.key_encryptions;

go


/*向数据库中添加证书。*/

IF EXISTS(select * from sys.certificates WHERE name=’HOST_B_cert’)

DROP CERTIFICATE HOST_B_cert;

GO

CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = ‘HOST_B certificate’,

START_DATE = ‘2010-02-28′;

GO

SELECT * FROM sys.certificates;

GO


/*创建数据库端点*/

IF EXISTS(SELECT * FROM sys.database_mirroring_endpoints WHERE name=’Endpoint_Mirroring’)

DROP ENDPOINT Endpoint_Mirroring;

GO

CREATE ENDPOINT Endpoint_Mirroring STATE=STARTED

AS TCP ( LISTENER_PORT=5022 ,

LISTENER_IP = ALL )

FOR DATABASE_MIRRORING( AUTHENTICATION = CERTIFICATE HOST_B_cert ,

ENCRYPTION = REQUIRED ALGORITHM RC4 ,

ROLE = PARTNER );

GO

SELECT * FROM sys.database_mirroring_endpoints;

GO


/*备份证书*/

BACKUP CERTIFICATE HOST_B_cert TO FILE = ‘D:\MIRROR\HOST_B_cert.cer’;

GO


这时还有很重要的一个步骤:

就是把Main上的HOST_A_cert.cer拷贝到Mirror服务器上的D:\mirror,

然后把Mirror上的HOST_B_cert.cer拷贝到Main服务器上的D:\mirror。

三、创建镜像访问用户

下面我们开始创建互相访问的有效用户吧。

3.1、主体数据库

首先在Main服务器上创建访问Mirror的用户并授权,代码如下:

/*创建用户,用于访问MIRROR*/

 IF EXISTS(select * from sys.sql_logins WHERE name=’HOST_B_login’)

DROP LOGIN HOST_B_login

GO


IF EXISTS(select * from sys.database_principals WHERE name=’HOST_B_user’)

DROP USER HOST_B_user

GO

 

create LOGIN HOST_B_login WITH PASSWORD = ‘123456789′;

create USER HOST_B_user FOR LOGIN HOST_B_login;


/*创建访问镜像服务器的证书*/


IF EXISTS(select * from sys.certificates WHERE name=’HOST_B_cert’)

DROP CERTIFICATE HOST_B_cert

GO


create CERTIFICATE

HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = ‘D:\MIRROR\HOST_B_cert.cer’;

GO

/*授权*/

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];

GO

3.2、镜像数据库

同样在Mirror上创建用于访问Main的用户并授权,代码如下:

/*创建用户,用于访问MAIN*/

IF EXISTS(select * from sys.sql_logins WHERE name=’HOST_A_login’)

DROP LOGIN HOST_A_login

GO

 

IF EXISTS(select * from sys.database_principals WHERE name=’HOST_A_user’)

DROP USER HOST_A_user

GO

 

create LOGIN HOST_A_login WITH PASSWORD = ‘123456789′;

create USER HOST_A_user FOR LOGIN HOST_A_login;



IF EXISTS(select * from sys.certificates WHERE name=’HOST_A_cert’)

DROP CERTIFICATE HOST_A_cert

GO

create CERTIFICATE


HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = ‘D:\MIRROR\HOST_A_cert.cer’;

GO

/*授权*/

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];

GO


四、准备要做镜像的数据库

我们假设要镜像的数据库叫做db_mirror,因为镜像的数据库是数据库级别的,而不是针对实例的,所以如果有多个数据库需要镜像,就重复下面代码和第五点。

在Main服务器执行:

backup database db_mirror to disk= 'D:\backupdb_mirror20160408.bak'
with init
go

在Mirror服务器执行:

restore database db_mirror from disk = 'D:\backup\db_mirror.bak' with replace,norecovery;

五、开启镜像

还有一个很关键的步骤,一定不能颠倒顺序哦!!!!

那就是设置服务器之间的伙伴关系,一定要先在Mirror服务器上设置,代码如下:

/*设置伙伴*/

ALTER DATABASE db_mirror SET PARTNER=’TCP://192.168.0.2:5022′;

/*IP地址为Main服务器的地址*/

GO


然后我们再在回到Main服务器上执行如下代码:

/*设置伙伴*/

ALTER DATABASE db_mirror SET PARTNER=’TCP://192.168.0.3:5022′;

/*IP为Mirror服务器的地址*/

GO

/*设置安全选项*/

ALTER DATABASE db_mirror SET SAFETY FULL

执行到这一步,如果您的Main服务器上的db_mirror的状态为“主体,已同步”,Mirror服务器上db_mirror的状态 为“镜像,已同步/正在还原”那么就大功告成咯!

 

有时按照上面步骤做的时候可能在最后一步,开启镜像会遇到下面的问题:

一、镜像时遇到“无法访问或不存在。请检查网络地址名称,并检查本地和远程端点的端口是否正常。

服务器网络地址 "TCP://192.168.1.118:5022" 无法访问或不存在。请检查网络地址名称,并检查本地和远程端点的端口是否正常运行。 (Microsoft SQL Server,错误: 1418)

解决办法:

1、确定服务器双方的能互相在镜像的端口(创建端点时指定的端口,默认为5022)能访问,可能通过telnet和netstat命令来测试。

2、如果你确定网络都是通的,但是还报上面的错误,看看SQL Server日志,可能让你快速找到问题的所在。如下图:


可以看到比较详细的错误:

日期     2016/4/7 18:42:23
日志     SQL Server (当前 - 2016/4/7 19:22:00)

源     Logon

消息
Database Mirroring login attempt failed with error: 'Connection handshake failed. The certificate used by the peer is invalid due to the following reason: The database principal has no mapping to a server principal. State 89.'.  [CLIENT: 192.168.1.118]

上面提示很明显是身份验证出了问题。我遇到上面的问题一般是通过重新建证书、端点和用户的,不过在重建之前要把原来的删除掉,这时要注意删除的顺序,不然要报错。例如:


drop login HOST_A_login
drop USER HOST_A_user
drop certificate HOST_A_cert

drop ENDPOINT Endpoint_Mirroring
drop CERTIFICATE HOST_A_cert
drop MASTER KEY


二、数据库可能有尚未备份的大容量日志记录更改,所以无法启用数据库镜像。必须在镜像上还原主体数据库的上一次日志备份。


解决办法:

这种问题,一般都是用主体服务器上的库的备份在镜像数据库上还原,而备份里面的事务不是最主体最后、最新的。

可能在备份后在镜像数据库还原完成之前又有新的事务产生。为了避免这样的问题一般会数据库设置为只读,如果遇到这种情况一般可以通过,重新备份一个备份在镜像数据库还原,备份类型这时候要选“事务日志”。


小编蓝狐