why would i want service broker instead of an ssis pkg that is run every 30 seconds

Question

hi we run 2014. 2019 to come.

When i trickle data into our warehouse i always wake a pkg up via sql agent using an agreed upon frequency in a job schedule.

i dont think i've ever had anything more frequent than 15 minutes. But now i need a frequency of every 30 seconds on a new extract. And our CM dept is adamant about using service broker instead of incremental (via special index) ssis to do this particular extract from our oltp. and i'm still a bit adverse to cdc etc.

CM cant explain to me why they feel so strongly about service broker. i would have a very specialized index for getting in and out of the core table very quickly. If i made it a covering index that includes the 2 columns i need, sql wouldnt even have to lookup the record on the base table.

Can the community help me to understand why CM is so adamant? I think they picture a trigger doing an insert and a windows service consuming the queue to do whatever is necessary. That could be calling dtexec or just doing the WH updates in a .net sql transaction.

Answer 1

Hi @db042190 ,

The environment of the machine is different and complex.

For performance issues, I suggest that you can do some local test to compare the affect of your server when running in agent job with running with service broker.

Helped to add a tag so that more experts could see this, hope they could give more advice.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

There are often more than one way to skin cats. We know very little about your case, but Service Broker could certainly be an option through a trigger that posts a message on a Service Broker queue.

It appears that your alternative is to poll with some frequency to find modified rows. Such queries can sometimes be victims to poor plans when you least expect it.

But since we don't know these CM persons, we cannot say what their particular reason for using Service Broker. You better ask them directly, than having us to read their minds on a distance.

Answer 3

thx erland. unfortunately i cant get them to say any more than "we dont want to use ssis for a real time application". Their oltp is medium traffic/busy at best.

this is trickle not real time. and it only grabs about 5 records (2 columns) at a time from the tail end of an index whose base table has 41 million rows that is joined to a 2nd table with 1,551 records on the second table's pk. and i just looked, i dont even need to do that join if i'm willing to throw away a few keys while updating the warehouse target table.

i have a feeling that there is a contributor out there who can at least offer an opinion at this point. That's why i phrased the question as "why would i...".

i'm now reading about query notifications at https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms181122(v=sql.105) which sounds perfect but the article doesn't list what type of process (eg proc, program, ssis, etc) might be the recipient of such notifications , how they can be "awakened" at some interval (eg 30 seconds) and maybe most importantly how they might be fed non deterministic parameters like a low key boundary and perhaps a high key boundary from another server where we keep that info. the one thing i do know about such notifications is that they are an alternative to service broker and the 2 cant be mixed. And i may be mixing my technologies but i got the sense it can only be driven by one table, not the result of a join.

Answer 4

The problem with all "event driven" triggers is you have to have something listening to that event, Service Broker, SSIS, an APP, something. In the end it doesn't really matter what that listener is, it just need to perform a process on the "event".

You can simply create an SSIS job which loops continuously, and a SQL Agent job which runs the SSIS job at startup and every 5 mins (this only happens the job terminates for some reason).

Answer 5

thx erland. unfortunately i cant get them to say any more than "we dont want to use ssis for a real time application". Their oltp is medium traffic/busy at best.

I would kind of agree with that sentiment. Then again, I would never opt to use SSIS for anyhing. Of the simple reason that I don't know SSIS. And you may not want to use Service Broker of the same reason.

This discussion here is kind of useless, because we don't anything about your system. We could say something, but would we then come on site and learn what this is really about, we could change to a completely different opinion.

Anyway, with Service Broker, you can have a trigger that posts a message on a Service Broker queue. That message could for instance be an XML representation of inserted/updated/deleted rows. On the other end there can be an activation procedure that receives the data and processes it. That activation can be in the same database, another database on the same instance, a database on another server in the data centre, or a database in a completely different data centre, to which your data centre has no direct connection. And, yes, the latter takes more configuration.

Query Notificiation is a special application that uses Service Broker and which also uses the infrastruture for indexed views. That is, you issue a query, and you say: call me when the result set changes. Whether this is applicable in your case, I don't know.