how often to run Tablediff

Question

I wonder if it is safe to run the Tablediff tool on a database fairly often? Let's say every 15 or 30 minutes. Also, the Data in the DB is around 100 GB.

Answer 1

Although I think possibly you could find a better way to do this task, believe you would be fine using TableDiff utility. It is a fast operation which should not affects your performance:

What is said in MSDN: http://msdn.microsoft.com/en-us/library/ms162843(v=sql.100).aspx

Perform a fast comparison by only comparing row counts and schema.

Perform column-level comparisons.

So I reckon you'll be fine as long as set the time out connection and number of retries to something meaningful, so in case things go wrong, the process doesn't waste time.

You can set these parameters:

*-rc*number_of_retries

Number of times that the utility retries a failed operation

*-t*connection_timeouts

Sets the connection timeout period, in seconds, for connections to the source server and destination server.

Also you need to estimate the time it takes to finish the process, avoiding to call this process sooner than the previous run is finished obviously :)

Answer 2

tablediff is not designed to be used as a real-time synchronization tool (it seems this is your goal). If you really need real-time sync, there's database mirroring. The primary server sends its transaction logs to the secondary, which then replays the transaction logs - so the databases are synchronized Check out MSDN for more info Database Mirroring (SQL Server)