环境如下:
主数据库:192.168.1.179 镜像数据库:192.168.1.183 见证服务器:192.168.1.117 数据库:Crn
在主数据库中执行如下操作:
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='123456789';
ELSE
create MASTER KEY ENCRYPTION BY PASSWORD='123456789';
GO
SELECT * FROM sys.key_encryptions; --查看密钥
go
--向数据库中添加证书。
IF EXISTS(select * from sys.database_mirroring_endpoints WHERE name='Endpoint_Mirroring')
DROP ENDPOINT Endpoint_Mirroring
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 = '2009-01-01';
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 AES ,
ROLE = PARTNER );
GO
SELECT * FROM sys.database_mirroring_endpoints;
GO
--备份证书
BACKUP CERTIFICATE HOST_A_cert TO FILE = 'D:\MIRROR\HOST_A_cert.cer';
GO
--在服务器之间手动拷贝证书,保证每个服务只器都有所有证书
--创建用户,用于访问MIRROR
IF EXISTS(select * from sys.certificates WHERE name='HOST_B_cert')
DROP CERTIFICATE HOST_B_cert
GO
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
--在host_a中为host_b建立登陆名
create LOGIN HOST_B_login WITH PASSWORD = '123456789';
--为host_b登陆名创建一个用户
create USER HOST_B_user FOR LOGIN HOST_B_login;
--使用这个用户与host_b的证书相关联
create CERTIFICATE
HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'D:\MIRROR\HOST_B_cert.cer';
GO
--授予对远程镜像端点的登录名的 CONNECT 权限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];
GO
--创建用户,用于访问WITNESS
IF EXISTS(select * from sys.certificates WHERE name='HOST_C_cert')
DROP CERTIFICATE HOST_C_cert
GO
IF EXISTS(select * from sys.sql_logins WHERE name='HOST_C_login')
DROP LOGIN HOST_C_login
GO
IF EXISTS(select * from sys.database_principals WHERE name='HOST_C_user')
DROP USER HOST_C_user
GO
create LOGIN HOST_C_login WITH PASSWORD = '123456789';
create USER HOST_C_user FOR LOGIN HOST_C_login;
create CERTIFICATE
HOST_C_cert AUTHORIZATION HOST_C_user FROM FILE = 'D:\MIRROR\HOST_C_cert.cer';
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_C_login];
GO
--拷贝用户
SELECT [name],[sid] FROM sys.database_principals WHERE [name]='BetterDev'
--备份数据库
--设置伙伴
ALTER DATABASE northwind SET PARTNER='TCP://192.168.1.183:5022';
GO
--设置见证
ALTER DATABASE NORTHWIND SET WITNESS='TCP://192.168.1.117:5022';
GO
--设置安全选项
ALTER DATABASE NORTHWIND SET SAFETY FULL
在镜像服务器中运行的代码:
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='123456789';
ELSE
create MASTER KEY ENCRYPTION BY PASSWORD='123456789';
GO
SELECT * FROM sys.key_encryptions;
go
--向数据库中添加证书。
IF EXISTS(select * from sys.database_mirroring_endpoints WHERE name='Endpoint_Mirroring')
DROP ENDPOINT Endpoint_Mirroring
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 = '2008-01-01';
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
IF EXISTS(select * from sys.certificates WHERE name='HOST_A_cert')
DROP CERTIFICATE HOST_A_cert
GO
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
--在host_b中为host_a建立登陆名
create LOGIN HOST_A_login WITH PASSWORD = '123456789';
--为该登陆名建立用户
create USER HOST_A_user FOR LOGIN HOST_A_login;
--使该用户同host_a的证书相关联
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
--创建用户,用于访问WITNESS
IF EXISTS(select * from sys.certificates WHERE name='HOST_C_cert')
DROP CERTIFICATE HOST_C_cert
GO
IF EXISTS(select * from sys.sql_logins WHERE name='HOST_C_login')
DROP LOGIN HOST_C_login
GO
IF EXISTS(select * from sys.database_principals WHERE name='HOST_C_user')
DROP USER HOST_C_user
GO
--
create LOGIN HOST_C_login WITH PASSWORD = '123456789';
create USER HOST_C_user FOR LOGIN HOST_C_login;
create CERTIFICATE
HOST_C_cert AUTHORIZATION HOST_C_user FROM FILE = 'D:\MIRROR\HOST_C_cert.cer';
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_C_login];
GO
--添加用户
EXEC sp_addlogin
@loginname=''
@passwd=''
@sid='';
GO
--手动还原数据库
--设置伙伴
ALTER DATABASE northwind SET PARTNER='TCP://192.168.1.179:5022';
GO
在见证服务器中运行的代码:
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='123456789';
ELSE
create MASTER KEY ENCRYPTION BY PASSWORD='123456789';
GO
SELECT * FROM sys.key_encryptions;
go
--向数据库中添加证书。
IF EXISTS(select * from sys.database_mirroring_endpoints WHERE name='Endpoint_Mirroring')
DROP ENDPOINT Endpoint_Mirroring
GO
IF EXISTS(select * from sys.certificates WHERE name='HOST_C_cert')
DROP CERTIFICATE HOST_C_cert;
GO
CREATE CERTIFICATE HOST_C_cert WITH SUBJECT = 'HOST_C certificate',
START_DATE = '2008-01-01';
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_C_cert ,
ENCRYPTION = REQUIRED ALGORITHM RC4 ,
ROLE = WITNESS );
GO
SELECT * FROM sys.database_mirroring_endpoints;
GO
--备份证书
BACKUP CERTIFICATE HOST_C_cert TO FILE = 'E:\MIRROR\HOST_C_cert.cer';
GO
--在服务器之间手动拷贝证书,保证每个服务器都有所有证书
--创建用户,用于访问MAIN
IF EXISTS(select * from sys.certificates WHERE name='HOST_A_cert')
DROP CERTIFICATE HOST_A_cert
GO
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;
create CERTIFICATE
HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'E:\MIRROR\HOST_A_cert.cer';
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];
GO
--创建用户,用于访问镜像
IF EXISTS(select * from sys.certificates WHERE name='HOST_B_cert')
DROP CERTIFICATE HOST_B_cert
GO
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;
create CERTIFICATE
HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'E:\MIRROR\HOST_B_cert.cer';
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];
GO
come from:http://hi.baidu.com/ownswords/blog/item/3a5305ddfc1e1ec88c102984.html
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/90618/viewspace-684011/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/90618/viewspace-684011/