December 17, 2008 at 1:31 pm
Hi All,
We are trying to synchronize 2 of their databases i.e changes made to one of the database should be automatically to the other database using Sql server service broker.
Can some one explain in detail how to achieve it. What are the various steps in it and from where to start?
Thanks in advance
December 17, 2008 at 3:33 pm
The plan you use should be based on the needs and specifications.
Lets start with a few obligatory questions: Why was Service Broker chosen as the solution instead of : 1) Replicaiton, 2)Log Shipping, or 3) Database Mirroring? SSB's (Server Broker) advantage over these is its flexibility & customizability. However, its corresponding disadvantages are the greater amount of time and effort that it takes to implement it.
That is, SSB can do a lot of things that are impossible or impractical for one ro more of the others to do, but for something that they could all do, SSB is probably the most expensive.
Which begs the question asked: what special features/capabilities of SSB make it the preferred solution for you over these other choices?
[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]
December 20, 2008 at 9:39 am
Hi Barry,
The reason for showing interest in service broker is the databases we are trying to synchronize are of different schema. What i mean is the structure of the tables in the databases is different. And one more thing is we want to get confirmation that the updates,inserts and deletes made in one database are made in the other database too..
I hope you understand the situation.
Can you send me a sample code for synchronizing two databases using Service Broker if possible?
December 20, 2008 at 11:03 am
sql server developer (12/20/2008)
Can you send me a sample code for synchronizing two databases using Service Broker if possible?
That's a pretty huge request. There's a lot that goes into service broker, from the contracts to the message types, the creation of the queues, the procs to read the queues, etc, etc.
Where exactly are you stuck?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 20, 2008 at 11:13 am
Hi Shaw,
We have created message types,contracts. We need to create stored procs that activates the queues....What i'm not understanding is the basic concept how service broker automatically posts the updates made in one database to the other database.
Can you explain me how the service broker is used for updating data automatically in the second database when they are made to the first database.
When we update data in the first database how the service broker automatically sends the message to the second database and updates it in the second database?
Can you explain it in detail?
Thanks in advance!
December 20, 2008 at 1:00 pm
sql server developer (12/20/2008)
When we update data in the first database how the service broker automatically sends the message to the second database and updates it in the second database?
Service broker doesn't automatically send a message. You have to create a message and sent it on a queue when you update the data in the 1st DB. So you would either create and send the message in a trigger or, if all updates are done through stored procedures, create and send the message in the procedure.
These two articles should give you a good overview of Service Broker
http://www.simple-talk.com/sql/learn-sql-server/service-broker-foundations-workbench/
http://www.simple-talk.com/sql/learn-sql-server/service-broker-advanced-basics-workbench/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 20, 2008 at 1:06 pm
Hi Shaw,
thanks for your reply. Can you please send me a sample queue for the trigger that sends the message upon update of data in the first db.
Thanks a lot!!
December 20, 2008 at 1:10 pm
Read the links I posted. There should be lots of examples there.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 20, 2008 at 1:35 pm
Hi Shaw,
Can you please tell me what all things we need to do for synchronizing two databases using service broker in a step by step manner like
1) create service broker objects in both the databases
2) write stored procs that activate the queues
3)write triggers for the first database that sends message if any updates were made
4)....
5)...
In that way.
If you can do it that will help me a lot
Thanks a lot in advance
December 20, 2008 at 8:20 pm
Please see the code that accompanies my powerpoint presentation "The Top 10 Reasons You Aren't Already Using Service Broker" at this location: http://www.movingsql.com/dnn/Portals/4/Materials.zip
It contains an example of most of the steps for how to do this.
[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]
December 21, 2008 at 2:07 am
Please read through the links I posted. There are pages of examples in there, much better than I can think up in a few minutes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 22, 2008 at 12:59 pm
You can also check out these two books:
The Rational Guide to SQL Server 2005 Service Broker (Roger Wolter)
and
Pro SQL Server 2005 Service Broker (Klaus Aschenbrenner)
Both books are excellent in both the basics and advanced topics and techniques for developing Service Broker apps.
Andy
December 22, 2008 at 3:20 pm
jac (12/22/2008)
The Rational Guide to SQL Server 2005 Service Broker (Roger Wolter)and
Pro SQL Server 2005 Service Broker (Klaus Aschenbrenner)
I can not recommend the first book (Wolter). It seems very lightweight, but nonetheless was of little or no use to me in getting started on Service Broker.
The second book (Aschenbrenner) on the other hand is very good, but presents a very CLR-centric viewpoint and approach.
And neither book addresses the questions asked by the OP.
[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]
December 22, 2008 at 3:43 pm
Thank you very much for your time
December 22, 2008 at 3:44 pm
They do not address the exact question by the OP but the do give a good head start into the fundamentals of Service Broker. With some practice the OP should be able to develop some kind of synchronization process that he/she is looking for.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply