?
Solved

SQL across the Internet

Posted on 2014-11-02
12
Medium Priority
?
183 Views
Last Modified: 2014-11-15
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.
0
Comment
Question by:jonmenefee
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 2000 total points
ID: 40418548
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!
0
 

Author Comment

by:jonmenefee
ID: 40418601
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.
0
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 2000 total points
ID: 40418967
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.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:jonmenefee
ID: 40419150
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
0
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 2000 total points
ID: 40419154
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.
0
 

Author Comment

by:jonmenefee
ID: 40419165
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
0
 

Author Comment

by:jonmenefee
ID: 40437454
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?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40437489
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.
0
 

Author Comment

by:jonmenefee
ID: 40437675
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
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40437684
You have your answer then. Good luck with the client.
0
 

Author Comment

by:jonmenefee
ID: 40437884
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
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 2000 total points
ID: 40437892
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question