Posts

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.

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 post SQL Server 2017: Making Backups Great Again! appeared first on SQL Server Consulting & Remote DBA Service.

/3 Comments/by

The Most Important Role of a SQL Server DBA

The most important task a DBA can perform is Recovery.

The work of a SQL Server DBA is ever-changing; data is fluid, and accordingly, so is the manner in which data is treated.  Thankfully there are a vast number of ways to keep up with the changes a DBA faces in his/her career.  There are various blogs, hashtags, local PASS chapter meetings, SQL Saturdays, and a host of people online willing and able to help.

I love a challenge, so this month’s blog invitation, T-SQL Tuesday #85 – Backup and Recovery hosted by Kenneth Fisher (b|t), is right up my alley, as this is the first thing I am learning as a new DBA!

 tsql2sday150x150

In a recent twitter poll, John Sterrett asked which is a DBA’s favorite job.

Favorite job as a DBA

Favorite job as a DBA

It is clear by the answer that Backups and Restore is not in the top percentage of favorites.  Why is that?  Well, because it is the simplest job that can be performed, and probably the least “sexy” of all the things a DBA does.  It does not require any special tools or shiny new toys,  Backup and Restore is the most basic of the basics when learning to be a DBA.

Backups are essential to a successful Restore.  Imagine you were asked to recover data that was never backed up…ever… as in never, ever, NEVER.  That feeling you have crawling up your spine right now, that is fear, anxiety, and panic.  If you don’t care for that feeling, you need to learn more about BACKUPS and RESTORES.

If it is so fundamental, why is recovery the most important job of a DBA?  Very simply, backups are the foundation of a disaster recovery plan; however, they are useless if you cannot recover with minimal data loss.

The three key things I have learned while studying backups and restores are:

  1. If you have no restore model, your database and any and all backups are WORTHLESS.
  2. If you have no automation process in place, you should start planning a new career.
  3. If #1 and #2 are ignored, know where to find a good lawyer.

If you do not understand why backups are so important, think about dropping your phone in water… and then it being eaten by an alligator.  Do you have your contacts, photos, passwords, banking information, etc., backed up to the cloud?  No?  My friend, now you understand why backups are so very important!  Don’t be that person who stands crying in a swamp  because an alligator is digesting your data!

Help! A gator ate my data!

Help! A gator ate my data!

DBAs should know by heart the various kinds of backups, how they are used, exactly what they do, and when they should be performed.  The good DBA knows that installing an automated process to perform backups is the key to a long and successful career.  Also, testing, testing, testing is KEY.  Backups and Restores should be the first things taught to a junior DBA, accidental DBA, or a DBA in training.

Hindsight is 20/20, so the saying goes.  Perhaps that is the reason so many DBAs skip learning backups and restores.  We don’t always know there is a need for something until there is a dire need for something.  Perhaps this is one reason all my beginning DBA books cover all the “fun stuff” first and throw in the backups and restores somewhere near the end of the book.  Case in point, I provide you with two examples of critical recovery failures.

Well I have Found the Quickest Way to Get Sacked

Childcare App Wipes Users’ Data

Backups and Recovery are so very important, that is why I am learning this first as a new DBA.  I am studying a great book by John Sterrett (b/t)  and Tim Radney (b/t) titled SQL Server 2014 Backup and Recovery.  I strongly suggest everyone get a copy and read this book.

Please come back, this is the 1st in a series of blog posts regarding Backups and Restores.  See you next time when we begin to discuss types of Backups and Restores in-depth!

Thank you for reading!

/7 Comments/by