数据库镜像问题
-- 建立镜像主体数据库
CREATE DATABASE DB_Mirror
ON( NAME = DB_Mirror_DATA,FILENAME = N'C:\DB_Mirror.mdf')
LOG ON( NAME = DB_Mirror_LOG,FILENAME = N'C:\DB_Mirror.ldf')
ALTER DATABASE DB_Mirror SET RECOVERY FULL –设置为完整恢复模式
GO
-- 完全备份
BACKUP DATABASE DB_Mirror
TO DISK = N'C:\DB_Mirror.bak'
WITH FORMAT
GO
-- 假设主体数据库的完全备份已经复制到c:\DB_Mirror.bak
RESTORE DATABASE DB_Mirror FROM DISK = N'C:\DB_Mirror.bak'
WITH REPLACE, NORECOVERY
GO
--主机执行:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'baijunlin' --创建密钥
CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A_certificate',START_DATE = '2010-12-15'
--备机执行:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'baijunlin'
CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B_certificate',START_DATE = '2010-12-15'
--主机执行:
create ENDPOINT Endpoint_Name –端点 名称 Endpoint_Name
STATE = STARTED
AS
TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL)
FOR
DATABASE_MIRRORING
( AUTHENTICATION = CERTIFICATE HOST_A_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL )
--主机执行:
BACKUP CERTIFICATE HOST_A_cert TO FILE = 'D:\HOST_A_cert.cer'
--备机执行:
BACKUP CERTIFICATE HOST_B_cert TO FILE = 'D:\HOST_B_cert.cer'
HOST_A_cert.cer复制到备机的D:\
HOST_B_cert.cer复制到主机的D:\
主机执行:
CREATE LOGIN HOST_B_login WITH PASSWORD = 'baijunlin';
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE =
'D:\HOST_B_cert.cer';
GRANT CONNECT ON ENDPOINT:: Endpoint_Name TO [HOST_B_login];
备机执行:
CREATE LOGIN HOST_A_login WITH PASSWORD = 'baijunlin';
CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE =
'D:\HOST_A_cert.cer';
GRANT CONNECT ON ENDPOINT:: Endpoint_Name_B TO [HOST_A_login];
前面的步骤都成功了!
到这一步:
主机上执行:
ALTER DATABASE DB_Mirror SET PARTNER = 'TCP://192.168.0.3:5022';
错误提示“消息 1418,级别 16,状态 1,第 1 行
服务器网络地址 "TCP://192.168.1.34:5022" 无法访问或不存在。请检查网络地址名称,并检查本地和远程端点的端口是否正常运行。
”
已经打了Sp3,Sp2补丁,电脑都在同一个局域网内的!端口号和IP绝对没错!
求高手帮忙啊!急死我了!