Creating Distributed Applications Using Service Broker
Dec 13, 2007
Hello everybody!
We are developing a distributed application based on Tree Network Topology. Any part of this application knows only for one parent part and all children parts. For synchronize data with parts of this application we using SQL Service Broker.
Every part contains database. Lets name it SB?. This database contains one QUEUE for received and sent messages, MESSAGE TYPE, CONTRACT and SERVICE. Any Service on part has unique name. Also there are one REMOTE SERVICE BINDING and ROUTE for parent part (service) and on one fore every child part (service). For each link Source Target we create separate conversation_handle:
"BEGIN DIALOG @ConversationHandle FROM SERVICE [Source] TO SERVICE 'Target' ON CONTRACT Contract WITH ENCRYPTION = OFF;"
All works fine, messages sends and received quite well.
But if we have opposite messages in one moment, with probability 100 percent one of messages, will be missing. Why it happened? Is duplex is not supported in Service Broker? May we do something wrong? Anybody encounter this problem?
We using flag retention = on for queue, to analyze messages after they processed. Body of Message that sent into several services (in one transaction) absent in that service from which the message during the same moment has been sent.
View 6 Replies
ADVERTISEMENT
Aug 14, 2007
hi all,
i want to create a service broker application between two different instances.
can i achieve the dialog security and transport security with out using certificate?
like we can achieve the transport security using windows authentication (both the instances are in same domain).
so how to achieve the dialog security with out using certificates.
if any body has any link plz provide me.
Thanks a lot!!!!!!!!!!!!!!!!!!!!!!!!!!
View 3 Replies
View Related
Nov 30, 2007
Hello,
I'm working on the design of a system that will have two queues. The first queue will take requests to do something. The second queue will hold the results of having done something. Both queues won't necessarily be on the same physical server. Clients reading/writing to the queue will not be using SQL server. Web service/WCF interfaces will be developed to interrogate/manipulate queues.
My question is: which is better - MSMQ or Service Broker; if I want reliable queues. I can affort to loose items off the first queue, but cannot afford to loose any result data on the second queue.
Can anyone make any recommendations please?
Regards,
Jonathan.
View 3 Replies
View Related
Apr 16, 2006
Hi, I have a scenario in which an application generates events upon updates to the database, which need to be reliably transmitted to consuming applications running a) other SQL Server instances and b) as remote web services.
Now,
1) I am planning to use SQL Server Notification Services to generate these events, and allow the consuming applications to subscribe to them.
2) The events will be sent to a ReceiveEvent service, which will then process the event and put it in the respective queues for the Subscriber (consumer) applications.
3) The services representing these consumer applications will then get activated, and sent the message across directly to the service broker on the consumer side in case of situation a - where there is a SQL Server on the other side
4) In case the other side is a web service, I plan to make a SOAP request directly from my queue handler, and depending on the response received, decide whether to commit or roll back that particular transaction.
Does this seem like a good solution? I am very new to the Service Broker, and I am still getting my hands dirty. Any comments/suggestions would be very highly appreciated!
Regards,
Siddhesh
siddhesh@persistent.co.in
View 3 Replies
View Related
Jul 25, 2006
Hi There
Ok i have done the following on my target instance i have created an endpoint, xml schema, message types, contact, activation sp , queue and service.
On the initiator i have created an endpoint, a route to the target service, same xml schema, same message types, same contact, queue and service.
When i try test this by doing the following:
SET @msg = CAST(@body AS XML)
BEGIN DIALOG CONVERSATION @dialogHandle
FROM SERVICE
[http://ewx.co.za/DemoInitiatorService]
TO SERVICE
'http://ewx.co.za/DemoService'
ON CONTRACT
[http://ewx.co.za/DemoContract];
SEND ON CONVERSATION @dialogHandle
MESSAGE TYPE [http://ewx.co.za/DemoMessage]
(@msg);
END CONVERSATION @dialogHandle;
END
================================================================================
Nothing happens and i dont know where to troubleshoot, if i check both queues they are empty ? I have altered the queues so that retention is ON.
I have checked the sql log of both instances and nothing.
When i execute the above sql it executes succesfully, so basically i am stuck i can execute the dialog sql, and set the activation on the target queue to OFF , so if i message gets there it should stay in the queue, but both que's are empty and no errors ? Not sure where to even start checking the issue ? For all i know the contract could be incorrect or port 4022 blocked or the endpoints not working, but i dont know where to find these errors other than the sql log and it has no errors ?
PLEASE HELP.
Thanx
View 7 Replies
View Related
May 22, 2005
I'm working with the April CTP of SQL Server and I'm trying to create a proof of concept using service broker. I'm struggling with the "abc's" of it. If anyone has or can point me to a distributed "Hello, World" for service broker between SQL Server 2005 and SQL Express instances it would save me some time and trouble.
View 14 Replies
View Related
Nov 18, 2005
I'm trying to set up Service Broker Services on SQL 2005 x86. I've got two services set up, and a stored procedure associated with one of them.
View 3 Replies
View Related
Sep 10, 2007
How to prevent the hang on the initator service broker if the target service broker is not started?
Our case has two service brokers (two databases), sometime, the target is need to turn off. But the sitation is the initator service broker (in fact, the message is sent from triggers) become hang, I want to prevent this case and continue to operation, and the messages should queue and will continue to send to target service broker when it startup. How should I do?
View 3 Replies
View Related
Feb 16, 2008
Hello, I receive this error "The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported. Please enable the Service Broker for this database if you wish to use notifications." I attach the database in Management Studio to query and enable the broker using the scrip below but to no avail. ALTER DATABASE DataName SET ENABLE_BROKER β''<<------successfulandSELECT is_broker_enabled FROM sys.databases WHERE name = 'Database name' β'''<<-------value is 1 Global.asax ... Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs) System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings("dataConnectionString1").ConnectionString) End Sub...Web.config ... <connectionStrings> <add name="dataConnectionString1" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|jbp_data.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> <add name="ASPNETDBConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> </connectionStrings>... Hope you could help. cheers,imperialx
View 1 Replies
View Related
Apr 5, 2007
Hi,
I am struggling with the position SSB could take in an SOA. If I would want a broker in the general sense, meaning an intermediary sitting between applications which exchange information through messaging, would SSB be a good candidate? I know Biztalk is probably the primary candidate, but in my scenario I would end up with Biztalk apps with empty orchestrations. Also, I think Biztalk is more expensive to manage. So I am looking for a lightweight broker for a simple SOA targeted at application interoperability, no fancy business processes in sight.
I look forward to some responses.
Kind regards,
Neeva
View 2 Replies
View Related
Sep 26, 2006
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
View 6 Replies
View Related
Aug 7, 2007
Hi,
We are doing a POC for transferring a huge number of messages(millions) from oner machine to another. The two approaches we are examining are MSMQ and SQL Broker. The MSMQ is set up as a remote queue on the target machine, and the source machine takes as little as 1 millisecond to send the message (using a .NET program). However, when testing on Service Broker, we find that the time taken to send message to the queue is significantly higher - like 70 millisecond. Could you please help us in understanding why this is happening?
The service broker distributed queues have been set up as per the directions in the posting at http://www.sqlservercentral.com/columnists/sindukuri/2797.asp
The source program (written in .NET) is calling a stored procedure in the source machine to write to the SSB queue. When we run SQL Trace, we find that the SP is responsible for 99% of the time taken. Here is our SP that send the message:
Declare @ConversationHandle uniqueidentifier
Begin Dialog @ConversationHandle
From Service SenderService
To Service 'ReceiverService'
On Contract SampleContract
WITH Encryption=off;
SEND
ON CONVERSATION @ConversationHandle
Message Type SenderMessageType
(<<XML String>>)
Please let us know if there are any additional settings required in the Service Broker to improve its performance. Or , what are the other approaches for building a distributed SSB application?
View 3 Replies
View Related
Mar 30, 2007
I am trying to send a message between to SQL Server 2005 instances on two different machines. I have checked all my routes and all my objects appear to be setup correctly. However, when running Profiler on the target machine, I receive the "This message has been dropped because the TO service could not be found. Service name: "[tcp://mydomain.com/TARGET/MyService]". Message origin: "Transport". This is my activated stored procedure that is sending the message to the target service. I am using certificate security. Any help appreciated....
CREATE PROCEDURE [usp_ProcessMessage]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @conversation_handle uniqueidentifier
DECLARE @message_body AS VARBINARY(MAX)
WHILE (1=1)
BEGIN
BEGIN TRANSACTION;
WAITFOR(RECEIVE TOP (1)
@conversation_handle = conversation_handle,
@message_body = message_body
FROM [tcp://mydomain.com/INITIATE/MyQueue]
), TIMEOUT 1000;
IF (@@ROWCOUNT = 0)
BEGIN
COMMIT;
BREAK;
END
END CONVERSATION @conversation_handle
IF @message_body IS NOT NULL
BEGIN
BEGIN DIALOG CONVERSATION @conversation_handle
FROM SERVICE [tcp://mydomain.com/INITIATE/MyService]
TO SERVICE '[tcp://mydomain.com/TARGET/MyService]'
ON CONTRACT [tcp://mydomain.com/INITIATE/MyMessage/v1.0]
WITH ENCRYPTION = ON, LIFETIME = 600;
SEND ON CONVERSATION @conversation_handle
MESSAGE TYPE [tcp://mydomain.com/TARGET/VisitMessage]
(@message_body);
END
COMMIT;
END
END
GO
My endpoints are created like so:
CREATE ENDPOINT MyEndpoint
STATE = STARTED
AS TCP
(
LISTENER_PORT = 4022
)
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE MasterCertificate)
GO
GRANT CONNECT TO CertOwner
GRANT CONNECT ON ENDPOINT::MyEndpoint TO CertOwner
GO
And my routes like so:
GRANT SEND ON SERVICE::[tcp://mydomain.com/INITIATE/MyService] TO CertOwner
GO
CREATE REMOTE SERVICE BINDING [MyCertificateBinding]
TO SERVICE '[tcp://mydomain.com/TARGET/MyService]'
WITH USER = CertOwner,
ANONYMOUS=OFF
CREATE ROUTE [tcp://mydomain.com/INITIATE/MyRoute]
WITH SERVICE_NAME = '[tcp://mydomain.com/TARGET/MyService]',
BROKER_INSTANCE = N'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx',
ADDRESS = N'TCP://xxx.xx.xx.xx:4022'
GO
View 10 Replies
View Related
Sep 26, 2007
I am doing some research to see if the Service Broker technology would help my company with our Enterprise application. Here is our scenario: We have a 3 tier system. The first tier needs to contact the second tier asynchronously. Hence, using queues is a good option. However, the process that needs to happen on the second tier is mostly process intensive with little database updates. Is it still worth our time to use Service Broker?
I like the concept of Activation that Service Broker provides. But, from what I am reading most of the documentation describes activation as a way to call another stored proc. I definitely dont' want to do any process intensive work on the SQL server. So here comes my question...
How would I use a windows service to listen to the activation event from the Service Broker. I could have multiple windows services watching the same queue (scalable). Would I have to handle collisions myself? If so, I think I would rather keep it simple, and just use a simple table as my queue.
Thanks for your comments in advance...
Vijay.
View 3 Replies
View Related
Apr 5, 2008
Hi to all, I want to study Sql server Service broker, have some questions1. What is the use of service broker ?2. Where this will use ? (With example)3. How to enable Service broker? Because i have sql server 2005 version but no folder like service broker.
View 2 Replies
View Related
Aug 29, 2006
Im having a hard time understanding everything required to create a simple Service Broker example. Can someone please assist? Source code would be ideal, but if not "do this, do that" would even be helpful.
Thanks.
View 1 Replies
View Related
Sep 8, 2006
I am trying to implement service broker. I send a message from my application code to the database to execute a specific stored procedure. How do i return the result set obtained by the execution of the stored procedure to the application.
View 4 Replies
View Related
May 16, 2006
My service broker seems to be broken... The database was restored from another crashed server but i have tried the
ALTER AUTHORIZATION ON DATABASE::[SPYDERONTHEWEB] TO [SA];
The error i'm getting is
Service Broker needs to access the master key in the database 'SpyderOnTheWeb'. Error code 25. The master key has to exist and th service master key encryption is required.
Error: 28054, Severity 11, State: 1.
View 4 Replies
View Related
Sep 26, 2006
Hi
It will be great to have an update on MS plans to solve the problem of using
Service Broker for remote users who sit behind the NAT.
Any news will be appreciated.
Leonid.
View 1 Replies
View Related
Sep 11, 2006
Hello , I am trying to Implement distribution of the Stock Quotes over the LAN(only within the Network) and showing the live changing stock Quotes on the front end (in datagrid) installed at each clients desktop.I am receiving the Stock prices over the TCP / IP from the Stock Exchange. I am recieving atleast 10-15 messages per second over the TCP / IP from the Stock Exchange. Now i need to distribute this feed to Each connected client.
I tried doing it from TCP / IP , but in vein. Can we install the SQL 2005 Database Client Version on every client and use Service broker instaed of Live TCP / IP connections programmatically?
Ideally Can i dump the meesages from Stock Exchange in to each connected client's database locally and each front end application will keep a record of all the incomming messages.i.e Front end have a notification event , it will referesh the Datagrid in Front end accordingly...
ALL my front end application are made in dot net
Pls suggest if this above workflow will help me
Yugant
View 2 Replies
View Related
Jan 8, 2008
Hi,
Is it possible to develop Service Broker in .Net 1.1 (VS 2003)? Currently I have a project developed in .Net 1.1 and I want to add a new method utilize the message queue concept (instead of using MSMQ, using Service Broker SQL 2005), although my DB is SQL server 2005.
Thanks,
View 1 Replies
View Related
Jul 3, 2007
Hi all
if any one have any white paper or artical cover this issue kindly i need it
thanks , regards
View 1 Replies
View Related
May 16, 2007
Hi,
I am looking at the Service Broker as a way to notify multiple clients that there has been data changed on a table in the shared database. These clients may or may not be online. When there is a change, the notification should fire off a query to refresh the clients local cache. Is this a situation where Service Broker would help me? Can multiple clients recieve the notification at different times ( some recieve while online, some recieve when they come back online)? Any help on this would be appreciated. It seems from what I read that the messages are pulled off the queue when a notification has taken place. Is this correct? If so, can I set it to behave differently?
Thanks,
-paul
View 1 Replies
View Related
Sep 15, 2005
Let's assume the situation: we have Initiator and Target. Target is behind ISP's NAT and can't be published outside. So, when Initiator sends a message to Target, Target will not be able to establish a backward connection and will not send an acknowledge. Initiator will retry and retry...
View 8 Replies
View Related
Apr 26, 2008
I have tried the following, each runs successfully with no error, but nothing is in the queues, what can be the issue?
CREATE MESSAGE TYPE SentMsgType
VALIDATION = WELL_FORMED_XML;
CREATE CONTRACT MQContract
(SentMsgType SENT BY ANY );
CREATE QUEUE SentQueue
WITH
STATUS=ON, RETENTION=OFF;
CREATE QUEUE ReceivedQueue
WITH
STATUS=ON, RETENTION=OFF;
CREATE SERVICE SentService ON QUEUE SentQueue
(MQContract);
CREATE SERVICE ReceivedService ON QUEUE ReceivedQueue
(MQContract);
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[insertTrigger]
ON [dbo].[tblBBB] FOR INSERT AS
BEGIN
SET NOCOUNT ON;
DECLARE @handle uniqueidentifier
DECLARE @msgBody nvarchar(500)
select @msgBody = someString from inserted
BEGIN DIALOG CONVERSATION @handle
FROM SERVICE SentService
TO SERVICE 'ReceivedService', 'CURRENT DATABASE'
ON CONTRACT MQContract;
--Sends a message
SEND ON CONVERSATION @handle
MESSAGE TYPE SentMsgType
('<message>' + @msgBody + '</message>')
END CONVERSATION @handle WITH CLEANUP;
END
SELECT * FROM SentQueue
SELECT * FROM ReceivedQueue;
View 3 Replies
View Related
Oct 16, 2006
How to create service broker and whic version is supported to create serveice broker.
can you plz exlain to create servece broker from the scratch
View 1 Replies
View Related
Feb 27, 2007
Hi,
Please let me know if the following server configuration is possible?
SQL Server Database Services on machine #1
Analysis Services and Reporting Services 2005 on machine #2 (without SQL Server Database Services)
Post installation, need to setup Reporting services configuration. In that, within Database Setup, point to database installed on machine #1.
I am facing problem in pointing to remote database.
Awaiting some reply.
Thanks!
View 3 Replies
View Related
Feb 16, 2007
Hi everyone,
Can anyone let meknow how do i enable a service broker. I am trying to enable a service broker for an issuetracker application to get change of events in my database. When ever i try enabling it using the ALTER DATABASE [ databse] set Enable_Broker. it takes abt more that 2 hrs or more but doesnt show as enabled.
Thanks in Advance,
Pawan Venugopal
View 2 Replies
View Related
Mar 22, 2007
So SQLDependencies failed to do what I wanted them to do for my Cache Invalidating, so i'm going to humor another possibility for a half day - Triggers on my database table that communicate messages to my C# inside my ASP.NET App. Any advice on how to tap into a message queue with C#? I'm thinking that my messages could be 1 of about 100 different strings as far as what occurred on the Database Tables
View 3 Replies
View Related
Apr 25, 2006
Hi,
We have a customer whos database just grows and grows. Not the customers own tables, but the:
sys.sysconvgroup
sys.sysdesend
sys.sysdercv
And these tables are linked to the Service Broker, and according to http://msdn2.microsoft.com/en-us/library/ms179503.aspx these tables exists in every database and are used by the Service Broker.
Now to my questions =)
HOW do I delete rows from these tables? How come these tables hust grows and grows, could it be any setting in the SQL 2005 Server or is it the customer who has programmed his application wrong?
Please respond as soon as possible.
Best regards
.Henrik
View 8 Replies
View Related
Aug 23, 2006
I'm in the process of doing the initial research for the architecture of a large scale, transactional messages routing platform.
My initial
design called for a series of MSMQ queues and Windows Services, written
in C#, to process the messages in these queues. There will be incoming
and outgoing queues, queues to store unroutable messages, etc.
My
application will be routing many hundreds of thousands (and eventually
millions) of messages per day. These message are very small (< 200
bytes each) and must be routed very quickly. (<1 second processing overhead per message for high priority messages.)
Using the term
"routing" may be a bit misleading. The messages arrive via TCP socket
connections. I will just need to take in a message, examine its
intended destination, and send it to one of several outgoing socket
connections, likely on different machines. Some messages require higher priority routing than others,
but I don't need multi-hop routing or anything like that.
Of
great concern to me is that there are absolutely no single points of
failure in the system. Because of this I was considering using a combination of MSMQ and Windows Services in a Clustered environment.
Can the Service Broker provide me with this kind of functionality? If so, how well does it perform and scale? Is it a better choice for messaging applications that require high transactional throughput than MSMQ?
I'm just trying to get an idea of what products/services I should look into further.
View 15 Replies
View Related
Feb 27, 2007
i'm new with service broker and need to develop mail site to send mass email and decide to use sevice broker i'm make aqueue ,sevice and all function for run service borker and creat databasie mail profile
then test it but it's don't work please help me to fine what's the problem ?
thanks very mcuh for every one read my request and response to me thanks very mcuh
View 3 Replies
View Related
Jan 17, 2007
Hello people
I am new to service broker and would like a little help please. I have a SP which gathers information from a collection of tables. Depending on the data gathered it may or may not begin a dialog conversation with a service broker queue. What i'm needing to know is should at the end of the SP once the required message has been sent should i end the conversation or not?
Many thanks in advance, Michael
View 1 Replies
View Related
