通过证书的方式实现SQL Server 数据库镜像

通过证书的方式实现SQL Server 数据库镜像

一、为主体数据库配置出站连接

--创建数据库主密钥

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '111111';

GO

--在MIR-A上为数据库实例创建证书

USE master;

CREATE CERTIFICATE MIR_A_cert

WITH SUBJECT = 'MIR_A certificate',

START_DATE = '01/27/2008',

EXPIRY_DATE = '01/01/2099'

GO

--在MIR-A上使用上面创建的证书为数据库实例创建镜像端点

CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

AS TCP (

LISTENER_PORT=5024

, LISTENER_IP = ALL

)

FOR DATABASE_MIRRORING (

AUTHENTICATION = CERTIFICATE MIR_A_cert

, ENCRYPTION = REQUIRED ALGORITHM RC4

, ROLE = ALL

);

GO

--备份MIR-A上的证书并拷贝到MIR-B，MIR-W上

BACKUP CERTIFICATE MIR_A_cert TO FILE = 'C:/MIR_A_cert.cer';

GO

二、为镜像数据库配置出站连接

--创建数据库主密钥

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '111111';

GO

--在MIR-B上为数据库实例创建证书

CREATE CERTIFICATE MIR_B_cert

WITH SUBJECT = 'MIR_B certificate for database mirroring',

START_DATE = '01/27/2008',

EXPIRY_DATE = '01/01/2099';

GO

--在MIR-B上使用上面创建的证书为数据库实例创建镜像端点

CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

AS TCP (

LISTENER_PORT=5024,

LISTENER_IP = ALL

)

FOR DATABASE_MIRRORING (

AUTHENTICATION = CERTIFICATE MIR_B_cert,

ENCRYPTION = REQUIRED ALGORITHM RC4,

ROLE = ALL

);

GO

--备份MIR-B上的证书并拷贝到MIR-A，MIR-W上

BACKUP CERTIFICATE MIR_B_cert TO FILE = 'C:/MIR_B_cert.cer';

GO

三、为见证数据库配置出站连接

--创建数据库主密钥

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '111111';

GO

--在MIR-W上为数据库实例创建证书

CREATE CERTIFICATE MIR_W_cert

WITH SUBJECT = 'MIR_W certificate for database mirroring',

START_DATE = '01/27/2008',

EXPIRY_DATE = '01/01/2099';

GO

--在MIR-W上使用上面创建的证书为数据库实例创建镜像端点

CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

AS TCP (

LISTENER_PORT=5024,

LISTENER_IP = ALL

)

FOR DATABASE_MIRRORING (

AUTHENTICATION = CERTIFICATE MIR_W_cert,

ENCRYPTION = REQUIRED ALGORITHM RC4,

ROLE = ALL

);

GO

--备份MIR-W上的证书并拷贝到MIR-A，MIR-B上

BACKUP CERTIFICATE MIR_W_cert TO FILE = 'C:/MIR_W_cert.cer';

GO

四、为主体数据库配置入站连接

--在MIR-A上为MIR-B创建登陆

USE master;

CREATE LOGIN MIR_B_login WITH PASSWORD = '111111';

GO

--为以上登陆创建一个用户

CREATE USER MIR_B_user FOR LOGIN MIR_B_login;

GO

--绑定证书到用户

CREATE CERTIFICATE MIR_B_cert

AUTHORIZATION MIR_B_user

FROM FILE = 'C:/MIR_B_cert.cer'

GO

--在镜像端点上为登陆赋予CONNECT权限

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [MIR_B_login];

GO

--在MIR_A上为MIR_W创建登陆

USE master;

CREATE LOGIN MIR_W_login WITH PASSWORD = '111111';

GO

--为以上登陆创建一个用户

CREATE USER MIR_W_user FOR LOGIN MIR_W_login;

GO

--绑定证书到用户

CREATE CERTIFICATE MIR_W_cert

AUTHORIZATION MIR_W_user

FROM FILE = 'C:/MIR_W_cert.cer'

GO

--在镜像端点上为登陆赋予CONNECT权限

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [MIR_W_login];

GO

五、为镜像数据库配置入站连接

--在MIR-B上为MIR-A创建登陆

USE master;

CREATE LOGIN MIR_A_login WITH PASSWORD = '111111';

GO

--为以上登陆创建一个用户

CREATE USER MIR_A_user FOR LOGIN MIR_A_login;

GO

--绑定证书到用户

CREATE CERTIFICATE MIR_A_cert

AUTHORIZATION MIR_A_user

FROM FILE = 'C:/MIR_A_cert.cer'

GO

--在镜像端点上为登陆赋予CONNECT权限

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [MIR_A_login];

GO

--在MIR-B上为MIR-W创建登陆

USE master;

CREATE LOGIN MIR_W_login WITH PASSWORD = '111111';

GO

--为以上登陆创建一个用户

CREATE USER MIR_W_user FOR LOGIN MIR_W_login;

GO

--绑定证书到用户

CREATE CERTIFICATE MIR_W_cert

AUTHORIZATION MIR_W_user

FROM FILE = 'C:/MIR_W_cert.cer'

GO

--在镜像端点上为登陆赋予CONNECT权限

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [MIR_W_login];

GO

六、为见证数据库配置入站连接

--在MIR-W上为MIR-A创建登陆

USE master;

CREATE LOGIN MIR_A_login WITH PASSWORD = '111111';

GO

--为以上登陆创建一个用户

CREATE USER MIR_A_user FOR LOGIN MIR_A_login;

GO

--绑定证书到用户

CREATE CERTIFICATE MIR_A_cert

AUTHORIZATION MIR_A_user

FROM FILE = 'C:/MIR_A_cert.cer'

GO

--在镜像端点上为登陆赋予CONNECT权限

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [MIR_A_login];

GO

--在MIR-W上为MIR-B创建登陆

USE master;

CREATE LOGIN MIR_B_login WITH PASSWORD = '111111';

GO

--为以上登陆创建一个用户

CREATE USER MIR_B_user FOR LOGIN MIR_B_login;

GO

--绑定证书到用户

CREATE CERTIFICATE MIR_B_cert

AUTHORIZATION MIR_B_user

FROM FILE = 'C:/MIR_B_cert.cer'

GO

--在镜像端点上为登陆赋予CONNECT权限

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [MIR_B_login];

GO

七、在MIR-A上备份数据库

--修改Northwind数据库使用完整恢复模式

ALTER DATABASE Northwind

SET RECOVERY FULL;

GO

--在 MIR_A 的主体服务器实例上备份数据库

--通过安全方法，将 C:/Northwind.bak 复制到MIR_B上

USE Northwind

BACKUP DATABASE Northwind

TO DISK = 'C:/Northwind.bak'

WITH FORMAT

GO

八、转到MIR-B上去恢复数据库

--在MIR-B的镜像服务器实例上还原数据库：

RESTORE DATABASE Northwind

FROM DISK = 'C:/Northwind.bak'

WITH NORECOVERY

GO

九、为镜像数据库配置镜像伙伴

--在 MIR-B 的镜像服务器实例上，将 MIR-A 上的服务器实例设置为伙伴

ALTER DATABASE Northwind

SET PARTNER = 'TCP://MIR-A:5024';

GO

十、为主体数据库配置镜像伙伴和见证服务器

--在 MIR-A 的主体服务器实例上，将MIR-B上的服务器实例设置为伙伴

ALTER DATABASE Northwind

SET PARTNER = 'TCP://MIR-B:5024';

GO

--在 MIR-A 的主体服务器实例上，将 MIR-W 上的服务器实例设置为见证服务器

ALTER DATABASE Northwind

SET WITNESS = 'TCP://MIR-W:5024';

GO

十一、配置数据库镜像事务安全级别

ALTER DATABASE Northwind SET SAFETY FULL

GO

十二、分别在MIR-A和MIR-B上查看数据库镜像的配置状态

-- 1.)通过Management studio 对象资源管理器，查看主体数据库、镜像数据库状态

-- 2.)通过Management studio 对象资源管理器中的数据库属性查看状态

-- 3.)通过系统目录视图查看数据库镜像配置情况

USE MASTER

GO

SELECT * FROM sys.database_mirroring_endpoints;

GO

SELECT * FROM sys.database_mirroring WHERE database_id =

(SELECT database_id FROM sys.databases WHERE name = 'Northwind')

GO