MSSQL 镜像

浏览数：24 / 时间：2015年06月12日

1、设置数据库CollectionDB 为完整备份模式
服务端：

USE master
ALTER DATABASE CollectionGuest SET RECOVERY FULL
GO

镜相端：

USE master
ALTER DATABASE CollectionGuest SET RECOVERY FULL
GO

2、创建证书

服务端：

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘123asd!@#‘
CREATE CERTIFICATE Host_A_cert WITH SUBJECT = ‘Host_A_certificate‘,
START_DATE=‘11/01/2014‘, EXPIRY_DATE=‘11/01/2099‘
GO
View Code

镜像端：

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘123asd!@#‘
CREATE CERTIFICATE Host_B_cert WITH SUBJECT = ‘Host_B_certificate‘,
START_DATE=‘11/01/2014‘, EXPIRY_DATE=‘11/01/2099‘
View Code

3、创建主连接的端点

服务端：

CREATE ENDPOINT Endpiont_Mirroring 
STATE = STARTED 
AS TCP (LISTENER_PORT = 5022,LISTENER_IP= ALL) 
FOR DATABASE_MIRRORING 
(AUTHENTICATION = CERTIFICATE Host_A_cert, ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );
View Code

镜像端

CREATE ENDPOINT Endpiont_Mirroring 
STATE = STARTED 
AS TCP (LISTENER_PORT = 5022,LISTENER_IP= ALL) 
FOR DATABASE_MIRRORING 
(AUTHENTICATION = CERTIFICATE Host_B_cert, ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );
View Code

4、备份证书以备建立互联（主备可并行执行）

服务端：

BACKUP CERTIFICATE HOST_A_cert TO FILE = ‘E:\DataBase_Bak\HOST_A_cert.cer‘;

镜像端：

BACKUP CERTIFICATE HOST_B_cert TO FILE = ‘E:\DataBase_Bak\HOST_B_cert.cer‘;

5、服务端与镜像端证书互换（互拷）

6、添加登陆名、用户

服务端：

CREATE LOGIN HOST_B_login WITH PASSWORD = ‘123asd!@#‘;
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = ‘E:\DataBase_Bak\HOST_B_cert.cer‘;
GRANT CONNECT ON ENDPOINT::Endpiont_Mirroring TO [HOST_B_login]
View Code

镜像端

CREATE LOGIN HOST_A_login WITH PASSWORD = ‘123asd!@#‘;
CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = ‘E:\DataBase_Bak\HOST_A_cert.cer‘;
GRANT CONNECT ON ENDPOINT::Endpiont_Mirroring TO [HOST_A_login];
View Code

7、准备备机数据库

服务端：

backup database CollectionGuest to disk= ‘E:\DataBase_Bak\backuptest.log‘
with init
GO
View Code

镜像端：

restore database CollectionGuest from disk = ‘E:\DataBase_Bak\backuptest.log‘ WITH replace ,norecovery;
View Code

8、增加镜像伙伴，必须先在镜像端上执行，再执行服务端

镜像端：

ALTER DATABASE CollectionGuest SET PARTNER = N‘TCP://192.168.0.21:5022‘;

服务端：

ALTER DATABASE CollectionGuest SET PARTNER = ‘TCP://192.168.0.30:5022‘;

9、成功验证

服务端：

镜像端：

10、镜像与主体切换

服务端：

use master;
alter database CollectionGuest set partner failover;
View Code

刷新数据库。

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

标签： class style src com log 数据 http Go si class style src com log 数据 http Go si

相关文章

随机文章

您可能还喜欢

您可能还喜欢