数据库镜像证书验证配置和维护

 

 可下载附件

/***********************主机ANNATROV*******************************/

/*

数据库镜像

主机:ANNATROV

备机:JOHN

见证机:KATE

以上三台机器均:XP SP3;SQL SERVER 2008 SP1.备机,见证机均为VM虚拟机,主机为本机

由于系统是XP,所以没有做域.因此采用证书认证方式来连接.所以,如果要用于透明数据加密的请注意.MASTER证书可以共用,需要建的是数据库证书.

数据库要能够互相访问,不懂的端口的可以把防火墙关掉.

由于数据库镜像只对数据库内数据进行同步,因此主库上的(msdb)作业,(master)登陆名需要手工同步,以免造成不必要的麻烦.

*/

/*

如果数据库MASTER有加密码了,可以删除.

删除证书

--SET ENCRYPTION OFF

DROP CERTIFICATE HOST_C_cert  --删除加密的证书,就是加密码的数据库证书.

drop MASTER KEY   --删除主密钥,也就是MASTER的

drop database encryption key

*/

--创建证书

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'OOOooo';

 CREATE CERTIFICATE ANNATROV WITH SUBJECT = 'ANNATROV' ,

START_DATE = '2010-07-23';

 

--创建链接端点

CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

AS

TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )

FOR

DATABASE_MIRRORING

( AUTHENTICATION = CERTIFICATE ANNATROV , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );

 

--备份证书,并拷贝证书至各机确保互联

 BACKUP CERTIFICATE ANNATROV TO FILE = 'g:\test\ANNATROV.cer';

 /*主机,备机,见证机三机都备份完证书以后,然后再将各自的证书拷贝到其他两台机器上,因为下面的添加登陆名创建的账号需要各自的

证书来验证.比如主机ANNATROV上,就需要有JOHN,KATE的证书*/

--添加登陆名,用户

    --备机用户--JOHN

CREATE LOGIN JOHN WITH PASSWORD = '123456';

CREATE USER JOHN FOR LOGIN JOHN;

CREATE CERTIFICATE JOHN AUTHORIZATION JOHN FROM FILE = 'g:\test\JOHN.cer'; --证书验证

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

    --见证机用户--KATE

CREATE LOGIN KATE WITH PASSWORD = '123456';

CREATE USER KATE FOR LOGIN KATE;

CREATE CERTIFICATE KATE AUTHORIZATION KATE FROM FILE = 'g:\test\KATE.cer';--证书验证

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

/*

--手工同步登陆名,密码

--镜像的缺点就是不能同步数据库用户名和作业,因为需要手工同这两项.

    --在主机上找出合建名

USE master;

select sid,name from syslogins;

    --在备机上创建登陆名

 USE master;

 exec sp_addlogin

 @loginame = 'Data_Syn',

 @passwd = '123,./',

 @sid = 0x9FD492E8D353394AA8893CE7B0EC1E08;

 */

 --等各主机都执行以上步骤再可以执行以下语句.

 --建立镜像

ALTER DATABASE PpP SET PARTNER = 'TCP://john:5022' --先在备机执行然后再这个

ALTER DATABASE PpP SET WITNESS = 'TCP://kate:5022' --见证机上不需要执行

/**************************备机 JOHN***************************/

 

/*

如果数据库MASTER有加密码了,可以删除.

删除证书

--SET ENCRYPTION OFF

DROP CERTIFICATE HOST_C_cert  --删除加密的证书,就是加密码的数据库证书.

drop MASTER KEY   --删除主密钥,也就是MASTER的

drop database encryption key

*/

--创建证书

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'OOOooo';

CREATE CERTIFICATE JOHN WITH SUBJECT = 'JOHN',

START_DATE = '2010-07-23';

---------------------

--创建点链接

CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

AS

TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )

FOR

DATABASE_MIRRORING

( AUTHENTICATION = CERTIFICATE JOHN , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );

--备份证书,并拷贝证书至各机确保互联

BACKUP CERTIFICATE JOHN TO FILE = 'c:\sqlt\JOHN.cer';

--添加登陆名,用户

    --主机用户--ANNATROV

CREATE LOGIN ANNATROV WITH PASSWORD = '123456';

CREATE USER ANNATROV FOR LOGIN ANNATROV;

CREATE CERTIFICATE ANNATROV AUTHORIZATION ANNATROV FROM FILE = 'c:\sqlt\ANNATROV.cer';

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

    --见证机用户

CREATE LOGIN KATE WITH PASSWORD = '123456';

CREATE USER KATE FOR LOGIN KATE;

CREATE CERTIFICATE KATE AUTHORIZATION KATE FROM FILE = 'c:\sqlt\KATE.cer';

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

--在备机上创建登陆名

/*

 USE master;

 exec sp_addlogin

 @loginame = 'Data_Syn',

 @passwd = '123,./',

 @sid = 0x9FD492E8D353394AA8893CE7B0EC1E08;

 */

--建立镜像

ALTER DATABASE PpP SET PARTNER='TCP://annatrov:5022' --先在备机执行再在主机执行

/***********************见证机 KATE**********************************/

/*

如果数据库MASTER有加密码了,可以删除.

删除证书

--SET ENCRYPTION OFF

DROP CERTIFICATE HOST_C_cert  --删除加密的证书,就是加密码的数据库证书.

drop MASTER KEY   --删除主密钥,也就是MASTER的

drop database encryption key

*/

--创建证书

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'OOOooo';

CREATE CERTIFICATE KATE WITH SUBJECT = 'KATE',

START_DATE = '2010-07-23';

-----------------

--创建端点链接

CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

AS

TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )

FOR

DATABASE_MIRRORING

( AUTHENTICATION = CERTIFICATE KATE , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );

--备份证书,并拷贝证书至各机确保互联

 BACKUP CERTIFICATE KATE TO FILE = 'c:\sqlt\KATE.cer';

--添加登陆名,用户

    --主机登陆用户

CREATE LOGIN ANNATROV WITH PASSWORD = '123456';

CREATE USER ANNATROV FOR LOGIN ANNATROV;

CREATE CERTIFICATE ANNATROV AUTHORIZATION ANNATROV FROM FILE = 'c:\sqlt\ANNATROV.cer';

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

    --备机登陆用户

CREATE LOGIN JOHN WITH PASSWORD = '123456';

CREATE USER JOHN FOR LOGIN JOHN;

CREATE CERTIFICATE JOHN AUTHORIZATION JOHN FROM FILE = 'c:\sqlt\JOHN.cer';

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

/*************************主备手工切换******************************/

--测试:主备互换

USE master;

ALTER DATABASE PpP SET PARTNER FAILOVER;

 

USE master;

ALTER DATABASE PpP SET PARTNER SAFETY FULL; --事务安全,同步模式

ALTER DATABASE PpP SET PARTNER SAFETY OFF;  --事务不安全,异步模式,高性能

----------------

--测试:主备互换

USE master;

ALTER DATABASE PpP SET PARTNER FAILOVER;

--主服务器Down掉,备机紧急启动并且开始服务

--备机执行

USE master;

ALTER DATABASE PpP SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;

--备机执行：

USE master;

ALTER DATABASE PpP SET PARTNER RESUME; --恢复镜像

ALTER DATABASE PpP SET PARTNER FAILOVER; --切换主备

--结果图

 

 

作者:ANNATROV

时间:2010-8-4