SQL backups/VMware/Transaction logs

Gammawolf

Gammawolf

Gammawolf

Associate
Joined
15 Nov 2007
Posts
2,365
Location
Sheffield, UK
Hi there, looking for some advise on the above topic. I'll briefly explain what's going on in the current environment:

Currently we've got Backup Exec 2010 that does a full back up of several SQL boxes every night. During the day it also backs up the transaction logs. This is Mon - Fri. The machine is virtual but the backups are treated as physical (ie with windows agents)

As a move to get this in line with everything else I want to get those SQL boxes backed up via vCenter as full VMs. Which isn't a problem in itself as we have BE 2014 doing this for everything else. My issue is does this mean I have to scrap the transaction logs during the day? I can't seem to think of a way to combine the two since the VM backup will leave the SQL database in an "on" state with renders laying the transaction log on top useless?

Any thoughts would be much appreciated!
 
I am virtualising my SQL server and I am going to use a combination.

SQL backup with transaction logs to backup server. Only two of the main dbs have transaction log backup, up to 15 mins. The rest are DB backups. These get put on to tape every night with arcserve. I could use arcserve sql agent but prefer to use sql maintenance jobs and just backup the sql backup files to tape using windows agent.

Full replication of VM including sql to DR side.

Then finally I have a uranium vmware backup that snapshots the vms once a week to our backup server and that also gets put on to tape backup once a week. I am still unsure whether I should add the new sql server to this backup. I am thinking it will be too large and a week old backup of sql is not that useful.
 
Don't try and consolidate them, use both methods.

I'd hope you have your SQL data on a separate disk, and not just on C: (In a VM, I can't think of a good reason not to). In which case exclude the SQL disk from your VMBackups and let Backup Exec carry on doing it's thing with the DB's.

I assume from you're post you're already aware, but If you don't use the agent or a maintenance plan and your DB's are in 'Full' recovery mode then the transaction logs will just expand to fill the disk.
 
Thanks for the input all, i had a feeling i wouldn't be able to fully consolidate it like that. Was hoping maybe there might be something clever that could be done but i guess that would be too nice!

I'll have to just use both methods and backup the VM, then backup the DB's / transaction logs via the agent. Unfortunately with BE 2014 they only support backing up the entire VM, the DB's aren't big so it's not going to take to much more time. I was approaching it more from the end of a DR situation and trying to eliminate that extra recovery step
 
Gammawolf said:
Hi there, looking for some advise on the above topic. I'll briefly explain what's going on in the current environment:

Currently we've got Backup Exec 2010 that does a full back up of several SQL boxes every night. During the day it also backs up the transaction logs. This is Mon - Fri. The machine is virtual but the backups are treated as physical (ie with windows agents)

As a move to get this in line with everything else I want to get those SQL boxes backed up via vCenter as full VMs. Which isn't a problem in itself as we have BE 2014 doing this for everything else. My issue is does this mean I have to scrap the transaction logs during the day? I can't seem to think of a way to combine the two since the VM backup will leave the SQL database in an "on" state with renders laying the transaction log on top useless?

Any thoughts would be much appreciated!
Click to expand...

The easiest way is to backup the SQL using SQL backup jobs.

First work out if you need to backup the transaction logs as frequent as you are and how much the data is worth. If your transaction log backups are so small you might be better of rolling them all up into an incremental and then a full daily/ weekly etc.

Back these files up to another separate folder within the VM.

Run your VM backup of the whole VM, since your files are on a separate drive it should be much easier to restore and you can dive into the VM backup and pull out whatever files.

If you use backup exec + vmware backups you need to create backup windows for both symantec and vmware to run backups so they do not cross
 
You must log in or register to reply here.
Back
Top Bottom