DSCResources/MSFT_xSQLAOGroupEnsure/MSFT_xSQLAOGroupEnsure.psm1

$currentPath = Split-Path -Parent $MyInvocation.MyCommand.Path
Write-Verbose -Message "CurrentPath: $currentPath"

Import-Module $currentPath\..\..\xSQLServerHelper.psm1 -Verbose:$false -ErrorAction Stop

function Get-TargetResource
{
    [CmdletBinding()]
    [OutputType([System.Collections.Hashtable])]
    param
    (
        [parameter(Mandatory = $true)]
        [ValidateSet('Present','Absent')]
        [System.String]
        $Ensure,

        [parameter(Mandatory = $true)]
        [System.String]
        $AvailabilityGroupName,
        
        [System.String]
        $SQLServer = $env:COMPUTERNAME,

        [ValidateNotNull()] 
        [System.String]
        $SQLInstanceName= 'MSSQLSERVER',

        [parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SetupCredential
    )

    $sql = Connect-SQL -SQLServer $SQLServer -SQLInstanceName $SQLInstanceName -SetupCredential $SetupCredential
    
    $vConfigured = Test-TargetResource -Ensure $Ensure -AvailabilityGroupName $AvailabilityGroupName -SQLServer $SQLServer -SQLInstanceName $SQLInstanceName -SetupCredential $SetupCredential

    $returnValue = @{
        Ensure = $vConfigured
        AvailabilityGroupName = $sql.AvailabilityGroups[$AvailabilityGroupName]
        AvailabilityGroupNameListener = $sql.AvailabilityGroups[$AvailabilityGroupName].AvailabilityGroupListeners.name
        AvailabilityGroupNameIP = $sql.AvailabilityGroups[$AvailabilityGroupName].AvailabilityGroupListeners.availabilitygrouplisteneripaddresses.IPAddress
        AvailabilityGroupSubMask =  $sql.AvailabilityGroups[$AvailabilityGroupName].AvailabilityGroupListeners.availabilitygrouplisteneripaddresses.SubnetMask
        AvailabilityGroupPort =  $sql.AvailabilityGroups[$AvailabilityGroupName].AvailabilityGroupListeners.portnumber
        AvailabilityGroupNameDatabase = $sql.AvailabilityGroups[$AvailabilityGroupName].AvailabilityDatabases.name
        BackupDirectory = ''
        SQLServer = $SQLServer
        SQLInstanceName = $SQLInstanceName
    }

    $returnValue
}

function Set-TargetResource
{
    [CmdletBinding()]
    param
    (
        [parameter(Mandatory = $true)]
        [ValidateSet('Present', 'Absent')]
        [System.String]
        $Ensure,

        [parameter(Mandatory = $true)]
        [System.String]
        $AvailabilityGroupName,

        [System.String]
        $AvailabilityGroupNameListener = $AvailabilityGroupName,

        [System.String[]]
        $AvailabilityGroupNameIP,

        [System.String[]]
        $AvailabilityGroupSubMask,

        [System.UInt32]
        $AvailabilityGroupPort = '1433',

        [ValidateSet('None', 'ReadOnly', 'ReadIntent')]
        [System.String]
        $ReadableSecondary = 'ReadOnly',

        [ValidateSet('Primary', 'Secondary')]
        [System.String]
        $AutoBackupPreference = 'Primary',
        
        [System.UInt32]
        $BackupPriority = '50',
        
        [System.UInt32]
        $EndPointPort = '5022',

        [System.String]
        $SQLServer = $env:COMPUTERNAME,

        [System.String]
        $SQLInstanceName = 'MSSQLSERVER',
        
        [parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SetupCredential
    )

    $null = [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo')
    $null = [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SmoExtended')
   
    $sql = Connect-SQL -SQLServer $SQLServer -SQLInstanceName $SQLInstanceName -SetupCredential $SetupCredential

    if (($AvailabilityGroupNameIP -and !$AvailabilityGroupSubMask) -or (!$AvailabilityGroupNameIP -and $AvailabilityGroupSubMask))
    {
        throw 'AvailabilityGroupNameIP and AvailabilityGroupSubMask must both be passed for Static IP assignment.'
    }

    switch ($Ensure)
    {
        'Present'
        {
            Grant-ServerPerms -SQLServer $SQLServer -SQLInstanceName $SQLInstanceName -AuthorizedUser 'NT AUTHORITY\SYSTEM' -SetupCredential $SetupCredential
            New-ListenerADObject -AvailabilityGroupNameListener $AvailabilityGroupNameListener -SetupCredential $SetupCredential -SQLServer $SQLServer -SQLInstanceName $SQLInstanceName
 
            $FailoverCondition = 3
            $HealthCheckTimeout = 30000
            $ConnectionModeInPrimary = 'AllowAllConnections'    

            $ConnectionModeInSecondaryRole = switch ($ReadableSecondary)
            {
                'None'
                {
                    'AllowNoConnections'
                }
                
                'ReadOnly'
                {
                    'AllowAllConnections'
                }
                
                'ReadIntent'
                {
                    'AllowReadIntentConnectionsOnly'
                }
                
                Default 
                {
                    'AllowAllConnections'
                }
            } 

           # Get Servers participating in the cluster
           # First two nodes will account for Syncronous Automatic Failover, Any additional will be Asyncronous
           try
           {
                $nodes = Get-ClusterNode -cluster $sql.ClusterName -Verbose:$false | Select-Object -ExpandProperty name
                $syncNodes = $nodes | Select-Object -First 2
                $asyncNodes = $nodes | Select-Object -Skip 2
                $availabilityGroup = New-Object -typename Microsoft.SqlServer.Management.Smo.AvailabilityGroup -ArgumentList $SQL, $AvailabilityGroupName
                $availabilityGroup.AutomatedBackupPreference = $AutoBackupPreference
                $availabilityGroup.FailureConditionLevel = $FailoverCondition
                $availabilityGroup.HealthCheckTimeout = $HealthCheckTimeout
           }
           catch
           {
                throw "Failed to connect to Cluster Nodes from $($sql.ClusterName)"
           }

           # Loop through Sync nodes Create Replica Object Assign properties and add it to AvailabilityGroup
           foreach ($node in $syncNodes)
           { 
                Try
                {
                    $Replica = New-Object -typename Microsoft.SqlServer.Management.Smo.AvailabilityReplica -ArgumentList $availabilityGroup, $node
                    $Replica.EndpointUrl = "TCP://$($node):$EndPointPort"
                    $Replica.FailoverMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaFailoverMode]::Automatic
                    $Replica.AvailabilityMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaAvailabilityMode]::SynchronousCommit
                    # Backup Priority Gives the ability to set a priority of one secondany over another valid values are from 1 - 100
                    $Replica.BackupPriority = $BackupPriority
                    $Replica.ConnectionModeInPrimaryRole = $ConnectionModeInPrimary
                    $replica.ConnectionModeInSecondaryRole = $ConnectionModeInSecondaryRole 
                    $availabilityGroup.AvailabilityReplicas.Add($Replica)
                }
                catch
                {
                    throw "Failed to add $Replica to the Availability Group $AvailabilityGroupName"
                }         
            }

            # Loop through ASync nodes Create Replica Object Assign properties and add it to AvailabilityGroup
            foreach ($node in $AsyncNodes)
            {
                try
                {
                    $asyncReplica = New-Object -typename Microsoft.SqlServer.Management.Smo.AvailabilityReplica -ArgumentList $availabilityGroup, $node
                    $asyncReplica.EndpointUrl = "TCP://$($node):$EndPointPort"
                    $asyncReplica.FailoverMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaFailoverMode]::Manual
                    $asyncReplica.AvailabilityMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaAvailabilityMode]::ASynchronousCommit
                    $asyncReplica.BackupPriority = $BackupPriority
                    $asyncReplica.ConnectionModeInPrimaryRole =  $ConnectionModeInPrimary
                    $asyncReplica.ConnectionModeInSecondaryRole = $ConnectionModeInSecondaryRole 
                    $AvailabilityGroup.AvailabilityReplicas.Add($asyncReplica)
                }
                catch
                {
                    Write-Error "Failed to add $asyncReplica to the Availability Group $AvailabilityGroupName"
                }
            }
        
            try
            {
                $AgListener = New-Object -typename Microsoft.SqlServer.Management.Smo.AvailabilityGroupListener -ArgumentList $AvailabilityGroup, $AvailabilityGroupNameListener
                $AgListener.PortNumber =$AvailabilityGroupPort
            }
            catch
            {
                Write-Error -Message ('{0}: Failed to Create AG Listener Object' -f ((Get-Date -format yyyy-MM-dd_HH-mm-ss)))
            }
         
         
            if ($AvailabilityGroupNameIP)
            {
                foreach ($IP in $AvailabilityGroupNameIP)
                {
                    $AgListenerIp = New-Object -typename Microsoft.SqlServer.Management.Smo.AvailabilityGroupListenerIPAddress -ArgumentList $AgListener
                    $AgListenerIp.IsDHCP = $false
                    $AgListenerIp.IPAddress = $IP
                    $AgListenerIp.SubnetMask = $AvailabilityGroupSubMask
                    $AgListener.AvailabilityGroupListenerIPAddresses.Add($AgListenerIp)
                    New-VerboseMessage -Message "Added Static IP $IP to $AvailabilityGroupNameListener..."
            
                }
            }
            else
            {
                # Utilize Dynamic IP since no Ip was passed
                $AgListenerIp = New-Object -typename Microsoft.SqlServer.Management.Smo.AvailabilityGroupListenerIPAddress -ArgumentList $AgListener
                $AgListenerIp.IsDHCP = $true
                $AgListener.AvailabilityGroupListenerIPAddresses.Add($AgListenerIp)
                New-VerboseMessage -Message "Added DynamicIP to $AvailabilityGroupNameListener..."
            }
         
            try
            {
                $AvailabilityGroup.AvailabilityGroupListeners.Add($AgListener);
            }
            catch
            {
                throw "Failed to Add $AvailabilityGroupNameListener to $AvailabilityGroupName..."
            }    

            # Add Availabilty Group to the SQL connection
            try
            {
                $SQL.AvailabilityGroups.Add($availabilityGroup)
                New-VerboseMessage -Message "Added $availabilityGroupName Availability Group to Connection"  
            }
            catch
            {
                throw "Unable to Add $AvailabilityGroup to $SQLServer\$SQLInstanceName"
            }
           
            # Create Availability Group
            try
            {
                $availabilityGroup.Create()

                New-VerboseMessage -Message "Created Availability Group $availabilityGroupName"
            }
            catch
            {
                throw "Unable to Create $AvailabilityGroup on $SQLServer\$SQLInstanceName"
            }
        }

        'Absent'
        { 
            try
            {
                 $sql.AvailabilityGroups[$AvailabilityGroupName].Drop()

                 New-VerboseMessage -Message "Dropped $AvailabilityGroupName" 
            }
            catch
            {
                 throw "Unable to Drop $AvailabilityGroup on $SQLServer\$SQLInstanceName"
            }
        }
    }
}

function Test-TargetResource
{
    [CmdletBinding()]
    [OutputType([System.Boolean])]
    param
    (
        [parameter(Mandatory = $true)]
        [ValidateSet('Present', 'Absent')]
        [System.String]
        $Ensure,

        [parameter(Mandatory = $true)]
        [System.String]
        $AvailabilityGroupName,

        [System.String]
        $AvailabilityGroupNameListener,

        [System.String[]]
        $AvailabilityGroupNameIP,

        [System.String[]]
        $AvailabilityGroupSubMask,

        [System.UInt32]
        $AvailabilityGroupPort,

        [ValidateSet('None', 'ReadOnly', 'ReadIntent')]
        [System.String]
        $ReadableSecondary ='ReadOnly',

        [ValidateSet('Primary', 'Secondary')]
        [System.String]
        $AutoBackupPreference = 'Primary',
        
        [System.UInt32]
        $BackupPriority = '50',
        
        [System.UInt32]
        $EndPointPort = '5022',

        [System.String]
        $SQLServer = $env:COMPUTERNAME,

        [System.String]
        $SQLInstanceName = 'MSSQLSERVER',
        
        [parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SetupCredential
    )

    $sql = Connect-SQL -SQLServer $SQLServer -SQLInstanceName $SQLInstanceName -SetupCredential $SetupCredential

    $result = $false

    switch ($Ensure)
    {
        'Present'
        {
            $availabilityGroupPresent = $sql.AvailabilityGroups.Contains($AvailabilityGroupName)
            if ($availabilityGroupPresent)
            {
                $result = $true
            }
        }

        'Absent'
        {
            if (!$sql.AvailabilityGroups[$AvailabilityGroupName])
            {
                $result = $true
            }
        }
    }

    return $result
}

Export-ModuleMember -Function *-TargetResource