Category Archives: Sql Server

For managing several environments, we utilize Ivanti Automation Manager, leveraging Microsoft SQL Server as the database. According to the documentation, Ivanti Automation Manager does not support “SQL Server Always On availability groups,” and unfortunately, there is no mention of using a “SQL Server multi-subnet failover cluster.”

Supported database systems https://help.ivanti.com/res/help/en_US/IA/2024/Admin/Content/48735.htm

Within our environments, however, the use of a “SQL Server multi-subnet failover cluster” is the standard database configuration that we must use. Simply by adding the parameter “MultiSubnetFailover=True” to the database connection string, the SQL Client recognizes that it’s a MultiSubnetFailover cluster. However, since the database connection string is initiated by Ivanti Automation Manager, we don’t have the ability to add “MultiSubnetFailover=True” to it directly. This parameter will need to be included from within the Ivanti Automation Manager software.

SqlConnection.ConnectionString Property https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.connectionstring

Upon inquiry with Ivanti, it was indeed confirmed that there is no support for a “SQL Server multi-subnet failover cluster.” The request from Ivanti was to submit a Uservoice through the Ivanti Ideas Portal for this feature. We have duly submitted the request as requested. However, for unclear reasons, Ivanti has chosen not to implement this feature.

Uservoice: MultiSubnetFailover support (Microsoft OLE DB Driver for SQL Server) https://ivanti.ideas.aha.io/ideas/IA-I-44

Without the “MultiSubnetFailover=True” value in the connection string, for example, Ivanti Automation Manager may fail to start after the active SQL node is changed.

Since we couldn’t avoid using a SQL Server multi-subnet failover cluster, we have temporarily resolved this by implementing a script. It may not be the most elegant solution, but it gets the job done!

We have created a scheduled task on all servers where the Ivanti Automation Manager Console and Ivanti Dispatchers are installed. This task runs every 5 minutes and executes a PowerShell script, which checks if the connection to the database is still possible. If not, it identifies the active SQL node and updates the hosts file accordingly, allowing the Consoles and Dispatchers to establish a connection with the database again.

<#
.SYNOPSIS
This PowerShell script updates the hosts file on a target machine with the current active SQL node IP address.
It checks if the specified target hostname is reachable. If not, it determines the active SQL node and updates the hosts file accordingly.

.DESCRIPTION
This script is designed to be run on a target machine to ensure that it always resolves a specific hostname to the active SQL node IP address.
It checks the availability of the target hostname and updates the hosts file with the IP address of the active SQL node if necessary.

.NOTES
- Script Name: Update-HostsFile.ps1
- Version: 1.0
- Authors: Rink Spies
- Date: 08-04-2024

.PARAMETER None
This script does not accept any parameters.

.EXAMPLE
.\Update-HostsFile.ps1
This command runs the script to update the hosts file with the current active SQL node IP address.

#>

# VARIABLES
$HostsFile = "$env:SystemRoot\System32\drivers\etc\hosts"
$TargetHostname = "MySqlServerName" # <<Update with SQL Server Instance name >>
$SQLNodes = @("1.2.3.4", "2.3.4.5", "3.4.5.6")  # << update with all SQL Node IP's >>
$LogFile = "C:\Windows\Temp\Update-hosts-file.log"

# FUNCTIONS

# Add-HostRecord function adds a record to the hosts file.
function Add-HostRecord {
    param(
        [string]$HostsFilePath,
        [string]$IP,
        [string]$Hostname
    )

    Add-Content -Path $HostsFilePath -Value "$IP`t`t$Hostname"
}

# Test-ActiveSQLNode function checks if a given SQL node is active.
function Test-ActiveSQLNode {
    param(
        [string]$SQLNode
    )

    return (Test-NetConnection -ComputerName $SQLNode -Port 1433 -InformationLevel Quiet -ErrorAction SilentlyContinue)
}

# Update-HostsFile function updates the hosts file with the IP address of the active SQL node.
function Update-HostsFile {
    foreach ($Node in $SQLNodes) {
        if (Test-ActiveSQLNode $Node) {
            Add-HostRecord -HostsFilePath $HostsFile -IP $Node -Hostname $TargetHostname
            return $Node
        }
    }
    return $null
}

# Log-Output function logs messages to the console and a log file.
function Log-Output {
    param(
        [string]$Message,
        [bool]$IncludeTimestamp = $true
    )

    $logEntry = if ($IncludeTimestamp) {
        "$(Get-Date -Format 'dd-MM-yyyy HH:mm:ss') $Message"
    } else {
        $Message
    }

    Write-Output $logEntry
    Add-Content -Path $LogFile -Value $logEntry
}

# SCRIPT

# Start the script
Log-Output "#############################################"
Log-Output "Starting update hosts file script."

# Check if the current IP for the target hostname is active
if (-not (Test-ActiveSQLNode $TargetHostname)) {
    Log-Output "Current IP for $TargetHostname is not active anymore."
    $activeNode = Update-HostsFile
    if ($activeNode) {
        Log-Output "Active IP $activeNode is online and configured in the hosts file."
    } else {
        Log-Output "None of the IPs are active."
    }
} else {
    Log-Output "Current IP for $TargetHostname is still active."
}

# End the script
Log-Output "Stopping update hosts file script."

As mentioned, not really the solution you’d ideally want to use, but hopefully Ivanti Automation Manager will still receive support for MultiSubnetFailover in the future.