Using Install-DbaInstance to install (and even uninstall) a SQL Server failover cluster instance

[andreasjordan]

Last updated: 2022-03-16
Tested with dbatools version: 1.1.81

I just had to setup a failover cluster instance in a lab and used Install-DbaInstance for that.
Maybe you can grab some code from that for your problem. But this is not a super documented and robust solution - it works on my maschine, mabe not on your maschine.

What is my setup? I have two nodes SQLCN01 and SQLCN02 based on Windows Server 2016 with the local drive C: for the operating system. I have two disks (10 GB for Quorum, 50 GB for SQL Server) mounted to both nodes. The SQL Server sources are located on a fileshare.

So lets get started with importing the needed modules and setting up some variables:

$ErrorActionPreference = 'Stop'

Import-Module -Name FailoverClusters
Import-Module -Name ActiveDirectory
Import-Module -Name dbatools

$clusterName = 'WINCluster'
$clusterNodes = 'SQLCN01', 'SQLCN02'
$clusterIP = '192.168.131.233'

$clusterSqlName = 'SQLCluster'
$clusterSqlDrive = 'S'
$clusterSqlIP = '192.168.131.234'
$clusterSqlIPSubnet = '255.255.255.0'
$clusterSqlInstance = 'TEST'
$clusterSqlVersion = 2019

$installCredential = Get-Credential -Message 'admin account for CredSSP' -UserName DOM\Admin
$sqlServiceCredential = Get-Credential -Message 'service account for SQL Server' -UserName DOM\SQLServer
$sqlSaCredential = Get-Credential -Message 'sa password for SQL Server' -UserName sa

We need the cluster software and I want the management software locally:

Add-WindowsFeature -Name RSAT-AD-PowerShell, RSAT-ADDS, RSAT-Clustering-PowerShell, RSAT-Clustering-Mgmt | Format-Table
Invoke-Command -ComputerName $clusterNodes -ScriptBlock { Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools } | Format-Table

Get the 10 GB drive which is RAW and initialize, partition and format it:

Invoke-Command -ComputerName $clusterNodes[0] -ScriptBlock { 
    $Disk = Get-Disk | Where-Object -Property Size -EQ -Value 10GB | Where-Object -Property PartitionStyle -EQ 'RAW' | Sort-Object -Property Number | Select-Object -First 1
    $Disk | Initialize-Disk -PartitionStyle GPT
    $Partition = $Disk | New-Partition -UseMaximumSize -DriveLetter Q
    $null = $Partition | Format-Volume -FileSystem NTFS -NewFileSystemLabel Quorum
}

Now we are ready to build the cluster. The test is optional but a good idea for the first run. I always like to have nice names for all the ressources, especially if you have more than one of a type.

# Run test and show result in default browser
$clusterTest = Test-Cluster -Node $clusterNodes
&$clusterTest.FullName

# Create the cluster
$cluster = New-Cluster -Name $clusterName -Node $clusterNodes -StaticAddress $clusterIP

# Change names of cluster resources
($cluster | Get-ClusterNetwork | Where-Object { $_.Role -eq 'ClusterAndClient' }).Name = 'Cluster Network Public'
($cluster | Get-ClusterResource | Where-Object { $_.OwnerGroup -eq 'Cluster Group' -and $_.ResourceType -eq 'Physical Disk' }).Name = 'Cluster Disk Quorum'

I also like to test the cluster and the drive letter of the quorum - as sometimes it is not the correct letter. So here is some code you will have to adapt to your environment:

# Display status
$cluster | Get-ClusterGroup -Name 'Cluster Group'

# Test current node
$ownerNode = ($cluster | Get-ClusterGroup -Name 'Cluster Group').OwnerNode
$result = Invoke-Command -ComputerName $ownerNode -ArgumentList Q, Quorum -ScriptBlock { 
    Param([string]$TargetDriveLetter, [string]$DriveDescription)
    $currentDriveLetter = (Get-PSDrive -PSProvider FileSystem | Where-Object Description -eq $DriveDescription).Name
    if ($currentDriveLetter -ne $TargetDriveLetter) {
        Get-Partition -DriveLetter $currentDriveLetter | Set-Partition -NewDriveLetter $TargetDriveLetter
    }
    Test-Path -Path "$($TargetDriveLetter):\"
}
$result  # Should be True

# Fail over to other node
$cluster | Move-ClusterGroup -Name 'Cluster Group'

# Test the new current node
$ownerNode = ($cluster | Get-ClusterGroup -Name 'Cluster Group').OwnerNode
$result = Invoke-Command -ComputerName $ownerNode -ArgumentList Q, Quorum -ScriptBlock { 
    Param([string]$TargetDriveLetter, [string]$DriveDescription)
    $currentDriveLetter = (Get-PSDrive -PSProvider FileSystem | Where-Object Description -eq $DriveDescription).Name
    if ($currentDriveLetter -ne $TargetDriveLetter) {
        Get-Partition -DriveLetter $currentDriveLetter | Set-Partition -NewDriveLetter $TargetDriveLetter
    }
    Test-Path -Path "$($TargetDriveLetter):\"
}
$result  # Should be True

# Fail over to the first node
$ownerNode = ($cluster | Get-ClusterGroup -Name 'Cluster Group').OwnerNode
if ($ownerNode -ne $clusterNodes[0]) {
    $cluster | Move-ClusterGroup -Name 'Cluster Group'
}

The next part is to grant the necessary rights to the computer account of the cluster to later add a computer account for the virtual computer for the SQL Server cluster instance. If you like to use a gui, use this website.

$adComputerGUID = [GUID]::new('bf967a86-0de6-11d0-a285-00aa003049e2')
# If you don't trust me or https://docs.microsoft.com/en-us/windows/win32/adschema/c-computer
# $adComputerGUID = [GUID](Get-ADObject -Filter 'Name -eq "Computer"' -SearchBase (Get-ADRootDSE).schemaNamingContext -Properties schemaIDGUID).schemaIDGUID

$adClusterComputer = Get-ADComputer -Filter "Name -eq '$clusterName'"
$adClusterIdentity = [System.Security.Principal.SecurityIdentifier]::new($adClusterComputer.SID)
$adClusterOU = [ADSI]([ADSI]"LDAP://$($adClusterComputer.DistinguishedName)").Parent

$accessRule1 = [System.DirectoryServices.ActiveDirectoryAccessRule]::new($adClusterIdentity, "ReadProperty", "Allow", [GUID]::Empty, "All", [GUID]::Empty)
$accessRule2 = [System.DirectoryServices.ActiveDirectoryAccessRule]::new($adClusterIdentity, "CreateChild", "Allow", $adComputerGUID, "All", [GUID]::Empty)

$adClusterOU.psbase.ObjectSecurity.AddAccessRule($accessRule1)
$adClusterOU.psbase.ObjectSecurity.AddAccessRule($accessRule2)
$adClusterOU.psbase.CommitChanges()

# Have a look at the result
(Get-Acl -Path $adClusterOU.Path.Replace('LDAP://', 'AD:\')).Access | Where-Object {
    $_.IdentityReference.Translate([System.Security.Principal.SecurityIdentifier]).Value -eq $adClusterIdentity
}

Now it is time to prepare the 50 GB disk for the SQL Server instance, wich will be added to the cluster and renamed in the last two lines:

Invoke-Command -ComputerName $clusterNodes[0] -ArgumentList $clusterSqlDrive, $clusterSqlName -ScriptBlock { 
    Param([string]$DriveLetter, [string]$NewFileSystemLabel)
    $Disk = Get-Disk | Where-Object -Property Size -EQ -Value 50GB | Where-Object -Property PartitionStyle -EQ 'RAW' | Sort-Object -Property Number | Select-Object -First 1
    $Disk | Initialize-Disk -PartitionStyle GPT
    $Partition = $Disk | New-Partition -UseMaximumSize -DriveLetter $DriveLetter
    $null = $Partition | Format-Volume -FileSystem NTFS -NewFileSystemLabel $NewFileSystemLabel
} 
$null = Get-ClusterAvailableDisk -Cluster $clusterName | Add-ClusterDisk 
($cluster | Get-ClusterResource | Where-Object { $_.OwnerGroup -eq 'Available Storage' -and $_.ResourceType -eq 'Physical Disk' }).Name = "Cluster Disk $clusterSqlName"

Let's again test if the drive letter is the correct one on both nodes:

# Display status
$cluster | Get-ClusterGroup -Name 'Available Storage'

# Test current node
$ownerNode = ($cluster | Get-ClusterGroup -Name 'Available Storage').OwnerNode
$result = Invoke-Command -ComputerName $ownerNode -ArgumentList $clusterSqlDrive, $clusterSqlName -ScriptBlock { 
    Param([string]$TargetDriveLetter, [string]$DriveDescription)
    $currentDriveLetter = (Get-PSDrive -PSProvider FileSystem | Where-Object Description -eq $DriveDescription).Name
    if ($currentDriveLetter -ne $TargetDriveLetter) {
        Get-Partition -DriveLetter $currentDriveLetter | Set-Partition -NewDriveLetter $TargetDriveLetter
    }
    Test-Path -Path "$($TargetDriveLetter):\"
}
$result  # Should be True

# Fail over to other node
$cluster | Move-ClusterGroup -Name 'Available Storage'

# Test the new current node
$ownerNode = ($cluster | Get-ClusterGroup -Name 'Available Storage').OwnerNode
$result = Invoke-Command -ComputerName $ownerNode -ArgumentList $clusterSqlDrive, $clusterSqlName -ScriptBlock { 
    Param([string]$TargetDriveLetter, [string]$DriveDescription)
    $currentDriveLetter = (Get-PSDrive -PSProvider FileSystem | Where-Object Description -eq $DriveDescription).Name
    if ($currentDriveLetter -ne $TargetDriveLetter) {
        Get-Partition -DriveLetter $currentDriveLetter | Set-Partition -NewDriveLetter $TargetDriveLetter
    }
    Test-Path -Path "$($TargetDriveLetter):\"
}
$result  # Should be True

# Fail over to the first node
$ownerNode = ($cluster | Get-ClusterGroup -Name 'Available Storage').OwnerNode
if ($ownerNode -ne $clusterNodes[0]) {
    $cluster | Move-ClusterGroup -Name 'Available Storage'
}

Now the cluster is up and running, time to install the SQL Server instance.

The first node needs the action "InstallFailoverCluster" and a lot of information. I tried to use as many parameters of Install-DbaInstance as possible and put the rest in the Configuration hashtable.

$paramsInstallFailoverCluster = @{
    ComputerName       = $clusterNodes[0]
    InstanceName       = $clusterSqlInstance
    Version            = $clusterSqlVersion

    Configuration      = @{
        ACTION                     = 'InstallFailoverCluster'
        FAILOVERCLUSTERNETWORKNAME = $clusterSqlName
        FAILOVERCLUSTERDISKS       = "Cluster Disk $clusterSqlName"
        FAILOVERCLUSTERGROUP       = "SQL Server ($clusterSqlInstance)"
        FAILOVERCLUSTERIPADDRESSES = "IPv4;$clusterSqlIP;Cluster Network Public;$clusterSqlIPSubnet"
        INSTALLSQLDATADIR          = "$($clusterSqlDrive):\$clusterSqlName"
    }

    Feature            = 'Engine'
    AuthenticationMode = 'Mixed'
    SaCredential       = $sqlSaCredential
    AdminAccount       = $installCredential.UserName

    EngineCredential   = $sqlServiceCredential
    AgentCredential    = $sqlServiceCredential
    Path               = '\\fs\Software\SQLServer\ISO'
    UpdateSourcePath   = '\\fs\Software\SQLServer\CU'
    Restart            = $true
    Credential         = $installCredential
    Confirm            = $false
}

The second node needs the action "AddNode" and far less information:

$paramsAddNode = @{
    ComputerName       = $clusterNodes[1]
    InstanceName       = $clusterSqlInstance
    Version            = $clusterSqlVersion

    Configuration      = @{ ACTION = 'AddNode' }

    EngineCredential   = $sqlServiceCredential
    AgentCredential    = $sqlServiceCredential
    Path               = '\\fs\Software\SQLServer\ISO'
    UpdateSourcePath   = '\\fs\Software\SQLServer\CU'
    Restart            = $true
    Credential         = $installCredential
    Confirm            = $false
}

And here is how these hashtables are uses, it's called "splatting" if you haven't heared of it yet:

$result = Install-DbaInstance @paramsInstallFailoverCluster
$result | Format-Table
if (-not $result.Successful) {
    throw "Failed to install SQL Server"
}

$result = Install-DbaInstance @paramsAddNode
$result | Format-Table
if (-not $result.Successful) {
    throw "Failed to install SQL Server"
}

Tip: Don't use -EnableException with Install-DbaInstance, as you would not get the details about the exception.

I still use the windows firewall in this lab, so I need firewall rules:

$paramsFirewallRule = @{
    SqlInstance = "$($clusterNodes[0])\$clusterSqlInstance", "$($clusterNodes[1])\$clusterSqlInstance"
    Type        = 'Engine', 'Browser'
    Confirm     = $false
}
$result = New-DbaFirewallRule @paramsFirewallRule
$result | Format-Table -Property ComputerName, InstanceName, Type, Successful, Status
if ($false -in $result.Successful) {
    throw "Failed to create filewall rules"
}

Let's test. Connect, failover and connect again:

$server = Connect-DbaInstance -SqlInstance "$clusterSqlName\$clusterSqlInstance"
$server | Format-Table -Property NetName, ServiceName, DomainInstanceName, ComputerNamePhysicalNetBIOS, Product, Version, IsClustered, ConnectedAs

$cluster | Move-ClusterGroup -Name "SQL Server ($clusterSqlInstance)"

$server = Connect-DbaInstance -SqlInstance "$clusterSqlName\$clusterSqlInstance"
$server | Format-Table -Property NetName, ServiceName, DomainInstanceName, ComputerNamePhysicalNetBIOS, Product, Version, IsClustered, ConnectedAs

You don't need the instance and the cluster anymore? Let's remove it...

Both nodes needs the action "RemoveNode" and only a little bit of information:

$paramsRemoveNode = @{
    InstanceName  = $clusterSqlInstance
    Version       = $clusterSqlVersion

    Configuration = @{ ACTION = 'RemoveNode' }

    Path          = '\\fs\Software\SQLServer\ISO'
    Restart       = $true
    Credential    = $installCredential
    Confirm       = $false
}

Both nodes are removed the same way. But one after the other, never at the same time:

$result = Install-DbaInstance @paramsRemoveNode -ComputerName $clusterNodes[0]
$result | Format-Table
if (-not $result.Successful) {
    throw "Failed to uninstall SQL Server"
}

$result = Install-DbaInstance @paramsRemoveNode -ComputerName $clusterNodes[1]
$result | Format-Table
if (-not $result.Successful) {
    throw "Failed to uninstall SQL Server"
}

I remove all the firewall rules I created:

$result = Remove-DbaFirewallRule @paramsFirewallRule
$result | Format-Table -Property ComputerName, InstanceName, Type, IsRemoved, Status
if ($false -in $result.IsRemoved) {
    throw "Failed to remove filewall rules"
}

Now it's time to remove the cluster. The computer accounts of both the cluster and the sql server network name still stay in the AD, so I remove them to not have any leftovers:

# To remove the granted rights:
$null = $adClusterOU.psbase.ObjectSecurity.RemoveAccessRule($accessRule1)
$null = $adClusterOU.psbase.ObjectSecurity.RemoveAccessRule($accessRule2)
$adClusterOU.psbase.CommitChanges()

Remove-Cluster -Cluster $clusterName -Force
Get-ADComputer -Filter "Name -eq '$clusterName'" | Remove-ADComputer -Confirm:$false
Get-ADComputer -Filter "Name -eq '$clusterSqlName'" | Remove-ADComputer -Confirm:$false

And I clean the disks to be able to start again from the top. In my case, the cluster leaves the disks in Offline state. Don't do this in production, always be sure you clean the correct disk. Don't blame me for an empty C: drive...

Invoke-Command -ComputerName $clusterNodes[0] -ScriptBlock { 
    $disk = Get-Disk | Where-Object { $_.OperationalStatus -eq 'Offline' }
    $disk | Set-Disk -IsOffline $false
    $disk | Set-Disk -IsReadOnly $false
    $disk | Clear-Disk -RemoveData -Confirm:$false
}

I've run this with SQL Server 2016, 2017 and 2019. Always with a named instance, but with some adaptations it should work with a default instance as well.

[mattcargile]

Great stuff as always Andreas. I've been wanting to use Install-DbaInstance on a single instance for my new server builds at work so this will be very helpful for me.

[andreasjordan]

Note as of 2022-03-16: This code was also updated today to use the new functionality of Install-DbaInstance.

To uninstall "normal" instances in my lab, I currently use this code:

Import-Module -Name dbatools

$domainAdminCredential = Get-Credential -Message DomainAdmin -UserName COMPANY\Administrator

$instances = Find-DbaInstance -ComputerName SRV1
$instances | Format-Table

$instanceToUninstall = $instances | Out-GridView -Title 'Select instance to uninstall' -OutputMode Multiple
$results = @( )
foreach ($inst in $instanceToUninstall) {
    # What information do we need?
    # * Version for Install-DbaInstance to select the correct setup.exe
    # * InstancePath for Remove-Item to remove all left over files
    try {
        # Try to get information from running instance
        $server = Connect-DbaInstance -SqlInstance $inst.SqlInstance
        $instanceVersion = ($server.GetSqlServerVersionName() -split ' ')[-1]
        $instancePath = $server.RootDirectory -replace 'MSSQL$', ''
    } catch {
        # Fallback to information about the service
        $service = Get-DbaService -ComputerName $inst.ComputerName -InstanceName $inst.InstanceName -Type Engine -EnableException
        $instanceVersion = switch ($service.BinaryPath -replace '^.*MSSQL(\d\d).*$', '$1') { 15 { 2019 } 14 { 2017 } 13 { 2016 } 12 { 2014 } 11 { 2012 } }
        $instancePath = $service.BinaryPath -replace '^"?(.*)MSSQL\\Binn\\sqlservr\.exe.*$', '$1'
    }
    $params = @{
        SqlInstance      = $inst.SqlInstance
        Version          = $instanceVersion
        Configuration    = @{ ACTION = 'Uninstall' } 
        Path             = '\\FS\Software\SQLServer\ISO'
        Restart          = $true
        Credential        = $domainAdminCredential
        Confirm          = $false
    }
    $result = Install-DbaInstance @params
    if (-not $result.Successful) {
        $result.Log | Set-Clipboard
        throw "Uninstall failed, see clipboard for details"
    }
    $results += $result
    
    # Remove firewall rule
    Get-DbaFirewallRule -SqlInstance $inst.SqlInstance | Remove-DbaFirewallRule

    # Remove directory
    Invoke-Command -ComputerName $inst.ComputerName -ScriptBlock { 
        param($path)
        Remove-Item -Path $path -Recurse -Force
    } -ArgumentList $instancePath
}

Yes, you have to change a lot to let it work in your environment...

[potatoqualitee]

Good stuff, thanks so much!