3

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.

  • 1
    Are data read-only at the remote sites? – Dan Guzman Jul 8 '17 at 19:05
  • How much delay can your users/application tolerate between synchronisation? Realtime? Minutes? Hours? – Sir Swears-a-lot Jul 8 '17 at 22:42
  • Does your application vendor support distibuted db's or availability groups? Is this 3rd party or in house development. – Sir Swears-a-lot Jul 8 '17 at 23:17
  • Thanks @DanGuzman and Peter, I edited my question to answer your questions. – roberth Jul 9 '17 at 6:10
2

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.

  • Thanks @Kevin3NF, this indeed looks like it's designed for it. It does, however, require some compatibility with the LOB-application. There are extra columns added to tables and some datatypes are not supported. When you think of it, it's logical but I don't think it can be used a a general solution for these kind of situations (may there isn't one). I'm still hoping for some magical easy solution without to many complications. – roberth Jul 10 '17 at 18:59
  • is the LOB-application from a 3rd party vendor? If so, check in with them to see what they support :) – Kevin3NF Jul 10 '17 at 19:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.