September 17, 2008 at 3:39 pm
I am not new to SQL Server but I am fairly new to SQL Server 2005. I am looking into "Service Brokers" to see if we could use this technology. And I have noooo idea from everything I've read. Microsoft marketing text makes statements but rarely tells you "How" and "Why" you would use any of their solutions. For some reason, many people don't have the ability to communicate across technological boundaries.
I can find how to do it. I would like help finding out Why one would use Service Brokers and why one Wouldn't use a Service Broker.
I don't know about anyone else, but I learn faster when I can see or hear examples which address real-life needs and fulfill them with software solutions.
If anyone can direct me, I would really appreciate it.
🙂 Thanks
September 18, 2008 at 3:23 am
Use Service Broker whenever you need asynchronous processing. See http://technet.microsoft.com/en-us/library/ms345113.aspx for examples.
September 18, 2008 at 8:16 am
Actually, that was very good. The two examples they gave, helped me get a visual and a sense of why and how queues are used. All the work I've done over the last 10 years:), with the exception of maintenance jobs or replication jobs is done in real time.
Thanks 🙂
September 18, 2008 at 2:56 pm
You're welcome. You can start learning the "nitty - gritty" with Adam Machanic's tutorial on http://www.simple-talk.com/sql/learn-sql-server/.
September 18, 2008 at 4:22 pm
In my experience, there are three different general things that you can use Service Broker for:
1. Asynchronous processing in response to events
2. Decoupling execution from a source caller
3. Implementing a synchronous service in SQL with a truly different security context
Asynchronous processing (#1) is what people typically think of, is essentially the ability to setup asynchronous Triggers or something very similar.
I think that the best simple example of this would be for a Universal DML Logging facility: that is, you setup after-triggers to log every data change in an applications database to a common log table. That's easy enough to do, but there's one big problem: the performance of that common log table will suffer long before the rest of the application otherwise would have. And once the writes to the common log table start to backup, all write performance in the application will suffer because they all are also trying to synchronously write to that same common log table. Service Broker can fix this because you can have all of the actual Log-Writes be done by one or more Service Broker services. (I am thinking of writing an article demonstrating this)
Decoupling execution (#2) is simply the ability to write regular stored procedures that can do things that are either very slow or take an indeterminate amount of time (like external communications) without affecting the process or procedure that called them. A good example is DB Mail which does exactly this. When you call a DBMail sp_send_dbmail it actually puts your request into a queue in MSDB, where shortly thereafter, a service routine will receive your request and begin using SMTP to communicate with a mail server to send your mail. Since SQL Server does not control the mail server, there's no telling how long that could take (one of the big problems with SQL Mail), but since you do not have to wait for it yourself, you don't have to care.
The last one, a synchronous service in a truly different security context, is not what people usually think of for Service Broker, but it is important also. Although SQL 2005 does provide a lot of security context options (owner-chaining, Execute As, certificates, etc.), none of them allow a process to truly act as another user or to completely hide your original Logon context (this is intentional). For instance, there is no way to give a single unprivileged user an execution context that will allow them to use xp_CmdShell, unless it has been enabled for all users.
However, because the routine servicing a Service Broker queue executes in it's own separate security context, it can be setup as a service to execute xp_cmdshell procedures and other wrappers on behalf of an unprivileged requester. So for instance, an unprivileged user could send a directory name to a MKDIR service and then execute a Recieve on a result queue using a synchronous wait. The service routine for that queue can be setup up to run under a context that is allowed to use xp_CmdShell, and upon receiving the requested directory name, call a usp_MkDir procedure that uses xp_CmdShell to execute the DOS MkDir command on behalf of the user. Then the service routine can return the success status plus any error information on the results queue to the original process that is still waiting for it.
And despite our example, this "seperate execution context" can be considerably wider than this: it can be a different user on the same DB, it can be a User in a different DB on the same server Instance, or on a completely different SQL Server Instance, etc.
[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]
September 18, 2008 at 4:42 pm
I should also add that access to the above described service can be controlled through GRANTS to the individual users,.
[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]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply