Sql-server – What do I need to sync a local site office SQL Server with the Head Office SQL Server

remotesql server

The company has a desktop application powered by SQL Server from the head office that is dependent on an internet connection to our remote sites. Usually when we move to a new site there is a good internet connection 5Mb+

We have a new site that is connected by a very slow satellite internet connection in the middle of nowhere. As you can expect our application is really suffering.
Unfortunately we cannot just install a SQL Server box locally to power the application on site.

The application database has been designed (by a now non-existent 3rd party) that all separate contracts live in the same database and share common tables.

We have 3 projects running all writing to the one database, updates are about 500MB a week during active projects for all 3 projects.

Also there is a project status website powered from the head office SQL Server. Management would not be happy if this new remote site showed no progress for the duration so we need to factor in getting data back to head office to power the website, again all from the one database.

It is outside the scope of this question to start re-designing the database. (hopefully that’s a project for later in the year)

  1. Is it possible to install and operate a local SQL Server /
    application server for the remote site clients that can update back
    to the main head office SQL Server as and when it either has an
    internet connection and / or enough bandwidth to do so?

  2. How would one achieve this using SQL Server given the super slow Internet access?

  3. What kind of timeframes would be involved to setup and test?

As usual other than money for a desktop box to act as a local server and a SQL Server 2008 Standard licence the budget is £0

EDIT: Added the tracert to show network latency from the remote site, unfortunately we are not the main contractor at this site and have little say in the provisioning of the comms.

1     1 ms     1 ms     1 ms  192.168.2.1
  2   544 ms   552 ms   564 ms  10.190.9.206
  3   543 ms   701 ms   655 ms  79.140.207.252
  4  1461 ms   542 ms   641 ms  79.140.207.254
  5   658 ms   564 ms   555 ms  ae1-143-ycr2.mcr.cw.net [213.38.254.81]
  6   601 ms   617 ms   588 ms  so-3-3-0-dcr1.lsw.cw.net [166.63.209.201]
  7   577 ms   568 ms   564 ms  xe-7-2-0-xcr1.lsw.cw.net [195.2.21.161]
  8   580 ms   564 ms   656 ms  xe-0-1-0-xcr1.lnd.cw.net [195.2.4.194]
  9   566 ms   565 ms   570 ms  ge-3-3-0.mpr1.lhr3.uk.above.net [195.66.226.76]
 10   605 ms   594 ms   569 ms  ge-5-1-0.mpr1.lhr3.uk.above.net [64.125.28.94]
 11   574 ms   569 ms   573 ms  so-0-0-0.mpr1.ams1.nl.above.net [64.125.30.130]
 12   570 ms   573 ms   570 ms  xe-0-0-0.er1.ams1.nl.above.net [64.125.26.81]
 13   635 ms   598 ms   581 ms  213.152.245.50
 14   588 ms   582 ms   582 ms  glfd-bb-1b-ae0-0.network.virginmedia.net [213.105.172.6]
 15   599 ms   618 ms   596 ms  manc-bb-1a-ae3-0.network.virginmedia.net [213.105.175.145]
 16   592 ms   609 ms   576 ms  manc-core-2a-ae0-0.network.virginmedia.net [82.15.206.246]
 17     *        *      592 ms  manc-lam-4-tenge11.network.virginmedia.net [213.104.242.26]
 18   583 ms   596 ms   636 ms  m229-mp4.cvx2-c.man.dial.ntli.net [62.252.216.229]
 19     *        *        *     Request timed out.

Best Answer

I ran into a similiar problem for an accounting package. Instead of trying to work out a way to move the SQL data around it proved to be much easier (and more cost effective) to provide a way to remotely access the application.

I opted for a TS 2008 box. This includes a large number of international users with terrible bandwidth compared to the states.

EDIT - As syneticon-dj points out there are still potential issues associated with using TS on a satellite link. You would definitely need to look at the latency of your link for a TS solution to work. Some satellite links provide around 300 millisecond latency, others may be over a second.

Related Question