20190801-SQLServer2012高可用镜像数据库 实施方案(非域环境)

一：环境准备

主数据库、镜像数据库、见证服务器之间的身份验证方式

一：域帐户

二：证书

本次采用证书验证方式；
所有数据库镜像会话都只支持一台主体服务器和一台镜像服务器。

1.1 服务器、数据库准备

IP 、主机、密码等敏感信息已脱敏

1.2 创建目录

三台服务器本地，分别创建D:\sharedir 目录，并设置共享, 权限everyone 可读写；
三台服务器本地，分别创建D:\certifications 目录，用于导出证书；

1.3 环境检查

(1) 检查三台服务器网络和端口是否测通；
(2) 检查三台服务器防火墙是否关闭，或1023,5022 等端口是否放开；
(3) 检查三台服务器上数据库版本和补丁版本是否一致；

##二：执行相关脚本
具体脚本内容见最后 十：脚本附件

2.1 创建主数据库密钥
2.2 创建证书
2.3 创建端点
2.4 备份并分发证书
2.5 创建登陆账号
2.6 创建用户
2.7 授权用户
2.8 授权端口

三：数据初始化

3.1 备份数据库( 主)

主库备份数据库和日志，并将备份文件备注到镜像数据库服务器上

use master
BACKUP DATABASE testdb
TO DISK = 'D:\sharedir\testdb0730.bak'
WITH COMPRESSION
GO

use master
BACKUP LOG testdb
TO DISK = 'D:\sharedir\testlog0730.trn'
GO

3.2 还原数据库( 镜像)

还原数据库和日志(RESTORE WITH NORECOVERY)

四：配置镜像

在主数据库上操作

五：故障转移测试

5.1 查看主库、镜像库状态

主库

镜像库

5.2 手动切换

在主库上执行

镜像数据库 102 ，已经切换成主库



102 切换回来镜像数据库

5.3 自动切换

101 主故障，测试自动切换
101 启动防火墙，1023 和5022 不通

use testdb


102

 use testdb
select count (*) from a0730 ;

–101 关闭防火墙


use testdb

—102

use testdb
select * from test0730 ;
select count (*) from a0730 ;
insert into test0730 values (7 );
update test0730 set id =100 where id =1 ;

手动将主切回101



101

 use testdb
select * from test0730 ;

六：查看数据库镜像监视器

七：搭建过程中遇到的问题

问题一：

服务器网络地址:TCP://192.168.100.102:5022 无法访问或不存在。
数据库 testdb 的伙伴服务器实例和见证服务器实例均不可用。



问题原因：
主服务器上启用了防火墙，并且1023 和5022 端口没有开放。

解决方案：

关闭防火墙，重新点击 开始镜像

八：应用客户端连接数据库

Microsoft .NET Data Provider for SQL Server 提供了对数据库镜像会话的客户端连接支持。

九：参考

https://docs.microsoft.com/zh-cn/sql/database-engine/database-mirroring/database-mirroring-sql-server?view=sql-server-2017

数据库镜像 (SQL Server)

https://blog.youkuaiyun.com/dba_huangzj/article/details/27652857

第三篇——第二部分——第四文 配置 SQL Server 镜像——非域环境

十：脚本附件

101 主服务器

– 创建主密钥

USE master ;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'testpasword)(*' ; -- 演示所需，否则不要设置这么简单的密码
GO
/*

– 删除主密钥

USE master;
DROP  MASTER KEY
*/

— 创建证书

USE master ;
CREATE CERTIFICATE HOST_A_cert
   WITH SUBJECT = 'HOST_A certificate' -- 在主实例上创建证书，命名为HOST_A_cert，这个选项是描述证书
   , EXPIRY_DATE = '2999-1-1' ; -- 证书过期时间，可以适当设置长一点，具体按实际需要设置
GO
/*

– 删除证书

USE master;
DROP CERTIFICATE HOST_A_cert
*/

— 创建端点

CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT = 5022   -- 使用5022端口，这个端口可以改成未被使用的端口，但是镜像过程中的所有合作者都应该使用相同的端口
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE HOST_A_cert    -- 使用证书来授权端点
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL         -- 表示这个端点可以作为任何角色，包括主服务器、镜像服务器、见证服务器。具体可看联机丛书。

   );
GO
/*

– 删除镜像端点

IF  EXISTS (SELECT * FROM sys.endpoints e WHERE e.name = N'Endpoint_Mirroring')
DROP ENDPOINT [Endpoint_Mirroring]
GO
*/

BACKUP CERTIFICATE HOST_A_cert TO FILE = 'D:\certifications\HOST_A_cert.cer' ;
GO

– 在主实例上创建一个登录名给Mirror实例

USE master ;
CREATE LOGIN HOST_B_login WITH PASSWORD = 'testpasword)(*' ;
GO

– 创建一个用于给这个登录名

CREATE USER HOST_B_user FOR LOGIN HOST_B_login ;
GO

-- 让该帐号使用证书授权
CREATE CERTIFICATE HOST_B_cert
   AUTHORIZATION HOST_B_user
   FROM FILE = 'D:\Certifications\HOST_B_cert.cer'
GO

-- 授予这个新账号连接端点的权限
GRANT CONNECT ON ENDPOINT :: Endpoint_Mirroring TO HOST_B_login ;
GO
/*

-- 删除账号
DROP LOGIN HOST_B_user
*/

-- 在主实例上创建一个登录名给见证实例

USE master ;
CREATE LOGIN HOST_C_login WITH PASSWORD = 'testpasword)(*' ;
GO

-- 创建一个用于给这个登录名
CREATE USER HOST_C_user FOR LOGIN HOST_C_login ;
GO

-- 让该帐号使用证书授权
CREATE CERTIFICATE HOST_C_cert
   AUTHORIZATION HOST_C_user
   FROM FILE = 'D:\Certifications\HOST_C_cert.cer'
GO

-- 授予这个新账号连接端点的权限
GRANT CONNECT ON ENDPOINT :: Endpoint_Mirroring TO HOST_C_login ;


---102 镜像服务器
-- 创建主密钥
USE master ;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'testpasword)(*' ; -- 演示所需，否则不要设置这么简单的密码
GO
/*

-- 删除主密钥
USE master;
DROP  MASTER KEY
*/

USE master ;
CREATE CERTIFICATE HOST_B_cert
   WITH SUBJECT = 'HOST_B certificate' -- 在Winess实例上创建证书，命名为HOST_C_cert，这个选项是描述证书
   , EXPIRY_DATE = '2999-1-1' ; -- 证书过期时间，可以适当设置长一点，具体按实际需要设置
GO
/*

-- 删除证书
USE master;
DROP CERTIFCATE HOST_B_cert
*/

CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT = 5022     -- 使用5022端口，这个端口可以改成未被使用的端口，但是镜像过程中的所有合作者都应该使用相同的端口
      , LISTENER_IP = ALL
   )

   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE HOST_B_cert    -- 使用证书来授权端点
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL          -- 表示这个端点可以作为任何角色，包括主服务器、镜像服务器、见证服务器。具体可看联机丛书。
   );
GO
/*

-- 删除镜像端点
IF  EXISTS (SELECT * FROM sys.endpoints e WHERE e.name = N'Endpoint_Mirroring')
DROP ENDPOINT [Endpoint_Mirroring]
GO
*/

BACKUP CERTIFICATE HOST_B_cert TO FILE = 'D:\certifications\HOST_B_cert.cer' ;
GO

 

-- 在镜像实例上创建一个登录名给Principal实例
USE master ;
CREATE LOGIN HOST_A_login WITH PASSWORD = 'testpasword)(*' ;
GO

-- 创建一个用于给这个登录名
CREATE USER HOST_A_user FOR LOGIN HOST_A_login ;
GO

-- 让该帐号使用证书授权
CREATE CERTIFICATE HOST_A_cert
   AUTHORIZATION HOST_A_user
   FROM FILE = 'D:\Certifications\HOST_A_cert.cer'
GO

-- 授予这个新账号连接端点的权限
GRANT CONNECT ON ENDPOINT :: Endpoint_Mirroring TO HOST_A_login ;
GO
/*

-- 删除账号
DROP LOGIN HOST_A_user
*/


USE master ;
CREATE LOGIN HOST_C_login WITH PASSWORD = 'testpasword)(*' ;
GO

-- 创建一个用于给这个登录名
CREATE USER HOST_C_user FOR LOGIN HOST_C_login ;
GO

-- 让该帐号使用证书授权
CREATE CERTIFICATE HOST_C_cert
   AUTHORIZATION HOST_C_user
   FROM FILE = 'C:\Certifications\HOST_C_cert.cer'
GO

-- 授予这个新账号连接端点的权限
GRANT CONNECT ON ENDPOINT :: Endpoint_Mirroring TO HOST_C_login ;

 
---103 见证服务器
-- 创建主密钥
USE master ;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'testpasword)(*' ; -- 演示所需，否则不要设置这么简单的密码
GO
/*

-- 删除主密钥
USE master;
DROP  MASTER KEY
*/

USE master ;
CREATE CERTIFICATE HOST_C_cert
   WITH SUBJECT = 'HOST_C certificate' -- 在Winess实例上创建证书，命名为HOST_C_cert，这个选项是描述证书
   , EXPIRY_DATE = '2999-1-1' ; -- 证书过期时间，可以适当设置长一点，具体按实际需要设置
GO
/*

-- 删除证书
USE master;
DROP CERTIFICATE HOST_C_cert
*/

CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT = 5022    -- 使用5022端口，这个端口可以改成未被使用的端口，但是镜像过程中的所有合作者都应该使用相同的端口
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE HOST_C_cert    -- 使用证书来授权端点
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL       -- 表示这个端点可以作为任何角色，包括主服务器、镜像服务器、见证服务器。具体可看联机丛书。

   );
GO
/*

-- 删除镜像端点
IF  EXISTS (SELECT * FROM sys.endpoints e WHERE e.name = N'Endpoint_Mirroring')
DROP ENDPOINT [Endpoint_Mirroring]
GO
*/

BACKUP CERTIFICATE HOST_C_cert TO FILE = 'D:\certifications\HOST_C_cert.cer' ;
GO

 
--- 在见证服务器上为主体、镜像服务器创建以证书为验证的账号、用户名及端点。

-- 在Witness实例上创建一个登录名给Principal实例
USE master ;
CREATE LOGIN HOST_A_login WITH PASSWORD = 'testpasword)(*' ;
GO

-- 创建一个用于给这个登录名
CREATE USER HOST_A_user FOR LOGIN HOST_A_login ;
GO

-- 让该帐号使用证书授权
CREATE CERTIFICATE HOST_A_cert
   AUTHORIZATION HOST_A_user
   FROM FILE = 'D:\certifications\HOST_A_cert.cer'
GO

-- 授予这个新账号连接端点的权限
GRANT CONNECT ON ENDPOINT :: Endpoint_Mirroring TO HOST_A_login ;
GO
/*

-- 删除账号
DROP LOGIN HOST_A_user
*/

-- 在Witness实例上创建一个登录名给Mirror实例
USE master ;
CREATE LOGIN HOST_B_login WITH PASSWORD = 'testpasword)(*' ;
GO

-- 创建一个用于给这个登录名
CREATE USER HOST_B_user FOR LOGIN HOST_B_login ;
GO

-- 让该帐号使用证书授权
CREATE CERTIFICATE HOST_B_cert
   AUTHORIZATION HOST_B_user
   FROM FILE = 'D:\certifications\HOST_B_cert.cer'
GO

-- 授予这个新账号连接端点的权限
GRANT CONNECT ON ENDPOINT :: Endpoint_Mirroring TO HOST_B_login ;
GO
/*

-- 删除账号
DROP LOGIN HOST_B_user
*/

欢迎关注我的微信公众号"IT小Chen"