Do All Conversations Have To Be Bi-directional?
Mar 20, 2008
From a service broker newbie...
Most of the examples I've found and played with demonstrate two way conversation. A sender initiates a call, and gets a message back.
My Requirements doesn't really need two way communication. I have a scenario where triggers on two different tables result in modifications to a third table, and I don't want the triggers to deadlock each other, so an asynchronous queueing mechanism seems like the perfect solution...
But I can't seem to make it work one way.
I can get one message through, and then all subsequent messages hang up in the transmission queue with the very informative "One or more messages could not be delivered to the local service targeted by this dialog."
I'm thinking all the examples work the way they do because you have to notify the transmitter that the message was
received by sending a message back... and by not doing this I'm stuck in the first conversation. I was thinking that by doing END CONVERSATION <Msg Handle> in the stored procedure bound to the receiver's queue was doing that.
Do I have to communicate bi-directionally always? I guess this is a safety feature but I trust MSMQ to deliver messages...
Thx
View 3 Replies
ADVERTISEMENT
Apr 24, 2007
I am currently designing an auditing application using Service Broker. Right now, when I send a message from a trigger, I start a conversation, and later on when the message has been processed, the conversation has ended. One thing I am concerned with is that when a lot of updates are occurring on the system, if the amount of conversations being created will eat up system resources. Does it make sense to create them and end them later, or should I try to reuse them?
Tim
View 10 Replies
View Related
Nov 18, 1999
SQL Version 7.0
I have set up bi-directional replication between two databases on one table that has the structure of
Table Name : tblTrade
id : int (identity)
TicketNo : int (globally assigned via a sp - therefore always unique)
Location : int (On Server 1 defaults to 1) ---|PRIMARY
(On Server 2 defaults to 2) ---|KEY
.General Trade Fields...
Hence when a trade is entered into Server1 the Location defaults to 1 and is then replicated via an horizontal partition filter on the Location field. And similar for Server2.
Now Inserts work fine. However when a the following operations occur
DELETE - A DELETE against a record on Server1 results in ALL records being deleted from Server2
UPDATE - An UPDATE operation against a record on Server1 then ALL records get updated with the UPDATE SET clause.
The same happens with Updates on Server2 replicated to Server1.
Now it looks like the WHERE clause is missing from the replicated statements.
WHAT IS GOING ON, AND DOES ANYONE HAVE AN IDEA OF HOW TO GET AROUND THIS OR SET UP REPLICATION TO WORK PROPERLY
Regards
Steve
View 1 Replies
View Related
Jan 5, 2006
Wierd problem here
As one user, when i select * from sys.conversation_endpoints I can see all (I assume) conversations in all states specifically DO, DI and CD
However when I change to another user I see only DI
Why is this?
If it is a permissions issue what permission do I have to grant to a user to see all conversations in sys.conversation.endpoints?
View 1 Replies
View Related
Aug 26, 2006
Say I have a conversation established and the initiator server needs to reboot. Will the conversation automatically restart when the server comes back up? If not, can I get it to with some setting? If not, what is the best way to handle this?
Thanks - Amos
View 1 Replies
View Related
Sep 27, 1999
The docs state that a timestamp column is required for bi-directional
replication. I am not syncing the databases since they are too big and
it is a new project so I can guarantee at start that there will be two
exact copies of data. The data is being converted from a FoxPro db to
sqlserver.
The question is:
How do you design the timestamp columns for partioned bi-directional
replication? Does the publisher get the timestamp and the subscriber get
a binary(8)? Since there are two publishers is a distinct timestamp and
binary pair required for each server? See below...
Server: REP1 REP2
Table: tblTest tblTest
Columns: col1... col1...
tsREP1 timestamp tsREP1 binary(8)
tsREP2 binary(8) tsREP2 timestamp
Do the binary(8) columns need to be removed from the articles? Can
anyone explain how the timestamp is used to stop loop back? Is the loop
back check done on the Publisher or Subscriber?
Thanks,
Norman
View 1 Replies
View Related
Jan 14, 2000
Server_1 contains DB_A and DB_B. Server_2 contains DB_A and DB_B.
Can replication be setup where Server_1 DB_A (publisher) publishes to Server_2
DB_A (subscriber) and Server_2 DB_B (publisher) publishes to Server_1 DB_B (subscriber)? The environment is NT 4 (sp3) and SQL 6.5 (sp5a). Thanks in advance.
View 1 Replies
View Related
Nov 12, 1999
Hi All
I am trying to set up bi-directional replication (between two domains) on a simple test table prior to installing it on my Live system. The table structure is along the lines of
id int (Primary Key)
name varchar(50)
location int
I use the Location field and the filter for the Publication. i.e. 1 = Server1 and 2 = Server2
Well I set the replication up on Server1 to Server2 and test the subscription and everything replicates perfectly. Note : When I set the Server2 subscription to Server1 I recreate the structure of the table on Server2
After performing a few replicated INSERTS into the table on Server1 and they have replicated through to Server2. I set Pub/Sub on Server2 to Server1. NOTE : on the setup of the subscription I select the option that the schema and data on the target table is correct.
As soon as I enter a line into the table on Server2 the replication monitor errors on Server2 with the message that the insert stored procedure for the replication cannot be found, indicating that they have not been created.
WHAT AM I DOING WRONG FOR THE SET UP OF BI-DIRECTIONAL REPLICATION??????
Thanks
Steve
View 2 Replies
View Related
Feb 28, 2006
Besides loopback detection, is there anything else that is needed to prevent the replication command from sending back to the source. Cos i am having problem with the subscriber B sending back the replication to A in the form of B as the publisher and A as the subscriber.
:confused:
View 3 Replies
View Related
Aug 25, 2005
Two Servers,1st 2nddatabase a -------> after replication a'b' <------- after replication bon 1st server a is replicated to 2ndon 2nd server b is replicated to 1stI tried with merge replication it is working fine.But if i tried to usetransactional replication it give some error like "accessviolation".Using tansactional replication is it possible.plz help me toslove this problem.Regards,Senthil prabu R
View 1 Replies
View Related
Oct 24, 2007
Following setup s1<--- p1<-->p2 ---->s2 (bidirectional replication between publishers as each one have its own subscriber)
What are the disadvantages of this solution if only one publisher gets written to at the time.
How about schema changes (would I need to stop all activity on p1 & p2 similar to p2p replication) ?
Would changes get republished to s1 & s2 ?
Are identities the only problem when instead on p1 as main server I start using p2 ?
Thank you.
View 4 Replies
View Related
Sep 17, 2007
Hi -
In my application, I need to be able to guarantee that processing for a re-used conversation is completed prior to starting processing the next (re-used) conversation. My application is based on the concepts from the sample posted on Remus's blog: http://blogs.msdn.com/remusrusanu/archive/2007/05/02/recycling-conversations.aspx#comments). Essentially (in this sample), we create a new conversation for each SPID and re-use the conversation, so that messages are sent through the queue (and processed in order) for each SPID. SPID was used in the sample code as an example of some application-specific "thing" that you care about message ordering for. To prevent a conversation from living forever (using up log/resources), they are ended after 1 hour using DialogTimer and a customer message type.
My conundrum is this:
Assume conversation 1 (on SPID 1) is flooded with a large number of messages just before the conversation timer expires. The DialogTimer then expires before the target queue is drained. The sample code (mentioned above) then creates a new conversation for the same SPID (with a DialogTimer of 1 hour). Until the queue for conversation1 is drained, we have 2 conversations being processed for the same SPID. This same problem would occur in any application where you re-use conversations for a period of time (using DialogTimer), and then start a new conversation when the DialogTimer expires.
So although I like having the idea fof being able to re-use conversations, I would need to guarantee that conversation 1 is finished processing before conversation 2 starts processing (for the same SPID, to be consistent with the sample above). If I could get these 2 conversations into the same conversation group on the target queue, the CG locking would solve the problem. But because conversation groups only apply to the initiator queue (when you begin dialog with related conversation), I have no "out of the box" way to control how the conversation groups are associated on the target queue. Remus posted an idea here (bottom of thread): http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=182646&SiteID=1, which was to just send a special message at the beginning of each new conversation (containing the conversation group to use), and then doing a move conversation to conversationgroupid on the target queue. I've tried this solution, and the problem is 1.) if the set convo message fails for some reason, the conversation group is not set and 2.) if the target queue seems to reject most of my move conversation commands with the error "The destination conversation group '<conversation guid>' is invalid." - which I am guessing is due to the fact that this convo group id is being used on the initiator as well.
Any ideas?
Thanks!
Terryc
View 6 Replies
View Related
Oct 19, 2004
Hi,
I am trying to setup bi-directional transactional replication between 2 SQL Servers,
I add the subscription on both the servers using the code below.
EXEC sp_addsubscription @publication = N'test',
@article = N'all', @subscriber = 'AnyServer',
@destination_db = N'test', @sync_type = N'none',
@status = N'active', @update_mode = N'read only',
@loopback_detection = 'true'
GO
I have defined 'AnyServer' on both the servers using cliconfg
(Server1's AnyServer pointed towards Server2 and Server2's AnyServer pointed towards Server1, I need to do that because there is a restriction to run the same code on both the servers),
After inserting a record on server1 in the 'test' database, the changes successfully transfers to the server2, then server2 sends it back to server1 and server1 generates the error of
"Violation of PRIMARY KEY constraint 'PK_Table1'. Cannot insert duplicate key in object 'Table1'."
It seems as if loop detection is failing if I keep the same subscriber name on both sides.
It runs fine when I change the subscriber name in the subscription (@subscriber = 'Server2' for server1 and @subscriber = 'Server1' for server2).
Can anybody explains this behavior to me?
View 2 Replies
View Related
Jul 23, 2005
Hi,Our company is an independent Voice applications solution provider withnumber clients using our suite. We have a CT application suite which isrunning with Application Server and SQL Server 7 / 2000 as DB Enginesat the back end.The SQL server has two databases configured:Logging Database - Massive updates every second, the data growsrapidly,Configuration Database - Generally small-sized and updatedoccasionally.Now we want to have the reslience implemented on the server. We have tosynchronize the two databases 'real-timely' and in 'efficient'manner, so that if Primary server or its Databases gets unavailable,the users are seamlessly switched over to the Secondry server that willhave its own set of data updated and well synchronized.Typically, it can be explained as follows:1. We will have 2 database servers A - Primary (acting as publisher)and B - Secondary (acting as subscriber). Our application will beinitially connected to A.2. When A becomes unavailable (for whatever reason), the applicationwill fail-over to B.3. All the users will be switched to server B and the updates are beingdone accordingly without being replicated on Server A temporarily.4. When A is back on-line, A needs to be brought up-to-date with Bautomatically (In other words, I shouldn't have to manually export allthe data from B to A ).Our requirements are:- The system should support Bi-directional Synchronizationbetween both the servers for their set of databases (the logging andconfiguration).- There will be constant and heavy activity in LoggingDatabase, thus if one server gets down the data should be logged andmaintained as it is on second server and on fail-back no data-lossshould occur with minimum latency time.- There could be a scenario when a server fails-over for aweek's time, there will be constant logging each second! Once itfails-back the system should rapidly synchronize the data withoutnoticeable delay among the two server database sets.- The system should also work fine if certain amount ofrecords are purged over a time period.Our concern is, observing the above scenario, how any of your SQLserver replication strategy can help us achieve the requirements.ThanksJohn
View 4 Replies
View Related
Nov 20, 2006
Greetings,
My desire is to have my primary db in FL and an online hot standby in both my MI and CA offices. The hardware is in place, as well as T1 size pipes. The solution I'm looking for would have all databases online in case of hardware / environmental failure although the application would only be pointing at the primary (or designated) database server. We have another 3rd party solution available, but I'd like to stay native SQL if possible.
Is replication the way to go?
Does one-to-many work?
Can all the db's be online and available while replicating?
Can I catch up if switched to one of the other db's temporarily?
note:This is 100% SQL 2005.
Thanks -- sorry for the newbie-flavor of the question, but I'm sure a gazillion people have already been down this decision branch.
Dan Ribar
View 5 Replies
View Related
Dec 7, 2006
I hope someone can help me with this as we plan on using Service Broker in a high volume production environment. The script that builds everything is available if it's needed to diagnose the problem I'm having.
I'm having an issue where sys.conversation_endpoints on the target side of a conversation is never getting purged of closed conversations even after the 30 minute delay. The view is filled with closed conversations and database size is growing every day. I'm aware I can end conversation with cleanup on these conversations, but I would prefer that Service Broker behaves as expected. I'm also aware of the problems with the fire and forget model, but my model is request/response/end between 2 databases on the same server instance. Here's the typical series of events:
Initiator sends request
Target receives request
Target processes request
Target sends response
Initiator receives response
Initiator processes response
Initiator ends conversation
Target receives EndDialog message
Target ends conversation
Occasionally during the target's processing of a request, an exception is caught and the Target ends the conversation with an error:
Initiator sends request
Target receives request
Target processes request and recognizes error
Target ends conversation with error
Initiator receives EndDialog message
Initiator ends conversation
Here's the trace where Database ID 23 is initiator and 24 is target, no error:
EventClass
DatabaseID
TextData
EventSubClass
Broker:Conversation Group
23
1 - Create
Broker:Conversation
23
STARTED_OUTBOUND
11 - BEGIN DIALOG
Broker:Conversation
23
CONVERSING
1 - SEND Message
Broker:Message Classify
23
1 - Local
Broker:Conversation Group
24
1 - Create
Broker:Conversation
24
STARTED_INBOUND
12 - Dialog Created
Broker:Conversation
24
CONVERSING
6 - Received Sequenced Message
Broker:Activation
24
1 - Start
Broker:Conversation
24
CONVERSING
1 - SEND Message
Broker:Message Classify
24
1 - Local
Broker:Conversation
23
CONVERSING
6 - Received Sequenced Message
Broker:Activation
23
1 - Start
Broker:Conversation
23
DISCONNECTED_OUTBOUND
2 - END CONVERSATION
Broker:Conversation Group
23
2 - Drop
Broker:Message Classify
23
1 - Local
Broker:Conversation
24
DISCONNECTED_INBOUND
7 - Received END CONVERSATION
Broker:Conversation
23
CLOSED
10 - Received END CONVERSATION Ack
Broker:Conversation
24
CLOSED
2 - END CONVERSATION
Broker:Conversation Group
24
2 - Drop
Broker:Activation
23
2 - Ended
Broker:Activation
24
2 - Ended
Here are the typical records in the target sys.conversation_endpoints. These records never disappear:
Normal
With Error
conversation_handle
3FE27EE5-1E86-DB11-B009-000BDB714730
53E17EE5-1E86-DB11-B009-000BDB714730
conversation_id
0A432392-55F5-461B-87D5-0058795BC3AE
BCCDFA85-86A3-43B8-9648-24FFE5C0ED3F
is_initiator
0
0
service_contract_id
0
0
conversation_group_id
00000000-0000-0000-0000-000000000000
00000000-0000-0000-0000-000000000000
service_id
0
0
lifetime
2074-12-25 21:29:28.640
2074-12-25 21:29:28.000
state
CD
CD
state_desc
CLOSED
CLOSED
far_service
http://my.domain.com/schemas/test/Initiator/2006-12-07
http://my.domain.com/schemas/test/Initiator/2006-12-07
far_broker_instance
227D0898-0399-40E0-954B-C8B685EE415A
227D0898-0399-40E0-954B-C8B685EE415A
principal_id
5
5
far_principal_id
6
6
outbound_session_key_identifier
DEBEB4DB-D186-410B-9555-A34F8F5C9FE2
B82BB074-5AE5-4164-9D0B-53E364B0B52B
inbound_session_key_identifier
1DBAE307-5DFF-4050-9D94-71003D8BD058
57B5C7D8-9E8B-4614-9325-5AA30AED3670
security_timestamp
2006-12-07 18:45:52.763
1900-01-01 00:00:00.000
dialog_timer
1900-01-01 00:00:00.000
1900-01-01 00:00:00.000
send_sequence
1
1
last_send_tran_id
0x550800000000
0x700700000000
end_dialog_sequence
-1
1
receive_sequence
2
1
receive_sequence_frag
0
0
system_sequence
0
0
first_out_of_order_sequence
-1
-1
last_out_of_order_sequence
0
0
last_out_of_order_frag
0
0
is_system
0
0
View 9 Replies
View Related
Aug 5, 2007
Hi! I'm wondering why is my sys.conversation_endpoints table inserting a new row for each message i send even when i reuse conversations?
when i send the first message i get the first row in the sys.conversation_endpoints with a uniqueidentifier for the conversation_handle. this uniqueidentifier is then saved in the table which i query the next time i send a message to reuse the dialog conversation.
But even though it looks like the uniqueidentifier is reused i still get a new row for every message i send with a different conversation_handle?
this happens in both target and initator db.
I've tried to understand this by i don't.
Also for the moment i don't end conversations. But as i understand it this shouldn't matter.
Also the message successfully arives to the target and sys.transmission_queue is empty in both databases.
Neither queues have any error messages in them.
Thanx
View 1 Replies
View Related
Dec 15, 2006
We have a system that has 35 million conversations piled up. We didn't know to explicitly end the conversation once the processing has completed. Oops. Now, our production box has 35 mm sitting in the table, and we have run into the problem where the amount in sys.conversation_endpoints has exceeded memory and they are being dumped into tempdb, which is killing our disk space, thus bringing the box down. We have fixed the code to end the conversations, but we now have to end the conversations in a hurry. If we select one by one out of the table and end the conversation via END CONVERSATION, it is slow. Very slow. It will finish in a few months. :(
Does anyone know how to get rid of these conversations in a hurry? All of the messages have been applied to our system, so killing the conversations will (should) have no affect on the processed data. Something like a TRUNCATE statement?
Thank you so much in advance,
John Hennesey
View 5 Replies
View Related
Nov 30, 2007
Hi,
I implemented the pattern suggested in the 'Recycling Conversations' article that Remus Resanu presented. Everything works great except ended conversations on the receiver remain in the sys.conversation_endpoints table forever in the 'CLOSED' state.
Is there some setting I am missing to have those conversations purged from the endpoints table. I am concerned that in the production environment this table will grow very large.
Thanks
View 2 Replies
View Related
Feb 8, 2008
Hi,
I have implemented Remus Resanu's implementation from the Recycling Conversations article and I am experiencing locking issue when I try to insert new conversation handles to the SessionConversations table. I have copied the code in the article exactly including the activation procedure. Any ideas why I may be locking. I am thinking it is related to the HOLDLOCK hint on the table.
The sepcific line where I see locking is directly from the article:
INSERT INTO [SessionConversations] (SPID, FromService, ToService, OnContract, Handle) VALUES (...etc)
Thanks
View 6 Replies
View Related
