SQLServer 2012 高可用之镜像搭建实录

最新推荐文章于 2024-07-28 18:18:33 发布

彼岸大洋

最新推荐文章于 2024-07-28 18:18:33 发布

阅读量5.4k

点赞数

本文链接：https://blog.csdn.net/elie_yang/article/details/88024042

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

SqlServer 2012高可用搭建过程

如题所述，记录下我的搭建“心路过程”，项目需要数据库实现高可用，手头只有sql server 2012 Express的安装包,实验的资源缺乏。起初对SqlServer高可用还没有实际做过，百度了些资料，决定用Alwayson 来实现，发现搭建环境的步骤比较复杂，又没有趟过坑的经验，相比下镜像还是来的方便快捷些。记录下搭建过程，对于需要快速解决问题的朋友来说，可以参考下：

首先说下服务器：对于打算利用阿里云服务器上安装虚拟机的来增加实验服务台数的想法是不可行的，阿里云服务器本身就是虚拟化技术产物，不支持再次虚拟化，Hyper-V或VMware 都不可以。

本次实验用了2台阿里云服务器，都是Windows Server 2012 R2 DataCenter版，数据库用的是SqlServer 2012 Express升级到企业版。升级过程看下我的这篇blog:https://blog.csdn.net/elie_yang/article/details/87920400

接着使用证书配置镜像，并备份还原数据库：主要参考blog:https://blog.csdn.net/dba_huangzj/article/details/27652857，

（说明：如果不点开链接，对照我下面的步骤也是可以完成搭建的）

下面关于这个主题项下的下划线文字属于转载，有做些补充：原文有些地方没有图示，对第一次部署的人来说可能会误导：

创建证书：
如果服务器使用Local System作为SQL Server服务账号，就需要使用证书授权。证书授权同时也可以在你的服务器不能通过其他服务器的账号访问对方服务器或者你不想授权给Windows登录时使用。

使用证书搭建镜像的步骤如下：
Step 1：创建数据库主密钥
主密钥的用处在这里是用于加密证书

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';
删除用Drop 命令

使用相同方式在镜像服务器创建数据库主密钥。

Step 2：创建证书，并用主密钥加密
创建证书时，默认在创建日期开始一年后过期，所以针对证书的创建，要注意其过期时间。下面是在“主体服务器”上创建HOST_A_cert证书的创建

USE master
GO
CREATE CERTIFICATE Host_A_Cert
WITH Subject = 'Host_A Certificate',
Expiry_Date = '2020-1-1'; --过期日期
删除用Drop
使用相同的方法在镜像服务器上实现对HOST_B_cert证书的创建

Step 3：创建端点
--使用Host_A_Cert证书创建端点
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_A_Cert, ENCRYPTION = REQUIRED Algorithm AES, ROLE =
ALL );
END

使用相同的方法在镜像服务器上创建镜像服务器的端点。

Step 4：备份证书
备份证书的目的是发送到别的服务器并导入证书，以便别的服务器能通过证书访问这台服务器（主体服务器）。

BACKUP CERTIFICATE Host_A_Cert
TO FILE = 'C:\ShareFolders\Host_A_Cert.cer';
同样的，在镜像服务器上重复一次，执行sql 语句后，可以看到给定的目录下有一个.cer后缀的证书文件；

Step 5：创建登录账号
针对每个服务器单独创建一个服务器登录账号，这里只需要创建一个登录给镜像服务器即可：

CREATE LOGIN Host_B_Login WITH PASSWORD = 'Pa$$w0rd';

同理，在镜像服务器上创建Host_A_Login给主体服务器。

Step 6：创建用户，并映射到Step 5中创建的登录账号中
在主体服务器上运行：

CREATE USER Host_B_User For Login Host_B_Login;

同理在镜像服务器也创建。

Step 7：使用证书授权用户
创建一个新的证书，并使用从伙伴服务器中复制过来的证书导入，然后映射step 6中的账号到这个新证书上。

CREATE CERTIFICATE Host_B_Cert
AUTHORIZATION Host_B_User
FROM FILE = 'C:\Certifications\Host_B_Cert.cer';

注意镜像服务器上也同样。

Step 8：把Step 5中的登录账号授权访问端口
GRANT CONNECT ON ENDPOINT::[DatabaseMirroring] TO [Host_B_Login];

镜像服务器也一样。到此为止，配置镜像的步骤已经完毕。

下面开始备份还原数据库：
1：在主体服务器上备份数据库：

设置下图所示测试数据库HgCloud的属性：恢复模式为：完整；

右键：“任务”——>"备份" 备份类型选择：“完整”

说明：有的blog里写到分两次备份和还原，一次是完整，一次是事务日志，此处测试只一次完整备份，数据库还原时分了“文件和文件组”和“日志”。但也顺利通过！此处需要稍微留意一下：

2：在镜像服务器上还原步骤1的备份文件：

并使用Nonrecovery方式

还原的路径这里我这里设置成和主体服务器一致的路径。

3：还原日志，同样使用Nonrecovery方式：（重要，否则后面执行镜像会报错）

执行镜像时不还原日志报错：数据库“HgCloud”可能包含尚未备份的大容量日志记录的更改。请在主体数据库或主数据库上进行日志备份。然后或者在镜像数据库上还原此备份以便启用数据库镜像，或者在每个辅助数据库上都还原此备份以使您可以将其联接到可用性组。

启动镜像操作：

在镜像服务器上执行：

ALTER DATABASE HgCloud
SET PARTNER = 'TCP://主体服务器名称:5022';
GO

PARTNER =‘TCP://服务器名称:5022 ’可以复制下面的界面来源：

再次到主体服务器上同样执行上面的sql 语句，完成镜像配置。

说明：目前为止，还没有配置见证服务器，所以上图为空，另外运行模式只有“高性能（异步）”和“高安全（同步）”可选择；

目前主体数据库和镜像数据库的状态显示界面如下：

测试：

在主体服务器中，点击下图中的“故障转移按钮”：然后观察主体和镜像数据库角色的切换。

带有见证服务器的非域环境镜像配置

因为没有多余的服务器可以做见证服务器，所以把见证服务器和主体服务器公用同一台处理。见证服务器实例可以与主体服务器实例或镜像服务器实例运行于同一台计算机上，但这样会明显降低自动故障转移的可靠性。

这里没有写step by step 的步骤，就和重新安装sql server 2012 一样，同不同的实例名区分（可以是Express版本，见证服务器可以是Express版本）

安装好后，在服务中可以看到2个实例对应的服务：

例如下图所示的登录：

说明：以上的脚本都可以用图像化的界面来完成。

例如通过向导完成：

学习使用一下脚本：如果能顺利做到本文开始的镜像，下面的脚本非常容易理解：

这里直接copy原创博主的Sql脚本：出处，有稍微的修改：https://blog.csdn.net/dba_huangzj/article/details/27652857

在见证服务器上创建主密钥、证书等

--创建主密钥 
USE master; 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'passW0rd@123'; 
GO

USE master; 
CREATE CERTIFICATE HOST_C_cert 
   WITH SUBJECT = 'HOST_C certificate'
   ,EXPIRY_DATE ='2020-1-1'
GO

CREATE ENDPOINT Endpoint_Mirroring 
   STATE = STARTED 
   AS TCP ( 
      LISTENER_PORT=7022   --使用7022端口
      , LISTENER_IP = ALL 
   ) 
   FOR DATABASE_MIRRORING ( 
      AUTHENTICATION = CERTIFICATE HOST_C_cert    --使用证书来授权端点 
      , ENCRYPTION = REQUIRED ALGORITHM AES 
      , ROLE = ALL --或者是WITNESS
   ); 
GO

BACKUP CERTIFICATE HOST_C_cert TO FILE = 'C:\Certifications\HOST_C_cert.cer'; 
GO

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

--在Witness实例上创建一个登录名给Principal实例 
USE master; 
CREATE LOGIN HOST_A_login WITH PASSWORD = 'Pa$$w0rd'; 
GO 
--创建一个用于给这个登录名 
CREATE USER HOST_A_user FOR LOGIN HOST_A_login; 
GO 
--让该帐号使用证书授权 
CREATE CERTIFICATE HOST_A_cert 
   AUTHORIZATION HOST_A_user 
   FROM FILE = 'C:\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 = 'Pa$$w0rd'; 
GO 
--创建一个用于给这个登录名 
CREATE USER HOST_B_user FOR LOGIN HOST_B_login; 
GO 
--让该帐号使用证书授权 
CREATE CERTIFICATE HOST_B_cert 
   AUTHORIZATION HOST_B_user 
   FROM FILE = 'C:\Certifications\HOST_B_cert.cer' 
GO 
--授予这个新账号连接端点的权限 
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO HOST_B_login; 
GO

分别在RepA和RepB中执行下面语句，为见证服务器创建连接端点的权限：

USE master; 
CREATE LOGIN HOST_C_login WITH PASSWORD = 'Pa$$w0rd'; 
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::DatabaseMirroring TO HOST_C_login; 
GO

在主体服务器上执行下面的sql脚本：

ALTER DATABASE AdventureWorks2008R2 SET WITNESS = 'TCP://iZmt2xpetxp62fZ:7022'

如果不够幸运，会看到下面的错误：

此时需要检查下SQL Server 实例服务的登录账户权限，改成本地系统账户后，重新尝试，成功执行！

再有报错：证书、非对称密钥或私钥文件无效或不存在；或是您不具有针对其的权限

碰见这个问题本人是重新生成了私钥文件，然后结合上图的权限修改后才解决了上面的报错。

至此，全部镜像部署就完成了，见下图：

下面来一个验证测试

首先：关闭主体服务器的SQL服务，然后观察各数据库状态，见下图：

最后看下数据库的连接：

引用文字：出处：https://www.sohu.com/a/201805071_629429

透明切换

一个完整的高可用机制除了后端节点的切换，还包括发生故障转移后，客户端如何能够快速地连接到冗余节点，继续业务读写。常用的实现，包括 Driver 层解决方案，例如 mongoDB replication set，也有通过二层内的广播方式实现 vip，例如 keepalived，当然还包括 proxy，以及三层 DNS 的实现。

SQL Server 的实现采用了 Driver 层处理的方式，开发者要实现自动的故障转移，在连接数据库时，除了必须要指定初始节点的 IP 和端口，还要指定故障转移的节点的 IP 和端口。客户端首先尝试使用初始节点创建连接，如果初始节点指向的实例当前为 principal，则连接会建立成功，可以正常的读写。当发生故障切换时，principal 会切断所有已有客户端连接，然后客户端建立到初始节点连接也会失败，通过一定的重试策略失败后，会尝试连接之前指定的故障转移节点，从而实现服务入口的切换。

SQL Server 常用的访问接口：OLE DB、ODBC、ADO 均支持指定故障转移节点，格式如下：

Server=250.65.43.21,4734; Failover_Partner=250.65.43.22,4734;

于是，也来一个测试：

connectionString="Server=iZmt2xpetxp62fZ;Failover Partner=HgwebHost;Initial Catalog=HgCloud;User ID=sa;Password=xx"

停用主体数据库的服务，看下系统的运行是否正常！测试成功！！！

本文完！附加有用的测试脚本

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

参考：

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

https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2005/ms190430(v=sql.90)

https://blog.csdn.net/jinlong_cai/article/details/85558011

https://blog.csdn.net/kelyon/article/details/79207585

https://blog.csdn.net/a497785609/article/details/80880064

https://www.sohu.com/a/201805071_629429