Best practices for replication of SQL/DBISAM databases

[Dave-Departed]

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]

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]

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]

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

[Dave-Departed]

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]

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]

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]

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]

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.