Solved

Disaster Recovery plan for SQL 2012 on Virtural Machines

Posted on 2014-10-23
3
178 Views
Last Modified: 2014-10-29
Hi Experts,

Here is what I have stepped into. Beginner DBA, trying to get my head wrapped around backups and restores in SQL 2012.

We have a couple of production databases that range from 300MB - 500MB. Probably a total of about 50-100 transactions during a typical business day. Transactions are small for the most part. DR calls for Point-in-Time recovery with no more than 30 minutes of lost time with little or no loss of availability (I do understand difference between high availability and recovery). Here is what I have been presented with.

Two VM's both running SQL 2012. One VM is in Production, the other is a backup. Each VM's is on different host. I hope I am not leaving anything out.

Okay, here goes.
Backup and recovery plan is as follows for the highly important databases (4 databases out of about 30).
Full backups performed every 24 hours.
Differential backups every 4 hours (with diff backups older than 4 hours being deleted)
Trans log backups every 30 minutes (with translogs older than 4 hours being deleted)

I believe this plan would allow me a Point-In-Time restore with maximum loss of 30 minutes of transactions (which is acceptable). The other databases, are Simple recovery mode as they are not as important.

Backups will be stored on a another server and also copied to an offsite location.

Question is really about this. Am I thinking correctly on the backup/restore plan. Secondly, regarding the VM, should I still separate my tempdb, data files and log files onto separate virtual drives on the VM?

Thanks for looking. I must say, the folks here (at all levels) have really helped me in the past. I have gained a ton of information here from other questions. I hope this question is relatively clear.


**Side note: Additionally, we were thinking of copying the backup files to the other VM and restoring them automatically. That way if our VM crashes, we can just bring the other inline and move forward with minimal downtime (although, this part is not a priority right now...but could be soon, just an idea).
0
Comment
Question by:Erick W
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 25

Accepted Solution

by:
Mohammed Khawaja earned 500 total points
ID: 40400837
I recommend changing DBs from simple to full recovery as you will be able to roll forward transactions in an event of a failure.  Your backup methodology seems good, however, restoring to a different host may seem like a good idea but it all depends on the applications.  Is the application flexible enough to allow changing SQL server?  In any event, recovery will require some downtime and if we are talking about 500 MB database, it should be minimal.  Are you using SQL maintenance jobs or are you using a third-party?  Your recovery time may depend on how good your SQL host server backups are?  Let's say the OS went corrupt and your DB goes down, you need to worry more than just the SQL DB restore.
0
 

Author Comment

by:Erick W
ID: 40402295
Greetings Mohammed.
Excellent questions! Yes, I'm already running full recovery mode. Thanks for the thought process on the application flexibility, I'll check on that. I'll be using SQL maintenance jobs to perform the operations.
Good point about the OS going corrupt. If the OS issue occurs there will certainly be more to worry about.

Thanks again for your input.
0
 

Expert Comment

by:jason97m
ID: 40411643
You might also think about distributing your databases geographically, that is if you have the resources.
0

Featured Post

Threat Trends for MSPs to Watch

See the findings.
Despite its humble beginnings, phishing has come a long way since those first crudely constructed emails. Today, phishing sites can appear and disappear in the length of a coffee break, and it takes more than a little know-how to keep your clients secure.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is an issue that we can get adding / removing permissions in the vCSA 6.0. We can also have issues searching for users / groups in the AD (using your identify sources). This is how one of the ways to handle this issues and fix it.
Giving access to ESXi shell console is always an issue for IT departments to other Teams, or Projects. We need to find a way so that teams can use ESXTOP for their POCs, or tests without giving them the access to ESXi host shell console with a root …
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…
In this video tutorial I show you the main steps to install and configure  a VMware ESXi6.0 server. The video has my comments as text on the screen and you can pause anytime when needed. Hope this will be helpful. Verify that your hardware and BIO…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question