Best practice bring MS SQL database to remote sites

Question

Currently we use terminalservers to enable remote users to work with a central LOB-application. The terminalserver is on the same LAN as the MS SQL-server.

Now my question is: what is the preferred way to replicate/mirror the database to the remote sites, so users don't need a terminalserver and can work in their local LAN.

These are my concerns:

1. SQL over internet is often too slow. It would however be my preferred solution because of simplicity (if internet doesn't work this will only temporary effect clients, no worry about shipping log files, etc)
2. Mirror will be removed in a future version of Microsoft SQL Server. Afaik, it leaves the mirror constantly in restore-mode so the LOB-application can't actually use, correct?
3. Always On availability groups look very complicated to me but they do support Active Secondary Replicas which sounds like the ability that every remote site can use it's local replica for reading and the central-db for writing. However, I wonder, does this work with all LOB-applications? And does this require a very fast and stable internet-connection? It must not fail with every short internet/VPN hick-up.

More info:

• I'm hoping for a general solution that works with 'all' Microsoft SQL-based applications (SQL over internet does when internet is fast and queries are limited).
• Data needs to be read/write on remote sites. It's okay if data can't be modified in case of a broken internet/VPN connection. I was hoping that mirroring could do this. That modifications are committed to the principal-db but all reads are directly read from the mirror (transparant to the application / without using 2 different db-servers). A bit like domain-controllers work. This isn't how mirroring works however.
• A delay of minutes would be acceptable in most LOB-applications if there is no risk off sync conflicts. I can imagine that if you work with orders, timesheets, etc. it doesn't matter if your data is a bit delayed because you are accessing it from another country.
• Assume the vendor doesn't provide a solution. I won't say they don't support it. I asked one and they didn't have plans for such a system. Their solution was to move all infrastructure (servers + desktops) to the 'cloud'. But it's just MS SQL, so I'm hoping for a better solutions. With terminal-server you can have a slow user experience with everything you do. It would be better if you just have a (small) delay in your LOB-application only, not when visiting a website or scrolling a PDF.

Any recommendations? Or is a central terminal-server the only way?

Thanks in advance.

Answer 1

This is exactly what replication is designed for. Sounds like Transactional replication with updateable subscriptions will work although MS is saying this will be removed down the road (no specifics)

Merge replication is also a choice, but is more complex to maintain.