SQLserver2016镜像备份不需要见证服务器(实战亲测)

环境:aliyun 系统版本:Microsoft Windows Server 2019 Datacenter 数据库版本:SQL server 2016 服务

环境: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镜像备份内容请搜索好代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持好代码网!

您可能有感兴趣的文章
SQL SERVER临时表排序问题的如何解决方法

Vscode上如何使用SQL的方法

SQL Server 批量插入数据的完美如何解决方案

浅析SQL Server授予了CREATE TABLE权限但是无法创建表

group by 按某一时间段分组统计并查询(推荐)