International SQL Server Replication

15 pts.
Tags:
SQL replication
SQL Server
SQL Server 2008
SQL Server 2008 R2
SQL Server database
SQL Server replication
I have a unique challenge involving keeping 2 SQL Server Databases in synch, one of which will reside in Iraq, the other will be in a base camp in Kandahar, Afghanistan where the local clinic will use a client application that communicates to the local database. The database in Iraq will be for billers to bill insurance, doctors to review the drug dispensing and medical services to ensure appropriateness, and the inventory people buy to replace stock. The problem is that they have very little bandwidth out of Afghanistan (30-40Kb on a good day) and high latency subject to long periods of outage. I need a robust method of deploying this that lets SQL do the work of keeping the DBs in sync when the connection is present. Since the data would contain sensitive medical information, it would also have to be a secure connection. What would be my best options to explore and what considerations should I account for? Is this even something that should be attempted? I look forward to your comments and thank you in advance.



Software/Hardware used:
SQL Server 2008 R2

Answer Wiki

Thanks. We'll let you know when a new response is added.

Yes this can be done, using SQL Server replication. You’ll probably want to initialize the subscription using a process called init from backup, which allows you to take a backup of the main database and then restore that database to the remote system.

Will you need to allow people to write to both servers? If you do you’ll want to use either merge or peer to peer replication. If people only need to be able to write to one server then you will want to use transactional replication from the database that people write to, to the database that people only read from.

You can easily enough encrypt the connection using SSL using SQL Server’s native SSL encryption functions, but keep in mind that encryption will slow things down as this does take bandwidth.

The key will be to only replicate the tables that you need to replicate so that you aren’t wasting bandwidth on things that don’t need to be moved.

If you are using stored procedures to run the application then you can also replicate the stored procedures, which will replicate the stored procedure execution instead of the actual table level replication which could reduce the amount of bandwidth needed (depending on how complex the stored procedures are, and how many changes each stored procedure makes).

You could also look at using something like SQL Service Broker (if you are using SQL Server 2005 or newer) to replicate the data instead of replication. This would give you more control.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • MattPoirier
    [...] give you more control. To see different replies submitted to the Respond Wiki View Respond History.SQL server replication – Google Weblog Search by Jason Mulligan What’s on Twitter: not somewhat I anticipated to ever see "Set up [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following