Can we use the WatchFile table to speed up our backups?

[cmarkovits]

Hello all,

We are running multiple Symantec EV for MS Exchange 9.0 SP1 Servers and are running in to a problem where we aren't able to complete our backups within a regularly scheduled maintenance window.  There are simply just too many file objects to enumerate in a timely fashion (and we don't want to rely purely just on snapshots - belts and suspenders).  As such, we have been looking for alternative solutions.  One possible solution that we have come up with is to use the WatchFile table.

We noticed EV uses the WatchFile table to keep track of what has been backed up, and what hasn't.  As such, it stands to reason that we should be able to use that table to target just what needs to be backed and skip what doesn't, Has anyone done this, and if so, do you have a SQL script that you could share that would dump the fully qualified path of the files that need to be backed up?

On a similarly related note, does anyone know if the WatchFile table tracks modifications and or delete's to archived data as well?  We would want to catch mod's / delete's as well as creations obviously.

Thanks!

Corey

[MarkBarefoot]

Are you using collections? These really help with backup time windows as you are backing up fewer, larger files, rather than 10000's of individual ones.

The WatchFile table is only looking at items that are awaiting backup, nothing to do with mod's or delete's etc. The JournalDelete table will hold a list of deletions from storage (one in each VS DB), and the JournalArchive for additions (until the item has been indexed and backedup and after a preset time)

[JesusWept3]

Theres actually quite a bit of debate as to whether to use collections or to not use collections, as having some discussions with some good netbackup people show that the throughput for a ten mb collection gives miniscule improvement.

The issue is though, there are too many small issues with collections and some bigger issues as well which is why at the moment i wouldn't recommend it, what i would recommend instead is look at flash backups and use of the ignoreArchiveBitTrigger.txt file

[cmarkovits]

GPKGuy - thanks for your reply.  So we used Collections initially for about the first year, after which we had several customers end up with some corrupt Indexes.  At that point we found out (the hard way) that EV had to open every CAB file that contained that a customer's archive data in it in order to rebuild the customers index.  Becasue the disk subsytem is not Tier 1 storage, it took more than 24 hours per customer at which point Collections lost it's "cool" factor and we decided to ditch collections from that point on.  Our backup system itself can handle the # of tiny files, it's the disk subsystem that services EV that is the bottleneck.

So good to know that the WatchFile table tracks additions, and the JournalDelete tracks the deletes (wasn't aware of that one).  I presume that the JournalDelete table is processed as soon as it comes in (would make sense)?  Any chance you know of a SQL Query to dump the paths and files of the items awaiting backup in the WatchFile?

Thanks GPKGuy!!!

[cmarkovits]

JesusWept2 - yeah, we steered away from collections as well (See my reply to GPKGuy above).  We currently do backups to tape as well as with multiple snapshots a day (like I said above - belts and suspenders).  The concern we have with just relying on just the Snapshot backups is that the snapshot's resides on the same storage system as the primary storage, so if we were to loose that storage system then we would be basically out of luck.  We do have a copy replucated offsite, but for the amount of data we are talking about it would not be feasible to be able to copy that back in a timely fashion.

So any chance you would have a SQL script to dump the list of files (and their full path) awaiting backup in the WatchFile table?  Thanks!!!

Corey

[JesusWept3 · Accepted solution]

I knocked this script up for you , its a bit over the top imo
(oh and as a side note, you do flash backup, then back that snapshot up to tape and delete it from disk)

SELECT (PE.PartitionRootPath + '\' + WF.ItemName)
FROM EnterpriseVaultDirectory.dbo.Root R,
     EnterpriseVaultDirectory.dbo.Archive A,
     EnterpriseVaultDirectory.dbo.VaultStoreEntry VSE,
     EnterpriseVaultDirectory.dbo.PartitionEntry PE,
     EVstore.dbo.ArchivePoint AP,
     EVstore.dbo.JournalArchive JA,
     EVstore.dbo.WatchFile WF
WHERE WF.ArchiveTransactionID = JA.TransactionID
  AND JA.ArchivePointIdentity = AP.ArchivePointIdentity
  AND AP.ArchivePointId = R.VaultEntryId
  AND R.RootIdentity = A.RootIdentity
  AND A.VaultStoreEntryId = VSE.VaultStoreEntryId
  AND WF.IdPartition = PE.IdPartition
  AND PE.VaultStoreEntryId = VSE.VaultStoreEntryId
  AND JA.BackupComplete = 0

[cmarkovits]

Thanks for the quick turn around!  So looks like I just need to replace the "EVstore" with the name with our database, which I just did.  Unfortunately our WatchFile table is actually empty (IE everything is backed up), so I'll try to run this script later tonight.  Presuming it works, which I expect it will, then this should be very helpful!  Thank you!

Corey

[cmarkovits]

JesusWept2 - I just wanted to follow up with you and let you know that this worked perfectly!  This was very much apprecaited.  I'm going to try and see if I can take this figure out how to write a similar SQL query for the JournalDelete table (presuming that the entries are held until a backup is actually run).  Thanks again!