Update-AlwaysON.ps1

<#
Author: David O'Brien, Dilignet, david.obrien@dilignet.com
Version: 1.0
Version History: 1.0 released on 24/10/2014

Usage: .\Update-AlwaysOn_v3.ps1 -SQLNodes @('SQL01AO', 'SQL02AO') -AlwaysOnInstance 'ALWAYSON' -AvailabilityGroupName 'AG01' -CollectionID 'HQ100066' -SMSProvider CM01.DO.LOCAL -Verbose

.\Update-AlwaysON_v3.ps1 -SQLNodes @('KWTSQL352','KWTSQL353') -AlwaysOnInstance MSSQLSERVER -AvailabilityGroupName 'AG_HYP_01' -CollectionID KP10001B -SMSProvider kwpcmp001.core.kmtltd.net.au -Verbose

.\Update-AlwaysON_v3.ps1 -SQLNodes @('KWTSQL352','KWTSQL353') -AvailabilityGroupName 'AG_HYP_01' -CollectionID KP10001B -SMSProvider kwpcmp001.core.kmtltd.net.au -Verbose

When Email is needed at the end of the process, use this command line and add the variables in lines 40-43:
.\Update-AlwaysON_v3.ps1 -SQLNodes @('KWTSQL352','KWTSQL353') -AvailabilityGroupName 'AG_HYP_01' -CollectionID KP10001B -SMSProvider kwpcmp001.core.kmtltd.net.au -SendEmail -Verbose 

#>


#region declare variables
[CmdletBinding()]

param(
  $SQLNodes,
  $global:AlwaysOnInstance,
  $AvailabilityGroupName,
  $CollectionID,
  $SMSProvider,
  [switch]$SendEmail
)

<#
$SQLNodes = @('KWTSQL352','KWTSQL353')
#$AlwaysOnInstance = 'AG_HYP_01'
$AvailabilityGroupName = 'AG_HYP_01'
$CollectionID = 'KP10001B'
$SMSProvider = 'kwpcmp001.core.kmtltd.net.au'
#>

$location = Split-Path $MyInvocation.MyCommand.Path -Parent
$LogLocation = "$(Join-Path $(Join-Path $env:windir system32\logfiles) SQLAlwaysOn.log)"
$MailSender = ''
$MailRecipient = ''
$MailPassword = ''
$SMTPServer = ''

#endregion declare variables

#region helper functions
Function Get-SQLAlwaysOnReplicaNode {
  # Full credit to Brian P ODwyer http://www.mssqltips.com/sqlservertip/3206/finding-primary-replicas-for-sql-server-2012-alwayson-availability-groups-with-powershell/
  # small changes to $connectionstring and output by David O'Brien
  [CmdletBinding()]
  param (
    [array]$SQLNodes,   
    [string]$SQLInstance,
    [ValidateSet('PRIMARY','SECONDARY')] 
    [string]$Role
  )
  
  ## Setup dataset to hold results
  $dataset = New-Object System.Data.DataSet
  ## Setup connection to SQL server inside loop and run T-SQL against instance 
  foreach($Server in $SQLNodes) {
    if ([string]::IsNullOrEmpty($SQLInstance)) {
      $connectionString = "Data Source=$Server;Initial Catalog=Master;Integrated Security=true;"
    }
    else {
      $connectionString = "Data Source=$Server\$SQLInstance;Initial Catalog=Master;Integrated Security=true;"
    }
    ## place the T-SQL in variable to be executed by OLEDB method 
    
    $sqlcommand="
      IF SERVERPROPERTY ('IsHadrEnabled') = 1
      BEGIN
      SELECT
      AGC.name
      , RCS.replica_server_name
      , ARS.role_desc
      , AGL.dns_name
      FROM
      sys.availability_groups_cluster AS AGC
      INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
      ON
      RCS.group_id = AGC.group_id
      INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
      ON
      ARS.replica_id = RCS.replica_id
      INNER JOIN sys.availability_group_listeners AS AGL
      ON
      AGL.group_id = ARS.group_id
      WHERE
      ARS.role_desc = '$Role'
      END
    "
    ## Connect to the data source and open it
    $connection = New-Object System.Data.SqlClient.SqlConnection $connectionString
    $command = New-Object System.Data.SqlClient.SqlCommand $sqlCommand,$connection
    $connection.Open()
    ## Execute T-SQL command in variable, fetch the results, and close the connection
    $adapter = New-Object System.Data.SQLClient.SQLDataAdapter $command
    [void] $adapter.Fill($dataSet)
    $connection.Close()
  }
  ## Return all of the rows from dataset object
  return ($DataSet.Tables[0] | Select-Object replica_server_name).replica_server_name
  
  #>
}

Function Check-AGHealth {
  [CmdletBinding()]
  param (
    [parameter(Mandatory)]
    [ValidateNotNullOrEmpty()]
    [string]$AGName
  )
  if (! $AlwaysOnInstance) {
    $objHealth = Test-SqlAvailabilityGroup -Path "SQLServer:\SQL\$PrimaryReplicaServer\Default\availabilitygroups\$AGName"
  }
  else {
    $objHealth = Test-SqlAvailabilityGroup -Path "SQLServer:\SQL\$PrimaryReplicaServer\availabilitygroups\$AGName"
  }
  if ($objHealth.HealthState -eq 'Healthy') {
    return $true;
  }
  elseif ($objHealth.HealthState -eq 'Unknown') {
    Write-Verbose 'The AG is in Health State unknown. The script might be checking against a Secondary Replica.';
    return $false;
  }
  else {
    Write-Verbose 'The AG is in an unhealthy state. Please check first before updating.';
    return $false;
  }
}

Function Check-ReplicaHealth {
  [CmdletBinding()]
  param()    
  [int]$HealthIssues = 0
  [array]$UnhealthyReplica = @()
  
  if (! $AlwaysOnInstance) {
    Set-Location "SQLServer:\SQL\$PrimaryReplicaServer\Default\availabilitygroups\$AvailabilityGroupName\AvailabilityReplicas"
  }
  else {
    Set-Location "SQLServer:\SQL\$PrimaryReplicaServer\availabilitygroups\$AvailabilityGroupName\AvailabilityReplicas"
  }
  $objReplicaHealth =  Get-ChildItem | Test-SqlAvailabilityReplica
  foreach ($obj in $objReplicaHealth) {
    if ($obj.HealthState -ne 'Healthy') {
      Write-Verbose "$($obj.Name) is in HealthState $($obj.HealthState)."
      $HealthIssues++
      $UnhealthyReplica += $obj.Name
    }
    else {
      Write-Verbose "$($obj.Name) is in HealthState $($obj.HealthState)."
    }
  }
  if ($HealthIssues -ge 1) {
    Write-Verbose "There were $HealthIssues issues in this AvailabilityGroup."
    return $UnhealthyReplica
  }
  else {
    Write-Verbose "There were $HealthIssues issues in this AvailabilityGroup."
    return $UnhealthyReplica
  }
}

Function Check-DBSynchronisation {
  [CmdletBinding()]
  param()
  [array]$NotSynchronisedDBs = @()
  
  if (! $AlwaysOnInstance) {
    Set-Location "SQLServer:\SQL\$PrimaryReplicaServer\Default\availabilitygroups\$AvailabilityGroupName\DatabaseReplicaStates"
  }
  else {
    Set-Location "SQLServer:\SQL\$PrimaryReplicaServer\availabilitygroups\$AvailabilityGroupName\DatabaseReplicaStates"
  }
  $DBHealthStates = Get-ChildItem | Test-SqlDatabaseReplicaState
  foreach ($DBHealthState in $DBHealthStates) {
    if ($DBHealthState.HealthState -ne 'Healthy') {
      $NotSynchronisedDBs += $DBHealthState.Name,$DBHealthState.AvailabilityReplica,$DBHealthState.HealthState
    }
  }
  Set-Location $location
  return $NotSynchronisedDBs
}
<# Not needed at Kmart, if needed in the Future, change Backup locations
Function Backup-AvailabilityDB {
  [CmdletBinding()]
  param()
  [int]$global:errorcount = 0
  Set-Location "SQLServer:\SQL\$PrimaryReplicaServer\availabilitygroups\Default\$AvailabilityGroupName\AvailabilityDatabases"
  $DBs = Get-ChildItem -Path "SQLServer:\SQL\$PrimaryReplicaServer\availabilitygroups\Default\$AvailabilityGroupName\AvailabilityDatabases"
  foreach ($DB in $DBs) {
    if ($DB.SynchronizationState -eq 'Synchronized') {
        Write-Verbose "Starting Backup of $($DB.Name)."
        Backup-SqlDatabase "$($DB.Name)" "\\dc01\sources\SQLAlwaysOn\$($DB.Name)_PoSh.bak" -BackupAction Database -Verbose
        Backup-SqlDatabase "$($DB.Name)" "\\dc01\sources\SQLAlwaysOn\$($DB.Name)_PoSh.trn" -BackupAction Log -Verbose
        Start-Sleep -Seconds 5
    
        if ((!(Test-Path "filesystem::\\dc01\sources\SQLAlwaysOn\$($DB.Name)_PoSh.bak")) -or (!(Test-Path "filesystem::\\dc01\sources\SQLAlwaysOn\$($DB.Name)_PoSh.trn"))) {
          Write-Verbose "$($DB.Name) is missing"
          $errorcount++
        }
    }
    else {
        Write-Error "$($DB.Name) is not in a synchronised state. Aborting."
    }
  }

  if ($errorcount -eq 0) {
    Write-Verbose 'this is true'
    return $true;
  }
  else {
    Write-Verbose 'this is false'
    return $false;
  }
  Set-Location $location
}
#>

Function Disable-AutomaticFailover {
  [cmdletbinding()]
  param(
    $Node,
    $AlwaysOnInstance
  )
  
  $ReplicaServer = $Node+'\'+$AlwaysOnInstance
  
  $NewReplicaString = $ReplicaServer#.Replace('\','%5C')
  try {
    Set-SqlAvailabilityReplica -AvailabilityMode 'SynchronousCommit' -FailoverMode 'Manual' -Path SQLSERVER:\Sql\$PrimaryReplicaServer\Default\AvailabilityGroups\$AvailabilityGroupName\AvailabilityReplicas\$NewReplicaString
  }
  catch {
    Write-Error $_
  }
}

Function Get-SiteCode {
  [cmdletBinding()]
  param (
    $SMSProvider
  )
  $wqlQuery = 'SELECT * FROM SMS_ProviderLocation'
  $a = Get-WmiObject -Query $wqlQuery -Namespace 'root\sms' -ComputerName $SMSProvider
  $a | ForEach-Object {
    if($_.ProviderForLocalSite)
    {
      $SiteCode = $_.SiteCode
    }
  }
  return $SiteCode
}

Function Add-NodeToConfigMgrCollection {
  [cmdletBinding()]
  
  param (
    $Node,
    $CollectionID,
    $SiteCode,
    $SMSProvider
  )
  
  $Device = Get-WmiObject -ComputerName $SMSProvider -Class SMS_R_SYSTEM -Namespace root\sms\site_$SiteCode -Filter "Name = '$Node'"
  $objColRuledirect = [WmiClass]"\\$SMSProvider\ROOT\SMS\site_$($SiteCode):SMS_CollectionRuleDirect"
  $objColRuleDirect.psbase.properties['ResourceClassName'].value = 'SMS_R_System'
  $objColRuleDirect.psbase.properties['ResourceID'].value = $Device.ResourceID
  
  $MC = Get-WmiObject -Class SMS_Collection -ComputerName $SMSProvider -Namespace "ROOT\SMS\site_$SiteCode" -Filter "CollectionID = '$CollectionID'"
  $InParams = $mc.psbase.GetMethodParameters('AddMembershipRule')
  $InParams.collectionRule = $objColRuledirect
  $R = $mc.PSBase.InvokeMethod('AddMembershipRule', $inParams, $Null)
}

Function Invoke-PolicyDownload {
  [CmdletBinding()]
  param(
    [Parameter(Position=0,ValueFromPipeline=$true)]
    [System.String]        
    $ComputerName=(get-content env:computername) #defaults to local computer name        
  )
  Invoke-WmiMethod -Namespace root\ccm -Class sms_client -Name TriggerSchedule '{00000000-0000-0000-0000-000000000021}' -ComputerName $ComputerName -ErrorAction SilentlyContinue | Out-Null
  #Trigger machine policy download
  Invoke-WmiMethod -Namespace root\ccm -Class sms_client -Name TriggerSchedule '{00000000-0000-0000-0000-000000000022}' -ComputerName $ComputerName -ErrorAction SilentlyContinue | Out-Null
  #Trigger Software Update Scane cycle
  Invoke-WmiMethod -Namespace root\ccm -Class sms_client -Name TriggerSchedule '{00000000-0000-0000-0000-000000000113}' -ComputerName $ComputerName -ErrorAction SilentlyContinue | Out-Null
  #Trigger Software Update Deployment Evaluation Cycle
  Invoke-WmiMethod -Namespace root\ccm -Class sms_client -Name TriggerSchedule '{00000000-0000-0000-0000-000000000114}' -ComputerName $ComputerName -ErrorAction SilentlyContinue | Out-Null
  
}
Function Get-ConfigMgrSoftwareUpdateCompliance {
  [CmdletBinding()]
  param(
    [Parameter(Position=0,ValueFromPipeline=$true)]
    [System.String]        
    $ComputerName=(get-content env:computername) #defaults to local computer name        
  )
  Invoke-PolicyDownload -ComputerName $ComputerName;
  do {
    Start-Sleep -Seconds 30
    Write-Output "Checking Software Updates Compliance on [$ComputerName]"
    
    #check if the machine has an update assignment targeted at it
    $global:UpdateAssigment = Get-WmiObject -Query 'Select * from CCM_AssignmentCompliance' -Namespace root\ccm\SoftwareUpdates\DeploymentAgent -ComputerName $ComputerName -ErrorAction SilentlyContinue ;
    
    Write-Output $UpdateAssigment
    
    #if update assignments were returned check to see if any are non-compliant
    $IsCompliant = $true                    
    
    $UpdateAssigment | ForEach-Object{     
      #mark the compliance as false
      if($_.IsCompliant -eq $false -and $IsCompliant -eq $true){$IsCompliant = $false}
    }
    #Check for pending reboot to finish compliance
    $rebootPending = (Invoke-WmiMethod -Namespace root\ccm\clientsdk -Class CCM_ClientUtilities -Name DetermineIfRebootPending -ComputerName $ComputerName).RebootPending
    
    if ($rebootPending)
    {
      Invoke-WmiMethod -Namespace root\ccm\clientsdk -Class CCM_ClientUtilities -Name RestartComputer -ComputerName $ComputerName
      do {'waiting...';start-sleep -Seconds 5} 
      while (-not ((get-service -name 'SMS Agent Host' -ComputerName $ComputerName).Status -eq 'Running'))
      
    } 
    else {
      Write-Output 'No pending reboot. Continue...'
    }
  }
  while (-not $IsCompliant)
}

#endregion helper functions


#region main script starts here

Start-Transcript -Path $LogLocation

if (Get-Module -Name SQLPS) {
  Write-Verbose 'SQLPS Module present, continue';
}
else {
  Write-Verbose 'SQLPS Module is not present, better import it';
  
  try {
    Import-Module SQLPS -DisableNameChecking -ErrorAction SilentlyContinue;
  }
  catch {
    Write-Error $_
  }
};

if (! (Test-Path 'SQLServer:')) {
  Write-Verbose 'Cannot access the SQLServer PSDrive. Exiting.';
  exit 1
  #exit after here
}
else {
  Set-Location $location
}

$PrimaryReplicaServer = Get-SQLAlwaysOnReplicaNode -SQLNodes $SQLNodes -SQLInstance $AlwaysOnInstance -Role PRIMARY;
$SecondaryReplicaServer = Get-SQLAlwaysOnReplicaNode -SQLNodes $SQLNodes -SQLInstance $AlwaysOnInstance -Role SECONDARY;


#region executing HealthChecks

if (!(Check-AGHealth -AGName $AvailabilityGroupName)) {
  Set-Location C:\
  Set-Location $location
  throw "AvailabilityGroup $AvailabilityGroupName is in an unhealthy or unknown state."
}
    
$UnhealthyReplica = Check-ReplicaHealth

if (-not [string]::IsNullOrEmpty($UnhealthyReplica)) {
  Set-Location C:\
  Set-Location $location
  throw "Issues found with $UnhealthyReplica. Please check the node for any issues."
}

$NotSynchronisedDBs = Check-DBSynchronisation

if (-not [string]::IsNullOrEmpty($NotSynchronisedDBs)) {
  Set-Location C:\
  Set-Location $location
  throw "Issues found with $NotSynchronisedDBs. Please check the node for any issues."
}

Write-Verbose 'All seems to be fine. Let''s go'

#endregion HealthChecks
<#
#region back up the AlwaysOn DBs
<
try {
    Backup-AvailabilityDB
}
catch {
    Write-Error $_
}

if (Backup-AvailabilityDB) {
    Write-Verbose 'All Databases backed up' 
    }
else {
    throw "Problem backing up Databases. $errorcount errors."
    }

#endregion backup
#>
foreach ($SQLNode in $SQLNodes)
{
  Disable-AutomaticFailover -Node $SQLNode -AlwaysOnInstance $AlwaysOnInstance -Verbose
}

#Start Updating one Secondary Node at a time

$SiteCode = Get-SiteCode -SMSProvider $SMSProvider
$i = 0
foreach ($SecondaryReplica in $SecondaryReplicaServer) {
  if (-not ($AlreadyPatched -contains $SecondaryReplica.Split('\')[0])) {
    try {
      $i++
      Write-Verbose "Patching Server round $i = $($SecondaryReplica.Split('\')[0])"
      
      #Add current secondary node to ConfigMgr collection to receive its updates
      Add-NodeToConfigMgrCollection -Node $SecondaryReplica.Split('\')[0] -SiteCode $SiteCode -SMSProvider $SMSProvider -CollectionID $CollectionID -Verbose
      
      Start-Sleep -Seconds 60
      Invoke-policydownload -computername $SecondaryReplica.Split('\')[0]
      
      Start-Sleep -Seconds 120
      Invoke-policydownload -computername $SecondaryReplica.Split('\')[0]
      
      Start-Sleep -Seconds 120
      #Check if all updates have been installed and server finished rebooting
      Write-Output 'Applying updates now'
      Get-ConfigMgrSoftwareUpdateCompliance -ComputerName $SecondaryReplica.Split('\')[0]
      
      $AlreadyPatched += $SecondaryReplica.Split('\')[0]
    }
    catch {
      Write-Error $_
    }
  }
  else {
    Write-Verbose "$($SecondaryReplica.Split('\')[0]) has already been patched. Skipping."
  }
}

# fail over to one of the secondary nodes and update the primary node, after that, fail over again to the original primary node

Switch-SqlAvailabilityGroup -Path SQLSERVER:\Sql\$(Get-Random -InputObject $SecondaryReplicaServer)\Default\AvailabilityGroups\$AvailabilityGroupName -Verbose
Add-NodeToConfigMgrCollection -Node $PrimaryReplicaServer.Split('\')[0] -SiteCode $SiteCode -SMSProvider $SMSProvider -CollectionID $CollectionID -Verbose

Start-Sleep -Seconds 60
Invoke-PolicyDownload -computername $PrimaryReplicaServer.Split('\')[0]

Start-Sleep -Seconds 90
Invoke-PolicyDownload -computername $PrimaryReplicaServer.Split('\')[0]

Start-Sleep -Seconds 90
#Check if all updates have been installed and server finished rebooting
Write-Output 'Applying updates now'
Get-ConfigMgrSoftwareUpdateCompliance -ComputerName $PrimaryReplicaServer.Split('\')[0]


#If the primary node is finished updating, fail over again to the Primary
Switch-SqlAvailabilityGroup -Path SQLSERVER:\Sql\$PrimaryReplicaServer\Default\AvailabilityGroups\$AvailabilityGroupName -Verbose

Set-Location C:\
Set-Location $location

Stop-Transcript 

#Send Email not implemented

If ($SendEmail) {
  $secpasswd = ConvertTo-SecureString $MailPassword -AsPlainText -Force
  $mycreds = New-Object System.Management.Automation.PSCredential ($MailSender, $secpasswd)
  
  Send-MailMessage -Body 'Transcript of SQL AlwaysOn Patching Process' -Subject 'SQL AlwaysON Completion Message' -From $MailSender -SmtpServer $SMTPServer -UseSsl -Credential $mycreds -To $MailRecipient -Attachments $LogLocation
}

#endregion that's it!