July 20, 2020 at 6:27 pm
Dear friends,
Using Always On in our corporate site, we have 3 nodes deployed, our main database backup is based on images of that virtual machines, but constantly I have databases requesting transaction log backups, what role should have a normal backup of transaction log files vs virtual machines images backup, our management team has choosen this option as the only backup policy.
Thanks for your support
July 21, 2020 at 9:59 am
There is a huge difference between image back-up and a database back-up. It's a long story to explain everything of a database back-up, so I suggest you first read articles/blogs to extend your knowledge (like: https://www.sqlservercentral.com/stairways/stairway-to-transaction-log-management-in-sql-server and https://www.sqlshack.com/understanding-sql-server-backup-types/ )
Short answer: it is NOT correct to only use image back-up.
- Images are like a snapshot and can only be used to revert to that single moment when the image was created. This type of back-up doesn't take database integrity into account and a revert could lead to a corrupt database. It will also NOT limit the size of the database LOG file, resulting in an ever increasing file and eventually a full disk.
- A good database back-up strategy can be used to revert the database to any moment in the past. Regular LOG back-ups are required for databases in FULL recovery model (as they are with Always On). This will keep LOG file size within limits.
July 21, 2020 at 11:47 am
So, have you and the business worked out your Recovery Time Objective and Recovery Point Objective? If not, go do that first. If so, test this method on a restore. It's that simple. Have them demonstrate that they can meet the RTO & RPO with a restore. If they can't, chuck that thing to the curb and start building out a backup strategy that can. It's that easy. Never argue. Just say, cool, sounds great, show me a restore that meets our business requirements. Now show me another. Do they pass? Awesome. If they don't, the statement is simple, "We're not using that."
"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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply