在没有域的工作组环境中必须配置证书才能够镜像成功,首先在三台服务器上配置出站连接，并把三个证
书互相复制到每台服务器上，然后分别配置入站连接
一、主体服务器
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
