首页 > 代码库 > MSSQL 镜像

MSSQL 镜像

2024-07-31 15:28:09   219人阅读

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

USE masterALTER DATABASE CollectionGuest SET RECOVERY FULLGO

镜相端：

USE masterALTER DATABASE CollectionGuest SET RECOVERY FULLGO

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 initGO
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

刷新数据库。

MSSQL 镜像


联系
我们