April 25, 2006 at 10:26 pm
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sdedhiya/thesqlserverpostalservice.asp
Regards,
Sachin Dedhia
May 8, 2006 at 8:15 am
Possible Disadvantages?
Performance impact?
Typical misuse of queues, (SQL 2005 as well as existing Win message queuing)?
Trade-offs are a natural part of engineering a solution.
[font="Arial"]Clifton G. Collins III[/font]
May 8, 2006 at 11:45 am
(Updated)
I got an error on the second statement:
ALTER
DATABASE MyDatabase SET ENABLE_BROKER
GO
The database does have Trustworthy set to True. This database was upgraded by restore process from 2000 database. 2 databases that were created in 2005 don't have this problem.
When I try to run the above statement on the database that was restored /upgraded from 2000, I am getting a message:
Msg 15581, Level 16, State 3, Line 1
Please create a master key in the database or open the master key in the session before performing this operation.
Msg 28053, Level 16, State 2, Line 1
Service Broker could not upgrade conversation session keys in database 'DBA' to encrypted format (Error: 15581). The Service Broker in this database was disabled. A master key is required to the database in order to enable the broker.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Regards,Yelena Varsha
May 9, 2006 at 4:37 am
Hi Yelena,
The ALTER DATABASE did work on the Northwind database which I restored from SQL Server 2000. My SQL Server 2005 is update with SP1.
Can you provide inputs on the following:
1. Is SP1 installed?
2. After the restore of the database from 2000, have you set any options like encryption, or created any certificates, keys etc?
Regards,
Sachin
Regards,
Sachin Dedhia
May 9, 2006 at 8:48 am
Hello Sachin,
To answer your questions, No SP1, Created nothing. This is probably the reason that it has to have a master key or something. This is OK. I am not going to implement Broker on the upgraded databases.
Another question:
I was following the example and it successfully created message types, contracts, queues, services in both databases. I tried to send a message using your code and I can see the messages in the InitiatorQueue with the status of 3 BOL says it means 3=Retained sent message. I did not have any messages in the Target queue and I did not have any error messages. When I run code that sends/receives messages I am getting only one Result Window 'Messages' which contains nothing. The Initiator queue contains nothing too. The send message sits in the Initiator queue.
Did I miss something with the configuration? Should I enable something on the Server level?
Regards,Yelena Varsha
May 10, 2006 at 1:51 am
Hi Yelena,
There is nothing that you have missed something or you need to do something. I hope the following explanation is good enough for you question.
Current Senario:
When you fisrt send the message you see the 'request message' in the InitiatorQueue with the status of 3 which means the message has been retained.
USE InitiatorDB
GO
SELECT CAST(message_body AS nvarchar(MAX)) AS msg_body, * FROM [InitiatorQueue]
You also see the 'request message' in the TargetQueue with the status of 1 which means a message has received.
USE TargetDB
GO
SELECT CAST(message_body AS nvarchar(MAX)) AS msg_body, * FROM [TargetQueue]
We need to respond to the received message. When the response is send you do not see the 'response message' in the TargetQueue. However, you will see the response message in the InitiatorQueue with the status of 1 which means the message has been received.
If you notice we have 'created the queues by turning on the retention setting'. Which means messages sent or received on conversations using this queue are retained in the queue.
But you may argue that the 'reponse message' is not retained in the TargetQueue. This is because all messages sent or received on conversations using the queue are retained in the queue 'until the conversations have ended'. While sending the reponse we have ended the conversation with END CONVERSATION therefore you do not see any message in the InitiatorQueue.
New Senario:
In the receving message code comment the 'END CONVERSATION...'
Now, send a request and then send a response. When the response is send you will still see the 'request message' in the TargetQueue but now the status will be 0. This is because for messages returned by the RECEIVE command, the status is always 0.
You will also see a 'response message' in the TargetQueue with the status of 3 which means the message has been retained.
Thus, if RETENTION = ON, all messages sent or received on conversations using this queue are retained in the queue "until the conversations have ended".
The 'message retention' feature allows you to retain messages for auditing purposes, or to perform compensating transactions if an error occurs. If not specified, the retention setting defaults to OFF.
Using message retention can reduce performance. This is because message retention increases the number of messages in the queue for active conversations and increases the amount of work that SQL Server performs when sending a message. Use this setting only if it requires that the application retain the exact messages sent and received.
HTH.
Regards,
Sachin
Regards,
Sachin Dedhia
May 10, 2006 at 1:57 am
Hi Clifton,
There is some performance hit with 'Message retention' as explained above.
Regards,
Sachin
Regards,
Sachin Dedhia
May 10, 2006 at 12:36 pm
Hello Sachin,
Thanks a lot for the explanation, I do understand the process now, it was VERY helpful.
But I dicovered that my problem was due to security. I told you I did not have any error messages. It was not the case. I did not get any messages in the Messages tab or Results tab. But after I commented out END CONVERSATION line I did not get any messages in the TargetQueue too. So I took a look what exactly I had in the InitiatorQueue. I did have my output to Grid so I could see only the beginning of the msg_body. I new first lines were some XML before the actual lines of messages, so I copied and pasted into text editor. The mesages were:
?<?xml version="1.0"?><Error xmlns="-8494You'>http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8494</Code><Description>You do not have permission to access the service 'ResponseService'.</Description></Error>
I started to look up what do I have on the Broker Security. I also enabled Cross Database Ownership Chaining and changed ownership of both databases to SA and logged in as SA (connected my MS as SA). I could not access the service. So in following the article:
http://msdn2.microsoft.com/en-us/library/ms166036.aspx
GRANT
CONTROL ONSERVICE :: ResponseService TO PublicAnd it Finally Worked! I was able to see my sent and response messages in the TargetQueue.
Couple of comments:
First, the GRANT statement is case-sensitive, when I typed Responseservice it could not find it. I install all my servers as AS_CI (Accent Sensitive Case Insensitive) but I do know that XML stuff is usually case sensitive
Second, I worked as SA with a DBO SA on both databases! I HAVE to have permissions, and I dont! I also tried as another sysadmin login, does not matter. What happened to the concept of the server administration?
Third, why would error messages go into the table (into queue)? They should be displayed as we used to or go into the special table (ErrorTable for example) for auditing.
Thanks again,
Yelena
Regards,Yelena Varsha
May 13, 2006 at 2:52 am
Hi Yelena,
I apologies for the delay in the response.
For your first comment, the GRANT statement is not case-sensitive, however the Service Broker's service names, contract names and message type names are case-sensitive. Service Broker is designed to let services in instances with different collation configuration communicate easily and effectively. The database that hosts a service that sends a message might not use the same collation as the database that hosts the service that receives the message. To overcome the barrier of different collations, the Service Broker uses a byte-by-byte comparision to match the service names, contract names, and message type names.
For you second comment, it would be great if you could provide more information. Could you also let me know the the owner of the Service Broker objects created. Is is 'dbo'?
For you third comment regarding error messages, say a conversion between two services fails for some reason. The program may require a compensation transaction or require to record the error occurred or require both or require none. Based on the requirement the program peforms a compensation transaction or logs the error to a logging table (ErrorTable for example) or does both or does nothing and ends the conversation.
HTH.
Regards,
Sachin
Regards,
Sachin Dedhia
May 17, 2006 at 5:46 am
Hi Clifton,
With Service Broker, there are areas that can have a negative performance impact and can be tuned with few additional considerations.
WAITFOR clause:
Without WAITFOR, the RECEIVE and GET CONVERSATION GROUP statements return immediately when there are no messages available on the queue. Depending on the implementation, the procedure may loop back through the statements or procedure may exit only to be reactivated. Both of this would consume more resources than simply continuing to run.
If an application runs continuously as a background service, do not specify a time-out in the WAITFOR statement. This handles unpredictable intervals beetween messages. If an application is activated by Service Broker, or runs as a scheduled job, specify a short time-out, for example, 500 milliseconds. Thus, an activated application that exits after a short time-out does not consume resources when there are no messages to process.
Conversation group:
Process multiple messages for a conversation group in the same transaction. However, only process messages for a single conversation group in a given transaction. This helps ensure that more than one instance of the application can process messages, even when the number of conversation groups is relatively small.
Message retention:
Avoid using message retention. Maintaining a separate log table that saves the most important information from a message improves performance.
End conversations:
End conversations when the task completes. Service Broker maintains state for each conversation. Although the amount of state for a particular conversation is small, an application that does not end conversations may suffer reduced performance over time.
Transaction duration:
Keep transactions short. For example, if the conversation pattern for the service involves a large number of messages on the same conversation group, limiting the number of messages processed in each transaction may improve overall throughput.
The overall performance of the Service Broker is determined by two factors that can be monitored
i) The number of messages arriving within a specified period of time
ii) The speed with which the application processes each message.
HTH.
Regards,
Sachin Dedhia
August 9, 2010 at 3:25 pm
Can the Service Broker Target(consumer) be an non SQL Server ? like an application server? Also can the service broker send messages to two seperate target servers?
Thanks
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply