MS-SQL HA database backup using Powershell and Ansible, Step by Step - 1 -
Backgaroud :
Currently Azure Stack HUB's MS-SQL HA deployment uisng the sql-2016-alwayson ARM template is not supporting the automated backup which is otherwise supported for MS-SQL standalone database as discribed below.
This works only for the standalone deployment so that you will have to get some other ways to backup your HA databases.
Reference:
Built-in support for SQL Server Always On :
#Autmated Backup is not supported on this matrix.
Solution summary:
MS support recommended (we opned the support call for this) us to use 3rd party backup tools to backup HA databases, such as NetBackup. That's the one way of doing it. But here, we have implemented like below:
- Set up a powershell script to backup all databases in the MS-SQL HA.
- Run the powershell script using Ansible.
# Backup location we uses is Azure Stack's blob storage.
Storage account & Powershell Backup script:
1. Create a blob storage within the Azure Stack HUB and get a storage account Key.
From the Azure Stack HUB user portal, navigate to "Dashborad > Resource groups > <Your Resource group>"
Click "+ Add"
Add a new "Storage Account" for an SQL backup.
Enter a storage account name and other fields as required, and click "Review + Create"
Create a new blob container.
Navigate to "Dashborad > Resource groups > <Your Resource group> > <your storage account name> - Blobs", and click "+ Container".
Check the newly created storage account and get a Access Key infomation.
Navigate to "Dashborad > Resource groups > <Your Resource group> > <your storage account name> ".
And select "Access keys" and keep your storage accout name and its key.
Also check "properties" and get your "PRIMARY BLOB SERVICE ENDPOINT".
2. Create an SQL credential on all instances of SQL Server.
On the SQL server host, launch Windows powerShell ISE. And run the script below:
#==========
#Create cred:
#==========
# load the sqlps module
import-module sqlps
# set parameters
$sqlPath = "sqlserver:\sql\$($env:COMPUTERNAME)"
$storageAccount = "<your storage accout name>"
$storageKey = "<your storage accout key>"
$secureString = ConvertTo-SecureString $storageKey -AsPlainText -Force
$credentialName = "BackupCredential-$(Get-Random)"
Write-Host "Generate credential: " $credentialName
#cd to sql server and get instances
cd $sqlPath
$instances = Get-ChildItem
#loop through instances and create a SQL credential, output any errors
foreach ($instance in $instances) {
try {
$path = "$($sqlPath)\$($instance.DisplayName)\credentials"
New-SqlCredential -Name $credentialName -Identity $storageAccount -Secret $secureString -Path $path -ea Stop | Out-Null
Write-Host "...generated credential $($path)\$($credentialName)." }
catch { Write-Host $_.Exception.Message } }
This script will generate a Backup credential like below:
Keep this "BackupCredential-xxxxx" credential.
Next, run the below script to back up all sql database on this host.
#==========
#Run backup
#==========
import-module sqlps
$sqlPath = "sqlserver:\sql\$($env:COMPUTERNAME)"
$storageAccount = "<your storage accout name>"
$blobContainer = "<your container name>"
$backupUrlContainer = "https://<primary endpoint URL>/$blobContainer/"
$credentialName = "BackupCredential-<backup cred number>"
Write-Host "Backup database: " $backupUrlContainer
cd $sqlPath
$instances = Get-ChildItem
#loop through instances and backup all databases (excluding tempdb and model)
foreach ($instance in $instances) {
$path = "$($sqlPath)\$($instance.DisplayName)\databases"
$databases = Get-ChildItem -Force -Path $path | Where-object {$_.name -ne "tempdb" -and $_.name -ne "model"}
foreach ($database in $databases) {
try {
$databasePath = "$($path)\$($database.Name)"
Write-Host "...starting backup: " $databasePath
Backup-SqlDatabase -Database $database.Name -Path $path -BackupContainer $backupUrlContainer -SqlCredential $credentialName -Compression On
Write-Host "...backup complete." }
catch { Write-Host $_.Exception.Message } } }
This script will backup all databases except "tempdb" and "model" databsaes.
After running the script, check if all sql databases have been saved to the blob storage.
