Solved

SQL across the Internet

Posted on 2014-11-02
12
166 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
  • 6
  • 6
12 Comments
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 500 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 500 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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 500 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 500 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Remote Apps is a feature in server 2008 which allows users to run applications off Remote Desktop Servers without having to log into them to run the applications.  The user can either have a desktop shortcut installed or go through the web portal to…
Background Information Recently I have fixed file server permission issues for one of my client. The client has 1800 users and one Windows Server 2008 R2 domain joined file server with 12 TB of data, 250+ shared folders and the folder structure i…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

830 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