January 21, 2009 at 4:46 pm
We have two development teams. 1 in USA and 1 in China. The dev database server is here in USA. The source control database (TFS) is also here in the USA. Due to network latency the developers in China have terrible performance when trying to access our server. I have been asked to come up with a solution that will allow the developers in China to essentially have a local SQL Server.
So, how do I keep a dev SQL Server in the USA and a dev SQL Server in China in synch? I know obviously that merge replication is an option, however I wonder about the performance accross the WAN from USA to China.
Any other suggestions from folks who have remote development teams?
A.J.
DBA with an attitude
January 21, 2009 at 5:33 pm
How "in synch" do they have to be?:
- What is the acceptable latency window?
- Does this have to handle Table Def changes also?
- What about changes to views, sprocs and udf's?
- How big is the database (GB, # of tables)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 22, 2009 at 8:49 am
- What is the acceptable latency window? -
5 minutes at most
- Does this have to handle Table Def changes also? '
Yes, this is what synchronized means in my book
- What about changes to views, sprocs and udf's?
Yes, this is what synchronized means in my book
- How big is the database (GB, # of tables)
20 GB+, hundreds of tables
A.J.
DBA with an attitude
January 23, 2009 at 7:32 pm
Ah, that's easy then: can't be done.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 24, 2009 at 10:40 am
Thanks for the reply. Now I can take your suggestion and... oh wait... there was no suggestion.
I guess I will just figure something out on my own.
A.J.
DBA with an attitude
January 24, 2009 at 11:43 am
A.J.,
"It can't be done" might not have been the answer you were looking for but it's honest and straight forward. You did imply the link between the sites is already suspect.
If you want them to "essentially have a local server" then instead of bringing the server to them, why not bring them to the server? Something like remote acccess to a virtual desktop environment located at your site that they could use for development. That pretty much removes synchronization as an issue.
January 24, 2009 at 11:47 am
I have lots of suggestions, but they will not meet your requirements.
The thing that comes the closest is merge replication, but it is going to have difficulty keeping 20GB of data in synch with a maximum of 5 minutes latency when the two sites are on opposite sides of the planet. You might be OK is the average size of UPDATE's and INSERTS are reasonable, but any big Update or bulk insert will blow you out of the water.
Plus, the dba overhead of setup and administration for replication on every table can be quite onerous. And finally, changes to your table definitions are killers: you have to tear down replication on the affected table first, make the definition change, then rebuild replication on it, which effectively means re-snapshotting it. You'll never make your 5-minute window with that.
Another approach would be to use a combination of table triggers, DDL events and Service Broker to implement your own two-way replication across the complete database. However, the development effort for this would be staggering and the administrative overhead would probably be even worse then merge replication.
So I would ultimately suggest a little more realistic requirement. For instance it is hard for me to understand why a development DB would need a 5-minute latency window. Usually a little project reorganization can relieve this so that at least table definition changes and large updates/inserts in a shared site can be subject to overnight scheduling.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 24, 2009 at 11:59 am
Another option is to purchase bandwidth on the trans-pacific cable. This should cut your developers' perceived response latency about in half which may be enough to placate them. On the other hand, I cannot even imagine what the cost of that kind of guaranteed bandwidth on the trans-pacific cable would be, probably close to a small state's annual budget.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 24, 2009 at 3:40 pm
Thanks for the reality check, it's actually helpful. At least I now can tell me boss that I am not the only one that thought these requirements were a bit unreasonable without spending some serious cash.
A.J.
DBA with an attitude
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply