Cloud Computing Cloud Computing
  • 专题推荐
  • Exchange
  • 服务器
    • Windows
    • Linux
  • 虚拟化
    • Citrix
    • Xen
    • Vmware
    • Kvm
    • Hyper-v
  • 超融合
    • Nutanix
  • 存储
    • Open-E存储
    • FCoE存储
    • FC SAN存储
    • NFS存储
    • iSCSI SAN存储
  • 数据库
    • Oracle
    • Microsoft SQL Server
  • Kubernetes
  • 监控系统
    • Zabbix
首页 › 数据库 › Microsoft SQL Server › SQL Server 2017 数据库镜像服务器配置完整篇—非域控环境中通过证书配置数据库镜像(四)

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

weimei
2年前Microsoft SQL Server
1,296 0 1

目录

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投票的能力,当我的一个组件不可达,并因此需要进行错误恢复的时候。见证服务器只有在你想实现自动错误恢复的时候才需要用到。

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

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.完成配置

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

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;
1
本文系作者 @weimei 原创发布在 Cloud Computing。未经许可,禁止转载。
SQL Server 2017 数据库镜像服务器配置完整篇—域控环境中创建数据库镜像 (三)
上一篇
Delivery Controller、Windows VDA、Director 和 Citrix 许可证服务器使用的默认网络端口
下一篇
评论 (0)
再想想
赞助一下
1
相关文章
SQL Server 2017 数据库镜像服务器配置完整篇—域控环境中创建数据库镜像 (三)
SQL Server 2017 数据库镜像服务器配置完整篇—域控环境中创建数据库镜像 (二)
SQL Server 2017 数据库镜像服务器配置完整篇—域控环境中创建数据库镜像 (一)
SQLServer 2017安装时的错误:Polybase要求安装Oracle JRE 7更新51或更高版本
云计算资源和交流中心

Cloud Computing是一个向访客介绍虚拟化、云存储、服务器等信息技术的网站,旨在为有提升个人技术能力的访客们提供一个信息获取以及交流的平台。网站开辟了多个特色栏目,精心编辑每一篇文章。

分类目录
服务器 Windows Linux 虚拟化 Xen Vmware Kvm Hyper-v 超融合 Nutanix 存储 NFS存储 iSCSI SAN存储 数据库 Microsoft SQL Server
友情链接
服务器 Windows Linux 虚拟化 Xen Vmware Kvm Hyper-v 超融合 Nutanix 存储 NFS存储 iSCSI SAN存储 数据库 Microsoft SQL Server
Copyright © 2019-2021 Cloud Computing. Designed by nicetheme. 闽ICP备16012274号-8
  • 专题推荐
  • Exchange
  • 服务器
    • Windows
    • Linux
  • 虚拟化
    • Citrix
    • Xen
    • Vmware
    • Kvm
    • Hyper-v
  • 超融合
    • Nutanix
  • 存储
    • Open-E存储
    • FCoE存储
    • FC SAN存储
    • NFS存储
    • iSCSI SAN存储
  • 数据库
    • Oracle
    • Microsoft SQL Server
  • Kubernetes
  • 监控系统
    • Zabbix
热门搜索
  • 36
  • 35
  • 38
  • 37
weimei
"那些听不见音乐的人以为跳舞的人疯了。"——尼采
92 文章
3 评论
176 喜欢
  • 1
  • 0
  • Top