Let's get it straight. You want to take care of your SQL DB like a pro. But where to begin? Properly understanding and answering this question is very important to guide your way to a perfect DBA.
First, we need to understand what are the basic needs of our Database? For answering this question, we need to think SQL Server like ourselves. Maslow has defined a hierarchy of human needs. Likewise, we need to define the hierarchy of our SQL DB needs.
The very first thing your SQL Database needs is itself. So,
you need to take care of this first before thinking about anything else. We
call these Backups. You need to take backups of your SQL server on a regular
basis so that when you hit a disaster, you can start from the last backup.
Before ensuring this, you cannot and should not move on to the next need of
your database. It’s like a human’s basic physiological need – food, shelter,
clothe, education, and medication.
So, how you can ensure the proper backup of your database? SQL Server has a nice GUI for that you can use it in SSMS. You might want to schedule your backup in the SQL Server maintenance plan. Let’s say, you want to schedule a full database backup every week. So, we should create a maintenance plan in SSMS which will take a backup every week. You probably think taking backup every week will consume a lot of your space if you have a big size database. There’s a work-around for that, in the maintenance plan, you can specify when this backup set will expire. If your backup plan if for one week, you can set this expire time to 14 days. So, every week there will be new backup taken and the old backup which is 2 weeks old will be expired. So, your space issue is lessened.
Now how about if you need the database backup in the middle of
the week? If you take a backup every Friday night, and you hit a disaster on
Tuesday, you can restore the database on Friday. But what about the transactions
occurred between Saturday and Tuesday? Your business stakeholders might not
accept that. So, what can you do about it? You have 2 options here. Taking the
backup more frequently is one of them which I do not suggest. Why? Because
taking backup is expensive and if you choose to backup in between working days,
the database might be slow and the end-users won’t be happy about it. So, we
jump to option 2, which we call taking differential backups. Wait a minute.
What is a differential backup and how can we perform this?
Differential backups are incremental backups from your last taken full backup. Let’s say, you have taken a 10GB full backup on Friday. On Saturday night, the database grew and now your DB size is 11 GB. If you take a differential backup on Saturday night, it will make the changes that occurred on Saturday and take the new 1 GB backup. If you schedule the backup on everyday end until the next Friday. We will get one differential 1 GB backup every night. So, if your DB goes down on Tuesday, you no have one full backup of last Friday and 3 differential backups on Saturday, Sunday, and Monday. You can restore these full backups and 3 differential backups and go back to the state of Monday. So, you lose one day worth of data. What if your management is not satisfied with this as well? Here come transactional log backups. Wait again, what is transactional log backups and how can we perform this?
Transactional log backups are log backups after the
differential backups. Let’s say we take log backups after 1 hour. So, on
Tuesday at 12 PM, we have 12 transactional log backups from 12AM to 12PM on Tuesday.
Let’s say, our DB goes down at 10:30 AM, So now we have 1 full backup, 3
differential backups and 10 transactional log backups with which you can restore
back your data to Tuesday 10AM state. So, you lose 30 minutes’ worth of data. What
if your management is not satisfied with this loss as well? Pretty straightforward,
we just need to take transactional log backups more frequently, like after each
15 minutes or 10 minutes. It all depends on your business rule.
Hope it helps! Next, we will discuss the second
hierarchy need of your database. Stay tuned. Can you guess what the second important
need for DB after Backup?
Comments
Post a Comment