Comprehensive data protection for all workloads
Post Reply
DGilmour
Novice
Posts: 5
Liked: never
Joined: Feb 23, 2010 10:18 pm
Full Name: Doug Gilmour
Contact:

Replication and SQL re-indexing tasks

Post by DGilmour »

Greetings,

How will Veeam Replication handle this situation?

Weekly the database is re-indexed generating an 8 GB log file. Typically this is "thrown away" by performing a full backup right after the fact and then truncating the transaction log and shrinking the file.

What can I do so that I won't be moving (or trying to move) 8 GB of data over the WAN each week?

Thanks,
Doug
Gostev
Chief Product Officer
Posts: 31814
Liked: 7302 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: Replication and SQL re-indexing tasks

Post by Gostev »

Hello Doug, I would create separate virtual disk on your SQL VM, and configure SQL to use this disk for log files. Then, just exclude this disk from the replication.
DGilmour
Novice
Posts: 5
Liked: never
Joined: Feb 23, 2010 10:18 pm
Full Name: Doug Gilmour
Contact:

Re: Replication and SQL re-indexing tasks

Post by DGilmour »

How can I exclude the LDF files of a SQL database? Am I missing something?
Gostev
Chief Product Officer
Posts: 31814
Liked: 7302 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: Replication and SQL re-indexing tasks

Post by Gostev »

You cannot exclude individual files with image-level backups, but you can move some files to a separate virtual disk, and exclude whole virtual disk from the job using advanced replication job settings. Our customer often use this technique to exclude swap file from replication, for instance.
DGilmour
Novice
Posts: 5
Liked: never
Joined: Feb 23, 2010 10:18 pm
Full Name: Doug Gilmour
Contact:

Re: Replication and SQL re-indexing tasks

Post by DGilmour »

I understand what you are talking about.

How can SQL function on the far side if it doesn't have an LDF file to work with?

Is there any other way of working with Maintenance Plans that make major modifications to the database while at the same time replicating?
Gostev
Chief Product Officer
Posts: 31814
Liked: 7302 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: Replication and SQL re-indexing tasks

Post by Gostev »

Sorry, I thought you said this file is not needed on the other side, and you do not want to replicate it.
DGilmour
Novice
Posts: 5
Liked: never
Joined: Feb 23, 2010 10:18 pm
Full Name: Doug Gilmour
Contact:

Re: Replication and SQL re-indexing tasks

Post by DGilmour »

OK. I see the confusion.

In MS SQL, the LDF is required, however a database administrator will typically "throw out" transactional data that occurs after database maintenance once they get a good clean backup. The way they throw it out is by manually truncating the log file then shrinking the file back down to reasonable size. So in this case, the maintenance tasks balloon the ldf file to 8 GB in a matter of hours. But it's all useless data because the real changes have occurred in the MDF file. So while we don't care about the LDF file during maintenance, we do care about it during the week. We're looking for a 15 min RPO on this system.

Is anyone else dealing with this? Any recommendations? If I put the LDF in a separate VMDK file, can I have it not replicate over the weekend? What will happen to the Changed Block Tracking when the file inside the VMDK file starts at a couple of hundred MB, balloons to 8 GB then drops back down to 100 MB..? If the snapshot happens at the two MB points in time, will it move 8 GB?

Thanks.
Gostev
Chief Product Officer
Posts: 31814
Liked: 7302 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: Replication and SQL re-indexing tasks

Post by Gostev »

Doug, I don't think there's any way to do this with our current version.
tsightler
VP, Product Management
Posts: 6035
Liked: 2860 times
Joined: Jun 05, 2009 12:57 pm
Full Name: Tom Sightler
Contact:

Re: Replication and SQL re-indexing tasks

Post by tsightler »

In the end, it won't matter if you truncate the file our not as far as Veeam's concerned. Veeam replicates virtual disks, not files. If I write an 8GB file, and then delete it, those 8GB of blocks were changed on the disk, so to make the disk on the remote side identical, they still have to be transferred. You could potentially use sdelete to zero out the space of the delete file, or the free space on the disk, which should minimize the amount of data that goes across the wire, but I'm not sure if that's realistic in your situation.

I'm luck enough to have WAN acceleration devices on my WAN links. This indirectly solves the problem because database blocks are generally highly compressible and generally have lots of opportunity for DRE (data redundancy elimination, basically, remote caching of repeating patterns). Replication of our SQL servers are hitting compression/DRE ratio's of 90+% with our WAN acceleration boxes in regards to the amount of data that actually crosses the wire, so even though our maintenance procedures create 10-15GB of logs, only about 1-2GB has to actually cross the WAN.

Baring that, I don't really see a way to do what you want.
Post Reply

Who is online

Users browsing this forum: Google [Bot], Semrush [Bot] and 35 guests