January 7, 2010 at 10:58 am
I am curious on the best method to spread a single database across multiple sites that are connected by a small pipe. Here is the situation:
I have two offices that are connected by a MPLS vpn line. It is only T1 size, so 1.5 Mbit. The business uses a program that stores and accesses data in a SQL database. I have a SQL server in Office #1, and everyone in Office #1 that uses the program has no speed problems. However, for anyone trying to use the program in Office #2, it takes forever for them to access the database (which is understandable with such a tiny connection between the buildings). My original solution, since I'm no SQL server expert, was to set up Terminal Servers in Office #1 and have people in Office #2 remote into those and run the program, which works fine. Can anyone recommend a better solution? Is there a way to have one SQL server in Office #1 and one in Office #2 that are both publishers of the same database? Etc. I have done some searching around, but can't seem to find anything that does exactly what I want, but that seems silly to me, so I figure I am misunderstanding a feature somewhere. Thanks for the help.
January 7, 2010 at 1:25 pm
You could explore replication http://msdn.microsoft.com/en-us/library/ms151198.aspx but this can be complicated depending on how data is accessed/updated. Your terminal server solution may well be the best one especially if this is an off-the-shelf application which you can not modify.
January 7, 2010 at 2:07 pm
Trust me, I would much rather stick with the Terminal servers but the bosses always seem to think I can build a better mouse trap. As far as replication goes, I have read all about it, but I can't figure out how it would work with two publishers. What happens if two people edit the same part of the database at the same time? What happens if the connection between the buildings goes down for an hour? Those kinds of things worry me. I have read that MSSQL transactional replication uses an intelligent algorithm to determine how to edit a database when multiple people try to edit it at once, but how well does this work?
January 7, 2010 at 4:08 pm
>>As far as replication goes, I have read all about it
It sounds like you know as much or more about it then me then. I wasn't sure if you had explored the topic.
In any type of data synchronization process there can be conflicts and therefore the need for conflict resolution. In the systems I have worked with (not SQL Server replication) there are rules you can tweak to pick the winners and also ways to review when conflicts have occurred.
For me, having a unified database is always preferable if the performance to the end users is acceptable.
January 7, 2010 at 4:46 pm
emily-1119612 (1/7/2010)
>>As far as replication goes, I have read all about itIt sounds like you know as much or more about it then me then. I wasn't sure if you had explored the topic.
In any type of data synchronization process there can be conflicts and therefore the need for conflict resolution. In the systems I have worked with (not SQL Server replication) there are rules you can tweak to pick the winners and also ways to review when conflicts have occurred.
For me, having a unified database is always preferable if the performance to the end users is acceptable.
Agree on the unified database. SQL does have a Conflict resolution app for this very purpose. Not sure how much it will apply to your scenario but worth a look at.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 7, 2010 at 8:11 pm
The "better mousetrap" requires a "better pipe".
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply