Distributed processing "farm" using Broker for middleware.

In our current project, we are attempting to use Broker for the middleware for a "queuing" solution that helps in throttling & load-balancing incoming messages. These messages could potentially be either long-running jobs or simply an incoming flood of messages received from our integration web services that are called upon via BizTalk.

For the posting of messages onto the "to do" queue, we are implementing the "fire & forget" pattern that has been discussed here previously and on Remus' blog. This aspect of the design seems to work fine for us. Currently we have this setup where activation is occuring on the target queue, but we don't want to hold open a thread there on the server while waiting for our synchronous calls to various web-services on the processing "farm" to complete.

The next evolution of our design is to try and move activation from off of the primary SQL cluster itself (i.e. activation is currently happening on the clustered SQL boxes) onto the individual processing nodes. For this model, we are looking at using SQL Express on each of the nodes as has been suggested here on the forums for other similar scenarios.

For resiliance to any node failures, we do not want to "route" the messages to the individual nodes hosting their own queues but rather have those nodes do a "read" from the primary queue and do the per-message processing and performing either a committed EndDialog of EndDialogWithError based on the success of processing each task/message.

To invoke the processing on each of the nodes, we need some form of mechanism to send a "wake up" and do the "reads" since no message is being sent to the node itself for any form of activation based on a queue that receives the actual "job". On the same hand, we are considering having a "wake up" queue on the nodes where a simple "wake up" message could be sent to all nodes/queues and then activation on those queues would then invoke the processing "loop" for each node.

My question is how to best establish this "wake up" call to each node. I think I've read about a queue that is internal to Broker itself that receives a message when new items are received in any queue. My initial thought is to put activation on that queue and have a procedure that sends the "wake up" to each of the nodes in our processing farm.

I am looking for any input where others have attempted to solve this type of problem with Broker.

Regards,

Bradley A. Hehe

Hi Brad!

Do you already had a look at the external activation mechanism available in Service Broker? With external activation you can subscribe to the event when a new message arrives at a queue. Then you can activate an external application (like a console application or a WinForms application) that processes the original message from the queue outside of SQL Server within his own process. Doing the processing of a message in an external process should solve your described problem.

You can find here (http://www.gotdotnet.com/codegallery/codegallery.aspx?id=9f7ae2af-31aa-44dd-9ee8-6b6b6d3d6319) a sample (the ExternalActivator sample) which implements the whole infrastructure needed for this task and also some documentation about setting up this sample.

HTH

Klaus Aschenbrenner
www.csharp.at
www.sqljunkies.com/weblog/klaus.aschenbrenner

|||

Brad Hehe wrote:

For resiliance to any node failures, we do not want to "route" the messages to the individual nodes hosting their own queues but rather have those nodes do a "read" from the primary queue and do the per-message processing and performing either a committed EndDialog of EndDialogWithError based on the success of processing each task/message.

I have a little trouble undertanding the reasoning here. What is the role of the SQL Express instance on each node? If you want to do reads from the node using TDS (i.e. ADO.Net or OLEDB), then why do you need a SQL Express instance on the node? If you want involve the SQL Express instances into linked servers (distributed queries) or other forms of DTC, then you will be way better to route the messages instead.

HTH,
~ Remus

|||

As for ExternalActivator, I'll go back and examine that one again. I was under the impression that I could only have 1 external "activator" listening to the events. In my case, I'd have a "farm" of "external" processes listening for events to process.

As for Remus's reply, I was looking to solve the problem without developing anything beyond a "procedure" for activation on the processing node itself. I was trying to avoid the creation of an "service" (i.e. Windows Service) or some similar mechanism polling the main database cluster for jobs. I was hopign to send some form of "wake up" or "heart beart" to the processing nodes to tell them to go out and find work & process through it..... Something more event driven than polling based.

|||Unfortunately, there's no way for a procedure to read a message from a queue on a remote machine. I think you really need to have your processing nodes open a connection to the database where the queue is and receive messages. This is actually much simplere than any of the solutions suggested so far. you just write a program that opens a connection, receives messages from the queue, processes then and then receives more messages. If you need more processing, just start up more jobs. If one of the processing nodes goes down, the connections will be broken, the transactions will roll back, the messages will appear on the queue again and another node can pick them up. This is absloutely the most efficient way to do load balancing because the processing nodes only pull as much work off the queue as they can handle. As soon as their done, they pull more, if there's no work left, they wait on a receive so no resources are wasted. If something goes wrong with a processing application or node, the transaction rolls back and another node or application picks it up. If instead you pull messages out into a local database on each processing node to process them, if one of the processing nodes goes down, it takes the messages with it and they don't get handled until the node comes back up. They aren't lost but they are delayed.|||

I follow you there Roger. Let me run this past you then.

The folks who preceded me on this effort had went about their selection of Broker to avoid going down the road of developing a "service" on each node that "polls" a simple table sitting on the server. Essentially, your proposed solution is doing the same, correct? It is "polling" a broker queue rather than a table with the benefits of conversation grouping, sequencing, etc. All of those are "gains" from the use of Broker over a table. Additionally there is this "WAITFOR" syntax being used. It is this "WAITFOR" syntax that I'm thinking justifies use of broker over the traditional polling approach.

From a resource friendliness view of the world, is Broker (A) the same in server resource utilization or (B) more efficient -- than the traditional approach of polling/updating a "jobs" table. Being that processing cycles / IO on your cluster are one of your most expensive resources to implement, we are looking to ensure we are using those resources wisely. The previous thought process was that polling would be hammering on any already very busy database.

|||Polling is efficient only if there's always something in the table to select each time. A select that returns nothing is a waste of resources so unless the incoming rate is at least as large as your maximum processing rate,polling is inefficient. WAITFOR aleviates this by not returning until there is something to return. Service Broker is optimized for pulling a message from the queue using the least possible resources. Because Service Broker is part of the database, it is able to do things directly that an external application can't.sql

Distributed processing "farm" using Broker for middleware.

In our current project, we are attempting to use Broker for the middleware for a "queuing" solution that helps in throttling & load-balancing incoming messages. These messages could potentially be either long-running jobs or simply an incoming flood of messages received from our integration web services that are called upon via BizTalk.

For the posting of messages onto the "to do" queue, we are implementing the "fire & forget" pattern that has been discussed here previously and on Remus' blog. This aspect of the design seems to work fine for us. Currently we have this setup where activation is occuring on the target queue, but we don't want to hold open a thread there on the server while waiting for our synchronous calls to various web-services on the processing "farm" to complete.

The next evolution of our design is to try and move activation from off of the primary SQL cluster itself (i.e. activation is currently happening on the clustered SQL boxes) onto the individual processing nodes. For this model, we are looking at using SQL Express on each of the nodes as has been suggested here on the forums for other similar scenarios.

For resiliance to any node failures, we do not want to "route" the messages to the individual nodes hosting their own queues but rather have those nodes do a "read" from the primary queue and do the per-message processing and performing either a committed EndDialog of EndDialogWithError based on the success of processing each task/message.

To invoke the processing on each of the nodes, we need some form of mechanism to send a "wake up" and do the "reads" since no message is being sent to the node itself for any form of activation based on a queue that receives the actual "job". On the same hand, we are considering having a "wake up" queue on the nodes where a simple "wake up" message could be sent to all nodes/queues and then activation on those queues would then invoke the processing "loop" for each node.

My question is how to best establish this "wake up" call to each node. I think I've read about a queue that is internal to Broker itself that receives a message when new items are received in any queue. My initial thought is to put activation on that queue and have a procedure that sends the "wake up" to each of the nodes in our processing farm.

I am looking for any input where others have attempted to solve this type of problem with Broker.

Regards,

Bradley A. Hehe

Hi Brad!

Do you already had a look at the external activation mechanism available in Service Broker? With external activation you can subscribe to the event when a new message arrives at a queue. Then you can activate an external application (like a console application or a WinForms application) that processes the original message from the queue outside of SQL Server within his own process. Doing the processing of a message in an external process should solve your described problem.

You can find here (http://www.gotdotnet.com/codegallery/codegallery.aspx?id=9f7ae2af-31aa-44dd-9ee8-6b6b6d3d6319) a sample (the ExternalActivator sample) which implements the whole infrastructure needed for this task and also some documentation about setting up this sample.

HTH

Klaus Aschenbrenner
www.csharp.at
www.sqljunkies.com/weblog/klaus.aschenbrenner

|||

Brad Hehe wrote:

For resiliance to any node failures, we do not want to "route" the messages to the individual nodes hosting their own queues but rather have those nodes do a "read" from the primary queue and do the per-message processing and performing either a committed EndDialog of EndDialogWithError based on the success of processing each task/message.

I have a little trouble undertanding the reasoning here. What is the role of the SQL Express instance on each node? If you want to do reads from the node using TDS (i.e. ADO.Net or OLEDB), then why do you need a SQL Express instance on the node? If you want involve the SQL Express instances into linked servers (distributed queries) or other forms of DTC, then you will be way better to route the messages instead.

HTH,
~ Remus

|||

As for ExternalActivator, I'll go back and examine that one again. I was under the impression that I could only have 1 external "activator" listening to the events. In my case, I'd have a "farm" of "external" processes listening for events to process.

As for Remus's reply, I was looking to solve the problem without developing anything beyond a "procedure" for activation on the processing node itself. I was trying to avoid the creation of an "service" (i.e. Windows Service) or some similar mechanism polling the main database cluster for jobs. I was hopign to send some form of "wake up" or "heart beart" to the processing nodes to tell them to go out and find work & process through it..... Something more event driven than polling based.

|||Unfortunately, there's no way for a procedure to read a message from a queue on a remote machine. I think you really need to have your processing nodes open a connection to the database where the queue is and receive messages. This is actually much simplere than any of the solutions suggested so far. you just write a program that opens a connection, receives messages from the queue, processes then and then receives more messages. If you need more processing, just start up more jobs. If one of the processing nodes goes down, the connections will be broken, the transactions will roll back, the messages will appear on the queue again and another node can pick them up. This is absloutely the most efficient way to do load balancing because the processing nodes only pull as much work off the queue as they can handle. As soon as their done, they pull more, if there's no work left, they wait on a receive so no resources are wasted. If something goes wrong with a processing application or node, the transaction rolls back and another node or application picks it up. If instead you pull messages out into a local database on each processing node to process them, if one of the processing nodes goes down, it takes the messages with it and they don't get handled until the node comes back up. They aren't lost but they are delayed.|||

I follow you there Roger. Let me run this past you then.

The folks who preceded me on this effort had went about their selection of Broker to avoid going down the road of developing a "service" on each node that "polls" a simple table sitting on the server. Essentially, your proposed solution is doing the same, correct? It is "polling" a broker queue rather than a table with the benefits of conversation grouping, sequencing, etc. All of those are "gains" from the use of Broker over a table. Additionally there is this "WAITFOR" syntax being used. It is this "WAITFOR" syntax that I'm thinking justifies use of broker over the traditional polling approach.

From a resource friendliness view of the world, is Broker (A) the same in server resource utilization or (B) more efficient -- than the traditional approach of polling/updating a "jobs" table. Being that processing cycles / IO on your cluster are one of your most expensive resources to implement, we are looking to ensure we are using those resources wisely. The previous thought process was that polling would be hammering on any already very busy database.

|||Polling is efficient only if there's always something in the table to select each time. A select that returns nothing is a waste of resources so unless the incoming rate is at least as large as your maximum processing rate,polling is inefficient. WAITFOR aleviates this by not returning until there is something to return. Service Broker is optimized for pulling a message from the queue using the least possible resources. Because Service Broker is part of the database, it is able to do things directly that an external application can't.

distinct columns

Please - please -please someone help me with this one:

current table looks like this:

Act Num City

1 New York

1 Tampa

2 Denver

3 LA

3 Tampa

3 New York

Im trying to (have to ) create a table that looks like this:

Act Num City 1 City 2 City 3 --City 10

1 New York Tampa Null Null

2 Denver null null null

3 LA Tampa New York null

I new to this and am at wits end, can some one help?If you are using SQL 2005, refer to Books Online, Topic: Rotating Columns|||

Here you go..

Code Snippet

Create Table #cities (

[Act Num] Int,

[City] Varchar(100)

);

Insert Into #cities Values('1','New_York');

Insert Into #cities Values('1','Tampa');

Insert Into #cities Values('2','Denver');

Insert Into #cities Values('3','LA');

Insert Into #cities Values('3','Tampa');

Insert Into #cities Values('3','New_York');

Select

*

,Identity(int,1,1) as RowId

INTO

#Data

From

#cities

Select

[Act Num]

,Max(Case RowId When 1 Then City End) [City1]

,Max(Case RowId When 2 Then City End) [City2]

,Max(Case RowId When 3 Then City End) [City3]

,Max(Case RowId When 4 Then City End) [City4]

,Max(Case RowId When 5 Then City End) [City5]

,Max(Case RowId When 6 Then City End) [City6]

,Max(Case RowId When 7 Then City End) [City7]

,Max(Case RowId When 8 Then City End) [City8]

,Max(Case RowId When 9 Then City End) [City9]

,Max(Case RowId When 10 Then City End) [City10]

From

(

select

[Act Num]

,[City]

,RowId - (Select Min(RowId)-1 From #Data Sub Where Sub.[Act Num]=Main.[Act Num]) as RowId

From

#Data Main

) as data

Group By [Act Num]

|||That did it - Thank You so much!|||

Hi Manivannan,

I liked your script. I would like to add just one point in order to use the new ROW_NUMBER() function in SQL2005

So instead of

Code Snippet

select

[Act Num]

,[City]

,RowId - (Select Min(RowId)-1 From #Data Sub Where Sub.[Act Num]=Main.[Act Num]) as RowId

From #Data Main

The below script can also be used

Code Snippet

select

[Act Num]

,[City]

,ROW_NUMBER() OVER (PARTITION BY [Act Num] ORDER BY City) AS RowId

From #Data Main

Eralper

http://www.kodyaz.com

Displaying report parameters in report header or body

Hi,
I like to see in the generated report what the parameters value are for the
current report like startdate and enddate
Thanx WimmoYou can do this by adding a textboxes to the page header and using
expressions like:
=Parameters!startdate.Value
=Parameters!enddate.Value
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Wimmo" <Wimmo@.discussions.microsoft.com> wrote in message
news:A1F341D0-9A5A-463E-9974-18A0232F9072@.microsoft.com...
> Hi,
> I like to see in the generated report what the parameters value are for
the
> current report like startdate and enddate
>
> Thanx Wimmo

Displaying previous transaction result

Hi Guys,

I want to display a report like this

Date Balance on Date Credit Debit Current Balance
02/05/06 0 1200 0 1200
05/05/06 1200 500 0 1700
10/05/06 1700 0 200 1500
15/05/06 1500 200 0 1700

I mean for the new date the balnace of the previous day transaction should be displayed.
How can it be done?
Any help is greately appreciated.
Thanks in advanceIs the day's balance a value you calculate, or read straight from the database?
If the latter then use the 'Previous' function. See help.|||or

Create a formul having the code
{Credit}-{Debit}

and create running total based on that formula and use that in report

Display report as PDF in current window

How could I display Reporting Services report in the opened window ?

This is my code in the Page_Load()

Dim RptParameters(0)As Microsoft.Reporting.WebForms.ReportParameter RptParameters(0) =New Microsoft.Reporting.WebForms.ReportParameter("ProjectId",CStr(Session("CurrentProject"))) ReportViewer1.ServerReport.SetParameters(RptParameters)Dim warningsAs Microsoft.Reporting.WebForms.Warning() =Nothing Dim streamidsAs String() =Nothing Dim mimeTypeAs String =Nothing Dim encodingAs String =Nothing Dim extensionAs String =Nothing Dim DeviceInfoAs String ="<DeviceInfo>" &" <OutputFormat>PDF</OutputFormat>" &" <PageWidth>29.7cm</PageWidth>" &" <PageHeight>21cm</PageHeight>" &" <MarginTop>0.5cm</MarginTop>" &" <MarginLeft>0.5cm</MarginLeft>" &" <MarginRight>0.5cm</MarginRight>" &" <MarginBottom>0.5cm</MarginBottom>" &"</DeviceInfo>"Dim bytesAs Byte() bytes = ReportViewer1.ServerReport.Render("PDF", DeviceInfo, mimeType, encoding, extension, streamids, warnings) Response.Clear() Response.ContentType = mimeType Response.AddHeader("content-disposition","attachment; filename=Project." & extension) Response.BinaryWrite(bytes) Response.End()

With this code I am able to get the report as PDF. However I have 2 issues:

First if I try to open directly the PDF (I press the Open button when prompted) Acrobat Reader opens but complains he cannot find the file. Where is the file coming from and why it cannot be found ?

In order to open the PDF I have to save it first on the local client. Then I am prompted again and I can open the PDF file in Acrobat.

Secondly, I would like to open the PDF directly in the current window and not in he Acrobat window. How to achieve it ?

hi,

i had similar problems with Acrobat Reader. The new version of it (7.0) cannot handle opening pdfs from the web.

So i think the problem is not in the code but in the reader itself.

You could try with another pdf reader like Foxit - http://www.foxitsoftware.com/pdf/rd_intro.php

I have a similar report exporting it my self, and works for me to open it without downloading (however not using Acrobat Reader).

Unfortunately, I don't know either how to display the pdf report directly into the browser. Tried playing a lot with the content-type, but didn't help.

In case you find it feel free to share :)

Cheers,

Yani