SQL server2016镜像备份（不需要见证服务器）实战亲测！

环境：aliyun

系统版本：Microsoft Windows Server 2019 Datacenter

数据库版本：SQL server 2016

服务器1：192.168.1.1 test001   

服务器2：192.168.1.2 test002

---

一、更改主机名字

主机：test001 →更改为→ test001.xxx.net

副机：test002 →更改为→ test002.xxx.net

操作过程（主/副机操作一样）

更改完会提示需要重启，确认重启即可；

二、更改host(主机/副机都要)

进入C:\Windows\System32\drivers\etc，找到host文件，编辑添加主机和副机的IP  主机名

如：

192.168.1.1     test001.xxx.net

192.168.1.2    test002.xxx.net

 

三、数据库/日志还原

主/副数据库的名字，账号密码建议统一一样；

1、数据库还原（备份时选择“完整”备份）：

 

2、日志还原（备份时选择“事务日志”就行）

在还原日志时，“选项”里选择“norecovery”选项；

四、创建镜像配置镜像（整个操作都需要在master下操作）

信息确认：

主机（生产数据库）：test001.xxx.net

副机（镜像数据库）：test002.xxx.net

切换到主机服务器

创建数据库主密钥

--user master --a.创建数据库主密钥 create master key encryption by password = 'qwe123'; --可用以下语句查看生成的数据库主密钥 --select * from   sys.symmetric_keys ;

 创建一个证书

--b.创建一个证书  create certificate db_host_a_cert with subject = 'db_host_a certificate for database mirroring', start_date = '02/12/2016', expiry_date = '12/31/2039' --db_host_a_cert 证书名字 --查询证书 --select * from sys.certificates; 

【创建主密钥小插曲 不报错可以忽略】

---

---

如果在创建数据库主密钥时，提示已经存在；

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'kk_2020'; ############################返回提示########################### 消息 15578，级别 16，状态 1，第 1 行 数据库中已存在主密钥。执行此语句前，请先删除该主密钥。

删除主密钥

DROP MASTER KEY ############################返回提示########################### 消息 15580，级别 16，状态 1，第 1 行 无法删除 主密钥，因为 证书 'db_host_a_cert' 是由它加密的。

先删除证书再删除主密钥即可

DROP CERTIFICATE db_host_a_cert ############################返回提示########################### 命令已成功完成。 ###若提示正在有一个或多个端点正在使用，要先 drop endpoint xxx端点名 ###########################删除主密钥########################### DROP MASTER KEY ############################返回提示########################### 命令已成功完成。

---

---

 创建镜像端点 

--c.创建镜像端点  create endpoint db_mirr state = started as tcp(listener_port=5022, --镜像端点使用的通信端口 listener_ip = all)              -- 侦听的IP地址     for database_mirroring (     authentication = certificate db_host_a_cert,   -- 证书身份验 encryption = required algorithm rc4,           -- 不对传输的数据加密,如果需要加密,可以配置为 SUPPORTED 或 REQUIRED, 并可选择加密算法     role = all);                                   -- 端点支持所有的数据库镜像角色, 也可以设置为 WITNESS(仅见证服务器),或 PARTNER(仅镜像伙伴) --查询 --select * from sys.tcp_endpoints        --select * from sys.database_mirroring_endpoints; 

 备份证书

--d.备份证书  backup certificate db_host_a_cert to file = 'D:\ShareFile\db_a_run.cer';

 创建登入

--e.创建登入  create login to_host_a_login with password = 'qwe123'; create user to_host_a_user for login to_host_a_login; ******************************************************* --有问题的时候需要删除之前的用户 --drop login to_host_a_login，drop user to_host_a_user

切换到副机服务器

-- user master --1.创建数据库主密钥  create master key encryption by password = 'qwe123'; --可用以下语句查看生成的数据库主密钥  --select * from   sys.symmetric_keys ; 

创建一个证书 

--2.创建一个证书  create certificate db_host_b_cert with subject = 'db_host_b certificate for database mirroring', start_date = '02/12/2016', expiry_date = '12/31/2029' --查询 --select * from sys.certificates; 

创建镜像端点

--3.创建镜像端点  create endpoint db_mirr state = started as tcp( listener_port=5022 ,listener_ip = all ) for database_mirroring( authentication = certificate db_host_b_cert, encryption = required algorithm rc4, role = all); --查询 --select * from sys.tcp_endpoints  --select * from sys.database_mirroring_endpoints; 

 备份证书

--4.备份证书  backup certificate db_host_b_cert to file = 'D:\ShareFile\db_b_run.cer';

创建登入

--5.创建登入  create login to_host_b_login with password = 'qwe123'; create user to_host_b_user for login to_host_b_login;

切换到主机服务器

从副机服务器上D:\ShareFile\下拷贝备份出来的证书db_b_run.cer到主机服务器的D:\ShareFile\下 

还原副机服务器证书到主机服务器上； 

use master create certificate db_host_b_cert authorization to_host_a_user from file = 'D:\ShareFile\db_b_run.cer' --赋权  grant connect on endpoint::db_mirr to [to_host_a_login];

切换到副机服务器 

从主机服务器上D:\ShareFile\下拷贝备份出来的证书db_a_run.cer到副机服务器的D:\ShareFile\下 

还原主机服务器证书到副机服务器上；

use master create certificate db_host_a_cert authorization to_host_b_user from file = 'D:\ShareFile\db_a_run.cer' --赋权  grant connect on endpoint::db_mirr to [to_host_b_login];

设置伙伴（自动启动镜像） 

切换到副机服务器 

alter database DB_1 set partner = 'tcp://test001.xxx.net:5022';

切换到主机服务器 

alter database DB_2 set partner = 'tcp://test002.xxx.net:5022';

镜像日志清理

在做SQL 2016镜像，由于主服务器必须做完整备份，这时log日志很大，必须定期清理log日志，将下列存储过程每6个小时执行一次，其定期会将日志文件缩小到300M

Create  PROC [dbo].[CleanTranLog] AS      BEGIN          DECLARE @num TINYINT --执行次数          DECLARE @backLogName VARCHAR(100) ;--备份日志文件名称          DECLARE @backLogPath VARCHAR(100) ; --备份日志文件的路径          SET @num = 0 ;          SET @backLogPath = N'C:\SQLBackup' ;--设定需要备份日志的路径          --备份3次镜像日志文件，同时删除          WHILE( @num < 3 )              BEGIN                  DECLARE @LogPath VARCHAR(100)                  SET @backLogName = CAST(@num as VARCHAR(2)) + '.trn' ;                  SET @LogPath = @backLogPath + '\' + @backLogName                  BACKUP LOG DB  TO DISK = @LogPath WITH NOFORMAT, NOINIT, NAME= @backLogName, SKIP, REWIND, NOUNLOAD,STATS = 10                  SET @num = @num + 1                  --删除刚备份的trn日志文件结束的备份日志文件                  EXECUTE master.dbo.xp_delete_file 0, @LogPath ;              end           --收缩日志文件到300M          DBCC SHRINKFILE (DB_log, 300) ;                        --注意          --DB 这里指： 数据库名称          --DB_log 为日志逻辑名称 可以通过数据库属性中“文件”查看日志的逻辑名称。      END

存储过程创建后（手动执行一下测试是否成功）

① 新建一个维护计划

② 在工具箱内打开“执行T-SQL语句”

​

双击打开执行框，确认即可

USE [testdb]      --数据库 GO exec CleanTranLog         --需要执行的存储过程

 

​

④ 在子计划中按照实际需求设置“计划”

确定后保存，最后测试一下执行计划是否成功即可！！！

Perfect！！！