Add Aviability Group DB

 #Requires -Version 3

 function Add-CustomAvailabilityDB{
 <# .SYNOPSIS   
        Adds an existing Database to an existing Availabilty Group on all replicas provided
    .DESCRIPTION   
        Utilizes SMO to access SQL Server
        If SQL Client Tools are not loaded on the machine it will error and fail.
Verifys DB exists on first server in list which is requried to be the Primary server.  If not Aborts
Verifys DB doesnt exist in an availabilty database already.  If it does Aborts.
Verifys DB is in Full Recovery mode if not puts db in Full recovery.
    .PARAMETER Servers   
        Servers participating in AlwaysOn, first server will be assumed to be where the database is which needs to be replicated.
    .PARAMETER SQLAgName
        Availability Group Name database will be created in
    .PARAMETER db
        Databases which will be added to the Availability Group
    .PARAMETER BackupDirectory
        Backup location which all nodes will have access to. Used to backup and restore database from.
If Paramater is not passed function assumes Join Only operation and Backup and Restore operations are skipped. 
.PARAMETER Timeout
Connection Timeout for SQL Server connections.  Function defaults this to 0 so that backup and restore will run as long as needed, however you have the ability to override.               
    .EXAMPLE   
        Add-CustomAvailabilityDB -Servers TNSQL3001,WASQL3001 -SqlAgName AG-TNWA3004 -Databases Test16 -BackupDirectory \\ohnas001\SQLBackups -verbose
.EXAMPLE
Add-CustomAvailabilityDB -Servers TNSQL3001,WASQL3001 -SqlAgName AG-TNWA3004 -Databases Test16  -verbose
.EXAMPLE
Add-CustomAvailabilityDB -Servers TNSQL3001,WASQL3001 -SqlAgName AG-TNWA3004 -Databases Test16,Test17,Test18  -verbose
.EXAMPLE
Add-CustomAvailabilityDB -Servers TNSQL3001,WASQL3001,MISQL3001,OHSQL3001 -SqlAgName AG-TNWA3004 -Databases Test16,Test17,Test18  -verbose
 #>
   [CmdletBinding()]
      Param(
  [Parameter(Mandatory=$true)]
      [string[]]$Servers,
  [Parameter(Mandatory=$true)]
      [string]$SqlAgName,
      [string[]]$Databases,
      [string]$BackupDirectory,
      [int]$Timeout=0
    )

    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") |Out-Null
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null
    Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Done Loading SMO Assemblies ...");
    $SqlServerPrim = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Servers[0]
    $SqlServerPrim.ConnectionContext.StatementTimeout = $Timeout

    #Loop through servers Backing up Database/Tranlog and Restore on each Secondary with NoRecovery
    $loopCnt = 0
    foreach ($Server in $Servers){
        $SqlConn = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Server
        $SqlConn.ConnectionContext.StatementTimeout = $Timeout
        If($loopCnt -eq 0) #First Server is Primary Back up Full and TranLogs
        {
                       
            foreach ($db in $Databases){
            Try{

                    $DBcheck = $sqlconn.Databases | Where-Object {$_.name -eq $db}
       
                    If(!$DBcheck){
                        Throw "$db doesn't exist on $Server please verify Primary Server is first in the list"
                        exit
                    }

                    If($DBcheck.AvailabilityGroupName){
                        $DBAGCheck = $DBcheck.AvailabilityGroupName
                        Throw "$db exists in $DBAGCheck availability group already."
                        exit
                    }
                    if ($SQLconn.Databases[$db].RecoveryModel -ne "FULL")
                    {
                        $sqlconn.Databases[$db].RecoveryModel = "Full"
                        $sqlconn.Databases[$db].Alter();
                        Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) +": Database is not in Full Recovery Mode Setting to Full...");
                    }
                }
            Catch{
                   Throw "Failed to set $db to Full Recovery."
                   Exit
                }

#If BackupDirectory is not provided We will assume you want to do a Join Only
If ($BackupDirectory){       
Try{
$DbBackup = New-Object -typename Microsoft.SqlServer.Management.Smo.Backup
$DbBackup.Database = $db
$DbBackup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database
$DbBackup.Initialize = $true
$DbBackup.Devices.AddDevice("$BackupDirectory\$($db)_AgSetup_full.bak", [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
$DbBackup.SqlBackup($SqlConn)
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": DbBackup Full for $db ...");
$DbBackup = New-Object -typename Microsoft.SqlServer.Management.Smo.Backup
$DbBackup.Database = $db
$DbBackup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Log
$DbBackup.Initialize = $true
$DbBackup.Devices.AddDevice("$BackupDirectory\$($db)_AgSetup_log.trn", [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
$DbBackup.SqlBackup($SqlConn)
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": DbBackup TranLog for $db ...");         
}
Catch{
Throw "Failed to backup $db"
Exit
}
}
           
            Try{
                $AvailabilityGroup = $SqlConn.AvailabilityGroups | Where-Object {$_.Name -eq $SqlAgName}
                $AvailabilityDb = New-Object -typename Microsoft.SQLServer.Management.Smo.AvailabilityDatabase -ArgumentList $AvailabilityGroup,$db
$AvailabilityGroup.AvailabilityDatabases.add($AvailabilityDb);
                $AvailabilityDb.create();
                $AvailabilityGroup.alter();
                Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Primary db $db added to AG..."); 
            }

            Catch{
                Throw "Failed  to Add $db on $SqlAgName..."
                Exit
            }
           
            }#For Each DB
        }
        else
        {
             foreach ($db in $Databases){
                #If BackupDirectory is not provided We will assume you want to do a Join Only   
If ($BackupDirectory){   
            try{
                    $DbRestore = New-Object -typename Microsoft.SqlServer.Management.Smo.Restore
                   
                    $DbRestore.Database = $db
                    $DbRestore.Action = [Microsoft.SqlServer.Management.Smo.RestoreActionType]::Database
                   
                    $DbRestore.Devices.AddDevice("$BackupDirectory\$($db)_AgSetup_full.bak",
                        [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
                    $DbRestore.NoRecovery = $true
                    $DbRestore.SqlRestore($SqlConn)
                    Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Restore Full for $db on $Server...");
                    $DbRestore = New-Object -typename Microsoft.SqlServer.Management.Smo.Restore
                    $DbRestore.Database = $db
                    $DbRestore.Action = [Microsoft.SqlServer.Management.Smo.RestoreActionType]::Log
                    $DbRestore.Devices.AddDevice("$BackupDirectory\$($db)_AgSetup_log.trn",
                        [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
                    $DbRestore.NoRecovery = $true
                    $DbRestore.SqlRestore($SqlConn)
                    Write-Verbose -Message  ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Restore Tran Log for $db on $Server...");     
                    }
               
                catch{
                        Throw "Failed  to Restore $db on $Server..."
                        Exit
                    }
}#End IF No BackupDir Join Only

Try{
                While($true){

                    $AvailabilityGroup = $SqlConn.AvailabilityGroups | Where-Object {$_.Name -eq $SqlAgName}
                    $AvailabilityDb = $AvailabilityGroup.AvailabilityDatabases | Where-Object {$_.Name -eq $db}
                    $AvailabilityGroup = $SqlConn.AvailabilityGroups | Where-Object {$_.Name -eq $SqlAgName}
                    $AvailabilityGroup.AvailabilityDatabases.Refresh()
                    $AvailabilityDb=$AvailabilityGroup.AvailabilityDatabases | Where-Object {$_.Name -eq $db}
                    if ($AvailabilityDb)
                    {break}
                    Start-Sleep -Seconds 15
                    }

                    $AvailabilityDb.JoinAvailablityGroup();
                    Write-Verbose -Message  ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": $db Joined to $SqlAgName on $Server..."); 
                }
                catch{
                      Throw "Failed  to Join $db on to $SQLAgName on  $Server..."
                      Exit
                }

            }#For each DB
        }#Else Secondary Servers
        $loopcnt=$loopCnt+1

    }#End For Each Server


    #Remove all Backup Files which were creating during this excersize
    If ($BackupDirectory){
Try{
        $DBBackupFile = "$BackupDirectory\$($db)_AgSetup_full.bak"
        $DBTRNFile = "$BackupDirectory\$($db)_AgSetup_log.trn"
        If (Test-Path -Path $DBBackupFile){Remove-Item -Path $DBBackupFile}
        If (Test-Path -Path $DBTRNFile){Remove-Item -Path $DBTRNFile}
        }   
        Catch{
            Throw "Failed  to Cleanup Backup Files..."
            Exit
}
Write-Verbose -Message  ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Done Cleaning Up Backup Files...");
}
}#Function End


#Add-CustomAvailabilityDB -Servers TNSQL3001,WASQL3001 -SqlAgName AG-TNWA3004 -Databases Test18 -BackupDirectory \\ohnas001\SQLBackups -Timeout 10 -verbose

SQL Server Always On

 #Requires -Version 3

 function New-CustomAG{
 <# .SYNOPSIS   
        Creates AlwaysOn availabilty group for servers provided.
    .DESCRIPTION   
        Utilizes SMO to access SQL Server to backup database and create Availabity Group Based on Params
        If SQL Client Tools are not loaded on the machine it will error and fail.
If AlwaysOn is not enabled on the Instance of SQL Server the service will be restarted to enable it.  This is required for it to continue with AG Creation.
If you do not want the service to be recycled enable prior to executing the script.   
    .PARAMETER Servers   
        Servers participating in AlwaysOn, first server will be assumed to be where the database is which needs to be replicated.
    .PARAMETER SQLAgName
        Availability Group Name database will be created in
.PARAMETER CNO
        Cluster Name Object which was created IN Active Directory.  If not passed assumes its derived from server names.
    .PARAMETER AgListenerName
        Listener Name to be created that references availability group if no listener is passed it will be created same as SQLAgname
    .PARAMETER SqlAgDatabase
        Database which will be placed in Availability Group. If HADRSeed database will be created and dropped.
    .PARAMETER AgListenerPort
        Port listener will listen on
    .PARAMETER IPAddresses
        One or Many Ip addresses which will be tied to the listener.  One Ip is required for each subnet
    .PARAMETER AgListenerSubnetMask
        Subnetmask for Listeners (Assumption all will utilize the same subnetmask)
    .PARAMETER BackupDirectory
        Backup location which all nodes will have access to. Used to backup and restore database from.                 
    .EXAMPLE   
        Creates Availability Group AG-TNTNTX10 for three servers cross subnet   
        New-CustomAG -Servers TNSQL5010,TNSQL5011,TXSQL5010 -SqlAgName AG-TNTNTX10 -IPAddresses 192.168.81.211,192.168.91.212 -BackupDirectory \\ohnas001\SQLBackups
    .EXAMPLE
        New-CustomAG -Servers TNSQL5010,TNSQL5011,TXSQL5010,WASQL5010 -SqlAgName AG-TNTXWA5001 -CNO CNOTNTNTX5010 -IPAddresses 192.168.81.31,192.168.91.31,192.168.71.31 -BackupDirectory \\ohnas001\SQLBackups -verbose
 #>
    [CmdletBinding()]
      Param(
  [Parameter(Mandatory=$true)]
      [string[]]$Servers,
  [Parameter(Mandatory=$true)]
      [string]$SqlAgName,
  [string]$CNO,
      [string]$AgListenerName,
      [string]$SqlAgDatabase="HADRSeed",
      [string]$AgListenerPort="1433",
  [string[]]$IPAddresses,
      [string]$AgListenerSubnetMask ="255.255.255.0",
  [Parameter(Mandatory=$true)]
      [string]$BackupDirectory
    )

Write-Output ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Loading SMO Assemblies ...");
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") |Out-Null
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null
Write-Output ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Done Loading SMO Assemblies ...");
    Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Done Loading SMO Assemblies ...");

    $HadrEndpointName = "Hadr_Endpoint"                         
    $HadrEndpointPort = 5022
    $LoginType = "WindowsUser"
    if (!$AgListenerName) #Listener was not passed default to AG Name
    {
        $AgListenerName = $SqlAgName
    }
    $SqlServerPrim = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Servers[0]

    #Loop through servers Backing up Database/Tranlog and Restore on each Secondary with NoRecovery
    $loopCnt = 0
    foreach ($Server in $Servers){
     
        $SqlConn = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Server
        Try{
            If($SqlConn.IsHadrEnabled -eq 0)
                {
                    Enable-SqlAlwaysOn -ServerInstance $Server -Force
                    Get-Service -computer $Server -DisplayName SQL*Server*MSSQL* -Exclude *Agent*|Restart-Service
                    Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Finished Enabling AlwaysOn on the nodes ...");
                }
            }
     
        catch{
                Throw "Failed Enabling AlwaysOn on the  $Server"
                Exit
             }
     
        If($loopCnt -eq 0) #First Server is Primary Back up Full and TranLogs
        {
            Try{               
                    If($SqlAgDatabase -eq "HADRSeed" -and ($SqlConn.Databases[$SqlAgDatabase].Name -eq $null))
                    {
                        $Db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database -ArgumentList $SqlConn,"HADRSeed"
                        $db.Create()
                    }
                }
            Catch{
                    Throw "Failed create $SqlAgDatabase database."
                    Exit                 
             
                 }

            Try{
                    if ($SQLconn.Databases[$SqlAgDatabase].RecoveryModel -ne "FULL")
                    {
                        $sqlconn.Databases[$SqlAgDatabase].RecoveryModel = "Full"
                        $sqlconn.Databases[$SqlAgDatabase].Alter();
                        Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) +": Database is not in Full Recovery Mode Setting to Full...");
                    }
                }
            Catch{
                   Throw "Failed to set $SqlAgDatabase to Full Recovery."
                   Exit
                }
         
            Try{
                    $DbBackup = New-Object -typename Microsoft.SqlServer.Management.Smo.Backup
                    $DbBackup.Database = $SqlAgDatabase
                    $DbBackup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database
                    $DbBackup.Initialize = $true
                    $DbBackup.Devices.AddDevice("$BackupDirectory\$($SqlAgDatabase)_AgSetup_full.bak", [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
                    $DbBackup.SqlBackup($SqlConn)
                    Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": DbBackup Full for $SqlAgDatabase ...");
                    $DbBackup = New-Object -typename Microsoft.SqlServer.Management.Smo.Backup
                    $DbBackup.Database = $SqlAgDatabase
                    $DbBackup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Log
                    $DbBackup.Initialize = $true
                    $DbBackup.Devices.AddDevice("$BackupDirectory\$($SqlAgDatabase)_AgSetup_log.trn", [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
                    $DbBackup.SqlBackup($SqlConn)
                    Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": DbBackup TranLog for $SqlAgDatabase ...");         
                }
            Catch{
                    Throw "Failed to backup $SqlAgDatabase"
                    Exit
                }
         

        }#if First Server Backup

        else
        {
                try{
                    $DbRestore = New-Object -typename Microsoft.SqlServer.Management.Smo.Restore
                    $DbRestore.Database = $SqlAgDatabase
                    $DbRestore.Action = [Microsoft.SqlServer.Management.Smo.RestoreActionType]::Database
                    $DbRestore.Devices.AddDevice("$BackupDirectory\$($SqlAgDatabase)_AgSetup_full.bak",
                        [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
                    $DbRestore.NoRecovery = $true
                    $DbRestore.SqlRestore($SqlConn)
                    Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Restore Full for $SqlAgDatabase on $Server...");
                    $DbRestore = New-Object -typename Microsoft.SqlServer.Management.Smo.Restore
                    $DbRestore.Database = $SqlAgDatabase
                    $DbRestore.Action = [Microsoft.SqlServer.Management.Smo.RestoreActionType]::Log
                    $DbRestore.Devices.AddDevice("$BackupDirectory\$($SqlAgDatabase)_AgSetup_log.trn",
                        [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
                    $DbRestore.NoRecovery = $true
                    $DbRestore.SqlRestore($SqlConn)
                    Write-Verbose -Message  ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Restore Tran Log for $SqlAgDatabase on $Server...");   
                    }
             
                catch{
                        Throw "Failed  to Restore $SqlAgDatabase on $Server..."
                        Exit
                    }
        }#Else Secondary Servers
        $loopcnt=$loopCnt+1
    }#For Each Loop through servers Backing up Database/Tranlog
                   

    $AvailabilityGroup = New-Object -typename Microsoft.SqlServer.Management.Smo.AvailabilityGroup -ArgumentList $SqlServerPrim, $SqlAgName
    $AvailabilityGroup.AutomatedBackupPreference="Primary"

    #Loop through servers and create Endpoints for AlwaysOn
    $loopCnt = 0
    Foreach($Server in $Servers){
        Try{
                $SqlConn = New-Object -typename Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Server
                $Endpoint = $SqlConn.Endpoints | Where-Object {$_.EndpointType -eq [Microsoft.SqlServer.Management.Smo.EndpointType]::DatabaseMirroring}
                $ServiceAccount = $SQLconn.ServiceAccount
         
                if(($SqlConn.logins | Where-Object {($_.LoginType -eq "WindowsUser") -and ($_.Name -eq $ServiceAccount)}) -eq $null){

                    $login = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Login -ArgumentList $SqlConn ,$ServiceAccount
                    $login.LoginType = $LoginType
                    $login.Create()
                    }

                if(!$Endpoint){

                        $Endpoint = New-Object -typename Microsoft.SqlServer.Management.Smo.Endpoint -ArgumentList $SqlConn,$HadrEndpointName
                        $Endpoint.EndpointType = [Microsoft.SqlServer.Management.Smo.EndpointType]::DatabaseMirroring
                        $Endpoint.ProtocolType = [Microsoft.SqlServer.Management.Smo.ProtocolType]::Tcp
                        $Endpoint.Protocol.Tcp.ListenerPort = $HadrEndpointPort
                        $Endpoint.Payload.DatabaseMirroring.ServerMirroringRole = [Microsoft.SqlServer.Management.Smo.ServerMirroringRole]::All
                        $Endpoint.Payload.DatabaseMirroring.EndpointEncryption = [Microsoft.SqlServer.Management.Smo.EndpointEncryption]::Required
                        $Endpoint.Payload.DatabaseMirroring.EndpointEncryptionAlgorithm = [Microsoft.SqlServer.Management.Smo.EndpointEncryptionAlgorithm]::Aes
                        $Endpoint.Create()
                        $Endpoint.Start()
                        Write-Verbose -Message  ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Create Endpoint on $Server...");
                        Write-Verbose -Message  ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Granting connect Permission on $Server to $ServiceAccount ...");
                        #$ConnectPerm = New-Object Microsoft.SqlServer.Managment.Smo.ObjectPermissionSet(Connect)
                        #$Endpoint.Grant($ConnectPerm,$ServiceAccount)
                        $Cmd = "GRANT CONNECT ON ENDPOINT::[" + $HadrEndpointName + "] TO [" + $ServiceAccount + "]"
                        $con = "server=$Server;database=master;Integrated Security=True;"
                        $da = new-object -typename System.Data.SqlClient.SqlDataAdapter -ArgumentList $Cmd, $con
                        $dt = new-object -typename System.Data.DataTable
                        $da.fill($dt) | out-null
     
                        Write-Verbose -Message  ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Done granting connect Permission on $Server to $ServiceAccount ...");
                    }

                If($loopCnt -eq 0){

                    $PrimaryReplica = New-Object -typename Microsoft.SqlServer.Management.Smo.AvailabilityReplica -ArgumentList $AvailabilityGroup, $SqlServerPrim.NetName
                    $PrimaryReplica.EndpointUrl = "TCP://$($SqlServerPrim.NetName):$($Endpoint.Protocol.Tcp.ListenerPort)"
                    $PrimaryReplica.FailoverMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaFailoverMode]::Automatic
                    $PrimaryReplica.AvailabilityMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaAvailabilityMode]::SynchronousCommit
                    $AvailabilityGroup.AvailabilityReplicas.Add($PrimaryReplica)
                    Write-Verbose -Message  ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Added Primary Replica..."+$PrimaryReplica.EndpointUrl);
                    }
                Else{

                    $SecondaryReplica = New-Object -typename Microsoft.SqlServer.Management.Smo.AvailabilityReplica -ArgumentList $AvailabilityGroup, $SqlConn.NetName
                    $SecondaryReplica.EndpointUrl = "TCP://$($SqlConn.NetName):$($Endpoint.Protocol.Tcp.ListenerPort)"
                    If ($loopCnt -eq 1)
                        {
                            $SecondaryReplica.FailoverMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaFailoverMode]::Automatic
                            $SecondaryReplica.AvailabilityMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaAvailabilityMode]::SynchronousCommit
                        }
                    Else{
                            $SecondaryReplica.FailoverMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaFailoverMode]::Manual
                            $SecondaryReplica.AvailabilityMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaAvailabilityMode]::ASynchronousCommit
                        }

                    $AvailabilityGroup.AvailabilityReplicas.Add($SecondaryReplica)
                    Write-Verbose -Message  ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Added Secondary Replica..."+$SecondaryReplica.EndpointUrl)
                    }
                    }
        Catch {
            Throw "Failed  to Create/Grant Endpoints on $Server..."
            Exit
        }     
        $loopCnt=$loopCnt+1
    }#For Each Create Endpoints

    $AvailabilityDb = New-Object -typename Microsoft.SqlServer.Management.Smo.AvailabilityDatabase -ArgumentList $AvailabilityGroup, $SqlAgDatabase
    $AvailabilityGroup.AvailabilityDatabases.Add($AvailabilityDb)
    Write-Verbose -Message  ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Added Database to AG..."+$AvailabilityDb);
 
#if No ip Addresses Passed we will not create a listener
if ($IpAddresses){
#If CNO is not passed we will derive it from server names
#First three Char ="CLU"
#Next Series are the first two char of server name which is location ex. NY for NewYork
#Final is the Last four digits of the Primary Server
#For the following Servers MISQL5010, TNSQL5010, OHSQL5010 CNO would be CluMITNOH5010
If (!$CNO){
Try
{
$CNO = "Clu"
foreach ($Server in $Servers)
{
$CNO = $CNO + $Server.Substring(0,2)
}
                        $CNO =$CNO + $Server.Substring($Server.Length - 4,4)
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) +": Captured Cluster Name as $CNO...")
}
Catch
{
Throw ": Failed to Get Cluster Name"
exit
}
}

#Verify Active Directory Tools are installed, if they are load if not Throw Error
If (!(get-module -ListAvailable | Where-Object {$_.Name -eq "ActiveDirectory"})){
Throw "Active Directory Module is Required."
Exit
}
else{import-module ActiveDirectory -ErrorAction Stop}
            try{
    $CNO_OU = Get-ADComputer $CNO
    $Trim = $CNO.length+4
    $CNOlgth = $CNO_OU.DistinguishedName.Length - $trim
    $OUPath = $CNO_OU.ToString().Substring($Trim,$CNOlgth)
    }
            catch{
            Throw ": Failed to find Computer in AD"
    exit
            }

#Create Computer Object for the AgListenerName
#Grant Full control to CNO Computer Object using DSACLS

$m = Get-ADComputer -Filter {Name -eq $SqlAgName} -Server $env:USERDOMAIN | Select-Object -Property * | Measure-Object

If ($m.Count -eq 0)
{
                    Try{
        New-ADComputer -Name $AgListenerName -SamAccountName $AgListenerName -Path $OUPath -Enabled $false
    }
                    Catch{
                    Throw "Failed to Create $AgListenerName in $AG_OU"
        Exit
            }
                     
                        $SucccessChk =0

#Check for AD Object Validate at least three successful attempts
$i=1
                    While ($i -le 5) {
                        Try{
    $ListChk = Get-ADComputer -filter {Name -like $AgListenerName}
    If ($ListChk){$SuccessChk++}
                            Start-Sleep -Seconds 10
    If($SuccesChk -eq 3){break}
                           }
                        Catch{
                             Throw "Failed Validate $AgListenerName was created in $OUPath"
             Exit
                        }
                        $i++
}
}
                Try{
    $AG_OU = Get-ADComputer $AgListenerName
    DSACLS $AG_OU /I:T /G $CNO_OU":GA" | Out-Null
    Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) +": Assigned permisions to $SqlAgName and $CNO in $OUPath")
                    }
                Catch{
                      Throw "Failed Validate grant permissions on $AgListenerName in location $OU_PAth to $CNO in location $CNO_OU"
      Exit
                }


$AgListener = New-Object -typename Microsoft.SqlServer.Management.Smo.AvailabilityGroupListener -ArgumentList $AvailabilityGroup, $AgListenerName
$AgListener.PortNumber = $AgListenerPort
Foreach($IP in $IPAddresses){
Try{
$AgListenerIp = New-Object -typename Microsoft.SqlServer.Management.Smo.AvailabilityGroupListenerIPAddress -ArgumentList $AgListener
$AgListenerIp.IsDHCP = $false
$AgListenerIp.IPAddress = $IP
$AgListenerIp.SubnetMask = $AgListenerSubnetMask
$AgListener.AvailabilityGroupListenerIPAddresses.Add($AgListenerIp)
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Added $IP to $AgListenerName...");
    }
Catch {
Throw "Failed  to Add $IP to $AgListenerName..."
Exit
}
}#For Each IP Address
         
            Try{
    $AvailabilityGroup.AvailabilityGroupListeners.Add($AgListener);
                }
            Catch{
                Throw "Failed  to Add $AgListenerName to $SqlAgName..."
Exit
                }
}#If Ipaddresses are passed

#Create Availabilty Group
    Try{
        $SqlServerPrim.AvailabilityGroups.Add($AvailabilityGroup)
        $AvailabilityGroup.Create()
        Write-Verbose -Message  ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Created AG...");
        }
    Catch{

        Throw ": Failed  to Create Availability Group $SqlAgName..."
        Exit
}
 
    #Loop through Secondary Servers and Join them to the Availability Group
    $loopCnt =0
    Foreach($Server in $Servers){
    Try {
        if ($loopCnt -ne 0){
            $SqlConn = New-Object -typename Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Server
            $SqlConn.JoinAvailabilityGroup($SqlAgName)
            $SqlConn.AvailabilityGroups[$SqlAgName].AvailabilityDatabases[$SqlAgDatabase].JoinAvailablityGroup()
            Write-Verbose -Message  ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Join Secondary $Server to AG...");
            }
        }
    Catch{
        Throw "Failed  to Join $Server to $SqlAgName..."
        Exit
        }
        $loopCnt=$loopCnt+1
    }#For Loop for Joining Secondaries

    Write-Verbose -Message  ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Done Creating Adding Replicas...");

    #Remove all Backup Files which were creating during this excersize
    Try{
        $DBBackupFile = "$BackupDirectory\$($SqlAgDatabase)_AgSetup_full.bak"
        $DBTRNFile = "$BackupDirectory\$($SqlAgDatabase)_AgSetup_log.trn"
        If (Test-Path -Path $DBBackupFile){Remove-Item -Path $DBBackupFile}
        If (Test-Path -Path $DBTRNFile){Remove-Item -Path $DBTRNFile}
        }    Catch{
        Throw "Failed  to Cleanup Backup Files..."
        Exit
}
 
    Write-Verbose -Message  ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Done Cleaning Up Backup Files...");

    #If we created a dummy database to create the AG Remove the database from AG and drop it
    If($SqlAgDatabase -eq "HADRSeed"){
        $loopCnt = 0
        $SqlServerPrim.AvailabilityGroups[$SqlAgName].AvailabilityDatabases[$SqlAgDatabase].Drop();
        Foreach($Server in $Servers){
        Try {
                Start-Sleep -Seconds 30;
                $SqlConn = New-Object -typename Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Server
                If($loopCnt -eq 0){$sqlconn.KillAllProcesses($SqlAgDatabase)};
                $sqlconn.Databases[$SqlAgDatabase].Drop()
                Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Removed $SqlAgDatabase from $Server");
           }
        Catch{
            Write-Verbose -Message  "Failed to remove $SqlAgDatabase from $Server"
            }
        $loopCnt=$loopCnt+1
        }#ForEach Server Remove Seed DB
    }#If HADRSeed
}#Function End


New-CustomAG -Servers TNSQL5010,TNSQL5011,TXSQL5010,WASQL5010 -SqlAgName AG-TNTXWA5001 -CNO CluTNTNTX5010 -IPAddresses 192.168.81.31,192.168.91.31,192.168.71.31 -BackupDirectory \\ohnas001\SQLBackups -verbose