Cluster/AOA script continued

I have done some more scripting related to building AOA etc in our environment. May be useful for someone. I admit it could be a bit cleaner etc but should work for helping others facing a truly automated build. I am sure I could change the Read-host entries to parameters/variables if we were going to use SCCM or something to deploy, but we are not quite there yet in my environment, unfortunately. Note: I have updated the script some more to create variables for the instance name etc add some error handling, but the basic function is the same as what is posted below.



######################################################################################

#######variables to modify as needed
$path="C:\temp\SQLClusterInstall" #Log File Location
$DomainSQLAcct ="somedomain\acct"
$BackupShare="G:\Backups"

#Set Policy
Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass

##################################################################
## Create and update logfile

if (!(Test-Path -Path $path))
 {
    New-Item -ItemType Directory -Force -Path $path
  }
$Logfile = "C:\temp\SQLClusterInstall\SQL_Cluster_LOG.log"
Function LogWrite
{
   Param ([string]$logstring,$color)

   $date = (Get-Date -Format "MM/dd/yyyy HH:mm:ss")
   $finalstring = $date + " -- " + $logstring
   Add-content $Logfile -value $finalstring
   Write-Host $logstring -ForegroundColor $color
}

LogWrite "Script for creating the AOA Group on the cluster has been initialized" -Color White

if ($env:USERNAME -notmatch ".dba")
{LogWrite "Script must be ran as a dba. You are logged in as $env:USERNAME " -Color Red
 return}

Write-Host "The first server entered is assumed to be the primary server for the AOA group.`nDo not run this script until after at least one Database has been installed which will be part of the AOA group" -ForegroundColor Yellow
do
    {
    $cont = Read-Host "Continue with script [y/n]"
    if ($cont -eq 'n'){return}
    if ($cont -eq 'y'){LogWrite "Continuing to AOA install" -color Green}
    }
While ($cont -ne 'y')


################################################################################
#Get Database Server Names and verify they exist
do {
    $allExist = $true
    $ArrComputers = Read-Host "Enter computer Names that will be part of AOA group (separate multiple computers by comma)"
    $ArrComputers = $ArrComputers.Split(",")



    foreach ($computer in $ArrComputers) {

        try {
            Get-ADComputer -Identity $computer | Out-Null
            LogWrite "$computer exists" -color green
            }
        catch {
            LogWrite "$computer doesn't exist, try again" -color red
            $allExist = $false
            }
    }
}until($allExist)


##############################################################################
#Get Databases to put into AOA

do {
    $allExist = $true
    $AgName = Read-Host "Enter the name for the AOA group."
    LogWrite "AOA group name is $AgName" -color White
    $DBList = Read-Host "Enter database/databases to be put into the AOA group (separate multiple computers by comma)"
    $DBList = $DBList.Split(",")

forEach($db in $dblist)
{
  $PrimDB= $ArrComputers[0] + "\PROD01"
  try{
    Get-SqlDatabase -ServerInstance $PrimDB -name $db
    LogWrite "Database $db exists" -color green
     }
  catch {
         LogWrite "$db doesn't exist, try again" -color red
         $allExist = $false
         }
    }
    }
    Until ($allExist)
######################################################################################
# Set to Full Recovery mode and backup the Databases
$Primary= $ArrComputers[0]
foreach ($db in $DatabaseList)
{
    #Change Databases to full recovery Mode
    $fullRec = "Use Master; Alter Database $db Set recovery Full;"
    Invoke-SQLCmd -Serverinstance "$PrimDB" -Query $fullRec

    #Backup Databases
    $bakFile = Join-Path $BackupShare "$db.bak"
    $trnFile = Join-Path $BackupShare "$db.trn"
    try
    {
    LogWrite "Backing up database '$db' on $primary to $bakFile" -White
    Backup-SqlDatabase -InputObject $primdb -Database $db -BackupFile $bakFile -Init
    LogWrite "Backing up the log of database '$db' on $primary to $trnFile" -White
    Backup-SqlDatabase -InputObject $primdb -Database $db -BackupFile $trnFile -BackupAction "Log" -Init
    LogWrite "Backups completed Successfully" -color greem
    }
    Catch
    {
    LogWrite "Backup of databases failed" -Color Red
    }
}

######################################################################################
#Create HADR Endpoints on the servers



foreach ($computer in $ArrComputers)
{

    New-SqlHADREndpoint -Path "SQLSERVER:\SQL\$computer\PROD01" -Name "Hadr_endpoint" -port 5022 -EncryptionAlgorithm AES -Encryption Required
    Set-SqlHADREndpoint -Path "SQLSERVER:\SQL\$computer\PROD01\Endpoints\Hadr_endpoint" -State Started

 }


########################################################################################
#Grant access to the service account
foreach ($computer in $ArrComputers)
{
$createLogin ="Create Login [$DomainSQLAcct] FROM WINDOWS;"
$grantConnectPermissions = "GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [$DomainSQLAcct];"


Invoke-SQLCmd -Serverinstance "$computer\PROD01" -Query $createLogin
Invoke-Sqlcmd -ServerInstance "$computer\PROD01" -Query $grantConnectPermissions

}

########################################################################################
#Create AG Group on Primary Replica

#Create the  replicas as a template object
$replicas =@()
foreach ($computer in $ArrComputers)
{
$endpoint = "TCP://" + $Computer + "." + $env:USERDNSDOMAIN + ":5022"
$replicas += New-SqlAvailabilityReplica -Name "$computer\Prod01" -EndpointUrl $endpoint -AvailabilityMode SynchronousCommit -FailoverMode Automatic -AsTemplate -Version 13

}

#Create Availability group on primary
New-SqlAvailabilityGroup -AvailabilityReplica ($replicas) -Name $AGName -Database $dblist -Path SQLSERVER:\SQL\$PrimDB
$arrcomputers |where-object {$_ -notmatch $primary}|ForEach-Object {Join-SqlAvailabilityGroup -Path SQLSERVER:\SQL\$_\PROD01 -name $AGName}