Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aaroww11
Starting Member
12 Posts |
 Posted - 2011-06-05 : 13:28:07
|
| I need to create distributed system with several databases on several sql server instances which will be either on one machine or on several machines. I would preface to use sql express edition but I can use one standard edition and other free editions.I have 3 ideas to establish good communication:1. with triggers and linked server with maybe one job which will check from time to time is something missing just fir incase if there are some network or other problems2. Service broker3. MSMQWhich option sholud you reccomend? |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-06-06 : 11:32:39
|
| Replication. Can writes occur on all instances or is it more of a publish/subscriber scenario?Be One with the OptimizerTG |
 |
|
|
aaroww11
Starting Member
12 Posts |
Posted - 2011-06-06 : 12:02:55
|
| More a publish/subscriber scenario.One sql server will be main and send messages to others and others will reply from time to time.I don't think that I can do it with replication |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-06-06 : 13:12:37
|
| Of your original suggestions:1. This will be a performance nightmare, even if the code is perfectly tuned, which is very hard to do. Avoid doing this.2. Better than MSMQ, but has significant coding requirements.3. Not as fully integrated with SQL Server as Service Broker.I agree with TG that replication is a much better solution, unless there are factors that rule it out (which you haven't provided). Regarding "messages" from subscribers, what exactly does that involve? Are the subscribers writing changes back to the publisher? |
|
|
|
aaroww11
Starting Member
12 Posts |
Posted - 2011-06-06 : 16:40:43
|
| The subscribers will write changes back to publisher. However in this moment I am not sure are that be only inserts to tables which are in 1-1 relationship with original tables in order to avoid updates or I would have also some updates in some situtations.If I only have some inserts and avoid updates can I use replication? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-06-06 : 17:03:07
|
| You don't have to avoid any operations, the level of replication will either support them all or none of them. What you've described can be done with merge replication, and possibly also with transactional or peer-to-peer replication.You should take a look at the replication information here: http://msdn.microsoft.com/en-us/library/ms151198.aspxI'm not sure how or if this will work with SQL Express instances, but I can say that if performance is an issue, or databases will be larger than 10 GB, Express will not be an option. Another option to consider is the Sync Framework: http://msdn.microsoft.com/en-us/sync/bb736753That has a lot more flexibility, but may be more difficult to write code for. (I haven't used it myself but people I know have and said it's fairly easy) |
|
|
|
aaroww11
Starting Member
12 Posts |
Posted - 2011-06-07 : 09:01:25
|
| Thank you.I will think about it.I know about limitations for SQL Express instances but I will organize them so that 1 processor and 10gb is enough.I can have one sql standard edition others have to be free editions so I would have to check can I use then replication on suggested way because free edition support only subscriber mode. |
|
|
|
|
|
|
|
|
Distributed databases