【原创】SQL 2005镜像操作--证书认证

原创作品，允许转载，转载时请务必以超链接形式标明文章 原始出处 、作者信息和本声明。否则将追究法律责任。http://blog.chinaunix.net/space.php?uid=9419692&do=blog&id=3182622

在没有域的工作组环境中必须配置证书才能够镜像成功,首先在三台服务器上配置出站连接，并把三个证

书互相复制到每台服务器上，然后分别配置入站连接

一、主体服务器
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'your password';
GO
USE master;
CREATE CERTIFICATE A_cert
WITH SUBJECT = 'A certificate for database mirroring',
start_date = '11/01/2007',
EXPIRY_DATE = '10/31/2099' ;
GO

USE master;
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5024
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE A_cert
, ENCRYPTION = REQUIRED ALGORITHM RC4
, ROLE = ALL
);
GO
BACKUP CERTIFICATE A_cert TO FILE = 'e:\key\A_cert.cer';
GO

--copy A_CERT.cer to other servers

--================以上据配置完出站========================

--================以下配置入站连接========================

-- 1 ==配置镜像服务器入站

USE master;
CREATE LOGIN B_login WITH PASSWORD = 'your password';
GO

USE master;
CREATE USER B_user FOR LOGIN B_login;
GO
SELECT * FROM sys.sysusers;
USE master;
CREATE CERTIFICATE B_cert
AUTHORIZATION B_user
FROM FILE = 'e:\key\B_cert.cer'
GO

SELECT * FROM sys.certificates
USE master;
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [B_login];
GO

-- 2 ===配置见证服务器入站
USE master;
CREATE LOGIN C_login WITH PASSWORD = 'your password';
GO

USE master;
CREATE USER C_user FOR LOGIN C_login;
GO
SELECT * FROM sys.sysusers;
USE master;
CREATE CERTIFICATE C_cert
AUTHORIZATION C_user
FROM FILE = 'e:\key\C_cert.cer'
GO

SELECT * FROM sys.certificates
USE master;
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [C_login];
GO

二、镜像服务器
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'your password';
GO

CREATE CERTIFICATE B_cert
WITH SUBJECT = B certificate for database mirroring',
start_date = '01/01/2005',
EXPIRY_DATE = '10/31/2099' ;
GO

CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5024
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE B_cert
, ENCRYPTION = REQUIRED ALGORITHM RC4
, ROLE = ALL
);
GO
--Backup HOST_B certificate.
BACKUP CERTIFICATE B_cert TO FILE = 'e:\key\B_cert.cer';
GO

--======================出站配置完毕====

--======================配置入站========

-- 1 ==配置主体服务器入站
USE master;

CREATE LOGIN A_login WITH PASSWORD = 'your password';
GO

CREATE USER A_user FOR LOGIN A_login
GO
--Obtain HOST_A certificate. (See the note
-- preceding this example.)
--Asscociate this certificate with the user, HOST_A_user.
CREATE CERTIFICATE A_cert
AUTHORIZATION A_user
FROM FILE = 'e:\key\A_cert.cer';
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO A_login
GO

-- 2 ===配置见证服务器入站
USE master;
CREATE LOGIN C_login WITH PASSWORD = 'your password';
GO

USE master;
CREATE USER C_user FOR LOGIN C_login;
GO
SELECT * FROM sys.sysusers;
USE master;
CREATE CERTIFICATE C_cert
AUTHORIZATION C_user
FROM FILE = 'e:\key\C_cert.cer'
GO

SELECT * FROM sys.certificates
USE master;
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [C_login];
GO

三、见证服务器

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'your password';
GO
USE master;
CREATE CERTIFICATE C_cert
WITH SUBJECT = 'C certificate for database mirroring',
start_date = '01/01/2005',
EXPIRY_DATE = '10/31/2099' ;
GO

USE master;
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5025
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE C_cert
, ENCRYPTION = REQUIRED ALGORITHM RC4
, ROLE = ALL
);
GO
BACKUP CERTIFICATE C_cert TO FILE = 'e:\key\C_cert.cer';
GO

--copy C_CERT.cer to 其他两台服务器

--================以上据配置完出站========================

--================以下配置入站连接========================

-- 1 ==配置主体服务器入站

USE master;
CREATE LOGIN A_login WITH PASSWORD = 'your password';
GO

USE master;
CREATE USER A_user FOR LOGIN A_login;
GO

USE master;
CREATE CERTIFICATE A_cert
AUTHORIZATION A_user
FROM FILE = 'e:\key\A_cert.cer'
GO

USE master;
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [A_login];
GO

-- 2 ===配置镜像服务器入站
USE master;
CREATE LOGIN B_login WITH PASSWORD = 'your password';
GO

USE master;
CREATE USER B_user FOR LOGIN B_login;
GO

USE master;
CREATE CERTIFICATE B_cert
AUTHORIZATION B_user
FROM FILE = 'e:\key\B_cert.cer'
GO

USE master;
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [B_login];
GO

最后我使用的是向导启用的镜像功能，注意的是用向导在最后不必填写用户帐号。

注意:

故障:
服务器网络地址 "TCP://test-server:5022" 无法访问或不存在。请检查网络地址名称，并检查本地

和远程端点的端口是否正常运行。

解决办法:
如果是这种情况，请检查一下每台SQL2k5引擎的远程TCP连接是否已经被启用，具体通过外围应用配

置工具-->服务和连接的外围应用配置器 --> Database Engine --> 远程连接，在这里启用"同时使用

TCP/IP和named pipes"，启用SQLBrowser服务，重新启动SQL Server，然后就应该可以了。

本文出自 “聆听未来” 博客，请务必保留此出处http://blog.chinaunix.net/space.php?uid=9419692&do=blog&id=3182622