What is meant by application throughput

Sascha Lorenz

I am writing about our repositories and there is already scolding from the community! Sascha said he used triggers. you don't even go! So because of bad role model and so! Triggers are so bööööööööse .. come right after Cursorn! :-)

You are right! Basically in a LOB / OLTP environment I'm very rarely a fan of triggers. Although I have to keep having this discussion with ISVs. So here are a few words about it.

Why are triggers actually bad? It's a cool thing, isn't it?
Yes, the idea behind triggers is actually extremely practical. Logic is triggered by a DML or DDL statement. A procedure does not have to be started. The unpleasant thing about a trigger is the fact that it is always in sync out-of-the-box. Unfortunately, this is a huge cost to the overall performance of a system if there is a little more load on it. This means that the action that the trigger starts always "happens" at EXACTLY the moment the trigger is called. This makes perfect sense in some areas, but in most cases this 100% synchronicity is not necessary. For example in almost all of my repository examples. That's why we use so-called asynchronous triggers!

Ok, what are asynchronous triggers now, please?
With asynchronous triggers we usually mean a trigger that uses a service broker service (service is always important)! Unfortunately, Service Broker leads a shadowy existence in many environments, although everyone does EACH, SQL Server "included" since 2005. (Ok, apart from small restrictions.) And triggers are, among many others, a very good example of the productive and not at all esoteric use of service brokers!

And what is this "service broker"? And do I need an Enterprise Edition for this?
Here comes some text from the BOL: "Service Broker supports database developers in building reliable and scalable applications. Because Service Broker is part of the Database Engine, managing these applications is also part of routine database management.

Service Broker provides queuing and reliable messaging for SQL Server. Service Broker is used for applications that use a single instance of SQL Server, as well as for applications that distribute work across multiple instances.

Service Broker provides a powerful asynchronous programming model within a SQL Server instance. Database applications typically use asynchronous programming to reduce interactive response time and generally increase application throughput.

Service Broker also provides reliable messaging between SQL Server instances. Service Broker supports developers in designing applications from independent, stand-alone components, the so-called services. Applications that require the functionality provided by these services use messages to interact with the services. Service Broker uses TCP / IP to exchange messages between entities. Service Broker includes features that help prevent unauthorized access over the network and encrypt messages sent over the network. "

This reads extremely exciting for many developers, but unfortunately often a bit far removed from their daily challenges! But that's not the case! So give the service broker a chance! Soon you won't want to be without it anymore! -> I like "Service Broker"!