Transactional Replication over WAN Tips

Your rating: None Average: 5 (1 vote)

If you have to resnapshot a large database, have your remote sites look to a local share (can manually change them whenever via Repl Monitor), pause your replication agent (SQL Agent job) on the subscriber(s) to prevent all the error messages, re-snapshot, zip up the file (I recommend 7-zip), copy the file over with robocopy or FTP to external site if bandwidth better over internet, unzip the files, and startup your replication agent job.
Tune your network TCPIP settings on both ends with tips from my post http://www.sqlwebpedia.com/content/tcp-optimization-windows.
Create a new WAN Replication Profile in Repl Monitor. Right click a subscriber and select Agent Profile, copy the default one and modify it (give it a new name with WAN in name so you remember), uncheck the "show only parameters used in this profile" and alter these settings (all well documented in BOL):
CommitBatchSize - 1000 (approx max number of batches - it will send if less than that)
CommitBatchThreshhold - 2500 (approx max total commands for all batches - definately test, this is just what I use)
HistoryVerboseLevel - 1
MaxBCPThreads - 4 (Nothing to do with ongoing operations, just when you create a snapshot it won't be single threaded.)
TransactionsPerHistory - 1000 (Just limits amount of updates you get in Repl Monitor, tweak to your comfort level)
QueryTimeout - 4000
PacketSize - 12288 (This is on a good network. Adjust in 4096 increments until SQL Agent Job doesn't crash or throw an error.)