What do you use Service Broker for?
SQL Server has a system called Service Broker which is a queued, asynchronous system enabling calls to be made to procs or simply messages posted onto a queue which can be removed at will and processed. I have seen various people ask when you would use service broker, with its complexity, over a simple call to a stored proc.
The two key words here are queue and asynchronous. A queue ensures that things are processed in order so if, for example, you are running an important financial system, presumably various things must happen in order even if there are thousands happening all the time. In other words if you have 100s of people making changes, you might want to ensure that they are placed in a queue as a single point of entry and which will ensure that the first-come is first-served. You have an amount of control over the queue and whether messages are retained, the way in which the conversation is carried out etc but it is a way of controlling parallel environments.
The second and perhaps most important is the asynchronous nature of the service broker. Why wait for something that you do not need to wait for? If you are sending a letter to someone, you don't post it and then wait at the post box until the recipient tells you they have received it. You either don't care or if you do, you set up a converation with the recipient who can tell you if/when it arrives. Many scenarios exist like this in computing. If I want to cancel my credit card account, once I have requested the close, the Customer Services agent should not have to wait for the whole process to execute, simply to start the ball rolling and then assume it has worked unless the other end fails and reports back. This is especially important if the sequence is fired from a user interface which is waiting for the code to complete before updating the screen.
SQL Server allows you to invoke a stored proc on receipt of a message into the queue which might be useful but you can also leave the queue to fill and service it on a job every so many hours which might help avoid server load or meet various Quality of Service (QOS) requirements (such as all accounts are always updated at 6pm).
My general rule of thumb for software is to keep it simple, although you can sometimes guess how intensive an operation is and go straight for something like service broker, our assumptions can be wrong (a view with 10000 rows runs in a few seconds whereas another seemingly more simple view takes 5 minutes) so best to approach it with a 'if it aint broke, don't fix it' since it is reasonably easy to retrofit Service Broker in place of a stored proc at a later date as needed.
The two key words here are queue and asynchronous. A queue ensures that things are processed in order so if, for example, you are running an important financial system, presumably various things must happen in order even if there are thousands happening all the time. In other words if you have 100s of people making changes, you might want to ensure that they are placed in a queue as a single point of entry and which will ensure that the first-come is first-served. You have an amount of control over the queue and whether messages are retained, the way in which the conversation is carried out etc but it is a way of controlling parallel environments.
The second and perhaps most important is the asynchronous nature of the service broker. Why wait for something that you do not need to wait for? If you are sending a letter to someone, you don't post it and then wait at the post box until the recipient tells you they have received it. You either don't care or if you do, you set up a converation with the recipient who can tell you if/when it arrives. Many scenarios exist like this in computing. If I want to cancel my credit card account, once I have requested the close, the Customer Services agent should not have to wait for the whole process to execute, simply to start the ball rolling and then assume it has worked unless the other end fails and reports back. This is especially important if the sequence is fired from a user interface which is waiting for the code to complete before updating the screen.
SQL Server allows you to invoke a stored proc on receipt of a message into the queue which might be useful but you can also leave the queue to fill and service it on a job every so many hours which might help avoid server load or meet various Quality of Service (QOS) requirements (such as all accounts are always updated at 6pm).
My general rule of thumb for software is to keep it simple, although you can sometimes guess how intensive an operation is and go straight for something like service broker, our assumptions can be wrong (a view with 10000 rows runs in a few seconds whereas another seemingly more simple view takes 5 minutes) so best to approach it with a 'if it aint broke, don't fix it' since it is reasonably easy to retrofit Service Broker in place of a stored proc at a later date as needed.