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

 可下载附件



/***********************主机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