SQLserver2016镜像备份不需要见证服务器(实战亲测)
目录
- 一、更改主机名字
- 二、更改host(主机/副机都要)
- 三、数据库/日志还原
- 1、数据库还原（备份时选择“完整”备份）：
- 2、日志还原（备份时选择“事务日志”就行）
- 四、创建镜像
- 配置镜像（整个操作都需要在master下操作）
- 切换到主机服务器
- 创建镜像端点
- 备份证书
- 创建登入
- 切换到副机服务器
- 创建一个证书
- 创建镜像端点
- 备份证书
- 创建登入
- 切换到主机服务器
- 切换到副机服务器
- 切换到副机服务器
- 切换到主机服务器
- 镜像日志清理
环境：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 --需要执行的存储过程
?④ 在子计划中按照实际需求设置“计划”
确定后保存，最后测试一下执行计划是否成功即可！！！
到此这篇关于SQLserver2016镜像备份不需要见证服务器(实战亲测)的文章就介绍到这了,更多相关SQL2016镜像备份内容请搜索电脑手机教程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持电脑手机教程网！
