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;

参考:配置SQL Server镜像——非域环境