Comprehensive data protection for all workloads
Post Reply
Dave-Departed
Expert
Posts: 167
Liked: 6 times
Joined: Feb 29, 2012 3:11 pm
Contact:

Best practices for replication of SQL/DBISAM databases

Post by Dave-Departed » Nov 19, 2014 3:56 pm

Hi All,

Just upgraded to v8 and liking it so far!

Up until now we've been using Backup Copy Jobs to suit our offsite DR needs, and running them overnight, as using VMware snapshots during production hours causes our 2 critical systems (based on SQL 2005 and a database called DBISAM) to freeze for a few minutes per backup session, and as they are sensitive to disconnections, perceived or otherwise, the business found it unacceptable to perform backups during business hours (previously we had been given an RPO/RTO of 24hrs by the business, so overnight backups were considered OK - Even though I argued against this from the start).

Fast forward to last week, and we had an issue where the shared storage containing our critical systems failed and wouldn't come back up (as it turns out, it was a BIOS config issue, and was easily resolved), but the potential was that, if that storage had been dead entirely, then the business would've lost 24hrs-worth of data on both of our critical systems, and the powers that be weren't at all happy about that (even though it was they who had given the original RPO/RTO figure of 24hrs, D'OH!), so I was instructed to see what we could do to further decrease the RPO/RTO without additional cost (of course).

We have a HP P4000, so we can leverage SAN snapshots within Veeam, so I decided we should try replication to our DR site, using Storage snapshots within Veeam, as opposed to VMware snapshots.

I've been testing it today, and even using Storage snapshots, this can cause the VMs running our critical systems to freeze for minutes at a time, which causes DBISAM disconnection/SQL error messages in the clients of the respective systems.

Does anyone have any best practices for replicating sensitive SQL (or other database) VMs on a reasonably frequent schedule, please? It'd be very nice if we could get it down to a 1hr RPO, but we can't have any freezing of the VMs...

Thanks in advance

Dave

Gostev
SVP, Product Management
Posts: 24643
Liked: 3470 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: Best practices for replication of SQL/DBISAM databases

Post by Gostev » Nov 19, 2014 7:42 pm 1 person likes this post

Hi, Dave. Have you considered using SQL Server transaction log backups (new v8 feature)? This will allow you to achieve RPO as low as 5 minutes, and these kind of backups do not use VM snapshots, so they will not impact your SQL servers at all. Thanks!

Dave-Departed
Expert
Posts: 167
Liked: 6 times
Joined: Feb 29, 2012 3:11 pm
Contact:

Re: Best practices for replication of SQL/DBISAM databases

Post by Dave-Departed » Nov 20, 2014 8:41 am

Thanks for that, Anton, sounds pretty intriguing! Do you have any further info at all please?

Also, long shot I know, but will that feature work with a DBISAM database? Or is there a way of backing up a DBISAM database without using snapshots? Our other critical system uses DBISAM, and is very, very prone to disconnecting clients if the server "freezes" for any more than about 1 min at a time.

Thanks

Dave

veremin
Product Manager
Posts: 16789
Liked: 1410 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Best practices for replication of SQL/DBISAM databases

Post by veremin » Nov 20, 2014 9:27 am 1 person likes this post

See the corresponding section of our Help Center for more information regarding SQL logs backup. Thanks.

Dave-Departed
Expert
Posts: 167
Liked: 6 times
Joined: Feb 29, 2012 3:11 pm
Contact:

Re: Best practices for replication of SQL/DBISAM databases

Post by Dave-Departed » Nov 20, 2014 9:35 am

Thanks Vladimir. Is this something that would only happen on a brand new backup in v8? Or a fresh v8 install, perhaps? I've upgraded our Veeam install from v7 to v8, and we have a pre-existing backup job for the SQL server, but I can't see any child jobs, as mentioned in your link above.

If anyone could tell me what I need to do in order to configure this feature, that would be great!

Thanks

Dave

Dave-Departed
Expert
Posts: 167
Liked: 6 times
Joined: Feb 29, 2012 3:11 pm
Contact:

Re: Best practices for replication of SQL/DBISAM databases

Post by Dave-Departed » Nov 20, 2014 9:53 am

Actually, would this be located under the Application-Aware Processing Options > SQL tab in the properties of the job?

If so, my current job settings are:

General tab - Transaction logs > Process transaction logs with this job (recommended)
SQL tab > Truncate logs (prevents logs from growing forever)

I'm guessing I should change this to Backup logs periodically? But how does this then deal with log truncation? Are the logs still truncated when the parent job is run (currently once daily for us)?

And secondly, if we use the SQL Transaction log backup feature, and then we want to use an offsite Replication job that points to the latest VM backup data (instead of to the production VM, so that we don't need to freeze it for snapshots), say, every hour during production hours, will the latest SQL information be included in that?

Thanks

Dave

Dave-Departed
Expert
Posts: 167
Liked: 6 times
Joined: Feb 29, 2012 3:11 pm
Contact:

Re: Best practices for replication of SQL/DBISAM databases

Post by Dave-Departed » Nov 20, 2014 11:42 am

Ok, so I have the answers to my above questions from support (thanks for that, Alexander!)...

But I still want the latest information to be available offsite ASAP. Seeing as we can't backup/replicate the production VM during working hours, and the SQL log backups will be stored in our local repository, can I just use some sort of file copy job to copy those SQL log backup files from the local VBR repository to the remote VBR repository perhaps?

veremin
Product Manager
Posts: 16789
Liked: 1410 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Best practices for replication of SQL/DBISAM databases

Post by veremin » Nov 20, 2014 12:02 pm

Actually, would this be located under the Application-Aware Processing Options > SQL tab in the properties of the job?
Correct.
I'm guessing I should change this to Backup logs periodically?
Correct.
Are the logs still truncated when the parent job is run (currently once daily for us)?
Nope, once the logs are backuped, they are automatically truncated by SQL.
And secondly, if we use the SQL Transaction log backup feature, and then we want to use an offsite Replication job that points to the latest VM backup data (instead of to the production VM, so that we don't need to freeze it for snapshots), say, every hour during production hours, will the latest SQL information be included in that?
The transaction logs are stored in backup repository and not copied by replication job.

Thanks.

foggy
Veeam Software
Posts: 18158
Liked: 1542 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Best practices for replication of SQL/DBISAM databases

Post by foggy » Nov 21, 2014 10:01 am

Dave-Departed wrote:But I still want the latest information to be available offsite ASAP. Seeing as we can't backup/replicate the production VM during working hours, and the SQL log backups will be stored in our local repository, can I just use some sort of file copy job to copy those SQL log backup files from the local VBR repository to the remote VBR repository perhaps?
Only if you copy logs backup along with it's corresponding parent VM backup it relies on and import this backup into Veeam B&R, you will be able then to replay them on restore.

Post Reply

Who is online

Users browsing this forum: Bing [Bot] and 21 guests