SQL Server 2017: Making Backups Great Again!
In some DBA circles, backups are just as popular as politicians! However, recoverability is the most important task for database administrators. While SQL Server 2017 added so many great features like graph, SQL on Linux, and more. Today, I want to focus on two small underdog features that might be game changers on how you do backups.
SQL Server Backups are often as popular as politicians.
Smart Differential Backups
Databases are getting bigger, not smaller. More storage capacity is needed for these backups. Backup compression might hurt your storage capacity. Today, I am seeing more policies include full and differential backups along with transactional log backups. Differential backups are used to offset daily full backups. Typically people will use time increments as the basis for when backups should occur. It’s very common to see automated jobs that do weekly full and daily differentials to reduce storage capacity needed for backups.
How often does your data change? Is the rate of change very consistent or does it change depending on the week? Let’s assume this week it’s Tuesday and over 80% of your data pages have changed. You are not benefiting from taking daily differentials for the rest of the week. The opposite goes for data that doesn’t change that often. Maybe you can save a lot of space by doing less frequent full backups.
Leveraging smart differential backups could greatly reduce your storage footprint and potentially reduce the time it takes to recover.
In SQL Server 2017 you can see exactly how many pages changed since your last full backup. This could be leveraged to determine if you should take a full or differential backup. Backup solutions and backup vendors will be better for this.
select CAST(ROUND((modified_extent_page_count*100.0)/allocated_extent_page_count,2) as decimal(6,2)) AS 'DiffChangePct' ,modified_extent_page_count ,allocated_extent_page_count from sys.dm_db_file_space_usage GO
Smart Transactional Log Backups
The time your users are offline while you are recovering to the point of failure is critical. It could be the difference between keeping and losing customers. Point-in-time recovery is mandatory for a critical database. Transactional log backups have to be restored in order.
Recovery Point Objectives (RPO) drive how often you take transactional log backups. If you have a policy that says you can only lose ten minutes of data, you need transactional log backups every ten minutes. Is this really true if there were no changes? What if your RPO is driven by the amount of data loss and not the time of the loss? Either way, you can now control when transactional log backups occur based on the amount of data that has changed since the last transactional log backup.
SELECT name AS 'DatabaseName',dls.log_since_last_log_backup_mb, dls.log_truncation_holdup_reason, dls.active_vlf_count, dls.active_log_size_mb FROM sys.databases s CROSS APPLY sys.dm_db_log_stats(s.database_id) dls
This post was written by John Sterrett, CEO & Principal Consultant for Procure SQL. Sign up for our monthly newsletter to receive free tips. See below for some great related articles.
- The most Important Role of a SQL Server DBA
- I got 9 SQL Backup Problems but a Disk Ain’t One
- Does Automation Kill the DBA “Store”
The post SQL Server 2017: Making Backups Great Again! appeared first on SQL Server Consulting & Remote DBA Service.
Thanks, awesome article. While I admire Microsoft for adding additional features each year. But why on earth call it sql 2017… name it sql 2016 r2, r3, r4 whatever. What’s the point in getting a sql server certifications if they are going to come out with a new version every year.
Hi Robert,
Thank you for reading and providing your feedback, Robert. I love it when people share their thoughts with us.
Microsoft recently changed its model to have yearly major releases and frequent CUs to remove service packs. SQL Server 2017 already has a lot of updates included in CU1 which came out in less than a month since SQL Server 2017 was released.
It will be interesting to see how this release schedule trickles down changes to other areas like certification. On one hand, I think you have a very valid concern. On the other, I kind of like how this could force people to do a little bit more continuing education to stay certified in the latest editions of SQL Server 2017.
Regards,
John
The smart differential backup feature was long overdue – I remember calculating differential vs full backups to accommodate effective backup and recovery timeframes for VLDBs. For the tlog backup mechanism based on data change vs. time based backups I figure this will necessitate much closer collaboration between business owner and infrastructure/dba team on a per database level to develop a fine grained understanding of how much data loss will be permissible.