Administering the Service Broker


The Service Broker is integrated with the database engine, so administrating the Service Broker application is a part of your day-to-day activities. In this part of the chapter, we describe the different tasks involved in administering a database that hosts a Service Broker application. Most administrative tasks are part of the normal administration for your database. Of course, there are some special things you have to do for a Service Broker application.

Installing the Service Broker Application

You learned earlier that the key components to develop a Service Broker application are message types, contracts, queues, services, routes, endpoints, and stored procedures. When a development team provides the installation script for a Service Broker application, those scripts will typically include T-SQL statements. A typical Service Broker application will have an initiator, which resides on a server, and a target, which may reside on the same server in the same database, or in a different database, or even on a different server. Obviously, complex Service Broker applications may involve more than a couple of servers communicating with one another. In general, you should have separate scripts, for installation on the initiator and on the target.

Preparing the Databases

When messages are sent from one database to another, the Service Broker uses a unique identifier to route the messages to the correct database. That unique identifier is called the Service Broker GUID. The Service_Broker_Guid column in the sys.databases catalog view shows the Service Broker identifier for each database. This identifier should be unique across all SQL Server instances on the same network to ensure proper message delivery.

Each database also has a setting for message delivery. The is_broker_enabled column in the sys.databases catalog view shows whether message delivery is enabled or disabled in a database.

Run the following T-SQL statement in the database in which you are going to install the application:

 SELECT is_broker_enabled, Service_Broker_Guid FROM sys.databases WHERE database_id = DB_ID() 

Make sure that the is_broker_enabled bit is set to 1 in order for the database to send and receive messages. If this bit is not 1, you can still install the Service Broker application and even send messages, but you will see an error for that conversation in the sys.transmission_queue catalog view. In this case, the error message in the transmission_status column would be "The broker is disabled in the sender's database." There are two ways you can set this bit to 1. Both of the following commands requires exclusive database access, so if you have users in the database in which you are running the command, you will be blocked by those connections.

 ALTER DATABASE database_name SET NEW_BROKER ALTER DATABASE database_name SET ENABLE_BROKER 

The NEW_BROKER option activates the Service Broker message delivery, creates a new Service Broker identifier for that database, and throws away any existing dialogs and messages. Therefore, you will see a different value in the Service_Broker_Guid column in the sys.databases view than what was shown before.

The ENABLE_BROKER option also activates Service Broker message delivery, but it preserves the Service Broker identifier for that database, so you will see the same value in the Service_Broker_Guid column that was there before.

We will talk about when to use these options, as well as DISABLE_BROKER and ERROR_BROKER_CONVERSATIONS, later in this section. When you create a brand-new database, this bit is set to 1 by default, so Service Broker message delivery is enabled by default in a newly created database.

If you are installing a Service Broker application for which more than one database is involved in the communication on the same instance of SQL Server, you have to set the is_trustworthy_on bit to 1 on the initiator database of the dialog. The is_trustworthy_on column in the sys.databases catalog view shows the bit value for each database. By default this bit is 0 (OFF). You can run the following statement to set the bit to 1:

 ALTER DATABASE database_name SET TRUSTWORTHY ON 

This statement doesn't require exclusive database access. By default, when you restore or attach a database this bit is set to 0 (OFF). Remember that in the first example in this chapter, you set this bit to 1 after creating the databases because that example sends and receives messages between two databases. For information on the TRUSTWORTHY bit, please refer to Chapter 9 or BOL.

Running the Scripts to Install the Application

Now that you have set up the database that is going to host the Service Broker application, review the installation script provided by the development team. Of course, the code review should have happened long before the application was released for system test from development. As a best practice, you should have the queue STATUS OFF by default, and ACTIVATION STATUS OFF by default. As a part of code review with the development team, you should notify them that you want to have these options as a part of the installation script, rather than you munging the installation scripts during deployment.

Another thing to consider is that until installation is completed, do not grant the SEND permission to the queue so that you can complete the internal testing before your application users go wild. You and your team want to ensure that everything looks good before you give the green light. Consider creating a table that lists the objects for which you have to grant permission, and write a stored procedure to grant the permission on those objects. That way, if you have to change the permission on any object involved in your application, you don't have to start searching for those objects. All you have to do is add, change, or remove the object from the table and run the stored procedure to grant the permission. The installation script will only insert the objects in the table where you hold the all the objects for your application and it will not grant any permissions, but you will.

Be sure to keep the installation document up-to-date, because even though your development team has shown due diligence, it is hard to mimic the production environment in your development or test. While you are installing the application, you will certainly find something that is not in the installation document, so you want to make sure that you update the document right away.

Setting Permissions on Service Broker Objects

In the section "Security Considerations for the Server Broker," we explained how to set up transport security using both Windows authentication and certificates. If your application involves more than one instance of SQL Server, you should consider creating a database principal for the application.

CONNECT Permission

Remote authorization is involved when more than one instance of SQL Server is involved in the application. To connect to a remote SQL Server, you have to grant CONNECT permission to the principal connecting to the remote instance.

If the initiator instance is running under a domain account, you have to add that domain account to the target instance and grant CONNECT permission:

 GRANT CONNECT ON ENDPOINT::TargetSSBEndPoint TO [MYDOM\mssdev1] 

You'll need to grant the same permission on the initiator instance, so that the target instance can connect to the initiator.

If the initiator instance is running under a "local system" account, you have to add that machine account to the target instance and grant CONNECT permission:

 GRANT CONNECT ON ENDPOINT::TargetSSBEndPoint TO [MYDOM\InitiatorMachine$] 

You have to do the same thing on the initiator instance so the target instance can connect to the initiator. Keep in mind that in order for the machine account to be authenticated on the remote instance, you need Kerberos on your network. If Kerberos authentication is not available on your network, this method will not work. You would have to use certificates in that case.

If certificates are used for transport authentication, you have to grant the CONNECT permission on the endpoint on the initiator instance to the user who owns the certificate that corresponds to the private key in the remote database.

See the scripts CreateLoginOnInitiator.sql and CreateEndPointWithCertOnInitiator.sql in Sample2.

 GRANT CONNECT ON ENDPOINT::InitiatorSSBEndPoint TO TargetServerUser 

You'll also have to do the same thing on the target instance. See the scripts CreateLoginOnTarget.sql and CreateEndPointWithCertOnTarget.sql in Sample2.

SEND Permission

When you send the message, you send it to a target service. You need SEND permission to send the message to a target service. The database principal that owns the initiating service must have SEND permissions on the target service.

When you do not configure full-dialog security, you have to grant SEND permission on the target service as public, because the target service cannot verify the identity of the initiating service. Operations on behalf of the initiating service run as a member of the fixed database role public in the target database. This is called anonymous dialog security:

 GRANT SEND ON SERVICE::TargetService TO PUBLIC 

When you use FULL dialog security, you can establish the trust by exchanging the certificates that contain public keys, as discussed in the "Dialog Security" section earlier in this chapter. For better security, you should have a different user who has SEND permission to a service than the one who runs the activation on a queue tied to that service.

You don't need either of these steps if your dialog remains in the same SQL Server instance.

RECEIVE Permission

The user executing the BEGIN DIALOG, END CONVERSATION, MOVE DIALOG, RECEIVE, or GET CONVERSATION GROUP command must have RECEIVE permission on the queue associated with the FROM SERVICE. The following statement is an example of how you can grant RECEIVE permission on a RequestQueue:

 GRANT RECEIVE ON dbo.RequestQueue TO [mydom\mssdev1] 

In addition, if you have activation specified on a queue, the user specified in EXECUTE AS in ACTIVATION must have RECEIVE permission on the queue.

EXECUTE Permission

When you create a queue and define the ACTIVATION, you specify the stored procedure name and EXECUTE AS option there. The user you specify there must have permission to execute the stored procedure. Of course, if that stored procedure does some cross-database queries, you have to set those permissions too.

Notice that when you create the stored procedure and you have defined the EXECUTE AS clause with some user, the statements in that stored procedure will run under the defined security principal. Therefore, when a stored procedure is activated, it will be under the security context of the database principal you have specified in the CREATE QUEUE statement, and after that it will change the security context to the database principal you have specified in the CREATE PROCEDURE EXECUTE AS clause.

Managing Service Broker Queues

A Service Broker application that is running well will generally keep the queues fairly small. Make sure you have enough disk space available to handle any problems. The Queue Monitor will try to create enough instances of the activation procedure to process the incoming messages, and the transport layer does a good job of moving messages to their destination as efficiently as possible. What if something goes wrong? If you have a lot of incoming messages in your application, a queue can get pretty big very quickly. Suppose your application has 1,000 messages coming in per second; if your network has to go down or your destination server is down for an hour, the sys.transmission_queue will have 4 million messages in it. You can have an SQL job that monitors for a heavily used queue and a sys.transmission_queue every hour or so and that also sends an e-mail if the messages in the queue exceed a certain threshold.

You can also plan to put the queue on a separate filegroup so it's easy to maintain if it runs out of space. Try putting the queue on a separate filegroup by specifying the filegroup in the CREATE QUEUE statement, or use the ALTER QUEUE statement.

Finally, one of the best things you can do with a queue is to use SELECT * FROM [queuename] to view what's in there.

Poison Message Handling

All transaction messaging systems have to deal with poison messages, which are messages that cannot be processed by the destination service. Any message containing data that can force the message processing transaction to roll back can become a poison message. The Service Broker will detect the repeated rollbacks of messages, and disable the queue to keep a poorly handled message from becoming a poison message. When you design the application, make sure all messages are handled correctly, because only the application knows how to handle the messages - that is, whether to roll back or end the conversation.

Open sample1 to simulate a poison message and how to remove it from the queue. Open the script PoisonMessageSimulate.sql and run it. This will compile the stored procedure you created on RequestQueue again. Part of the code is reproduced here:

 BEGIN TRY    WHILE (1 = 1)    BEGIN       BEGIN TRANSACTION       -- Receive the next available message       WAITFOR       (          RECEIVE TOP(1)                     -- just handle one message at a time             @xmlmessage_body = CASE WHEN validation = 'X'                                     THEN CAST(message_body AS XML)                                     ELSE CAST(N'<none/>' AS XML)                                END            ,@message_type_name = message_type_name            ,@conversation_handle = conversation_handle          FROM dbo.RequestQueue       ), TIMEOUT 3000       -----------------------------------------------------------------------------       -- If we didn't get anything or if there is any error, bail out       IF (@@ROWCOUNT = 0 OR @@ERROR <> 0)       BEGIN          ROLLBACK TRANSACTION          BREAK       END       ELSE       BEGIN          IF @message_type_name = //www.wrox.com/MTCustomerInfo/RequstCustomersCount'          BEGIN             ROLLBACK TRAN          END       END    END --WHILE END here. END TRY 

The preceding script receives the message and rolls it back. This simulates a problem processing the message, so after the rollback the message will go back to the queue. This will be detected by the Service Broker, which will disable the queue.

Run the script ActivateSPOnTarget.sql, which will activate this stored procedure to run when the message arrives. Now open the SendMessageFromInitiator.sql script and run it. It will send the message to RequestQueue in the TargetDB database.

Now open the script RemovePoisonMessage.sql:

 USE TargetDB GO SELECT name, is_receive_enabled FROM sys.service_queues GO ALTER QUEUE RequestQueue WITH  ACTIVATION (STATUS = OFF) GO ALTER QUEUE RequestQueue WITH STATUS = ON GO DECLARE     @message_type_name  nvarchar(256)    ,@xmlmessage_body    xml    ,@MessageFromTarget  varchar(255)    ,@MessageText varchar(255)    ,@conversation_handle UNIQUEIDENTIFIER; RECEIVE TOP(1)    @xmlmessage_body = message_body   ,@message_type_name = message_type_name   ,@conversation_handle = conversation_handle FROM dbo.RequestQueue END CONVERSATION @conversation_handle GO SELECT name, is_receive_enabled FROM sys.service_queues GO 

Because the queue is now disabled, you will see 0 in the is_receive_enabled column in the sys.service_queues catalog view for the RequestQueue.

First, you have to enable the queue, because when queue is disabled, you can not send or receive messages. After the queue is enabled, you receive the first message from the top of the queue, which is the one causing the problem. This assumes that only one message is processed at a time by the application. If this is not the case, the poison message could be any of the messages in the same conversation group as the message at the top of the queue. You can look at the SQL error log for more details on what's causing the message to fail.

You can also subscribe to event called Broker_Queue_disabled, which will send the message to a designed queue when a queue is disabled. Here's how you can do that. Open the script PoisonMessage Notification.sql.

 USE TargetDB GO CREATE QUEUE dbo.PoisonMessageNotification GO CREATE SERVICE ServicePoisonMessageNotification ON QUEUE dbo.PoisonMessageNotification ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]) GO --below notification will send the message to queue --when queue PoisonMessageNotification is disabled. The event --generated is BROKER_QUEUE_DISABLED CREATE EVENT NOTIFICATION PoisonMessageEvent ON QUEUE RequestQueue WITH FAN_IN FOR BROKER_QUEUE_DISABLED TO SERVICE 'ServicePoisonMessageNotification', 'current database' GO 

Now create a poison message on the RequestQueue as we described earlier. Run the following script:

 USE TargetDB SELECT CAST(message_body as xml), * FROM PoisonMessageNotification 

You will notice that an event got generated because the RequestQueue got disabled because of poison message, and a message is put into the PoisonMessageNotification queue, which shows that RequestQueue is disabled. You can do whatever you like with that message in the PoisonMessage Notification queue, such as sending an e-mail. In fact, you can send the message to different server, where you are probably monitoring other servers using routing machanism you learned about earlier.

Also, this example above shows the power of Service Broker where you can receive event notifications in a Service Broker queue and take action on that event. Refer to Books Online to see how event notification works.

Moving Service Broker Applications

Sometime you need to move your application from one server to another. Maybe you got lucky, and your management got you bigger, latest-and-greatest hardware, or some other reason. Usually you have to move the database where you are hosting the Service Broker application to some other instance and many aspects of Server Broker application move with the database. Some aspects of the application must be recreated or reconfigured in the new location.

The stored procedure, users, certificates, and outgoing routes will move with the database. But some of the things you have to reconfigure on the new instance. If your Service Broker application sends messages across instances, you will need to reconfigure the endpoint on new instance. Of course, if the server you are moving the application to already has an endpoint configured, you do not need to do anything because endpoint and transport security apply at the server level, not the database level. Make sure that the endpoint is configured according to your application requirement, though. For example, if your application required transport security and the server you are moving the application doesn't have its endpoint configured with transport security, you will have to reconfigure it.

You must configure logins for any users that the application uses.

Most Service Broker databases have a database master key, but if you are using certificates for transport or dialog security, make sure you preserve the password for the master key you have created because you will need the password when you restore the database in the new location. Remember the script CreateCertOnInitiator.sql and ConfigureDialogSecurityOnInitiator.sql where you created the master key with a password. You have to keep that password in a safe place.

If you have routing configured using CREATE ROUTE, and if you have specified the option called BROKER_INSTANCE in the statement, you have to make sure that after you restore the database on new location you use the ALTER DATABASE database_name SET ENABLE_BROKER statement to preserve the BROKER_INSTANCE. If you don't have that issue, or if you can reconfigure the route with a new initiator and target, you may use ALTER DATABASE database_name SET NEW_BROKER.

If you use NEW_BROKER in the ALTER DATABASE statement, it will clean up all the undelivered messages sitting in sys.transmission_queue and all the conversations in sys.conversation_endpoints view. When you back up the database to restore on the new location, make sure that application has finished processing all the messages, or if you have to process these messages after restoring to the new location, make sure that you do not use ALTER DATABASE database_name SET NEW_BROKER.

Also, you should not change the BROKER_INSTANCE identifier when you attach a database:

  • For recovery purposes.

  • To create a mirrored pair (see Chapter 17).

If you want to keep the same unique identifier for the database but want to ignore all the existing conversations (error them out), you will have to use the following statement.

 ALTER DATABASE database_name SET ERROR_BROKER_CONVERSATIONS 

Also remember that the msdb database should have always the is_broker_enabled bit set to 1, especially if you have any incoming or outgoing Service Broker messages. You can run following statement to check that:

 SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'msdb' 

Make sure that if you have any routes on your destination, to point back to the new server, otherwise you will not get messages back to your new server.

Copying Service Broker Applications

The Development and Testing groups frequently refresh the environment with new data from production. In that case, as a DBA you provide a back up of the database. Most of the things we discussed in the "Moving Service Broker Applications" section apply here as well with some small changes.

Since the backup is a copy of the original application, make sure to change the unique identifier of that database using following statement because Service Broker routing relies on a unique identifier in each database to deliver messages correctly:

 ALTER DATABASE database_name SET NEW_BROKER 

This is the one thing you must not forget to do. Also, you have to change the routing on servers as we described in the previous section.

If you are using the BROKER_INSTANCE option in CREATE ROUTE, we highly recommend that, when you build an application, you store the route name in a table with a server name. (See the following table structure and stored procedure.) You should then write a stored procedure to retrieve the Broker_Instance from the sys.routes table, based on the route name you have stored in the table, to use in your application. That way, when you move or copy your application, you may have to drop and create the route, which may or may not have the same BROKER_INSTANCE, so you do not have to change your application at all.

 CREATE TABLE ServerRoute (  ServerName varchar(50) NOT NULL ,RouteName varchar(100) NOT NULL ); CREATE PROC GetRouteName (  @ServerName varchar(50) ,@Broker_Instance uniqueidentifier OUTPUT ) AS ---------- SELECT @Broker_Instance = Broker_Instance FROM sys.routes r JOIN ServerRoute s   ON s.RouteName = r.RouteName  AND s.ServerName = @ServerName ---------- 

Replacing Expired Certificates

By default, certificates expire and become unusable. If you do not specify the expiration date in the CREATE CERTIFICATE statement, the expiration date is set to one year from when the certificate is created. When the certificate is about to expire, it must be replaced. To avoid disruptions, you must do this in the correct order. If you change the certificate at the endpoint, all connections to endpoints that do not have the new certificate will fail. However, the dialog won't fail, so when the certificate is replaced, it will continue from where it left off. To avoid this, create a new certificate and send the public key to all the remote connections. The remote Service Broker can associate this certificate with a user who represents the SQL Server instance whose certificate will be changed. Once all the remote endpoints have added the certificate to their users, you can change the local endpoint to the new certificate by using the ALTER ENDPOINT command.

Troubleshooting Service Broker Applications

We have learned how the Service Broker works throughout this chapter. In this section, you will learn what to do when it doesn't. You can use DMV, catalog views, and profiler to troubleshoot the problems in Service Broker application.

Design and Code Review

Because the Service Broker is a new technology to both SQL DBAs and developers, we highly recommend that you do code review (if you are not doing one, start now!), and pay close attention to the following issues in the code review process so that any problems will disappear in the earliest stage of your application.

Not Handling All Message Types

You may sometimes wonder why, after sending a message of a particular type, nothing happened. You have probably forgotten to handle that message type in the stored procedure in which you receive the message. In addition to all the application message types, be sure to handle at least the following system message types:

  • [http://schemas.microsoft.com/SQL/ServiceBroker/Error]

  • [http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog]

You can see how to handle these message types in one of the activation stored procedures we have created in Sample1 or Sample2.

Not Dealing with Poison Messages

We discussed poison messages earlier in this chapter. Make sure you handle them; otherwise, the Service Broker will disable the queue and your application will come a halt.

Not Ending the Conversation

Remember that conversations persist in sys.conversation_endpoints, and they are not completely eliminated until both the initiator and target have issued the END CONVERSATION command. When to end the conversation is totally up to your application, but make sure that you do.

Service Broker Commands Outside of an Explicit Transaction

The key advantage of the Service Broker is data integrity. When you receive messages from the queue with the RECEIVE command without a BEGIN TRANSACTION before the RECEIVE, your message will be deleted from the queue, which means that if something goes wrong while you are processing the message, you will not get the message back in the queue because you specified otherwise. If you do not see BEGIN TRANSACTION before the RECEIVE statement, carefully examine the logic to ensure that there is an active transaction. It may happen that in your application you may not need the message after you receive it, so in that case you can avoid BEGIN TRANSACTION before RECEIVE.

Using Catalog Views for Service Broker Metadata

There are many catalog views available to look at the metadata objects created for the Service Broker, and some views to look at the data for Service Broker conversations. The following list shows the views you can use. You can refer to Books Online for each and every column in that metadata.

  • sys.service_message_types

  • sys.service_contracts

  • sys.service_contract_message_usages

  • sys.services

  • sys.service_contract_usages

  • sys.service_queues

  • sys.certificates

  • sys.routes

  • sys.remote_service_bindings

  • sys.service_broker_endpoints

  • sys.tcp_endpoints

  • sys.endpoints

  • sys.databases

Not all of these views are for the Service Broker, but they are related to the Service Broker in some way. The following example queries should give you an idea about how you can use these views.

This query will determine which Service Brokers are enabled in the current SQL Server instance:

 SELECT name, is_broker_enabled, service_broker_guid, is_trustworthy_on FROM sys.databases 

The following query will determine the Service Broker endpoint state, port, connection authentication type, encryption algorithm, and whether forwarding is enabled or not:

 SELECT se.name, te.port, se.protocol, se.state_desc, se.is_message_forwarding_enabled, se.connection_auth as authentication_method, se.encryption_algorithm_desc FROM sys.service_broker_endpoints se JOIN sys.tcp_endpoints te   ON se.endpoint_id = te.endpoint_id 

This query provides all the service names along with their queues, and activation stored procedures and their status:

 SELECT s.name AS Service, sq.Name AS Queue, CASE WHEN sq.is_receive_enabled = 1 THEN 'Yes' ELSE 'No' END AS QueueActive, ISNULL(sq.activation_procedure, 'N/A') ,CASE WHEN sq.is_activation_enabled = 1 THEN 'Yes' ELSE 'No' END  AS Is_Activation_Enabled FROM sys.services s JOIN sys.service_queues sq   ON s.service_queue_id = sq.object_id 

This next query will give you the queue and message types associated with that queue. This is helpful when you write the RECEIVE statement in the queue to make sure that you have covered all the message types for that queue:

 SELECT sq.name AS Queue, mt.Name AS Message_Type_Name FROM sys.service_queues sq JOIN sys.services s   ON s.service_queue_id = sq.object_id JOIN sys.service_contract_usages scu   ON s.service_id = scu.service_id JOIN sys.service_contracts sc   ON scu.service_contract_id = sc.service_contract_id JOIN sys.service_contract_message_usages mtu   ON mtu.service_contract_id = sc.service_contract_id JOIN sys.service_message_types mt   ON mt.message_type_id = mtu.message_type_id GROUP BY sq.name, mt.Name 

Using Catalog Views for Service Broker Data

The catalog views that expose the queue data and conversation endpoints are the most useful views. The two views we use the most are sys.conversation_endpoints and sys.transmission_queue. In addition, to look at the data in the queue, you can always use SELECT * FROM [queuename], as we mentioned earlier.

Take a look at the sys.conversation_endpoints and sys.transmission_queue views. The query shown in Figure 8-10 will give you the state of some of the dialogs in the sys.conversation_endpoints view. We have selected only a few columns here. For all the column descriptions, please refer to Books Online.

image from book
Figure 8-10

In Figure 8-10, the first results set (sys.conversation_endpoints) shows that the first dialog has State_Desc of STARTED_OUTBOUND, which means that the dialog on the initiator side has been started (with BEGIN DIALOG) but no messages have been sent on this dialog yet. The second row in that result set shows that a message has been sent (State_Desc is CONVERSING), but notice in the second result set (sys.transmission_queue) that the message is trying to send to a far service (TargetService) but it has failed because of some connection issue in this case. Conversation_handle is the key to tie messages in the sys.conversation_endpoints and sys.transmission_queue views.

Together, both sys.conversation_endpoints and sys.transmission_queue provide valuable information in troubleshooting problems. Later in this section you will look at the most common errors you get in Service Broker communications, and their solutions using both of these views and the Profiler, described in the following section.

Dynamic Management Views

You can use some Service Broker-related DMVs to get information on activated tasks and queue_monitors:

  • sys.dm_broker_queue_monitors: This DMV provides how many Queue Monitors you have in the current instance of SQL Server. For example, the query shown in Figure 8-11 will tell you when messages are being received in the queue, and how many sessions are waiting in a RECEIVE statement for this queue in the TargetDB database.

  • sys.dm_broker_activated_tasks: This DMV returns a row for each stored procedure activated by the Service Broker in the current instance of SQL Server. The query in Figure 8-12 will give you the number of stored procedures activated in the database TargetDB for each queue. Notice that five stored procedures are activated on TargetQueue.

  • sys.dm_broker_connections: Whenever the Service Broker makes network connections, you will see rows in this DMV. If the connection is idle for 90 seconds, the Service Broker will close the connection; otherwise, it will keep it open so messages can be sent using the same connection.

  • sys.dm_broker_forwarded_messages: When a SQL Server instance is working as a message forwarder, you will see a row for each message in the process of being forwarded on a SQL Server instance.

image from book
Figure 8-11

image from book
Figure 8-12

Using SQL Profiler to View Service Broker Activities

Most of the activities in the Service Broker happen in the background threads, and are not visible to the user. Many times when you issue BEGIN DIALOG and SEND the message, the Service Broker does it successfully but you later realize that the message never reached the destination! You are left wondering why you are not getting any errors when you send the message. The message is on its way, but because you have forgotten to configure the route properly, or forgot some permissions to set properly, the Service Broker keeps trying to send the message repeatedly until it times out or you end the dialog. The Service Broker guarantees reliable message delivery, so it will keep trying in spite of failure and misconfigurations, which are required when you implement a highly reliable network application; but you are not going to get the errors reported back to your application when things go wrong. You have to determine for yourself what's going on behind the scenes. In most cases the views described in the preceding section provide you with the necessary information to troubleshoot the problem, but sometime you will need more information to diagnose an issue, and that's where you use traces.

The Service Broker has a large number of traces that provide enough information about internal performance to enable you to diagnose the problem. Figure 8-13 shows the Service Broker Profiler events.

image from book
Figure 8-13

Figure 8-14 shows the trace of an event in which a message cannot be delivered to the remote server because the account that connects to the remote server doesn't have permission to connect on this endpoint.

image from book
Figure 8-14

This trace was run on the target instance, and the message here shows that the remote login Redmond\mssdev1 does not have connect permission on the endpoint of the target machine. In Figure 8-15, you can also see that the conversation is sitting on sys.transmission_queue on the initiator, so the Service Broker will keep trying to send the message until it times out, the conversation is ended, or you fix the problem. Notice in Figure 8-14 the multiple error messages regarding "connection handshake failed" because the Service Broker keeps trying to send the message. The first retry is after four seconds, and if that does not succeed, the Service Broker resends exponentially up to 64 seconds (4, 8, 16, 32, and 64 seconds). When the Service Broker reaches the maximum resend timeout (64 seconds), it will try once every 64 seconds (about once a minute).

image from book
Figure 8-15

As you can see, the Profiler is a great tool for determining what's going in the background. The next several sections describe the most common Service Broker problems and what you can do to resolve them.

No Return Route

You have sent the message and you have verified that it reached the target, but you are expecting something from the target and that message never comes back to the initiator. If you trace the Message undeliverable event you will see continuous duplicate message events, as shown in Figure 8-16.

image from book
Figure 8-16

The issue is that you have correctly defined the route to the target so that messages are delivered from the initiator to the target, but you have not defined the route back to the initiator on the target. In sample2, if you drop the route with DROP ROUTE in the TargetDB database, and then send the message from the initiator, you will be able to see this error in the Profiler if you connect it to your target instance.

Dropped Messages

When the Service Broker cannot process a message, it will drop it. Some of the reasons why messages are dropped are as follows:

  • The certificate in the message header doesn't exist or it doesn't have the required permission, or public doesn't have send permission on the service.

  • The service that sent the messages doesn't exist.

  • The message or header is corrupt or has been altered.

  • The XML message doesn't validate against the schema for the message type.

If you notice a lot of messages are piling up in sys.transmission_queue, you should use SQL Profiler to see why messages are being dropped.

Conversation Endpoint is Not in a Valid State for SEND

As soon as you send the message it will be placed in the queue. If for some reason a message cannot be placed in the queue, you will see an error like the following:

 Msg 8429, Level 16, State 1, Line 14 The conversation endpoint is not in a valid state for SEND. The current endpoint state is 'ER'. 

The most common cause of this error is that the conversation lifetime has expired, but it also happens when the conversation is ended by the far end, or by some other error. If you want to produce this error, then run the following script after you've set up sample1 or sample2:

 USE InitiatorDB GO DECLARE @Conversation_Handle UNIQUEIDENTIFIER        ,@SendMessage xml        ,@MessageText varchar(255) SET @MessageText =  'Request From Server: \\' + @@SERVERNAME                   + ', DATABASE: ' + DB_NAME()                   + ': ** Please send the total number of customers.' SET @SendMessage = N'<message>'+ N'<![CDATA[' + @MessageText + N']]>' + N'</message>' BEGIN DIALOG CONVERSATION @Conversation_Handle FROM SERVICE [InitiatorService] TO SERVICE 'TargetService' ON CONTRACT [//www.wrox.com/CTGeneralInfo/ProductCustomer] WITH ENCRYPTION = OFF     ,LIFETIME = 5 WAITFOR DELAY '00:00:07'; BEGIN TRY; SEND ON CONVERSATION @Conversation_Handle MESSAGE TYPE [//www.wrox.com/MTCustomerInfo/RequstCustomersCount] (@SendMessage); END TRY BEGIN CATCH    SELECT ERROR_NUMBER() AS ErrorNumber          ,ERROR_MESSAGE() AS ErrorMessage          ,ERROR_SEVERITY() AS ErrorSeverity          ,ERROR_LINE() AS ErrorLine END CATCH 

This script starts the dialog, but does not send the message until after seven seconds have passed. You specified the dialog lifetime in the LIFETIME clause to be five seconds, so you are not in a valid state to send the message on the expired conversation_handle.

Queue Disabled

If your application suddenly stops working, the first thing you want to check is queue status, using the following query:

 SELECT name, CASE WHEN is_receive_enabled = 1 THEN 'Yes' ELSE 'No' END AS QueueEnabled FROM sys.service_queues 

You can reenable the queue with the following query:

 ALTER QUEUE ReceiveAckError WITH STATUS = ON 

In addition, make sure that activation is enabled if you have it. After you enable the queue, check the queue status again. If it is disabled again, you have a poison message on the queue. Read the "Poison Message Handling" section earlier in the chapter for details about detecting and handling poison messages.

Service Broker Is Disabled in a Database

If the Service Broker is disabled in the database, your application can still send messages but they will not be delivered. This can be caused by restoring or attaching a database without the Service_Broker_Enabled option. Any message will sit in the sys.transmission_queue until the Service Broker is enabled. Using the following, you can check whether the database is enabled for the Service Broker or not:

 SELECT name, CASE WHEN is_broker_enabled = 1 THEN 'Yes' ELSE 'No' END AS Service_Broker_Enabled FROM sys.databases 

See the "Preparing Databases" and "Moving Service Broker Applications" sections of this chapter for more details.

Could Not Obtain Information about Windows NT Group/User

When you log in under your domain account, if you are not connected to the domain, you will get the following error when you try to send the message:

 Msg 15404, Level 16, State 19, Line 15 Could not obtain information about Windows NT group/user 'MYDOM\ketan', error code 0x54b. 

Whenever SQL Server needs to authorize a user, it needs to connect to a domain controller if SQL Server doesn't have enough information cached for authorization. The only way to correct this problem is to connect to your domain.

Duplicate Routes

Suppose half the messages you send are lost somewhere. Maybe you forgot to drop a route that is not required and messages are being sent to that. For example, when you refresh your environment with test data, you have the old route, which is pointing to ServerX. After the refresh, you add another route to ServerY and forget to drop the route to ServerX. If the Service Broker finds multiple routes to the same service, then it assumes that you want to do load balancing and it divides the dialogs between the two servers. In this case, half the dialogs will go to ServerX even though you don't want them to go there. The solution is to drop the unnecessary route. You can also specify the BROKER_ID in the BEGIN DIALOG or CREATE ROUTE statement to prevent this, but as long as you are careful about dropping unnecessary routes, you will be fine.

Activation Stored Procedure Is Not Being Activated

Frequently, messages are not picked up from the queue, even though you know that you have defined the activation stored procedure on that queue. The first thing you should check is whether the activation status is ON or not using the following query:

 SELECT name, CASE WHEN is_activation_enabled = 1 THEN 'Yes' ELSE 'No' END AS ActivationEnabled FROM sys.service_queues 

You can enable the activation using the following query on a RequestQueue:

 ALTER QUEUE RequestQueue WITH ACTIVATION (STATUS = ON) 

If activation is enabled, it's possible that you don't have correct permission on the stored procedure for the user you have specified in the EXECUTE AS clause in the CREATE QUEUE statement. Look in the SQL error log, and if that is the case you will see an error like this one:

 The activated proc [dbo].[CheckResponseFromTarget] running on queue InitiatorDB.dbo.ReceiveAckError output the following:  'EXECUTE permission denied on object 'CheckResponseFromTarget', database 'InitiatorDB', schema 'dbo'.' 

Grant proper permission on the stored procedure to avoid this error.

Performance

If you think that the Service Broker is not performing as well as you expect, look for following things. Keep in mind that the Service Broker uses SQL Server for all data storage, so all the things that affect database performance affect Service Broker performance. Don't worry about the RECEIVE message from the queue, because that is a very well tuned part.

Suppose you send a bunch of messages to a queue and you have defined an activation stored procedure on that queue with MAX_QUEUE_READERS = 10, but you still see only one instance of that stored procedure running. That's happening because all those messages are on the same dialog. In that case, activation would not start an additional instance of the activation stored procedure because all of the messages have to be processed serially. If you want parallelism, you need more than one dialog. You have to use dialogs effectively. If you think that some tasks have to be processed serially, then group them in one dialog. Don't send the messages you want to be processed in parallel on the same dialog.

Not ending the conversation is another performance problem. You know that as soon as you issue BEGIN DIALOG an entry will be made to the sys.conversation_endpoints catalog. This will remain until you end the dialog with END CONVERSATION on both ends of the conversation. Do not assume that specifying LIFETIME in BEGIN DIALOG is sufficient to clear the rows from that catalog. If you forgot END CONVERSATION, you may see millions of rows in the sys.conversation_endpoints view if you have a very active service. Conversations are cached for performance, so millions of expired and useless conversations waste a lot of cache space. If there is not enough cache size available, these conversations are swapped out into tempdb, so tempdb will grow too. Too many conversations are not good because they use a lot of cache size, and too few conversations are not good either because you will get less parallelism, so make sure that you use conversation wisely in your application to get the best possible performance. We believe that the Service Broker excels in many areas, including sending and receiving the messages in the queue, how quickly activation is initiated, and receiving from the queue.



Professional SQL Server 2005 Administration
Professional SQL Server 2005 Administration (Wrox Professional Guides)
ISBN: 0470055200
EAN: 2147483647
Year: 2004
Pages: 193

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net