SQL Server 2017 数据库镜像服务器配置完整篇—非域控环境中通过证书配置数据库镜像（四）

环境准备

数据库镜像是将数据库事务处理从一个SQL Server数据库移动到不同SQL Server环境中的另一个SQL Server数据库中。镜像不能直接访问;它只用在错误恢复的情况下才可以被访问。

要进行数据库镜像所需的最小需求包括了两个不同的SQL Server运行环境。主服务器被称为“主机”，第二个服务器被称作“备机”。主机数据库就是你实际用着的数据库，镜像数据库就是你的数据库的备用拷贝。当事务写入你的基本服务器的时候，他们也同样被传送到并写入你的镜像数据库中。

除了基本和镜像之外，你还可以引入另一个可选的组件，名为“见证”。见证服务器是第三个SQL Server 2005/2008运行实例，它是在判断什么时候进行错误恢复的时候，用于基本和镜像之间内部交流。只有当你想实现自动错误恢复的时候用到这个选项。它实现了2比1投票的能力，当我的一个组件不可达，并因此需要进行错误恢复的时候。见证服务器只有在你想实现自动错误恢复的时候才需要用到。

实现互通可以使用域或证书来实现，非域环境下使用证书配置数据库镜像(如不需要见证服务器，省略掉主体服务器和镜像服务器建立见证服务器的登录、见证服务器的配置、主体服务器设置见证服务器三个环节即可)。

SQL Server 2017 数据库镜像服务器配置完整篇---非域控环境中通过证书配置数据库镜像（四）-Cloud Computing

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;