SQL server2016镜像备份（不需要见证服务器）实战亲测！
环境：aliyun
系统版本：Microsoft Windows Server 2019 Datacenter
数据库版本：SQL server 2016
服务器1：192.168.1.1 test001
服务器2：192.168.1.1 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！！！