SQL across the Internet

Hey guys

Ok, here is the scenario.  My customer has 40 offices scattered around the world.  They all need to access a common SQL database thru a website front end.  Right now about 1/2 of them can access the database (here in the United States) with no slow downs, but the other half are experiencing slow downs due to their location (Asia, Australia etc.).  So they want a solution that will enable everyone to have access to the same Data from a server that is local to them.  So I looked around and since we are using Microsoft products I saw that SQL Enterprise has Peer to Peer Replication.  That of course is the most expensive one (at $30,000 per 4 core VM), so I want to offer them another alternative if possible.  Which of the replication models would work with this do you think?  Each region would have two servers.  One is a website server and the other is the database server (both are VM's).  It would be nice if the data was up to date on all the servers on a constant basis but if there is a small delay of 10 or 15 minutes I think they can live with that (if we tell them up front).
So, what do you guys think?  We want to stay with the most recent edition of SQL because we are using 2005 now and we want this solution to last as long as the one we have now has.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Have you thought about Transactional Replication? It only requires Standard Edition.

What are your requirements re updates? Do the regions need to be able to update their databases, and have them propagate out? Are there latency issues? discuss!
jonmenefeeAuthor Commented:
Thank you for the additional questions.

Yes, all sites need to be able to update the Data that is shared globally on the server in their own region.  The only latency I am aware of is when Australia is having issues connecting to the US Servers.  

Here is the situation about how much data is being updated and uploaded on a regular basis.  Each region will be uploading documents and creating new projects on a nearly daily basis, so all regions need to be able to update the server in their own region which will then propagate this info to the other servers globally.
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Then could you use Merge Replication? Whilst Peer-to-Peer requires Enterprise, I believe Merge only requires Standard.

If not, then I'm afraid you are stuck with Peer-to-Peer, unless you could use the local database for reading, and the US server for writing.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

jonmenefeeAuthor Commented:
I like the idea of sending writes to Houston.  I will talk to the programmer  I thought merge replication was for client/server scenarios though
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
If that could be the case, then you could Transactional Replication.

Regarding Merge Replication, whilst there is a central publisher and a central distributor, all of the subscribers can make alterations, and you set up how conflicts are resolved - see http://technet.microsoft.com/en-us/library/ms151329(v=sql.105).aspx for more details.

There are also videos on youtube available, such as https://www.youtube.com/watch?v=8XDfIBGY2fE for more information.
jonmenefeeAuthor Commented:
So a server can be a subscriber in a merge replication scenario.  That's very good to hear. :-)  thanks for the links. I will check them out after breakfast
jonmenefeeAuthor Commented:
Sorry for the long delay in response to this question.  I have a couple small questions

If I use SQL 2014 Standard and go with transactional replication (not Enterprise Peer to Peer) how would an application read only on one database and write to another?

I guess what I am trying to figure out is how to make this application global without spending 60,000 in SQL Licenses.  The application has a Website front end where people make changes to existing projects and submit new projects on a daily basis.  SQL Standard's replication model allows one master database and multiple subscribers.  Can the subscribers make changes though or are they read only?  If they are read only then they are only good for doing reports and such right?
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
I don't know the details of your web application, so I can't answer fully.

Subscribers should be treated as read only, so you would them for reporting at a local level, and any changes you would send through to Houston. It used to be the case that there were some update possibilities at the Subscriber level, but Microsoft has now stopped that (see http://msdn.microsoft.com/en-us/library/ms151718.aspx for their comment).

Obviously, try it small scale to start with (just two servers - a publisher/distributor and subscriber) and test your system, and build up from there.
jonmenefeeAuthor Commented:
Hmm.  Ok.  I can't fully describe this application in here but I will give a small taste.   This company inspects cargo ships for insurance purposes.  They do testing and they look at the structure to make sure it's sea worthy.  The website they login to is where they submit their reports on the ships.  They use the website to send the reports to the customer. Because they have 40 plus offices around the world the users in the Asia Pacific area have a hard time logging into the website and uploading their documents. So the idea I had was to have servers in each region that the users in that region can upload and email documents.   It appears to me that to do this I would have to use SQL Enterprise Peer to Peer. Now. If the client can wait for one region to merge with another region then maybe we can use Standard
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You have your answer then. Good luck with the client.
jonmenefeeAuthor Commented:
One last question.  I just spent an hour talking to my SQL developer and some red flags were raised.  If we went with a central database and had multiple read only databases how fast does the publisher database send out changes to the subscribers?  If it's pretty fast (3 to 4 seconds) then we could stick with SQL Standard and not have to spend 100,000 on SQL Enterprise licensing
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
It can very - see this article http://msdn.microsoft.com/en-gb/library/ms152770.aspx as a guide to how to speed up performance.

Bear in mind you are always going to have some latency going half-way round the world. I would suggest a small-scale test to see what results you get.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.