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
    }
   }
  }
 }
}

jQuery Form Validation and Twitter Bootstrap 3

I have been doing some work developing mobile applications for my MSc recently.  To support mobile data capture requirements I have made use of twitter bootstrap and the jquery validation framework.  These two frameworks together provide a great toolset for validating input and building responsive applications.  I have made this process slicker by including the mechanism to show and hide validation errors when the forms are changed.  A sample html form is found below which demonstrates how this works.

<form role="form" class="form-horizontal" id="registrationform" name="registrationform" method="post" action="registersave.php">

<div class="form-group">

<label class="col-sm-2 control-label"  for="username">Username</label>

              <div class="input-group">

                     <span class="input-group-addon">*</span>

                     <input type="text" class="form-control" id="username" name="username" placeholder="Username" required="required">

</div>

              <label class="error label label-danger" for="username"></label>           

       </div>

</form>

And the javascript to support the validation is found below:

$(function() {
 $('#username').focus();
 
 $('#registrationform').validate({
  highlight: function(element) {
   $(element).closest('.form-group').removeClass('has-success').addClass('has-error');
   $(element).closest('.form-group').children('.error').removeClass('hide');
  },
  success: function(element) {
   $(element).closest('.form-group').removeClass('has-error').addClass('has-success');
   $(element).addClass('hide');
  }
 });  
});

This produces the following results when data is entered into the form.