December 2, 2019 at 12:46 am
What is considered best practice for SQL backups for SQL Servers (specifically 2017) running in an Azure VM?
I'm aware of:
What I'm considering is:
What I'm concerned about is the size of that VM Backup in that is going to grow and grow every time, but curious as to if the VM was to have problems, how I would get it back online quickly.
What's the best approach here.
December 2, 2019 at 1:47 pm
Whether running in a VM on Azure, AWS, or locally, a VM is just a VM. You're responsible for your database backups the same way across all of them. The VM backup will allow you to recover the databases on the VM, but only if you recover all of them at the same time. It won't allow for individual recovery. Personally, while I'd use it, I wouldn't count on it for my database backups. I'd still do standard full/differential/log backups on those so that I can meet individual Recovery Point Objectives and Recovery Time Objectives. Further, it makes testing recovery, a must, easier to do.
So, both. The VM backup because you can recover everything, the database backups because you're much more likely to need discrete recovery.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 2, 2019 at 2:03 pm
Whether running in a VM on Azure, AWS, or locally, a VM is just a VM. You're responsible for your database backups the same way across all of them. The VM backup will allow you to recover the databases on the VM, but only if you recover all of them at the same time. It won't allow for individual recovery. Personally, while I'd use it, I wouldn't count on it for my database backups. I'd still do standard full/differential/log backups on those so that I can meet individual Recovery Point Objectives and Recovery Time Objectives. Further, it makes testing recovery, a must, easier to do.
So, both. The VM backup because you can recover everything, the database backups because you're much more likely to need discrete recovery.
Preach it, brother. I'll also restate to emphasize that the VM backups will not accommodate PIT (Point-in-Time) backups.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 2, 2019 at 9:02 pm
Thanks Grant/Jeff, that is exactly my methodology as well that I just wanted to confirm.
What do you both do surrounding the VM backup, because if you have the "SQL Server VSS Writer" service enabled, then the VM backup typically takes a full backup of your databases as well throwing your transaction log backups out of sync with your own full backups? I always disable the service as part of install procedures but just wondering what others do here also.
December 2, 2019 at 11:12 pm
Thanks Grant/Jeff, that is exactly my methodology as well that I just wanted to confirm.
What do you both do surrounding the VM backup, because if you have the "SQL Server VSS Writer" service enabled, then the VM backup typically takes a full backup of your databases as well throwing your transaction log backups out of sync with your own full backups? I always disable the service as part of install procedures but just wondering what others do here also.
Well, let's be clear. That shouldn't interfere with log backups. That's going to interfere with differentials, but not logs.
And, if I'm using differentials as part of our RPO/RTO, that may have to be disabled. However, it's one of those things that gets worked out.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 3, 2019 at 5:41 am
Thanks Grant/Jeff, that is exactly my methodology as well that I just wanted to confirm.
What do you both do surrounding the VM backup, because if you have the "SQL Server VSS Writer" service enabled, then the VM backup typically takes a full backup of your databases as well throwing your transaction log backups out of sync with your own full backups? I always disable the service as part of install procedures but just wondering what others do here also.
I only take care of the SQL Server Native Backups. The infrastructure folks take care of backing up the VMs and, since I do a full restore of my big database and a smaller one every night, I can assure you that we don't have a problem with the VM backups instantiating SQL Server full backups. Not sure what their setup is. I can take a look tomorrow to see if VSS Writer is disabled but I'm pretty sure that it isn't because SQL Server Backups kind of rely on that service a whole lot.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 3, 2019 at 5:45 am
PhilipC wrote:Thanks Grant/Jeff, that is exactly my methodology as well that I just wanted to confirm.
What do you both do surrounding the VM backup, because if you have the "SQL Server VSS Writer" service enabled, then the VM backup typically takes a full backup of your databases as well throwing your transaction log backups out of sync with your own full backups? I always disable the service as part of install procedures but just wondering what others do here also.
I only take care of the SQL Server Native Backups. The infrastructure folks take care of backing up the VMs and, since I do a full restore of my big database and a smaller one every night, I can assure you that we don't have a problem with the VM backups instantiating SQL Server full backups. Not sure what their setup is. I can take a look tomorrow to see if VSS Writer is disabled but I'm pretty sure that it isn't because SQL Server Backups kind of rely on that service a whole lot.
Question for your Jeff, how do you know that the VM level backups aren't instantiating SQL Server full backups unless you try and restore your transaction log backups from that full backup and find that the log chain is broken?
I've always disabled the VSS Writer because of this issue and never had a problem with my SQL backups but interested when it comes to the cloud.
December 3, 2019 at 5:47 am
PhilipC wrote:Thanks Grant/Jeff, that is exactly my methodology as well that I just wanted to confirm.
What do you both do surrounding the VM backup, because if you have the "SQL Server VSS Writer" service enabled, then the VM backup typically takes a full backup of your databases as well throwing your transaction log backups out of sync with your own full backups? I always disable the service as part of install procedures but just wondering what others do here also.
Well, let's be clear. That shouldn't interfere with log backups. That's going to interfere with differentials, but not logs.
And, if I'm using differentials as part of our RPO/RTO, that may have to be disabled. However, it's one of those things that gets worked out.
I've found that Full Backups are taken through a snapshot of the VM itself within Azure and in return it breaks the log chain. That was what started my search for what the correct backup methodology is for VMs within Azure.
December 3, 2019 at 6:04 am
I've found that Full Backups are taken through a snapshot of the VM itself within Azure and in return it breaks the log chain. That was what started my search for what the correct backup methodology is for VMs within Azure.
People keep driving nails in the coffin of Azure for me. The more I hear things about it like what you posted above, the more I hope I'll never have to work with it.
To wit (and I guess I missed it), if this is all about an Azure instance, I'm not qualified to make anything but what I think are common sense recommendations because I don't use it.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 3, 2019 at 8:56 am
PhilipC wrote:I've found that Full Backups are taken through a snapshot of the VM itself within Azure and in return it breaks the log chain. That was what started my search for what the correct backup methodology is for VMs within Azure.
People keep driving nails in the coffin of Azure for me. The more I hear things about it like what you posted above, the more I hope I'll never have to work with it.
To wit (and I guess I missed it), if this is all about an Azure instance, I'm not qualified to make anything but what I think are common sense recommendations because I don't use it.
No problem, appreciate you taking the time to contribute to the thread anyway mate 🙂
December 3, 2019 at 12:14 pm
I've found that Full Backups are taken through a snapshot of the VM itself within Azure and in return it breaks the log chain. That was what started my search for what the correct backup methodology is for VMs within Azure.
Don't take my word for it. Here's one of Paul Randal's myth busting posts. Log chain breaks are caused by things other than full backups. Here's a TechNet article also listing causes of breaks to the log chain, no full backup. Finally, here's one, of several, of Gail Shaw's posts on the topic saying, quite explicitly, full backups don't interfere with the log chain.
Now, I'm not saying that whatever process you're using for the Azure backups might not be breaking the log chain, but if they are, it's got to be a change in the recovery model or something else that's occurring, because it's not the full backup.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply