Skip to main content

6 Letters for SQL Disaster Emergencies : RPO and RTO

 


As I discussed previously, the most important thing for any database is the database itself. To secure the data properly, you need to backup it regularly with a proper backup plan.

Let’s say you hit a disaster and the last proper backup is 10 hours old. So, you are going to lose 10 hours’ worth of data even if you can restore it instantly. How much data you lose depends on which 10 hours you are dealing, your most busy business time, or a relatively less busy hour? This is what we call the Recovery Point Objective.

Now come to restoring the 10-hour old backup that you have in your hand. How much time you need to get to the last proper backup? How much time it takes for restore? How much time for changing the App connection string and other configurations? In short, how long the end-users are going to wait before they can access the database again? This is what we call Recovery Time Objective.

RPO and RTO are two very important topics for any Database Management System. Setting RPO and RTO poorly can lead to devastating business situations. Today we will try to understand RPO and RTO by answering some questions.

  • What are RPO and RTO?
  • How can we set RPO and RTO for our business?
  • What are the technologies and tools involved in achieving the RPO and RTO we have set?
  • How much cost is involved here?
  • How can we automate our backups and more importantly, restores properly?

Hang on tight, we are going to cover it one by one.

What are RPO and RTO?

RPO

The recovery point objective is how much worth of data you lose if you hit a disaster? We often think it's measured in Megabytes. No. It's measured in time. If your last good backup is 10 hours old, your RPO is 10 hours. You can lose any amount of data based on when the disaster takes place.

RTO

Recovery time objective means how much time you need to restore the last good backup and make you database back online. It's measured in time.

How can we set RPO and RTO for our business?

RPO

You need to ask yourself how much worth of data your business is comfortable to lose in case of disaster? It’s really the task to discuss with the business stakeholders, but you need to set it yourself first before discussing it with them. I am warning you; your business stakeholders are going to say that they don't want to lose any data at all. Hold on tight, I am going to show you how you can manage your business stakeholders so that they can agree to lose some data(which means disagreeing to spend more) or agree to spend more (which means disagree to lose data), more on that in the later section.

RTO

You can calculate RTO by answering and summing down the following:

  • How much time to get access to the last good backup?
  • How much time to move the backup to the destination server?
  • How much time needed for restoring the database?
  • How much time to make app configuration changes?

Sum all these required times and allow some buffer time, since you never know what more challenges you are going to face while making the database back online. Its huge stress, trust me since the end users are waiting for you.

What are the tools and technologies involved in achieving the RPO and RTO we have set?

For optimal RPO you can use any of the following or a combination of the following:

If your business data is highly valuable and you need very low RPO,

  • SQL Server Always On high availability.
  • Asynchronous database mirroring
  • Go to Cloud, like Azure or Amazon AWS

If you are comfortable with losing some data,

  • Find your current RPO with sp_blitzbackup, an open source dba script
  •  Follow my backup plan article for planning a proper backup strategy Backup SQL Database like Batman
  •  Maintain backup maintenance script, you can try ola hallengren's automate scripts
  • Test your backup script, and I am damn serious about it.
  •  Implement proper notification whenever a backup fails. You can perform it in SSMS maintenance plan. This can notify the responsible persons when a backup fails.

How much cost is involved?

When you try to have near-zero RPO and RTO, it’s going to cost you more. Always on can be enabled with synchronous mirroring. Whenever the primary node fails, you instantly move to the secondary node. SQL Server 2019 have launched with 5 Always on Synchronized nodes, which is cool, since it had 3 nodes in 2017. In always On, you need to configure your App connection string to Always On listener, see how easy it is to setup connectionstring on Availibility Group

My point is, show your management how much the tools are going to cost if you want to achieve a good RPO and RTO, so that they can decide either to spend more money or lose more data.

Automate Backup and Restores

  • First use sp_blitzbackup to see what are you currently at
  • Use ola hallengren's maintenance script to maintain backups and restores
  • Use dbatools.io to perform the same things if you are comfortable with PowerShell.

Labels

Labels:
Location: Dhaka, Bangladesh

Comments

Post a Comment

Most Loved Posts

How to deal with Slow SQL Server due to Autogrowth issue

  Why you should not stick to SQL Server’s default Initial file size and autogrowth We hear a lot of these statements : My SQL Server is running slow My Production DB was fine when we started, But it is staggeringly slow now My Business end users are frustrated to wait too long Well, there are lots of reasons why your SQL Server might be slow. Setting the Autogrowth option to default is definitely one of the vital ones which we seem to ignore most of the time. Slow SQL Server and Tortoise SQL Server provides you with some default settings for autogrowth when you install it for the first time. These default cases are defined with increment by 8MB or by 10%. You need to change it to suit your own needs. For Small application, this default value might work but as soon as your system grows, you feel the impact of it more often. What Happens SQL Server Files needs more space SQL Server Requests the Server PC for more space The Server PC takes the request and asks the SQL request...
Post a Comment
Read more

Threadpool - A deadly poison wait for SQL Server (The What, When and How)

Introduction  Threadpool is a  poison  wait. Yes, I mean it. Its poison for SQL Server, its poison for the Business and of course, the end-users! The most devastating thing about threadpool is you hardly recognize it because it comes in disguise, meaning you see no memory or cpu pressure in the system, yet you cannot run any query, it seems like your SQL Server is frozen solid. That scary, isn't it?
2 comments
Read more

How to generate C# Class from SQL Server Table

C# Class from SQL Database Table There are multiple ways you can generate a C# class from your Database Table. We will be covering the following topics in today’s article. Generate Class with foreign key relation Generate Class with only entities Generate Class with foreign key relations For this we can simply use Entity Frameworks EDMX update feature which will generate our C# class from Database Tables. The output from EDMX will contain foreign key relations which we can see from the virtual interfaces like this -  public partial class AssetItem { [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")] public AssetItem() { this .AssetItemDepreciations = new HashSet<AssetItemDepreciation>(); this .AssetTaxMappings = new HashSet<AssetTaxMapping>(); this .AssetVatMappings = new HashSet<AssetVatMapping>(); ...
Post a Comment
Read more

SQL Schema Compare with Visual Studio (A complete Guide)

Introduction When you're working on your Dev Database, an urgent issue comes along, and you instantly solve it by changing Scheme in the Staging Database or Production Database :3, few more these type of patching and you're completely out of sync! A lot of paid alternatives are there like SQL Data Compare by RedGate, but my first choice is Visual Studio's SQL Data Tools. In the following article, I tried to image-describe the steps for SQL Data Tool. Like I said before, there are lots of handly DBAtools out there to compare Schema between two DB Sources. I would like to discuss how you can compare two SQL Server DB with Visual Studio. Make sure you have SQL Server Data tools checked while installing Visual Studio.
Post a Comment
Read more

How to encrypt an entire excel file using AES-128 encryption

What Is AES? AES stands for Advanced Encryption Standard. It is a symmetric block cipher that is used by the U.S. government to protect classified information. AES is used worldwide to protect classified data around the world. AES is essential in cybersecurity, electronic data protection, and computer security. Variations of AES AES is used in three block cipher versions namely AES 128, AES 192, AES 256. AES 128 uses 128-bit key length to encrypt and decrypt block messages. This is a symmetric secret key which means it uses same secret key for encrypting and decrypting message blocks. AES Encryption of Excel File Today we will AES 128 to encrypt an Excel File. The excel file looks like the following image. To encrypt this excel file, we will use C#. First, we need to create a console project. Let's name the project ExcelEncryption. First, we try to understand what we need to achieve. We need a KEY and Initialization Vector (IV) pair for AES 128 encryption. Let’s say, th...
Post a Comment
Read more