I'm seeking guidance and advice about how to set up replication in MSSQL to deal with the following criteria.
We have a central server hosted at a data center - this is where our web app is located.
We have various clients who have VPN connections (restricted) to our central server, so our web app can access their databases which our clients manage.
We are looking at providing a Documents database which will hold various small files and pictures - e.g. word docs, excel files, pdfs etc.
I want to design the system in such a way, that if they so wish, they can set up replication for this documents database so a 'local' copy is available in our data center, which means we don't have to go down the VPN to get the file.
I've read various articles such as these :
Which seem to cover most of what I need. However, from time to time, our VPN connections die and the publishing SQL servers will be unavailable - is this handled by the replication agents at all - i.e. by retrying a little later - or would the replication just error?
Also, can the subscriber be configured to PULL from multiple sources say every hour or two - I don't need the central server to be in sync instantly as its effectively just a cache for our web app.