May 3, 2012 at 10:31 am
opc.three,
I agree there IS some complexity BUT it isn't really all that complex. There are some great how-tos and even BOL does a really good job.
Straight queueing it may be overkill. But using the auto-activation it can handle so many things that in the past required more exotic solutions.
Given all this I stand by my original suggest that SB be used.. And I think the poster decided to give it a go if I remember.
CEWII
May 3, 2012 at 10:39 am
opc.three (5/3/2012)
I don't know about this one, Paul. I see this type of comment a lot on the net...that the bar is simply too high to get started with SB so use SQL Agent or an infinite loop or some other home-brew that will somehow be easier to understand or support...and it plays well in the Forums but for my money it simply is not true.
I will defend to the death your right to hold a different opinion to me on this 🙂
Let me just say, though, that many DBAs will be loathe to use such a script in production without fully understanding all the elements. This is fair and reasonable since the DBA in question will be the one that will have to support it in the future, and perhaps debug it after an early-morning paging alert.
In fairness, there are quite a few concepts in there (and a fair bit of syntax) that many DBAs will not have encountered before, let alone have any real world experience with. In fact, there is probably a new concept for many people on just about every other line! OTOH, a SQL Agent job, queue table, and procedure consists of quite basic elements that a junior-to-intermediate DBA should be able to understand and support.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 3, 2012 at 1:11 pm
Elliott Whitlow (5/3/2012)
opc.three,I agree there IS some complexity BUT it isn't really all that complex. There are some great how-tos and even BOL does a really good job.
Straight queueing it may be overkill. But using the auto-activation it can handle so many things that in the past required more exotic solutions.
Given all this I stand by my original suggest that SB be used.. And I think the poster decided to give it a go if I remember.
CEWII
Agreed, in terms of implementing a basic queue to handle one of the use cases held up as a primary example for its existence...I think MS and the community at large has nailed that one down pretty good in terms of documentation and examples. Based solely on that complex is not how I would evaluate SB at all.
I understand that SB can become complex in a hurry, however, and maybe that is a barrier to adoption for the entry-level use cases. If that were indeed the case, it would be unfortunate. I would love to be privy to observe and work with the MySpace SB implementation, however we're not talking about anything like that on this thread.
SQL Kiwi (5/3/2012)
opc.three (5/3/2012)
I don't know about this one, Paul. I see this type of comment a lot on the net...that the bar is simply too high to get started with SB so use SQL Agent or an infinite loop or some other home-brew that will somehow be easier to understand or support...and it plays well in the Forums but for my money it simply is not true.I will defend to the death your right to hold a different opinion to me on this 🙂
Yikes *visceral reaction* things just got serious 😛 seriously though, ditto. Know that I respect your contributions greatly. I am excited to be having this exchange with you and Elliott.
Let me just say, though, that many DBAs will be loathe to use such a script in production without fully understanding all the elements. This is fair and reasonable since the DBA in question will be the one that will have to support it in the future, and perhaps debug it after an early-morning paging alert.
In fairness, there are quite a few concepts in there (and a fair bit of syntax) that many DBAs will not have encountered before, let alone have any real world experience with. In fact, there is probably a new concept for many people on just about every other line! OTOH, a SQL Agent job, queue table, and procedure consists of quite basic elements that a junior-to-intermediate DBA should be able to understand and support.
I can appreciate your point. In a fishbowl I concede that grabbing the most familiar thing off the shelf can be comforting, and therefore feel safe, and that in a Forum-setting LCD usually rules the discourse. In my opinion the implementer must be at least a little invested in the technology to effectively drive home a solution, and if SB comes across as scary that investment is at risk.
SB can appear scary at first glance, but when it is represented as overly complex when used to handle one of the core use cases for which it was developed, I am compelled to voice a disagreement. The nouns MS chose to build the syntax are foreign at first, but in a couple hours a decent knowledge-foundation and comfort level can be achieved. How long do you suppose it might take for a junior to intermediate DBA to understand the FIFO queue implementation in the post you provided, and the concepts introduced in usp_dequeueFifo? I would submit that while a queue is a familiar general concept to many, the implementation details to properly implement one in T-SQL, plus wrap said queue with a processing proc (loop on WAITFOR or exiting after checking) and an Agent job would be a similar hill to climb for a junior to intermediate DBA.
This is not to mention that SB has been around since SQL 2005 (the code sample I posted from rusanu works on SQL 2005 SP4 Build 5292 as well). I ran into similar apprehension when attempting to deploy a PowerShell script at one stop, where it was a major undertaking to get folks to consider using it over CmdShell scripts (.bat) or VBScript to accomplish basic sys admin tasks in a much more elegant manner...after it had already been in the wild for several years and it was clear MS was backing the technology. At the risk of trying to predict the future, it is my understanding that SB is firmly embedded in SQL Server and is not dropping out of favor anytime soon. It is used internally for Database Mail and was recently enhanced to support multi-casting, mostly as a result of MySpace's experience when using it to create a distributed database architecture. IMHO if an intermediate DB Professional wants to make the leap to Senior DB Professional, they better be willing to tackle SB.
That's my two cents and I'm sticking to it 😀
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 3, 2012 at 2:06 pm
opc.three,
Well said.
CEWII
May 29, 2012 at 9:27 am
Update: After studying SB, implementing it in some test scenarios, and liking it, the decision was made to use the ETL tool Informatica as a queue management tool.
At any rate, I appreciate all the guidance. Thanks!
May 29, 2012 at 1:07 pm
Glad we could help..
CEWII
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply