Bub Pusateri has a helpful script for us:
Last year I wrote about how to upload data to Azure Blob Storage Archive Tier, and included a PowerShell script to do so. It’s something I use regularly, as I have hundreds of gigabytes of photos and videos safely (and cheaply!) stored in Azure Blob Storage using Archive Tier.
But read on for a recent announcement to make the process easier.
John Sterrett has run into a few issues with Azure Backup for SQL Server VMs:
Having the ability to backup new databases automatically is taken for granted. So much, that I noticed that Azure Backup for SQL Server VM’s will not automatically backup new databases for you. That’s right. Make sure you remember to go in and detect and select your new database every time you add a database or you will not be able to recover.
Azure Backup for SQL Server VM’s has an interesting feature called Autoprotect. This should automatically backup all your databases for you. Unfortunately, this does not work. Yes, I double-checked by enabling autoprotect for a VM and I added a new database. The database didn’t get backed up so I had to manually add the database.
Some of these seems like it’s easy enough to fix, so hopefully the product gets better over time.
Joey D’Antoni recommends backing up database to Blob Storage via URL in Azure:
Unlike in your on-premises environment, where you might have up to a 32 Gbps fibre channel connection to your storage array and then a separate 10 Gbps connection to the file share where you write your SQL Server backups, in the cloud you have a single connection to both storage and the rest of the network. That single connection is metered and correlates to the size (and $$$) of your VM. So bandwidth is somewhat sacred, since backups and normal storage traffic go over the same limited tunnel. This doesn’t mean you can’t have good storage performance, it just means you have to think about things. In the case of the customer I mentioned, they were saturating their network pipe, by writing backups to the file system, and then having the Azure backup service backup their VM, they were saturating their pipe and making regular SQL Server I/Os wait.
Read on to see what the alternative is.
Lori Brown reminds us to perform checksums and verify backups on completion:
I found out that I have been missing something from our regular database backups that I had no idea that I should have been using all along. I know about verifying your backup file and have incorporated into our standard maintenance routines one that will periodically test backups by restoring using
VERIFYONLY. However, I totally missed also havingCHECKSUMspecified when creating backup files. Ugh!! Not sure how that happened but I am totally onboard with it now. Better late than never!
Lori does explain what the consequences are in terms of time and CPU utilization so that you’re aware of the tradeoffs when enabling these options.
Jovan Popovic shows how you can audit who is taking backups on an Azure SQL Managed Instance:
One mechanism to ensure that nobody can take the COPY_ONLY backup of your database is to use Transparent Data Encryption that automatically encrypts all backups. In that case you would need to use Customer-managed (BYOK) TDE where you will keep your encryption key in Azure Key-Vault. User-initiated COPY_ONLY backups are (currently) not allowed if you are using Service-managed TDE.
If you don’t use TDE on the database or there is a risk that someone can remove TDE from database and then take a backup, Managed Instance provides auditing mechanism that enables you to track who performed a backup and when. This way you can always track if some unauthorized action is happening.
Read on for the solution. Looking through it, it seems like conceptually it’d work equally well with on-prem/IaaS SQL Server as with Managed Instances.
Bob Pusateri wants you to test your restores, probably right now:
I had a client that was hit by a ransomware virus, encrypting several of their systems including the database server. Not to worry, though, they had “full backups” of all the affected machines, done by a third-party backup utility. After taking a day to cleanse their network, they restored these backups onto their servers. Now it was just a simple matter of bringing all the applications back online, right? Well, not exactly…
Just because a backup completes with no error code doesn’t mean it’s really a successful backup.
John McCormack shows how you can use AZCopy to move SQL Server backups into Azure Storage:
AZCopy is a useful command line utility for automating the copying of files and folders to Azure Storage Account containers. Specifically, I use AZCopy for SQL Backups but you can use AZCopy for copying most types of files to and from Azure.
In this blog post example (which mirrors a real world requirement I had), the situation is that whilst I need to write my SQL backups over a network share, I also want to push them up to Azure Storage (in a different region) to allow developers quicker downloads/restores. This is why I need to use AZCopy. If I only needed my backups to be written to Azure, I could have used BACKUP TO URL instead.
Read on to see how John did it.
John Morehouse has a Powershell script to estimate how much disk space you’re using with backups:
Recently, I needed to be able to determine the total size for all the most recent full backups for certain servers. Luckily for me these particular servers were using Ola Hallengren’s Maintenance Solution which simplified things since each back up had it’s own specific directory. Powershell has the flexibility to easily provide what I need with little effort.
Let’s take a look!!
John shows specifically the latest full backup, but you can extend this to include other sets as well.
Shane O’Neill wants to know whether the dbcreator built-in role has the ability to back up the databases it creates:
I knew from reading the documentation that
dbcreatorgrants permissions to create, alter, drop, and restore databases. My question was does it give permission to backup databases?It seems to give everything else so is backup databases just missing there? Or is it intentionally left out?
To find out whether it does, click on through.
We are recommending to rely on automatic backups only, with the build-in restore functionality to restore a database from a point-in-time, restore a database to another instance (for instance from production to dev)or Geo-restore functionalities to move your database. These automatic backups can be kept up to 35 days. These built-in automatic backups are secure and enables you to be fully compliant. In this scenario COPY_ONLY backups are only in some specific cases.
Strict TDE protection don’t allow you to take your own custom backups. If you need a backup of a TDE protected database, you would need to temporary disable TDE, take a backup, and then enable TDE again.
It’s not really a Managed Instance-specific thing, but rather TDE: if you want to take a non-encrypted backup of an encrypted database, you’ve got to kill encryption first.
Kevin Feasel
2019-09-11
Backups, Cloud, Storage
No Comment