MSSQL分布式可用性组异地容灾故障转移的自动化脚本_mssql 数据库故障转移自动切换-优快云博客

本文链接：https://blog.youkuaiyun.com/csdn277/article/details/107512465

本文介绍了一种使用Python和pymssql库实现SQL Server数据库分布式可用性组故障转移的方法。通过官方推荐的五个步骤，包括停止事务、设置同步提交、改变角色、测试故障转移准备情况和实际故障转移，确保数据安全性和系统稳定性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

采用的故障转移方式是官方提供的步骤，实现方式是python3+pymssql

这里直接贴代码造福全人类：

注意的是，直接用是不行的，该脚本是针对特定开发测试环境编写的，不具有通用性，当然小修改一下就可以了。

#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
mssqlScript.py 处理sql server 数据库分布式可用性组的故障转移
故障转移方式采用官方推荐的5个步骤
Steps:
#####1.若要确保不会丢失任何数据，请停止全局主数据库（即主可用性组的数据库）上的所有事务，然后将分布式可用性组设置为同步提交。
#####2.等待直到分布式可用性组同步完成，且每个数据库具有相同的 last_hardened_lsn。
#####3.在全局主要副本上，将分布式可用性组角色设置为 SECONDARY。
#####4.测试故障转移就绪情况。
#####5.故障转移主要可用性组。
"""
# author: zhoujiajun@gsafety.com
# Copyright by GSafety.HeFei 2020
import pymssql
import decimal
import time
import sys
import configparser

decimal.__version__

cp = configparser.ConfigParser()
cp.read("database.ini")
system = 'system'
distributeAG = cp.get(system, 'distribute_availability_group_name')
primaryAG = cp.get(system, 'primary_availability_group_name')
secondaryAG = cp.get(system, 'secondary_availability_group_name')
# ------------------------------------------------sql-------------------------------------------------------------------
# -- sets the distributed availability group to synchronous commit
sql1 = " ALTER AVAILABILITY GROUP [" + distributeAG + "] " \
       "MODIFY " \
       "AVAILABILITY GROUP ON " \
       "'" + primaryAG + "' WITH " \
       "( " \
       "AVAILABILITY_MODE = SYNCHRONOUS_COMMIT " \
       "), " \
       "'" + secondaryAG + "' WITH  " \
       "( " \
       "AVAILABILITY_MODE = SYNCHRONOUS_COMMIT " \
       " );"

# -- verifies the commit state of the distributed availability group
sql2 = "select ag.name, ag.is_distributed, ar.replica_server_name, ar.availability_mode_desc," \
       " ars.connected_state_desc, ars.role_desc, ars.operational_state_desc," \
       " ars.synchronization_health_desc from sys.availability_groups ag  " \
       "join sys.availability_replicas ar on ag.group_id=ar.group_id " \
       "left join sys.dm_hadr_availability_replica_states ars " \
       "on ars.replica_id=ar.replica_id " \
       "where ag.is_distributed=1 "

# -- Run this query on the Global Primary and the forwarder
# -- Check the results to see if synchronization_state_desc is SYNCHRONIZED,
# and the last_hardened_lsn is the same per database on both the global primary and forwarder
# -- If not rerun the query on both side every 5 seconds until it is the case
sql3 = "SELECT ag.name" \
       ", drs.database_id" \
       ", db_name(drs.database_id) as database_name" \
       ", drs.group_id" \
       ", drs.replica_id" \
       ", drs.synchronization_state_desc" \
       ", drs.l