June 5, 2011 at 11:27 am
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 prefer 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 problems
2. Service broker
3. MSMQ
Which option sholud you reccomend?
June 5, 2011 at 5:28 pm
Heya!!
Have you thought about using MSSQL's replication agent, once it is set up correctly it manages all the hard stuff for you.
If you need a second to second copy of this database on all the other clients you can use a transaction replication which will push every change from the Master database to all of the slaves, or if you just want it updated every so often a snapshot replication is the way to go.
I have only ever used replication for reporting servers and data warehouses so I normally use snapshot in order to control network traffic. I know a little bit about transact replication if you need more help on to.
I hope this helps!!
Sam.
June 6, 2011 at 6:00 am
I didn't think about replication. I am not sure would it be good solution and can I use it
if I have only sql express editions but I will think about it.
Thank you.
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
June 7, 2011 at 12:19 pm
aaroww11 (6/5/2011)
2. Service broker
MySpace successfully implemented a distributed database using Service Broker in 2009 to replace a very complex replication scheme. They were probably the majority, if not the sole customer influencer, for Microsoft to add the multi-casting feature to Service Broker in SQL "Denali". They implemented their own multi-casting and have presented on the topic publicly.
Some of the folks that architected and were actively maintaining and developing the system were interviewed back in 2010 for SQL Server magazine. All jokes aside about what MySpace is in terms of a social networking site, they probably support the transaction volume necessary to justify studying their design and implementation.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 8, 2011 at 5:53 am
Thank you I am very close to choose Service Broker
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply