Disaster Recovery plan for SQL 2012 on Virtural Machines
Posted on 2014-10-23
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).