sql server 主从数据库镜像+见证服务器

数据库镜像

参考：https://www.cnblogs.com/gered/p/10601202.html

一、Sql server 数据库镜像配置文档

1. 设置sql server 服务启动账号
   1. 在主服务器和从服务器上创建相同windows账户sqladmin（用户名和密码完全相同）.
   2. 为sqladmin分配windows权限，至少为管理权限。
   3. 设置sql server服务的启动账户为sqladmin（注：更改后sql server服务会重启）

    DROP MASTER KEY key名–删除KEY

DROP CERTIFICATE  证书名–删除证书

DROP ENDPOINT镜像端点名  --删除镜像端点

 

1. 创建主库镜像端点及证书（镜像认证分为:AD域认证，加密证书认证）
   1. 创建 master 数据库主密钥

--删除 KEY

--drop master key

 

Use master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';

GO

 

1. 1. 对服务器实例创建一个用于其数据库镜像出站连接的加密证书。

--删除证书

-- DROP CERTIFICATE 证书名

USE master;

CREATE CERTIFICATE SQLSVR1_cert WITH SUBJECT =  'SQLSVR1 certificate for database mirroring',

   start_date='2019-03-01',expiry_date='2030-01-01';

GO

 

 

1. 1. 使用主服务器实例的证书 SQLSVR1_cert 为主服务器 SQLSVR1 创建端点。

--查看镜像端点

select * from sys.endpoints ;

--删除镜像

Drop endpoint 镜像名

Use master;

CREATE ENDPOINT [默认的镜像端点]

   STATE = STARTED AS TCP (

      LISTENER_PORT=5022

      , LISTENER_IP = ALL

   )

 

FOR DATABASE_MIRRORING (

      AUTHENTICATION = CERTIFICATE SQLSVR1_cert

      , ENCRYPTION = REQUIRED ALGORITHM AES

      , ROLE =  ALL

   );

GO

 

1. 1. 备份主体服务器 SQLSVR1 的加密证书。一定要保证证书安全无损。

BACKUP CERTIFICATE SQLSVR1_cert TO FILE ='E:\temp_bak\SQLSVR1.cer';

注：要在E盘或相应位置创建目录

 

1. 1. 在主服务器实例的 master 数据库中为镜像服务器创建一个登录名

USE master;

CREATE LOGIN SQLSVR2_login WITH PASSWORD='Sample@#';

GO

 

1. 1. 为5中新创建的登录名创建一个用户

USE master;

CREATE  USER SQLSVR2_user FOR LOGIN SQLSVR2_login;

GO

 

1. 创建从库镜像端点及证书（镜像认证分为:AD域认证，加密证书认证）
   1. 创建 master 数据库主密钥

Use master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';

GO

 

1. 1. 对服务器实例创建一个用于其数据库镜像出站连接的加密证书。

USE master;

CREATE CERTIFICATE SQLSVR1_cert WITH SUBJECT =  'SQLSVR2 certificate for database mirroring',

   start_date='2019-03-01',expiry_date='2030-01-01';

GO

 

 

1. 1. 使用主服务器实例的证书 SQLSVR2_cert 为主服务器 SQLSVR2 创建端点。

--查看镜像端点

select * from sys.endpoints ;

--删除镜像

Drop endpoint 镜像名

Use master;

CREATE ENDPOINT [默认的镜像端点]

   STATE = STARTED AS TCP (

      LISTENER_PORT=5022

      , LISTENER_IP = ALL

   )

 

FOR DATABASE_MIRRORING (

      AUTHENTICATION = CERTIFICATE SQLSVR2_cert

      , ENCRYPTION = REQUIRED ALGORITHM AES

      , ROLE =  ALL

   );

GO

 

1. 1. 备份主体服务器 SQLSVR2 的加密证书。一定要保证证书安全无损。

BACKUP CERTIFICATE SQLSVR2_cert TO FILE ='E:\temp_bak\SQLSVR2.cer';

注：要在E盘或相应位置创建目录

 

1. 1. 在主服务器实例的 master 数据库中为镜像服务器创建一个登录名

USE master;

CREATE LOGIN SQLSVR1_login WITH PASSWORD='Sample@#';

GO

 

1. 1. 为5中新创建的登录名创建一个用户

USE master;

CREATE  USER SQLSVR2_user FOR LOGIN SQLSVR1_login;

GO

1. 将用户与镜像服务器证书关联
   1. 已经将从服务器的证书本分SQLSVR2.CER拷贝到E:\temp_bak\
   2. 已经将主服务器证书的证书备份SQLSVR2.CER拷贝到E:\temp_bak\
   3. 在主库上执行：

Use master;CREATE CERTIFICATE SQLSVR2_cert AUTHORIZATION SQLSVR2_user FROM FILE = 'D:\temp_bak\SQLSVR2.cer'

1. 1. 在从库上执行：

Use master;

     CREATE CERTIFICATE SQLSVR1_cert    AUTHORIZATION SQLSVR1_user FROM FILE='D:\temp_bak\SQLSVR1.cer'

1. 主从库上授予登录名对数据库镜像端点的 CONNECT 权限。
   1. 主库：

      GRANT connect on endpoint::[默认的镜像端点] TO [SQLSVR2_login];

     GO

1. 1. 从库

      GRANT connect on endpoint::[默认的镜像端点] TO          [SQLSVR1_login];

     GO

1. 设置SQLSVR1中主库为完整恢复模式，并备份到从服务器SQLSVR2

        Alter databases db_name set recovery full with no_wait

      Backup databases db_name to disk='URL'

1. 还原SQLSVR1服务器上传过来的主数据库备份文件

      Restore database db_name from disk='RRL' with norecovery,stats=10,

     Move N'logic_name'to 'RRL', Move N'logic_name'to 'RRL'

1. （先做）设置镜像主从

      Alter database db_name set partner='TCP//192.168.1.1:5022'

1. （后做）设置镜像主从

      Alter database db_name set partner='TCP//192.168.1.2:5022'

从第五步开始，可以用图形操作

1. 确认主库恢复模式为 完整

 

1. 备份主数据库：先做完整备份、再做日志备份。

 

1. 将主库的完整备份文件和日志备份文件复制到从数据库主机，进行从库还原

 

 

 

注：还原 恢复状态选择 RESTORE WITH NORECOVERY 模式。

 

1. 回到主库创建EBAP数据库镜像

此处用配置安全性向导：

此处我们创建的是不带有见证服务器的镜像（所以不能自动故障转移）

 

此处默认即可：

 

选择 连接》连接到从数据库

然后下一步：

下图中的 主休、镜像 不用填写，下一步：

此处可选择开始或 不开始镜像。

 

选择运行模式：因是不带见证服务器的镜像，所以只能选择高性能或者高安全模式

 

到此，从主数据库镜像已完成。如下图：

 

 

                                   

注：如果需要故障自动转移功能的高可用性功能需要增加一台服务器做为见证服务器。

加入见证服务器配置如下：

1. 在见证服务器上安装SQL SERVER
2. 创建 master 数据库主密钥

Use master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';

GO

 

1. 然后对服务器实例创建一个用于其数据库镜像出站连接的加密证书。

CREATE CERTIFICATE SQLSVR3_cert

WITH SUBJECT = 'SQLSVR3 certificate for database mirroring',

start_date='2019-03-01',expiry_date='2030-01-01';

GO

1. 使用主体服务器实例的证书 SQLSVR3_cert 为主体服务器 SQLSVR3 创建端点。

CREATE ENDPOINT [默认的镜像端点]

STATE = STARTED

AS TCP (

LISTENER_PORT=5022

, LISTENER_IP = ALL

)

FOR DATABASE_MIRRORING (

AUTHENTICATION = CERTIFICATE SQLSVR3_cert

, ENCRYPTION = REQUIRED ALGORITHM AES

, ROLE = ALL

);

GO

 

1. 备份见证服务器 SQLSVR3 的加密证书。请确保此证书保存在安全可靠的存储介质上。

BACKUP CERTIFICATE SQLSVR3_cert TO FILE = 'e:\temp_bak\SQLSVR3.cer';

GO

 

1. 创建登录名，在见证服务器实例的 master 数据库中为主体、镜像服务器分别创建1个登录名

USE master;

CREATE LOGIN SQLSVR2_login

WITH PASSWORD = 'Sample@#';

GO

 

CREATE LOGIN SQLSVR1_login

WITH PASSWORD = 'Sample@#';

GO

 

 

 

1. 为新创建的2登录名分别创建一个用户

USE master;

CREATE USER SQLSVR2_user FOR LOGIN SQLSVR2_login;

GO

 

CREATE USER SQLSVR1_user FOR LOGIN SQLSVR1_login;

GO

 

1. 将用户与镜像服务器的证书相关联（注：需要有相应路径文件夹）

USE master;

CREATE CERTIFICATE SQLSVR2_cert

AUTHORIZATION SQLSVR2_user

FROM FILE = 'E:\temp_bak\SQLSVR2.cer'

GO

 

CREATE CERTIFICATE SQLSVR1_cert

AUTHORIZATION SQLSVR1_user

FROM FILE = 'E:\temp_bak\SQLSVR1.cer'

GO

 

1. 授予其各自的登录名对数据库镜像端点的 CONNECT 权限。

USE master;

GRANT connect on endpoint::[默认的镜像端点] TO [SQLSVR2_login];

GO

 

GRANT connect on endpoint::[默认的镜像端点] TO [SQLSVR1_login];

GO

 

1. 然后在主、从库上都创建登录名、用户、绑定证书、授权端点（已经复制见证服务器的SQLSVR3.cer证书过去）

USE master;

CREATE LOGIN SQLSVR3_login

WITH PASSWORD = 'Sample@#';

GO

CREATE USER SQLSVR3_user FOR LOGIN SQLSVR3_login;

GO

 

CREATE CERTIFICATE SQLSVR3_cert

AUTHORIZATION SQLSVR3_user

FROM FILE = 'E:\temp_bak\SQLSVR3.cer'

GO

 

GRANT connect on endpoint::[默认的镜像端点] TO [SQLSVR3_login];

GO

 

1. 在主库上进行与见证服务器连接（注：只能在主库上连接，10.10.10.85为见证服务器IP）

ALTER DATABASE ebap SET WITNESS = 'TCP://10.10.10.85:5022'

 

1. 然后查看见证服务器连接情况：

到此有见证服务器的数据库镜像搭建完成。

注：带有见证服务器的数据库镜像模式只能运行 高性能和高可用模式

 

数据库镜像主从切换

主备切换（注意有高可用、高安全、高性能模式的区别）

【1】.在高安全模式下：

在主机执行:

use master;

 

alter database Demo1 set partner failover;

即完成主备切换

【2】.在高性能模式下，需要先切换到高安全模式下再执行切换

use master;

alter database Demo1 set partner safety full;

alter database Demo1 set partner failover;

 

【3】.在主机（SQLSVR1）宕机的情况下在备机（SQLSVR2）进行强制切换：

use master;

alter database Demo1 set partner FORCE_SERVICE_ALLOW_DATA_LOSS;

 

 

当主机(SQLSVR1)重新开机后，在SQLSVR2机器上执行

use master;

alter database Demo1 set partner resume;

 

此时SQLSVR1成为了备机，而SQLSVR2成为了主机。

 

再到SQLSVR2机器上执行

alter database Demo1 set partner failover;

 

就成了SQLSVR1成为主机，SQLSVR2成为备机

 

 【4】切换镜像在高性能模式下(慎用，可能会丢失数据)

use master;

alter database Demo1 set partner safety off;

 

【5】.关闭数据库镜像

ALTER DATABASE Demo1 SET PARTNER OFF

 

 【6】.暂停与恢复数据库镜像

 

 在主体镜像服务器上，若是不小心日志过大，可以进行暂停来设置日志上限

（1）暂停：ALTER DATABASE AdventureWorks2012 SET PARTNER SUSPEND;

（2）恢复：ALTER DATABASE AdventureWorks2012 SET PARTNER RESUME;

 

【7】移除见证服务器

USE [master]

GO

ALTER DATABASE Demo1 SET WITNESS OFF

GO