当前位置：首页 > news >正文

sqlserver镜像设置

news 来源：原创 2025/5/13 15:01:49

本案例是双机热备，只设置主体服务器（主）和镜像服务器（从），不设置见证服务器

设置镜像前先检查是否启用了主从服务器数据库的 TCP/IP协议和 RemoteDAC

（1）打开SQL Server配置管理器，选择“SQL Server网络配置”-“MSSQLSERVER的协议”，在右侧启用TCP/IP协议，打开TCP/IP属性把IP地址设置为本地服务器地址。

（2）在SQL Server Management Studio中右键服务器连接选择Facets，方面选择“外围应用配置器”，将属性“RemoteDACEnabled”的值改为“True”。

一、SQL Server基于数据库镜像的主从同步

主服务器：SQLSVR1, 192.168.10.2

从服务器：SQLSVR2, 192.168.10.3

--如果已经执行过镜像，则需要删除已有的镜像端点、加密证书、登录用户。相关命令：
--关闭数据库镜像
alter database Test set partner off;
--删除端点
drop endpoint Mirroring;
--删除关联的证书和登录用户
drop certificate SQLSVR1_cert;
drop certificate SQLSVR2_cert;
drop login SQLSVR2_login;
drop user SQLSVR2_user;

--1、创建 master 数据库主密钥
USE master;
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'password';
GO

--2、对服务器实例创建一个用于其数据库镜像出站连接的加密证书
CREATE CERTIFICATE SQLSVR1_cert
WITH SUBJECT = 'SQLSVR1 certificate for database mirroring',
EXPIRY_DATE='2100-01-01'
GO

--3、使用主服务器实例的证书 SQLSVR1_cert 为主服务器 SQLSVR1 创建端点( Mirroring为默认的镜像端点)
CREATE ENDPOINT Mirroring
STATE = STARTED AS TCP (
LISTENER_PORT = 5022,
LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE SQLSVR1_cert,
ENCRYPTION = REQUIRED ALGORITHM AES,
ROLE = ALL
);
GO

--如果已存在[镜像]导致创建端点失败
--查询列表

SELECT * FROM sys.endpoints;
SELECT * FROM sys.endpoints WHERE name = 'Mirroring';

--删除端点再重新创建
ALTER ENDPOINT [镜像] STATE = STOPPED;
GO

DROP ENDPOINT [镜像];
GO

--4、备份主体服务器 SQLSVR1 的加密证书
BACKUP CERTIFICATE SQLSVR1_cert TO FILE = 'C:\cert\SQLSVR1_cert.cer';
GO

--5、将从服务器的证书备份SQLSVR2.CER拷贝到主服务器的证书备份目录

--6、在主服务器实例的 master 数据库中为镜像服务器创建一个登录名并设置密码
USE master;
CREATE LOGIN SQLSVR2_login
WITH PASSWORD = 'password';
GO

--7、为6中新创建的登录名创建一个用户
USE master;
CREATE USER SQLSVR2_user
FOR LOGIN SQLSVR2_login;
GO

--8、将创建的用户与镜像服务器的证书相关联
USE master;
CREATE CERTIFICATE SQLSVR2_cert
AUTHORIZATION SQLSVR2_user
FROM FILE = 'C:\cert\SQLSVR2_cert.cer';
GO

--9、授予其7中登录用户名对数据库镜像端点的 CONNECT 权限( Mirroring为默认的镜像端点)
GRANT CONNECT ON ENDPOINT:: Mirroring TO SQLSVR2_login;
GO

--10、设置SQLSVR1中主库为完整恢复模式，并备份到从服务器SQLSVR2（这部分建议手动在SSMS操作）
USE master;

ALTER DATABASE db_name SET recovery full with no_wait;
BACKUP DATABASE db_name
TO DISK = 'C:\backup\db_name-full.bak'
WITH FORMAT,CHECKSUM
GO
BACKUP LOG db_name
TO DISK = 'C:\backup\db_name-log.trn'
WITH FORMAT,CHECKSUM
GO

注意：
【1】先关闭事务备份作业。
【2】注意：模式的区别
（1）简单模式：设置为完整模式后全备到SQLSVR2即可；
（2）大容量模式：设置为完整模式后，要利用之前的大容量模式下的全备；（即要把主备+事务备+设置好完整模式后的事务备）；
（3）完整模式：
　　如要利用以前的备份，则需要全备+全备之后的所有事务备；
　　如果库小可以直接全备，再传输到SQLSVR2。

--11、（后做）设置镜像主从
ALTER DATABASE db_name
SET partner = 'TCP://192.168.10.3:5022'

--1、创建 master 数据库主密钥
USE master;
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'password';
GO

--2、对服务器实例创建一个用于其数据库镜像出站连接的加密证书
CREATE CERTIFICATE SQLSVR2_cert
WITH SUBJECT = 'SQLSVR2 certificate for database mirroring',
EXPIRY_DATE='2100-01-01'
GO

--3、使用从服务器实例的证书 SQLSVR2_cert 为从服务器 SQLSVR2 创建端点( Mirroring为默认的镜像端点)
CREATE ENDPOINT Mirroring
STATE = STARTED AS TCP (
LISTENER_PORT = 5022,
LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE SQLSVR2_cert,
ENCRYPTION = REQUIRED ALGORITHM AES,
ROLE = ALL
);
GO

--如果已存在[镜像]导致创建端点失败
--查询列表
SELECT * FROM sys.endpoints;
SELECT * FROM sys.endpoints WHERE name = 'Mirroring';

--删除端点再重新创建
ALTER ENDPOINT [镜像] STATE = STOPPED;
GO

DROP ENDPOINT [镜像];
GO

--4、备份从服务器 SQLSVR2 的加密证书
BACKUP CERTIFICATE SQLSVR2_cert TO FILE = 'C:\cert\SQLSVR2_cert.cer';
GO

--5、将主服务器的证书备份SQLSVR1.CER拷贝到从服务器的证书备份目录

--6、在从服务器实例的 master 数据库中为主服务器创建一个登录名并设置密码
USE master;
CREATE LOGIN SQLSVR1_login
WITH PASSWORD = 'password';
GO

--7、为6中新创建的登录名创建一个用户
USE master;
CREATE USER SQLSVR1_user
FOR LOGIN SQLSVR1_login;
GO

--8、将创建的用户与主服务器的证书相关联
USE master;
CREATE CERTIFICATE SQLSVR1_cert
AUTHORIZATION SQLSVR1_user
FROM FILE = 'C:\cert\SQLSVR1_cert.cer';
GO

--9、授予其7中登录用户名对数据库镜像端点的 CONNECT 权限( Mirroring为默认的镜像端点)
GRANT CONNECT ON ENDPOINT:: Mirroring TO SQLSVR1_login;
GO

--10、还原SQLSVR1服务器上传过来的主数据库备份文件（这部分建议手动在SSMS操作）
USE master;
RESTORE DATABASE db_name
FROM DISK = 'C:\backup\db_name-full.bak'
WITH FILE = 1,
MOVE N'db_name' TO N'C:\DB\db_name.mdf',
MOVE N'db_name_log' TO N'C:\DB\db_name_log.ldf',
NOUNLOAD, NORECOVERY, STATS = 10
GO
RESTORE DATABASE db_name
FROM DISK = N'C:\backup\db_name-log.trn'
GO

注意：
（1）还原数据库：一定要和主库的数据库名一模一样。
（2）还原模式：一定要选择norecovery模式，否则失败。
（3）还原库文件的存放路径：尽量和主库的盘符及目录及名字一摸一样，否则不支持新增文件同步（若没用到新增文件同步暂时不用管）。
如果机器盘符目录不一致，那么需要在还原时用move选项来进行位置移动。

--11、（先做）设置镜像主从
ALTER DATABASE db_name
SET partner = 'TCP://192.168.10.2:5022'

注意：
从库的版本必须和主库一致，如都是标准版，才能设置镜像。

注：以上命令全程都在系统数据库 master 创建的查询执行即可

二、SQL Server 数据库备份还原

1.备份数据库，备份两次。

(1)主服务器找到需要备份的数据库，右键 → 任务(Tasks) → 备份(Back up)，备份类型选择完整(Full)，备份到磁盘路径自定义命名跟数据库一样，得到 .bak 备份文件。

(2)重复同上操作，但备份类型选择事务日志(Transaction log)，得到 .trn 备份文件或者追加到上一步的.bak文件中。

2.还原数据库，也是分两次还原，将备份文件 .bak .trn 拷贝到从服务器主机

(1)在SSMS连接数据库，右键数据库，选择还原数据库（还原文件和文件组）都可以 → 目标数据库命名一样Test → 选择源设备(From device)，然后找到添加 .bak 文件然后确定 → （重要）选项恢复状态选择不对数据库执行任何操作（RESTORE WITH NORECOVERY）→ 点击确定开始还原数据库。