您的位置:首页 > 博客中心 > 数据库 >

MSSQL 镜像

时间:2022-03-14 02:05

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

USE master
ALTER DATABASE CollectionGuest SET RECOVERY FULL
GO

镜相端:

USE master
ALTER DATABASE CollectionGuest SET RECOVERY FULL
GO

2、创建证书

服务端:

gxlsystem.com,布布扣gxlsystem.com,布布扣
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

镜像端:

gxlsystem.com,布布扣gxlsystem.com,布布扣
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、创建主连接的端点

服务端:

gxlsystem.com,布布扣gxlsystem.com,布布扣
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

镜像端

gxlsystem.com,布布扣gxlsystem.com,布布扣
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、添加登陆名、用户

服务端:

gxlsystem.com,布布扣gxlsystem.com,布布扣
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

镜像端

gxlsystem.com,布布扣gxlsystem.com,布布扣
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、准备备机数据库

服务端:

gxlsystem.com,布布扣gxlsystem.com,布布扣
backup database CollectionGuest to disk= ‘E:\DataBase_Bak\backuptest.log‘
with init
GO
View Code

镜像端:

gxlsystem.com,布布扣gxlsystem.com,布布扣
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、成功验证

服务端:

gxlsystem.com,布布扣

镜像端:

gxlsystem.com,布布扣

10、镜像与主体切换

服务端:

gxlsystem.com,布布扣gxlsystem.com,布布扣
use master;
alter database CollectionGuest set partner failover;
View Code

刷新数据库。

热门排行

今日推荐

热门手游