使用Powershell脚本做SQL Server镜像切换

使用Powershell脚本做SQL Server镜像切换。

1、打开脚本，确认本地IP地址，与浮动VIP地址配置正确。

2、执行以下脚本。将在主服务器上卸载VIP。并开始做数据库的切换。

###############################################################################################
#
# 文 件 名：mirror_failover.ps1
# 功能说明：镜像主备切换，并卸载主体服务器上的浮动IP和备份的浮动IP
# 运行环境：需要使用administrator账号在主体服务器上运行
# 作    者：老中堂
# 修改时间：2024-04-24
# 流    程：
#   1、检查SQL Server镜像数据库是否同步正常
#   2、卸载浮动IP
#   3、镜像切换到新的主体服务器，切换完成后本地服务器会成为SQL Server镜像服务器
#
###############################################################################################

# 业务浮动IP, 是应用端配置用来访问数据库的IP，只配置在主服务器端。
[string] $global:virtual_ip = '192.168.223.155'
# 本地IP, 用来本地远程访问
[string] $global:local_ip = '192.168.223.151'
# 备份浮动IP, 是备份服务器用来备份数据库
[string] $global:backup_virtual_ip = '169.254.117.21'
# 备份本地IP
[string] $global:backup_local_ip = '169.254.117.22'
#端口号
[string] $global:port = '1433'
# 全局变量只要已经卸载了浮动IP就可以开始切换数据库
[Boolean] $global:can_failover = $false

# 获取所有的IP信息
function get_all_ipaddrs {
    PROCESS {
        return Get-NetIPAddress
    }
}

# 获取IP地址的所有系统信息包含子网掩码、网卡INDEX、类型等信息
function get_ipaddr_info
{
    param (
        $addrs,
        $ip
    )
    PROCESS {
        foreach($cur_ip in $addrs) {
            if ($cur_ip.IPAddress -eq $ip) {
                return $cur_ip
            }
        }
        return $false
    }
}

# 卸载VIP,包括业务VIP和备份VIP
function unmount_vip {
    PROCESS {
        $ip_addrs = get_all_ipaddrs
        $vip = get_ipaddr_info -ip $global:virtual_ip -addrs $ip_addrs
        if ($vip -ne $false) {
            Remove-NetIPAddress -IPAddress $global:virtual_ip
        }
        $global:can_failover = $true
        Write-Output('正在卸载浮动IP: {0}' -f $global:virtual_ip)
        Write-Output('Can Failover: {0}' -f $global:can_failover)
        $bvip = get_ipaddr_info -ip $global:backup_virtual_ip -addrs $ip_addrs
        if ($bvip -ne $false) {
            Remove-NetIPAddress -IPAddress $global:backup_virtual_ip
        }
        Write-Output('正在卸载备份浮动IP: {0}' -f $global:backup_virtual_ip)
    }
}

#执行SQL脚本
function execute_sql {
    param (
        [string] $t_sql
    )
    PROCESS {
        return Invoke-Sqlcmd -Database 'master' -ServerInstance ($global:local_ip + ',' + $global:port) -Query $t_sql
    }
}

# 获取所有镜像数据库
function get_databases {
    [string] $t_sql = '
        SELECT DB_NAME(database_id) AS name
        FROM sys.database_mirroring
        WHERE mirroring_state = 4
        AND mirroring_role = 1
        ORDER BY name
        '
    return execute_sql -t_sql $t_sql
}

# 检查所有数据库镜像的同步状态
function check_database_mirroring {
    [string] $t_sql = '
    SELECT DB_NAME(database_id) AS name,
    mirroring_state_desc
    FROM sys.database_mirroring
    WHERE mirroring_state <> 4
    '
    return execute_sql -t_sql $t_sql
}

# 对数据库做主备切换
function failover {
    param (
        $db
    )
    PROCESS {
        $sql_mode = 'ALTER DATABASE [{0}] SET PARTNER FAILOVER'
        $t_sql = $sql_mode -f $db
        $result = execute_sql -t_sql $t_sql
        return $result
    }
}

# 将数据库改为同步模式
function safety_full {
    param (
        $db
    )
    PROCESS {
        $sql_mode = 'ALTER DATABASE [{0}] SET PARTNER SAFETY FULL'
        $t_sql = $sql_mode -f $db
        Execute_sql -t_sql $t_sql
    }
}

# 切换所有符合条件的数据库
function failover_all_dbs {
    PROCESS {
        $databases = get_databases
        foreach($db in $databases) {
            Write-Output('[{0}] 开始切换' -f $db.name)
            safety_full -db $db.name
            failover -db $db.name
            Write-Output('[{0}] 切换完成' -f $db.name)
        }
        Write-Output('所有数据库切换完成')
    }
}

function main {
    PROCESS {
        # 检查所有数据库的同步状态
        $checked = check_database_mirroring
        if ($checked) {
            Write-Output('请检查以下数据库的同步状态是否正常')
            foreach($db in $checked) {
                Write-Output('Database: {0} | Mirroring_Satae:{1}' -f $db.name, $db.mirroring_state_desc)
            }
            return $false
        }

        # 卸载VIP
        unmount_vip
        $all_ips = get_all_ipaddrs
        $all_ips
        # 数据库切换
        if ($global:can_failover -eq $true) {
            failover_all_dbs
        }
    }
}

main

3、在备机上挂载IP

# 业务浮动IP, 是应用端配置用来访问数据库的IP，只配置在主服务器端。
[string] $global:virtual_ip = '192.168.223.155'
# 本地IP, 用来本地远程访问
[string] $global:local_ip = '192.168.223.151'
# 备份浮动IP, 是备份服务器用来备份数据库
[string] $global:backup_virtual_ip = '169.254.117.21'
# 备份本地IP
[string] $global:backup_local_ip = '169.254.117.22'
# 用来存储所有IP的信息
[Object] $global:all_addrs

function get_all_ipaddrs {
    PROCESS {
        return Get-NetIPAddress
    }
}

function get_ipaddr_info {
    param (
        $ip
    )
    PROCESS {
        foreach($cur_ip in $global:all_addrs) {
            if ($cur_ip.IPAddress -eq $ip) {
                return $cur_ip
            }
        }
        return $false
    }
}

function mount_vip {
    param (
        $addr,
        $local_ip
    )
    PROCESS {
        if (Test-Connection -ComputerName $addr -Count 1 -Delay 1)
        {
            Write-Output('')
        }
        New-NetIPAddress -IPAddress $addr -InterfaceIndex $local_ip.InterfaceIndex -PrefixLength $local_ip.PrefixLength -AddressFamily $local_ip.AddressFamily
    }
}

function main {
    PROCESS {
        # 获取所有本地IP信息
        $global:all_addrs = get_all_ipaddrs

        # 基于本地IP的信息配置浮动IP
        $local_ip = get_ipaddr_info -ip $global:local_ip
        if ($local_ip -ne $false) {
            mount_vip -addr $global:virtual_ip -local_ip $local_ip
        }
        else {
            Write-Output('浮动IP:{0}未能挂载成功,请检查本地IP:{1}是否配置正确' -f $global:virtual_ip, $global:local_ip)
        }

        # 基于本地备份IP的信息配置备份浮动IP
        $backup_local_ip = get_ipaddr_info -ip $global:backup_local_ip
        if ($backup_local_ip -ne $false) {
            mount_vip -addr $global:backup_virtual_ip -local_ip $backup_local_ip
	    }
        else {
            Write-Output('备份浮动IP:{0}未能挂载成功,请检查本地IP:{1}是否配置正确' -f $global:backup_virtual_ip, $global:backup_local_ip)
        }

        # 重新获取本地IP信息，用来检查是否挂载成功
        $global:all_addrs = get_all_ipaddrs
        $global:all_addrs
    }
}

main