Art of the DBA Rotating Header Image

A Month of SQLPS: Forget-Me-Nots

You’ve probably gotten the impression over this series that it was a little ad hoc. It’s true, because this was as much about exploring the module as documenting it. I figured the best approach was just to dive in and start writing about the ones that could be fit together. This worked out, but over the series I did sections on cmdlets and missed a couple. I’m going to cover them now.

The first is New-SqlBackupEncryptionOption, which I should have covered when I talked about the backup and restore cmdlets. This cmdlet is for supporting the encrpyted backup features of SQL 2014 and allows you to generate an Smo.BackupEncryptionOptions object that can be used by either the Backup-SqlDatabase or Set-SqlSmartAdmin cmdlets. You use it to define a certificate or key and encryption algorithm so that your backup file will be encrypted:

$encryption = New-SqlBackupEncryptionOption -Algorithm Aes256 -EncryptorType ServerCertificate -EncryptorName AzureManagedBackup
Backup-SqlDatabase -ServerInstance PICARD -Database AdventureWorks2012 -EncryptionOption $encryption -BackupFile C:\Backups\AW_Encrypted.bak

Restoring the database does not require you to declare an encryption option, you just need the appropriate certificate or asymmetric key to be available. This makes encrypting backups for SQL2014 pretty simple. The only real challenge is how you manage your keys.

The other cmdlet I skipped over was Test-SqlDatabaseReplicaState, which should have been included when I talked about databases in Availability Groups. This cmdlet will use Policy Based Management to evaluate all the databases in an Availability Group replica set to determine their health. As with the other AG cmdlets, it is a little cumbersome to use because it relies on the provider paths. And, of course, it has its own path to reference: AvailabilityGroups\<AGNAME>\DatabaseReplicaStates.

$replicastates = Get-ChildItem SQLSERVER:\SQL\KIRK\DEFAULT\AvailabilityGroups\ENTERPRISE\DatabaseReplicaStates
$replicastates | Test-SqlDatabaseReplicaState


We get a handy little report of the policy evaluations that is a collection of PowerShell.Hadr.DatabaseReplicaHealthState objects. This could be used to drive alerts or remediations of replicas that become unhealthy within the AG.

That does it! That’s all the current cmdlets in the SQLPS module, at least as of SQL Server 2014. With the impending release of 2016, I’m sure we’ll get some new ones. As of the this post, I haven’t yet looked at SQL Server 2016 CTP 3, so I’m not sure what to expect. Watch for that in a future post. Tomorrow I’ll give you a quick wrap up of the series.

Leave a Reply

Your email address will not be published. Required fields are marked *