Solved

SQL across the Internet

Posted on 2014-11-02
12
159 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
 

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
The problems with reply email signatures

Do you wish that you could place an email signature under a reply? Well, unfortunately, you can't. That great Exchange/Office 365 signature you've created will just appear at the bottom of an email chain. What a pain! Is there really no way to solve this? Well, there might be...

 

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Know what services you can and cannot, should and should not combine on your server.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

759 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now