sqlserver镜像搭建

来源：互联网发布：网络文化节上的讲话编辑：程序博客网时间：2024/06/05 03:05

--主体创建数据库
USE [master]
GO
CREATE DATABASE [DemoDB]
GO
ALTER DATABASE [DemoDB] SET RECOVERY FULL WITH NO_WAIT
GO
--主体：创建证书和备份
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'kk_2012@';
GO
CREATE CERTIFICATE Cert_kk_db1_mssqlserver
WITH SUBJECT = 'Cert_kk_db1_mssqlserver',
START_DATE = '2015-06-01',EXPIRY_DATE = '2018-06-01';
GO
BACKUP CERTIFICATE Cert_kk_db1_mssqlserver
TO FILE = 'C:\Databases\Cert_kk_db1_mssqlserver.cer';
GO
--镜像：创建证书和备份
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'kk_2012@';
GO
CREATE CERTIFICATE Cert_kk_db2_mssqlserver
WITH SUBJECT = 'Cert_kk_db2_mssqlserver',
START_DATE = '2015-06-01',EXPIRY_DATE = '2018-06-01';
GO
BACKUP CERTIFICATE Cert_kk_db2_mssqlserver
TO FILE = 'C:\Databases\Cert_kk_db2_mssqlserver.cer';
GO
--见证：创建证书和备份
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'kk_2012@';
GO
CREATE CERTIFICATE Cert_kk_db3_mssqlserver
WITH SUBJECT = 'Cert_kk_db3_mssqlserver',
START_DATE = '2015-06-01',EXPIRY_DATE = '2018-06-01';
GO
BACKUP CERTIFICATE Cert_kk_db3_mssqlserver
TO FILE = 'C:\Databases\Cert_kk_db3_mssqlserver.cer';
GO
-- 交换证书(相互拷贝证书):
/*
主体证书(拷贝到)————>镜像、见证
镜像证书(拷贝到)————>主体、见证
见证证书(拷贝到)————>主体、镜像
*/
-- 主体(创建用户、还原证书、创建端点)：
USE master
GO
CREATE LOGIN [KK\UserReplMirror] FROM WINDOWS;
GO
CREATE USER [UserReplMirror] FOR LOGIN [KK\UserReplMirror];
GO
CREATE CERTIFICATE [Cert_kk_db2_mssqlserver]
AUTHORIZATION [UserReplMirror]
FROM FILE = 'C:\Databases\Cert_kk_db2_mssqlserver.cer';
GO
CREATE CERTIFICATE [Cert_kk_db3_mssqlserver]
AUTHORIZATION [UserReplMirror]
FROM FILE = 'C:\Databases\Cert_kk_db3_mssqlserver.cer';
GO
CREATE ENDPOINT [Endpoint_For_Mirror]
AUTHORIZATION [KK\UserReplMirror]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING
(ROLE = PARTNER, AUTHENTICATION = CERTIFICATE Cert_kk_db1_mssqlserver, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
--GRANT CONNECT ON ENDPOINT::Endpoint_For_Mirror TO [KK\UserReplMirror];
--GO
-- 镜像(创建用户、还原证书、创建端点)：
USE master
GO
CREATE LOGIN [KK\UserReplMirror] FROM WINDOWS;
GO
CREATE USER [UserReplMirror] FOR LOGIN [KK\UserReplMirror];
GO
CREATE CERTIFICATE [Cert_kk_db1_mssqlserver]
AUTHORIZATION [UserReplMirror]
FROM FILE = 'C:\Databases\Cert_kk_db1_mssqlserver.cer';
GO
CREATE CERTIFICATE [Cert_kk_db3_mssqlserver]
AUTHORIZATION [UserReplMirror]
FROM FILE = 'C:\Databases\Cert_kk_db3_mssqlserver.cer';
GO
CREATE ENDPOINT [Endpoint_For_Mirror]
AUTHORIZATION [KK\UserReplMirror]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING
(ROLE = PARTNER, AUTHENTICATION = CERTIFICATE Cert_kk_db2_mssqlserver, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
--GRANT CONNECT ON ENDPOINT::Endpoint_For_Mirror TO [KK\UserReplMirror];
--GO
-- 见证(创建用户、还原证书、创建端点)：
USE master
GO
CREATE LOGIN [KK\UserReplMirror] FROM WINDOWS;
GO
CREATE USER [UserReplMirror] FOR LOGIN [KK\UserReplMirror];
GO
CREATE CERTIFICATE [Cert_kk_db1_mssqlserver]
AUTHORIZATION [UserReplMirror]
FROM FILE = 'C:\Databases\Cert_kk_db1_mssqlserver.cer';
GO
CREATE CERTIFICATE [Cert_kk_db2_mssqlserver]
AUTHORIZATION [UserReplMirror]
FROM FILE = 'C:\Databases\Cert_kk_db2_mssqlserver.cer';
GO
USE master;
CREATE ENDPOINT [Endpoint_For_Mirror]
AUTHORIZATION [KK\UserReplMirror]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING
(ROLE = WITNESS, AUTHENTICATION = CERTIFICATE Cert_kk_db3_mssqlserver, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
--GRANT CONNECT ON ENDPOINT::Endpoint_For_Mirror TO [KK\UserReplMirror];
--GO
--主体：备份数据库
USE master;
BACKUP DATABASE [DemoDB]
TO DISK = 'C:\Databases\DemoDB.BAK' WITH INIT,FORMAT
GO
BACKUP LOG [DemoDB]
TO DISK = 'C:\Databases\DemoDB_LOG.BAK' WITH INIT,FORMAT
GO
--镜像：还原数据库（NORECOVERY）
USE master;
RESTORE DATABASE [DemoDB]
FROM DISK = N'C:\Databases\DemoDB.BAK'
WITH FILE = 1,
MOVE N'DemoDB' TO N'C:\Databases\DemoDB.mdf',
MOVE N'DemoDB_log' TO N'C:\Databases\DemoDB_log.ldf',
NOUNLOAD, NORECOVERY, STATS = 10
GO
RESTORE DATABASE [DemoDB]
FROM DISK = N'C:\Databases\DemoDB_LOG.BAK'
WITH NORECOVERY
GO
--开始镜像
--在【镜像】执行,PARTNER为主服务器
USE [master]
GO
ALTER DATABASE [DemoDB] SET PARTNER = 'TCP://192.168.2.10:5022';
GO
--在【主体】执行,PARTNER为镜像服务器
USE [master]
GO
ALTER DATABASE [DemoDB] SET PARTNER = 'TCP://192.168.2.11:5022';
GO
ALTER DATABASE [DemoDB] SET WITNESS = 'TCP://192.168.2.12:5022';
GO
--在主体执行:设置为高安全模式
ALTER DATABASE [DemoDB] SET SAFETY FULL

阅读全文

0 0