Tuesday, 10 December 2013

Dynamic Addition Of Always On AG Databases

Today I have been working on scripting the capabilities to dynamically add missing databases to secondary clusters in the event of databases being created dynamically.  With the mouthful out the way the following powershell provides the mechanism to dynamically add databases automatically to all existing AG if they are not available.


#**********************************************************************************
# DYNAMIC ADDITION OF USER-DATABASES TO EXISTING AG GROUP
# GARY MCALLISTER 2013
# RUNS FROM THE PRIMARY DATABASE AND UTILISES REMOVE \\SECONDARY\BACKUP SHARES
# SECONDARY MOST ALLOW THE SQL ACCOUNT PERMISSON TO BACKUP SHARE
#**********************************************************************************
clear
import-module sqlps

$dbs = @();
$excludedDbs = @('[master]','[model]','[msdb]','[tempdb]','[SSISDB]','[ReportServer]','[ReportServerTempDb]')

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$sqlServer = new-object ("Microsoft.SqlServer.Management.Smo.Server") "LOCALHOST\ENOTINGINSTANCE"

#THIS ENSURES THAT THE DATABASE IS ADDED TO ALL AVAILABILITY GROUPS 
#IN REALITY YOU PROBABLY ONLY WANT IT IN ONE.
foreach ($agGroup in $sqlServer.AvailabilityGroups)
{
 $agName = $agGroup.name
 $agDbs = $agGroup.AvailabilityDatabases
 
 if ($agName -ne $null)
 { 
  foreach($sqlDatabase in $sqlServer.databases) 
  {
   $db = "$sqlDatabase";
   $found = $false
   foreach ($agDb in $agDbs) 
   {
    if ($db -eq $agDb){
     $found = $true;
     break;
    }
   }
   
   foreach ($sysDb in $excludedDbs)
   {
    if ($db -eq $sysDb){
     $found = $true;
     break;
    }   
   }
   
   #Add this to the ag group
   if ($found -ne $true) {
    $dbs += $sqlDatabase
   }
  }
 } 
 
 $agPrimary;
 foreach ($db in $dbs) {
  foreach ($repl in $agGroup.AvailabilityReplicas) {
   if ($repl.Role -eq "Primary") 
   {
    $agPrimary = $repl;
   }
  
   #if this is the secondary
   if ($repl.Role -eq "Secondary"){
    $dbfName = $db.Name;
       
    $secondaryServerName = $repl.Name.Substring(0, $repl.Name.IndexOf("\"));
    $secondaryServerInstanceName = $repl.ToString().Replace("[","").Replace("]","");
    
    $primaryServerName = $agPrimary.Name.SubString(0, $agPrimary.Name.IndexOf("\"));
    $primaryServerInstanceName = $agPrimary.ToString().Replace("[","").Replace("]","");
    
    $backupFullLocation = "\\$secondaryServerName\backup\$dbfName.bak";
    $backupLogLocation = "\\$secondaryServerName\backup\$dbfName.trn";
    $agPrimaryPath = "SQLSERVER:\SQL\$primaryServerInstanceName\AvailabilityGroups\$agName"
    $agSecondaryPath = "SQLSERVER:\SQL\$secondaryServerInstanceName\AvailabilityGroups\$agName"
    
    if ((Test-Path -Path FileSystem::$backupFullLocation)){
     Remove-Item -Path FileSystem::$backupFullLocation -Force
    }
    
    if ((Test-Path -Path FileSystem::$backupLogLocation)){
     Remove-Item -Path FileSystem::$backupLogLocation -Force
    }
        
    #COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION
    Backup-SqlDatabase -Database $dbfName -BackupFile $backupFullLocation -ServerInstance $primaryServerInstanceName -CopyOnly
    Restore-SqlDatabase -Database $dbfName -BackupFile $backupFullLocation -ServerInstance $secondaryServerInstanceName -NoRecovery
    
    #NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION
    Backup-SqlDatabase -Database $dbfName -BackupFile $backupLogLocation -ServerInstance $primaryServerInstanceName -BackupAction 'Log'
    Restore-SqlDatabase -Database $dbfName -BackupFile $backupLogLocation -ServerInstance $secondaryServerInstanceName -RestoreAction 'Log' -NoRecovery
    
    Add-SqlAvailabilityDatabase -Path $agPrimaryPath -Database $dbfName
    Add-SqlAvailabilityDatabase -Path $agSecondaryPath -Database $dbfName
    
    if ((Test-Path -Path FileSystem::$backupFullLocation)){
     Remove-Item -Path FileSystem::$backupFullLocation -Force
    }
    
    if ((Test-Path -Path FileSystem::$backupLogLocation)){
     Remove-Item -Path FileSystem::$backupLogLocation -Force
    }
   }
  }
 }
}

No comments:

Post a Comment