Replication and SQL re-indexing tasks

[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]

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]

How can I exclude the LDF files of a SQL database? Am I missing something?

[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]

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]

Sorry, I thought you said this file is not needed on the other side, and you do not want to replicate it.

[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]

Doug, I don't think there's any way to do this with our current version.

[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.