SQL Server, RMO, and slow WAN links

If you’ve ever had to replicate data over a slow link you know it can be quite a pain.  The environment in which I work has several hundreds subscrivers pulling data over various DSL circuits and 3G wireless circuits.  Sometimes connectivity can be spotty.  During a reinitialization we often end up with multiple restarts and an unusable database for long periods of time.  Obviously this doesn’t please the business side of the house.
Part of our strategy to relieve this problem is to break our two publications into 4, remove some unneeded tables from replication, and trim out old or unused data.  Still we had snapshot sizes around 50MB for some tables.  In our current environment, SQL Server 2000, compression was not enabled and subscribers point to the default snapshot location.  Microsoft SQL Server engineers (one in particular, who I might say is awesome) provided us with a great solution. 
We will be upgrading both our backend SQL instances to 2008 Enterprise and our store level SQL instances to 2008 Express.  With SQL Server 2008 Express we can use RMO to control our replication syncronization and custoize how it happens.  To ensure that the store (or "local database") doesn’t get wiped over and over as re-initialization loops we use a .NET application to execute Robocopy and move the snapshot files from the server to the store’s local hard drive.  Once the copy has completed with no errors we re-initialize the subscription andthen start the syncronization.
If you are using SQL Server replication over slow links this could be a great solution for you.  Please let me know if you’d like more information or sample code.
BTW, I haven’t forgotten about the HTA information, but I’ve been wicked busy!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s