#********************************************************************************** # 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 } } } } }
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.
Thursday, 5 December 2013
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.
And the javascript to support the validation is found below:
This produces the following results when data is entered into the form.
<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.
Subscribe to:
Posts (Atom)