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 |
|
missMac
Posting Yak Master
124 Posts |
 Posted - 2008-10-17 : 15:18:39
|
| Hello,What is service broker ? And can i use it to run TSQL Scripts as jobs can ?Thanks |
|
|
SimpleSQL
Yak Posting Veteran
85 Posts |
Posted - 2008-10-17 : 23:50:37
|
| Yes You can.In simple words,service broker is a queing mechanism, provided by SQL Server.The way it is implmented is 1. You create a queue at initiator (assuming this is where your messages are comign frmo).2. You create a queue at target (this is where your message go).3. You create a service (this facilitates message movement).4. You create T SQL code to retrieve message from target queue and activate a Stored procedure that consumes the message and takes action (executing TSQL code) per business need.Hope I didn't confuse you :)http://msdn.microsoft.com/en-us/library/ms166043.aspxhttp://msdn.microsoft.com/en-us/library/bb839489(SQL.90).aspxhttp://msdn.microsoft.com/en-us/library/ms171562.aspxhttp://msdn.microsoft.com/en-us/library/ms171562.aspxThe above links has details on Service Broker. It also has example code, which you can manipulate per your requirement. It is not really complex to implement, once you get hang of it. |
 |
|
|
missMac
Posting Yak Master
124 Posts |
Posted - 2008-10-18 : 02:56:38
|
| Brilliant. Thanks a great deal.I also read Spirit1's service broker implementation on the home pagehttp://www.sqlteam.com/article/scheduling-jobs-in-sql-server-express (Brilliant article)Which is better to use as a queuing system ?1. Jobs2. Service broker ?3. OtherAs from my understanding jobs are for adminstrative tasks and not to run TSQL scripts, also owing to the fact that you can only run one instance of a jobtnx |
|
|
|
SimpleSQL
Yak Posting Veteran
85 Posts |
Posted - 2008-10-21 : 12:34:58
|
| Sorry for delayed response. While you can use SQL Agent jobs to run TSQL Scipts, it is better to use Service Broker. SQL Agent Job is best for fixed Schedule tasks, and is not a event based mechanism to implment solutions. Since you seek something that is event based, Service Broker gives you the best infrastructure to make this happen. It only triggers when a certain event occurs (messages are enqued).It scales well as well. Apart from Jobs and SB, you have option of triggers as well, but there is no way to manage it manually, that is, you cannot control how many triggers run concurrently. If you have say 100 messages coming at once, you will have 100 triggers fired, and that can become bottlenck.With Service broker, you can control how many instances of Stored Procedure are runnign at any given time and you can also control, the polling period for queue, so the activation stored procedures are not activaed right away as soon as message is in the queue. |
|
|
|
|
|
|
|
|
What is service broker ?