MSSQL2016 AG主从同步简单设置

若水
2026-04-20 20:32:15
技术
MYSQL/MSSQ

在建立了集群故障转移后, 建立相关的AG, 然后开始下面的操作;

-- 在主库中执行:

-- 1. 最新完整备份
BACKUP DATABASE [AIS202512142220260]
TO DISK = 'F:\bak\FULL.bak'
WITH COMPRESSION, INIT, STATS=10;
GO


-- 2. 立即做日志备份
BACKUP LOG [AIS202512142220260]
TO DISK = 'F:\bak\LOG1.trn'
WITH COMPRESSION, INIT;
GO

-- 3.（把这 2 个文件复制到从库 D:\Backup\）

-- 下面在从库中执行

-- 4. 从库删除旧库(如果存在的话, 不存在则跳过)
USE master;
GO
ALTER DATABASE [AIS202512142220260] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE [AIS202512142220260];
GO


-- 5. 还原完整备份(从库)
RESTORE DATABASE AIS202512142220260
FROM DISK = 'E:\bak\FUll.bak'
WITH 
    REPLACE, NORECOVERY,  -- 必须用这个，才能继续接日志
    MOVE 'SqlServer_BC_20120727194252_Data'   TO 'E:\Data\AIS20251214222026.mdf',
    MOVE 'SqlServer_BC_20120727194252_Log'    TO 'E:\Data\AIS20251214222026.ldf';


-- 6. 还原第1个日志备份(从库)
RESTORE LOG [AIS202512142220260]
FROM DISK = 'E:\bak\LOG1.trn'
WITH NORECOVERY;
GO

-- 这一步在主库执行
-- 7.再次做日志备份, 这一步是为了追上生产环境最新数据， 复制 LOG2.trn 到从库
BACKUP LOG [AIS202512142220260]
TO DISK = 'F:\bak\LOG2.trn'
WITH COMPRESSION, INIT;
GO

-- 8. 还原第2个日志(从库)
RESTORE LOG [AIS202512142220260]
FROM DISK = 'E:\bak\LOG2.trn'
WITH NORECOVERY;
GO


-- 9. 加入 AG
ALTER DATABASE [AIS202512142220260]
SET HADR AVAILABILITY GROUP = [AG_AIS];
GO

在此之后, AG同步就做完了, 下面附上相应的状态查询语句:

AG检查状态:

-- 检查 AG 同步队列

SELECT
    ag.name AS AG名称,
    ar.replica_server_name AS 副本服务器,
    --drs.database_name,
    drs.synchronization_state_desc AS 同步状态,
    -- 主库有待发送的日志大小 (KB)
    drs.log_send_queue_size,
    -- 辅助库有待重做的日志大小 (KB)
    drs.redo_queue_size,
    drs.synchronization_health_desc AS 健康状态
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id
JOIN sys.availability_groups ag ON ar.group_id = ag.group_id
WHERE ag.name = 'AG_AIS';

关于用户的建立, 首先在主库先建立登录用户和数据库用户(这个会自动同步到从库), 然后在主库建立登录用户

-- 主库执行
USE [AIS202512142220260];
GO

-- 创建登录名（服务器级别）
CREATE LOGIN [report_user] 
WITH PASSWORD = N'Aa@12345600088',
CHECK_EXPIRATION = OFF,
CHECK_POLICY = ON;
GO

-- 创建数据库用户
CREATE USER [report_user] 
FOR LOGIN [report_user];
GO

-- 授予【只读/查询权限】（最安全，只给查询）
ALTER ROLE [db_datareader] ADD MEMBER [report_user];
GO

--  在主库中, 查出已经建立用户的SID(登录SID)
USE master;
GO

SELECT 
    name AS 登录名,
    sid AS 登录SID
FROM sys.sql_logins 
WHERE name = 'report_user_tang';
GO



-- 下面在从库执行

-- KILL掉 report_user 的所有连接
DECLARE @sql NVARCHAR(1000)
WHILE EXISTS (SELECT * FROM sys.dm_exec_sessions WHERE login_name = 'report_user')
BEGIN
    SELECT @sql = 'KILL ' + CAST(session_id AS VARCHAR)
    FROM sys.dm_exec_sessions
    WHERE login_name = 'report_user'
    
    EXEC sp_executesql @sql
END
GO

-- -- 先删掉旧的用户
DROP LOGIN report_user; 
GO


-- 执行这一段(建立登录用户)
CREATE LOGIN report_user
WITH 
    PASSWORD = N'Aa@123456',
    SID = 0x54AA942152A2AF4D9B65F07AE11CA3F1,  --这里填之前查询主库的SID
    CHECK_EXPIRATION = OFF,
    CHECK_POLICY = ON;
GO

本文为宁若水!原创文章,转载无需和我联系,但请注明来自[若水]博客 www.lalaya.net