SQL Server数据库镜像配置步骤

一、使用证书实现主备SQL Server实例的互通
1.在主机和备机上分别创建证书
在主机上执行如下语句:
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'abcdefg';
CREATE CERTIFICATE CERT_HOST_A WITH SUBJECT = 'HOST_A certificate' ,
START_DATE = '01/01/2010';
GO
在备机上执行如下语句:
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'abcdefg';
CREATE CERTIFICATE CERT_HOST_B WITH SUBJECT = 'HOST_B certificate' ,
START_DATE = '01/01/2010';
GO
2.创建连接的端点
分别在主备机上执行如下语句:
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS
TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )
FOR
DATABASE_MIRRORING
( AUTHENTICATION = CERTIFICATE A1 , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );
GO
3.备份证书
在主机上执行如下语句,备份主机上已创建好的证书
BACKUP CERTIFICATE CERT_HOST_A TO FILE = 'D:\CERT_HOST_A .cer';
在备机上执行如下语句,备份备机上已创建好的证书
BACKUP CERTIFICATE CERT_HOST_B TO FILE = 'D:\CERT_HOST_B.cer';
4.互换证书
将HOST_A_cert.cer复制到备机的D盘,HOST_B_cert.cer复制到主机的D盘.
5.添加登陆名和用户
在主机上执行如下语句,以添加登陆名和用户
CREATE LOGIN HOST_B_Login WITH PASSWORD = 'abcdefg';
CREATE USER HOST_B_User FOR LOGIN  HOST_B_Login;
CREATE CERTIFICATE HOST_B_CERT AUTHORIZATION HOST_B_User from FILE = 'D:\CERT_HOST_B.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_Login];
在备机上执行如下语句,以添加登陆名和用户
CREATE LOGIN HOST_A_Login WITH PASSWORD = 'abcdefg';
CREATE USER HOST_A_User FOR LOGIN  HOST_A_Login;
CREATE CERTIFICATE HOST_A_CERT AUTHORIZATION HOST_A_User from FILE = 'D:\CERT_HOST_A.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_Login];
二、建立镜像
1.同步登陆名和密码,避免出现孤立用户
在主机上执行如下语句,以获得登陆名和sid
USE master;
select sid,name from syslogins;
在备机上执行如下语句,以添加主机上的登陆名
USE master;
exec sp_addlogin 
@loginame = '<LoginName>',