SQL Server 2017 数据库镜像服务器配置完整篇—非域控环境中通过证书配置数据库镜像(四)
目录
SQL Server 2017 数据库镜像服务器配置完整篇—域控环境中创建数据库镜像 (一)
SQL Server 2017 数据库镜像服务器配置完整篇—域控环境中创建数据库镜像 (二)
SQL Server 2017 数据库镜像服务器配置完整篇—域控环境中创建数据库镜像 (三)
SQL Server 2017 数据库镜像服务器配置完整篇—非域控环境中通过证书配置数据库镜像(四)
环境准备
数据库镜像是将数据库事务处理从一个SQL Server数据库移动到不同SQL Server环境中的另一个SQL Server数据库中。镜像不能直接访问;它只用在错误恢复的情况下才可以被访问。
要进行数据库镜像所需的最小需求包括了两个不同的SQL Server运行环境。主服务器被称为“主机”,第二个服务器被称作“备机”。主机数据库就是你实际用着的数据库,镜像数据库就是你的数据库的备用拷贝。当事务写入你的基本服务器的时候,他们也同样被传送到并写入你的镜像数据库中。
除了基本和镜像之外,你还可以引入另一个可选的组件,名为“见证”。见证服务器是第三个SQL Server 2005/2008运行实例,它是在判断什么时候进行错误恢复的时候,用于基本和镜像之间内部交流。只有当你想实现自动错误恢复的时候用到这个选项。它实现了2比1投票的能力,当我的一个组件不可达,并因此需要进行错误恢复的时候。见证服务器只有在你想实现自动错误恢复的时候才需要用到。
实现互通可以使用域或证书来实现,非域环境下使用证书配置数据库镜像(如不需要见证服务器,省略掉主体服务器和镜像服务器建立见证服务器的登录、见证服务器的配置、主体服务器设置见证服务器三个环节即可)。

1. 数据库备份还原
---------主体:设置数据库“恢复模式”为“完整”模式[DBName]:数据库名称--------------
USE master;
ALTER DATABASE [DBName] SET RECOVERY FULL
GO
---------主体:备份数据库-保存的路径C:\DBName.BAK------------------------------
USE master;
BACKUP DATABASE [DBName]
TO DISK = 'C:\DBName.BAK' WITH INIT,FORMAT
GO
---------镜像:还原数据库(NORECOVERY)-----------------------------------------
USE master;
RESTORE DATABASE [DBName]
FROM DISK = N'C:\DBName.BAK'
WITH FILE = 1,
MOVE N'DBName' TO N'C:\DBName.mdf',
MOVE N'DBName_log' TO N'C:\DBName_log.ldf',
NOUNLOAD, NORECOVERY, STATS = 10
GO
----------------------------------------------------------------------------
2. 创建数据库主密钥和证书,备份交换证书
主体服务器:
---------1.创建数据库主密钥---------------------------------------------------
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd';
---------删除主密钥----------------------------------------------------------
USE master;
DROP MASTER KEY
---------2.创建证书并使用主密钥加密--------------------------------------------
USE master;
GO
CREATE CERTIFICATE Host_sqlserver01_Cert
WITH SUBJECT = 'sqlserver01_certificate',
EXPIRY_DATE = '2060-1-1';
---------删除证书------------------------------------------------------------
USE master;
DROP CERTIFICATE Host_sqlserver01_Cert
---------3.创建端点:---------------------------------------------------------
---------创建端点之前,先查找下看是否已经存在端点:
SELECT *
FROM sys.database_mirroring_endpoints;
---------如果需要删除端点
DROP ENDPOINT 端点名称;
---------创建端点:-----------------------------------------------------------
IF NOT EXISTS ( SELECT 1
FROM sys.database_mirroring_endpoints )
BEGIN
CREATE ENDPOINT [DatabaseMirroring] STATE = STARTED AS TCP ( LISTENER_PORT = 5022,
LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION =
CERTIFICATE Host_sqlserver01_Cert, ENCRYPTION = REQUIRED ALGORITHM AES,
ROLE = ALL );
END;
---------4.备份证书----------------------------------------------------------
BACKUP CERTIFICATE Host_sqlserver01_Cert
TO FILE = 'C:\cert\Host_sqlserver01_Cert.cer';
镜像服务器:
---------1.创建数据库主密钥---------------------------------------------------
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd';
---------删除主密钥----------------------------------------------------------
USE master;
DROP MASTER KEY
---------2.创建证书并使用主密钥加密--------------------------------------------
USE master;
GO
CREATE CERTIFICATE Host_sqlserver02_Cert
WITH SUBJECT = 'sqlserver02_certificate',
EXPIRY_DATE = '2060-1-1';
---------删除证书------------------------------------------------------------
USE master;
DROP CERTIFICATE Host_sqlserver02_Cert
---------3.创建端点:---------------------------------------------------------
---------创建端点之前,先查找下看是否已经存在端点:
SELECT *
FROM sys.database_mirroring_endpoints;
---------如果需要删除端点
DROP ENDPOINT 端点名称;
---------创建端点:-----------------------------------------------------------
IF NOT EXISTS ( SELECT 1
FROM sys.database_mirroring_endpoints )
BEGIN
CREATE ENDPOINT [DatabaseMirroring] STATE = STARTED AS TCP ( LISTENER_PORT = 5022,
LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION =
CERTIFICATE Host_sqlserver02_Cert, ENCRYPTION = REQUIRED ALGORITHM AES,
ROLE = ALL );
END;
---------4.备份证书----------------------------------------------------------
BACKUP CERTIFICATE Host_sqlserver02_Cert
TO FILE = 'C:\cert\Host_sqlserver02_Cert.cer';
见证服务器:
---------1.创建数据库主密钥---------------------------------------------------
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd';
---------删除主密钥----------------------------------------------------------
USE master;
DROP MASTER KEY
---------2.创建证书并使用主密钥加密--------------------------------------------
USE master;
GO
CREATE CERTIFICATE Host_sqlserver03_Cert
WITH SUBJECT = 'sqlserver03_certificate',
EXPIRY_DATE = '2060-1-1';
---------删除证书------------------------------------------------------------
USE master;
DROP CERTIFICATE Host_sqlserver03_Cert
---------3.创建端点:---------------------------------------------------------
---------创建端点之前,先查找下看是否已经存在端点:
SELECT *
FROM sys.database_mirroring_endpoints;
---------如果需要删除端点
DROP ENDPOINT 端点名称;
---------创建端点:-----------------------------------------------------------
IF NOT EXISTS ( SELECT 1
FROM sys.database_mirroring_endpoints )
BEGIN
CREATE ENDPOINT [DatabaseMirroring] STATE = STARTED AS TCP ( LISTENER_PORT = 5022,
LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION =
CERTIFICATE Host_sqlserver03_Cert, ENCRYPTION = REQUIRED ALGORITHM AES,
ROLE = ALL );
END;
---------4.备份证书----------------------------------------------------------
BACKUP CERTIFICATE Host_sqlserver03_Cert
TO FILE = 'C:\cert\Host_sqlserver03_Cert.cer';
将主体服务器(10.36.11.12)C盘cert目录下的Host_sqlserver01_Cert证书复制到镜像服务器(10.36.11.13)和见证服务器(10.36.11.14)C盘cert目录 ,
同时将镜像服务器 (10.36.11.13) C盘cert目录下的 Host_ sqlserver02_Cert证书复制到 主体服务器(10.36.11.12) 和见证服务器(10.36.11.14) C盘cert目录 。
再将见证服务器(10.36.11.14) C盘cert目录下的 Host_ sqlserver03_Cert证书复制到 主体服务器(10.36.11.12) 和镜像服务器(10.36.11.13) C盘cert目录 。
即保证每个服务器C盘路径下都要有3个证书文件
3.创建镜像登录用户
主体服务器:
---------5.创建一个登录给镜像服务器使用----------------------------------------
CREATE LOGIN sqlserver02_Login WITH PASSWORD = '@1q2w3e4r';
---------6.创建一个用户以映射到上面创建的登录中---------------------------------
USE master;
CREATE USER sqlserver02_User FOR LOGIN sqlserver02_Login;
---------7.使用证书进行授权---------------------------------------------------
CREATE CERTIFICATE Host_sqlserver02_Cert
AUTHORIZATION sqlserver02_User
FROM FILE = 'C:\Cert\Host_sqlserver02_Cert.cer';
---------8.对登录进行连接端点的授权--------------------------------------------
GRANT CONNECT ON ENDPOINT::[DatabaseMirroring] TO [sqlserver02_Login];
---------9.创建一个登录给见证服务器使用----------------------------------------
CREATE LOGIN sqlserver03_Login WITH PASSWORD = '@1q2w3e4r';
---------10.创建一个用户以映射到上面创建的登录中--------------------------------
USE master;
CREATE USER sqlserver03_User FOR LOGIN sqlserver03_Login;
---------11.使用证书进行授权--------------------------------------------------
CREATE CERTIFICATE Host_sqlserver03_Cert
AUTHORIZATION sqlserver03_User
FROM FILE = 'C:\Cert\Host_sqlserver03_Cert.cer';
---------12.对登录进行连接端点的授权-------------------------------------------
GRANT CONNECT ON ENDPOINT::[DatabaseMirroring] TO [sqlserver03_Login];
镜像服务器:
---------5.创建一个登录给主体服务器使用----------------------------------------
CREATE LOGIN sqlserver01_Login WITH PASSWORD = '@1q2w3e4r';
---------6.创建一个用户以映射到上面创建的登录中---------------------------------
USE master;
CREATE USER sqlserver01_User FOR LOGIN sqlserver01_Login;
---------7.使用证书进行授权---------------------------------------------------
CREATE CERTIFICATE Host_sqlserver01_Cert
AUTHORIZATION sqlserver01_User
FROM FILE = 'C:\Cert\Host_sqlserver01_Cert.cer';
---------8.对登录进行连接端点的授权--------------------------------------------
GRANT CONNECT ON ENDPOINT::[DatabaseMirroring] TO [sqlserver01_Login];
---------9.创建一个登录给见证服务器使用----------------------------------------
CREATE LOGIN sqlserver03_Login WITH PASSWORD = '@1q2w3e4r';
---------10.创建一个用户以映射到上面创建的登录中--------------------------------
USE master;
CREATE USER sqlserver03_User FOR LOGIN sqlserver03_Login;
---------11.使用证书进行授权--------------------------------------------------
CREATE CERTIFICATE Host_sqlserver03_Cert
AUTHORIZATION sqlserver03_User
FROM FILE = 'C:\Cert\Host_sqlserver03_Cert.cer';
---------12.对登录进行连接端点的授权-------------------------------------------
GRANT CONNECT ON ENDPOINT::[DatabaseMirroring] TO [sqlserver03_Login];
见证服务器:
---------1.创建一个登录给主体服务器使用----------------------------------------
CREATE LOGIN sqlserver01_Login WITH PASSWORD = '@1q2w3e4r';
---------2.创建一个用户以映射到上面创建的登录中---------------------------------
USE master;
CREATE USER sqlserver01_User FOR LOGIN sqlserver01_Login;
---------3.使用证书进行授权---------------------------------------------------
CREATE CERTIFICATE Host_sqlserver01_Cert
AUTHORIZATION sqlserver01_User
FROM FILE = 'C:\Host_sqlserver01_Cert.cer';
---------4.对登录进行连接端点的授权--------------------------------------------
GRANT CONNECT ON ENDPOINT::[DatabaseMirroring] TO [sqlserver01_Login];
---------5.创建一个登录给镜像服务器使用----------------------------------------
CREATE LOGIN sqlserver02_Login WITH PASSWORD = '@1q2w3e4r';
---------6.创建一个用户以映射到上面创建的登录中---------------------------------
USE master;
CREATE USER sqlserver02_User FOR LOGIN sqlserver02_Login;
---------7.使用证书进行授权---------------------------------------------------
CREATE CERTIFICATE Host_sqlserver02_Cert
AUTHORIZATION sqlserver02_User
FROM FILE = 'C:\Host_sqlserver02_Cert.cer';
---------8.对登录进行连接端点的授权--------------------------------------------
GRANT CONNECT ON ENDPOINT::[DatabaseMirroring] TO [sqlserver02_Login];
4. 配置镜像
-------------先在镜像服务器(10.36.11.13)中执行--------------------------------
ALTER DATABASE ctxddc
SET PARTNER = 'TCP://10.36.11.12:5022';
GO
-------------然后再在主体服务器(10.36.11.12)中执行-----------------------------
ALTER DATABASE ctxddc
SET PARTNER = 'TCP://10.36.11.13:5022';
GO
-------------然后再在主体服务器(10.36.11.12)中执行-----------------------------
ALTER DATABASE ctxddc
SET PARTNER = 'TCP://10.36.11.14:5022';
GO
注意执行顺序。
5.完成配置

6. 备用脚本
---------(1)在高安全模式下:在主体执行---------------------------------------
USE master;
ALTER DATABASE DBname SET PARTNER FAILOVER; -----------主备切换
---------(2)在高性能模式下,需要先切换到高安全模式下再执行切换-----------------
USE master;
ALTER DATABASE DBname SET PARTNER SAFETY FULL; ------高安全模式切换
ALTER DATABASE DBname SET PARTNER FAILOVER; -----------主备切换
---------(3)在主体宕机的情况下在镜像机进行强制切换:--------------------------
USE master;
ALTER DATABASE DBname SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;
--当主体重新开机后,在主体机器上执行
USE master;
ALTER DATABASE DBname SET PARTNER RESUME;
--此时原来的主体成为了镜像机,而镜像机成为了主体。再到镜像机机器上执行
ALTER DATABASE DBname SET PARTNER FAILOVER;
---------(4)切换镜像在高性能模式下(慎用,可能会丢失数据)-----------------------
USE master;
ALTER DATABASE DBname SET PARTNER SAFETY OFF;
---------(5)关闭数据库镜像--------------------------------------------------
ALTER DATABASE DBname SET PARTNER OFF;