Sqlserver镜像高可用搭建
1.安装前准备
- 系统相同
- 数据库软件版本补丁相同
- 数据库目录相同
- 数据库恢复模式为完整
- 主备可以ping通，可以相互通过SMSS登录对方
2.创建证书
2.1.主服务器和镜像服务器创建主密钥
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';
-- 删除主密钥
USE master;
GO
DROP MASTER KEY
2.2.主服务器和镜像服务器创建证书
-- 主服务器
USE master
GO
CREATE CERTIFICATE Host_A_Cert
WITH Subject = 'Host_A Certificate',
Expiry_Date = '2099-1-1'; -- 过期日期
-- 镜像服务器
USE master
GO
CREATE CERTIFICATE Host_B_Cert
WITH Subject = 'Host_B Certificate',
Expiry_Date = '2099-1-1'; -- 过期日期
-- 删除证书
USE master;
GO
DROP CERTIFICATE HOST_A_cert
2.3.主服务器和镜像服务器创建端点
-- 使用Host_A_Cert证书创建端点（主服务器）
USE master;
GO
IF NOT EXISTS ( SELECT 1
FROM sys.database_mirroring_endpoints )
BEGIN
CREATE ENDPOINT [DatabaseMirroring] STATE = STARTED AS TCP ( LISTENER_PORT = 5022,
LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION =
CERTIFICATE Host_A_Cert, ENCRYPTION = REQUIRED Algorithm AES, ROLE =
ALL );
END
-- 使用Host_B_Cert证书创建端点（镜像服务器）
USE master;
GO
IF NOT EXISTS ( SELECT 1
FROM sys.database_mirroring_endpoints )
BEGIN
CREATE ENDPOINT [DatabaseMirroring] STATE = STARTED AS TCP ( LISTENER_PORT = 5022,
LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION =
CERTIFICATE Host_B_Cert, ENCRYPTION = REQUIRED Algorithm AES, ROLE =
ALL );
END
2.4.主服务器和镜像服务器备份证书
-- 主服务器
USE master;
GO
BACKUP CERTIFICATE Host_A_Cert
TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Host_A_Cert.cer';
-- 镜像服务器
USE master;
GO
BACKUP CERTIFICATE Host_B_Cert
TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Host_B_Cert.cer';
2.5.主服务器和镜像服务器创建登录账号
-- 主服务器，创建给镜像服务器登录的登录账号
USE master;
GO
CREATE LOGIN Host_B_Login WITH PASSWORD = 'Pa$$w0rd';
-- 镜像服务器
USE master;
GO
CREATE LOGIN Host_A_Login WITH PASSWORD = 'Pa$$w0rd';
2.6.主服务器和镜像服务器创建用户
-- 主服务器创建
USE master;
GO
CREATE USER Host_B_User For Login Host_B_Login;
-- 镜像服务器创建
USE master;
GO
CREATE USER Host_A_User For Login Host_A_Login;
2.7.证书授权用户
首先将2.4创建的证书相互传送一份
-- 主服务器创建
USE master;
GO
CREATE CERTIFICATE Host_B_Cert
AUTHORIZATION Host_B_User
FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Host_B_Cert.cer';
-- 镜像服务器创建
USE master;
GO
CREATE CERTIFICATE Host_A_Cert
AUTHORIZATION Host_A_User
FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Host_A_Cert.cer';
2.8.授权访问端点
-- 主服务器
USE master;
GO
GRANT CONNECT ON ENDPOINT::[DatabaseMirroring] TO [Host_B_Login];
-- 镜像服务器
USE master;
GO
GRANT CONNECT ON ENDPOINT::[DatabaseMirroring] TO [Host_A_Login];
3.备份还原数据库
备份
还原，恢复状态要选择RESTORE WITH NORECOVERY
4.启动镜像
这次是镜像服务器然后是主服务器，100是主服务器的地址，
-- 镜像服务器
USE [master]
GO
ALTER DATABASE test
SET PARTNER = 'TCP://192.168.2.100:5022';
-- 主服务器
USE [master]
GO
ALTER DATABASE test
SET PARTNER = 'TCP://192.168.2.200:5022';
-- 删除镜像
USE [master]
GO
alter database test set partner off;
5.管理SQL
5.1.修改镜像运行模式
-- 高性能模式，事务不需要在主和镜像数据库提交才返回
USE [master]
GO
ALTER DATABASE [test] SET SAFETY OFF;
-- 高安全模式，事务需要在主和镜像数据库提交才返回
USE [master]
GO
ALTER DATABASE [test] SET SAFETY FULL;
5.2.查看镜像同步状态
-- 查看镜像同步状态
USE [master]
GO
select * from sys.database_mirroring where database_id in (DB_ID('test'),DB_ID('monkey'));
5.3.手动切换主和镜像
-- 手动切换主和镜像，在主体服务器上执行，运行模式必须在SAFETY FULL模式下
USE [master]
GO
ALTER DATABASE test SET PARTNER FAILOVER;
5.4.主库突然宕机，拉起镜像
-- 强制拉起来镜像库
USE [master]
GO
alter database test set partner FORCE_SERVICE_ALLOW_DATA_LOSS;
-- 主库恢复后，重新恢复镜像（在当前主库）
USE [master]
GO
alter database test set partner resume;
-- 需要的话，参考5.3手动切换为原来的模样
USE [master]
GO
ALTER DATABASE test SET PARTNER FAILOVER;
6.C#连接镜像数据库
目前测试了C#和python，其中C#的ado.net使用如下连接字符串可以在镜像切换后继续连上数据库，python的odbc不行。
// 配置数据库连接参数
string principalServer = "192.168.2.100";
string mirrorServer = "192.168.2.200";
string database = "monkey";
string username = "sa";
string password = "monkey";
// 构建连接字符串
string connectionString = $"Data Source={principalServer};Failover Partner={mirrorServer};Initial Catalog={database};User Id={username};Password={password}";
