SQL 2014 introduced some new features to expand capabilities into Azure. One of these features is Azure Managed Backup. There is some SQLPS support provided for this new feature, grouped under the SqlSmartAdmin cmdlets:
These cmdlets give us some limited control over managed backups, allowing us to set up and configure the managed backup settings. I’m going to save that for another post, but for the detailed instructions you can follow the blog post linked above or the Microsoft documentation.
Once we have the managed backups setup, we can retrieve the smart admin object using Get-SqlSmartAdmin. While it accepts the usual parameters, I prefer to use the -ServerInstance argument for simplicity reasons. As with other cmdlets, we’ll call it and pass the output to Get-Member to see what the underlying type is:
Get-SqlSmartAdmin -ServerInstance PICARD | Get-Member
The object is another member of the SMO library, the Smo.SmartAdmin class. There’s not much to say about this class. It contains a lot of properties you would expect, such as BackupEnabled, EncryptionType, and StorageUrl. It should be noted that this class only contains the instance level settings, so you can’t use it to show what individual databases you’ve set up for managed backups.
Set-SqlSmartAdmin will allow us to update those instance level settings. The documentation for this cmdlet lies a little, as it says you can pass the provider path as one of the arguments. I’ve tried this and couldn’t get it to work, but did find that we could use Get-SqlSmartAdmin and the pipeline to get this to work:
Get-SqlSmartAdmin -ServerInstance PICARD | Set-SqlSmartAdmin -BackupRetentionPeriodInDays 10
In addition to managing our instance level settings, we can also validate that the managed backups are working correctly. SQL 2014 included two new Policy Based Management policies: SmartAdminSystemHealthPolicy and SmartAdminUserActionsHealthPolicy. These two policies will validate that your managed backup processes are working correctly. Test-SqlSmartAdmin will essentially evaluate these policies and report if there’s a failure:
Get-SqlSmartAdmin -ServerInstance PICARD | Test-SqlSmartAdmin
Managed backups are a cool concept and can be of great value for DBAs. The Azure Managed Backup system not only takes care of your backups for you without a lot of intervention, but also stores them offsite in the cloud, giving you additional insurance for a disaster recovery scenario. These SQLPS cmdlets feel like a good start for managing this functionality, but it feels incomplete.
I’ll confess I’m not as strong on using Azure with SQL Server, so I would have to further investigate this in combination with the Azure Powershell support. Once I finish this series, I plan to return to these and explore them a little more in detail. I think there is a lot of appeal with managed backups and being able to script them out in Powershell can be a huge boon to automating work in smaller shops.
I’m tweeting!