一、使用证书实现主备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 CERT_HOST_A, ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );

GO

在备机上执行如下语句:

CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS
TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )
FOR
DATABASE_MIRRORING
( AUTHENTICATION = CERTIFICATE CERT_HOST_B, 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 = '', --主机上的登陆名
@passwd = '', --对应的密码
@sid =  ; --主机上的sid
2.准备备用数据库

在主机上对需要进行镜像的数据库进行完整备份和事物日志备份,并将备份文件复制到备机上;

在备机上进行数据库还原和事务日志还原,还原时都需要选择[RESTORE WITH NORECOVERY]

3.建立镜像

在主机上执行如下语句:

ALTER DATABASE TESTDB SET PARTNER = 'TCP://192.168.1.10:5022';

在备机上执行如下语句:

ALTER DATABASE TESTDB SET PARTNER = 'TCP://192.168.1.11:5022';

执行成功后,主机上的数据库状态显示为[主体,已同步],主机上的数据库状态显示为[镜像,已同步/正在还原…]

三、实用操作

1.主备切换

在主机上执行如下语句,执行完毕后主机即成为备机,备机成为主机

USE master;
ALTER DATABASE TESTDB SET PARTNER FAILOVER;

2.主机Down掉时,启动备机提供服务

在备机上执行如下语句(只有在备机不能提供服务时方能执行成功),完全恢复数据库后开始提供服务

USE master;
ALTER DATABASE TESTDB SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;

3.主服务器恢复后重新设定镜像

主服务器恢复后需要在主机上执行如下语句,以恢复镜像并将主机重新切换设定为主机
USE master;
ALTER DATABASE TESTDB SET PARTNER RESUME; --恢复镜像
ALTER DATABASE TESTDB SET PARTNER FAILOVER; --切换主备

4.切换运行模式

在主机上执行如下语句,可将运行模式切换为高安全的同步模式

USE master;
ALTER DATABASE TESTDB SET PARTNER SAFETY FULL;

在主机上执行如下语句,可将运行模式切换为高性能的异步模式

USE master;
ALTER DATABASE TESTDB SET PARTNER SAFETY OFF;