Best practice for replication situation

Question

I have just lost pretty much a day and a half trying to get pull replication going for an off site server. After experiencing the pain that was this experience I am now thinking it shouldn't be this hard so maybe I am doing it wrong. I never did get it to work, I had to go to push replication.

Here is the situation. We have a virtual server being hosted off site that will a database for a public web application. We want to push all the data from a few of our tables in our internal database to this off site location and it has to be done almost instantaneously so that the web information is current. We don't want to set up VPN because if that machine gets compromised we don't want that vulnerability.

If sql server replication is not the best method how would you do it?

FYI: Publisher = SQL Server 2005 & Subscriber = SQL Server 2008 Web Edition

Answer 1

Well, if you want it to be fast and easy to manage, one solution is to set a merge replication topology, having your main server as a publisher, and the hosted server as a suscriber. Replication can then be done through http, thus without vpn.

Be careful: web replication is not as straighforward as opening a page in your browser! You can find some interesting infos here

I do not have SQL Studio on this machine, but I guess you can parameter your subscription in such a way that only downloads will be replicated to the suscriber, while uploads will be ignored.

By running the replication script (it's a BAT file) from the suscriber every minute (through any scheduled tasks manager), you can have a quasi-instantaenous update of your suscriber's tables.