December 12, 2008 at 12:50 am
Hello,
I have front end with klient SQLExpres where is the QUEUE.
Also I have one server (back end) where is master QUEUE.
Front end sending the message to back end and back end the message save.
All of them is correct.
But what can I do if I would like add more Front ends.
A tried it - the front end sending the message, Back end the message save, but the answer (back to Front end) dosnt work.
I have more routes with IP adresses, and I have more certificates (for each of the front end).
The main problem is with the END conversation. Message processing on the Back end is without problems.
December 12, 2008 at 9:46 am
The back end should be able to reply on the conversation handle that it receives from the front-end. If the front-end waits for a reply from the back-end on that conversation handle, then it should receive it.
What is happening when the back-end replies?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 14, 2008 at 11:52 pm
in the first front end-> There is not complicate - Front end send message to "Queue" on the back end and the back end send the confirm to "Queue" on the front end.
int the second,(and others) Fron end send the message to "Queue" on the back end bad the confirms dos´nt will come to front end.
Its look like the back end dos not recognize other front ends...
just the back end know accepted the message from front ends.
------------------------
I think - somethink must be diferent between first and others klients(front ends) but I dont know what.
in the back end
I have got routes for each of them with IP adreses (every one has the same port).
certificate for each of them
one end points for every one
int he front end
I have imported the same certificate from the back end server
------------------------
I use the same name of message type, same name of services.
--
After test:
Send the message from front end
in conversation_endpoints on klients is one row with state: CONVERSING
in queue on back end is one row with the message
in conversation_endpoint on backend is one row with state: CONVERSING
RECIEVED from queue
in conversation_endpoints on back end is still one row: DISCONNECTED_OUTBOUND
queue on back end is empty
queue on fron end is also empty (this is wrong)
in conversation on front end is still one raw: CONVERSING
in transmition queue on back end is 2 rows : transmission_status is empty
------
After the second test
Send the message from front end
in conversation_endpoints on klients is one row with state: CONVERSING
in queue on back end is one row with the message
in conversation_endpoint on backend is one row with state: CONVERSING
-- I send (manual) the message from back end to front end (by the same handle)
the queue on front end is empty
in transmition queue is one row with status: An error occurred while receiving data: '64(error not found)'.
December 15, 2008 at 9:09 am
It's pretty hard to understand what is happening to you.
Could you provide the code that you are using or some example code that can demonstrate the problem?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 16, 2008 at 4:17 am
For server:
use master
GO
/*Clear data*/
IF EXISTS(SELECT * FROM sys.endpoints WHERE name = 'SB_2008_Endpoint')
BEGIN
DROP ENDPOINT SB_2008_Endpoint
END
GO
IF EXISTS(SELECT * FROM sys.certificates WHERE name = 'DDT2008Master_Cert')
BEGIN
DROP CERTIFICATE DDT2008Master_Cert
END
GO
DECLARE @certName nvarchar(max);
DECLARE DropCur CURSOR FOR
SELECT name FROM sys.certificates WHERE subject LIKE '%Transport Security SB cert%' OR name LIKE 'Mybroker_%'
OPEN DropCur FETCH NEXT FROM DropCur INTO @certName
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE ('DROP CERTIFICATE '+ @certName)
FETCH NEXT FROM DropCur INTO @certName
END
CLOSE DropCur
DEALLOCATE DropCur
GO
IF EXISTS(SELECT name FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
BEGIN
DROP MASTER KEY
END
GO
IF EXISTS(SELECT * FROM sys.database_principals WHERE name = 'SBUser')
BEGIN
DROP USER SBUser
END
GO
IF EXISTS(select * from master.dbo.syslogins where loginname = 'SBLogin')
BEGIN
DROP LOGIN SBLogin
END
GO
IF EXISTS(SELECT * FROM sys.Databases WHERE name = 'My_server')
BEGIN
DROP DATABASE My_server
END
GO
----------------------------------------------
/*Create database*/
CREATE DATABASE My_server
GO
ALTER DATABASE My_server SET TRUSTWORTHY ON
GO
ALTER DATABASE My_server SET ENABLE_BROKER
GO
-----------------------------------------------
use My_server
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='Heslo123'
GO
CREATE MESSAGE TYPE [SendCampaignLog] VALIDATION = WELL_FORMED_XML
CREATE MESSAGE TYPE [CampaignReceived] VALIDATION = NONE
GO
CREATE CONTRACT [My_klientContract] ([SendCampaignLog] SENT BY INITIATOR, [CampaignReceived] SENT BY TARGET)
GO
CREATE QUEUE My_server_Queue WITH STATUS= ON
GO
CREATE SERVICE [My_server_Service] ON QUEUE My_server_Queue ([My_klientContract])
GO
CREATE TABLE dbo.created_certficates(created_certficate_id int NOT NULL IDENTITY (1,1) PRIMARY KEY, created_certficate_name nvarchar(255) NULL)
GO
----------------------------------------------------------------------------------
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='123594934B!'
GO
CREATE CERTIFICATE DDT2008Master_Cert WITH SUBJECT = 'DDT2008 Security SB cert', EXPIRY_DATE = '10/1/2090', START_DATE = '1/1/2008'
GO
BACKUP CERTIFICATE DDT2008Master_Cert TO FILE ='\\mssql\certifikaceSQL\DDT2008Master_Cert.cer'
GO
CREATE ENDPOINT SB_2008_Endpoint STATE = STARTED AS TCP (LISTENER_PORT = 4021)
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE DDT2008Master_Cert, ENCRYPTION = REQUIRED)
GO
CREATE LOGIN SBLogin WITH PASSWORD = 'passworD123'
GO
CREATE USER SBUser FOR LOGIN SBLogin
GO
GRANT CONNECT ON Endpoint::SB_2008_Endpoint TO SBLogin
GO
----------------------------------------------------------------------------------
use My_server
GO
CREATE CERTIFICATE My_serverCert WITH SUBJECT = 'My_serverCert SB cert', EXPIRY_DATE = '1/1/2090', START_DATE = '1/1/2008'
GO
BACKUP CERTIFICATE My_serverCert TO FILE ='\\mssql\certifikacesql\My_serverCert.cer'
GO
CREATE USER My_klient_User WITHOUT LOGIN
GO
GRANT SEND ON SERVICE::[My_server_Service] TO My_klient_User
GO
CREATE REMOTE SERVICE BINDING My_klient_Binding TO SERVICE 'My_klientService' WITH USER = My_klient_User
GO
------- AFTER EXISTS FIRST klient
use master
GO
CREATE CERTIFICATE Mybroker_1 AUTHORIZATION SBUser FROM FILE ='\\mssql\certifikacesql\Mybroker_1.cer'
GO
----------------------
use My_server
GO
CREATE CERTIFICATE My_klientCert_1 AUTHORIZATION My_klient_User FROM FILE ='\\mssql\certifikacesql\My_klientCert_1.cer'
GO
CREATE ROUTE My_server_route_1
WITH SERVICE_NAME = 'My_klientService',
ADDRESS = 'TCP://10.20.80.103:4030',
BROKER_INSTANCE = 'F3127637-AFC9-4950-A1F2-01DB66674DD2' -- The unique of klient
GO
------- AFTER EXISTS SECOND klient
use master
GO
CREATE CERTIFICATE Mybroker_2 AUTHORIZATION SBUser FROM FILE ='\\mssql\certifikacesql\Mybroker_2.cer'
GO
use My_server
GO
CREATE CERTIFICATE My_klientCert_2 AUTHORIZATION My_klient_User FROM FILE ='\\mssql\certifikacesql\My_klientCert_2.cer'
GO
CREATE ROUTE My_server_route_2
WITH SERVICE_NAME = 'My_klientService',
ADDRESS = 'TCP://10.20.80.103:4040',
BROKER_INSTANCE = 'F26209F6-68BD-43C3-8A32-7909030E1328'
GO
--------------------------------------------------------------------------------------------
FOR CLIENT
use master
GO
IF EXISTS(SELECT * FROM sys.Databases WHERE name = 'My_klient') BEGIN DROP DATABASE My_klient END
GO
IF EXISTS(SELECT * FROM sys.endpoints WHERE name = 'SB_2005_Endpoint') BEGIN DROP ENDPOINT SB_2005_Endpoint END
GO
IF EXISTS(SELECT * FROM sys.certificates WHERE name = 'Mybroker_1') BEGIN DROP CERTIFICATE Mybroker_1 END
GO
IF EXISTS(SELECT * FROM sys.certificates WHERE name = 'DDT2008Master_Cert') BEGIN DROP CERTIFICATE DDT2008Master_Cert END
GO
DECLARE @certName nvarchar(max);
DECLARE DropCur CURSOR FOR
SELECT name FROM sys.certificates
WHERE name LIKE 'Mybroker_%'
OPEN DropCur FETCH NEXT FROM DropCur INTO @certName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('DROP CERTIFICATE '+@certName)
FETCH NEXT FROM DropCur INTO @certName
END
CLOSE DropCur
DEALLOCATE DropCur
GO
IF EXISTS(SELECT name FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##') BEGIN DROP MASTER KEY END
GO
IF EXISTS(SELECT * FROM sys.database_principals WHERE name = 'SBUser') BEGIN DROP USER SBUser END
GO
IF EXISTS(select * from master.dbo.syslogins where loginname = 'SBLogin') BEGIN DROP LOGIN SBLogin END
GO
IF EXISTS(SELECT * FROM sys.certificates WHERE name = 'DDT2008Master_Cert') BEGIN DROP CERTIFICATE DDT2008Master_Cert END
GO
use master
GO
CREATE DATABASE my_klient
GO
ALTER DATABASE my_klient SET TRUSTWORTHY ON
GO
ALTER DATABASE my_klient SET ENABLE_BROKER
GO
use my_klient
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='Heslo123'
GO
CREATE MESSAGE TYPE [SendCampaignLog] VALIDATION = WELL_FORMED_XML
GO
CREATE MESSAGE TYPE [CampaignReceived] VALIDATION = NONE
GO
CREATE CONTRACT [My_klientContract] ([SendCampaignLog] SENT BY INITIATOR, [CampaignReceived] SENT BY TARGET)
GO
CREATE QUEUE My_klientQueue WITH STATUS= ON
GO
CREATE SERVICE [My_klientService] ON QUEUE dbo.My_klientQueue ([My_klientContract])
GO
use master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='1294934A!'
GO
CREATE CERTIFICATE Mybroker_1 WITH SUBJECT = 'My service broker certificate for master', EXPIRY_DATE = '10/1/2090', START_DATE = '1/1/2008'
GO
BACKUP CERTIFICATE Mybroker_1 TO FILE ='\\mssql\certifikacesql\Mybroker_1.cer'
GO
----------------------------------- FOR SERVER DB My_server
use master
GO
CREATE CERTIFICATE Mybroker_1 AUTHORIZATION SBUser FROM FILE ='\\mssql\certifikacesql\Mybroker_1.cer'
GO
------------------------------------
--Back to the klient
use master
GO
CREATE ENDPOINT SB_2005_Endpoint STATE = STARTED AS TCP (LISTENER_PORT = 4030) FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE Mybroker_1, ENCRYPTION = REQUIRED)
GO
CREATE LOGIN SBLogin WITH PASSWORD = 'Heslo123'
GO
CREATE USER SBUser FOR LOGIN SBLogin
GO
GRANT CONNECT ON Endpoint::SB_2005_Endpoint TO SBLogin
GO
CREATE CERTIFICATE DDT2008Master_Cert AUTHORIZATION SBUser FROM FILE ='\\mssql\certifikacesql\DDT2008Master_Cert.cer'
GO
use My_klient
GO
CREATE CERTIFICATE My_klientCert_1 WITH SUBJECT = 'My_klient SB cert', EXPIRY_DATE = '1/1/2090', START_DATE = '1/1/2008'
GO
BACKUP CERTIFICATE My_klientCert_1 TO FILE ='\\mssql\certifikacesql\My_klientCert_1.cer'
GO
CREATE USER My_serverUser WITHOUT LOGIN
GO
CREATE CERTIFICATE My_serverCert AUTHORIZATION My_serverUser FROM FILE ='\\mssql\certifikacesql\My_serverCert.cer'
GO
GRANT SEND ON SERVICE::[My_klientService] TO My_serverUser
GO
CREATE ROUTE Route_My_server WITH SERVICE_NAME = 'My_server_Service', ADDRESS = 'TCP://10.20.80.10:4021'
GO
CREATE REMOTE SERVICE BINDING My_serverBinding TO SERVICE 'My_server_Service' WITH USER = My_serverUser
GO
use master
GO
IF NOT EXISTS(select * from master.dbo.syslogins where loginname = 'MyLogin')
BEGIN CREATE LOGIN [MyLogin] WITH PASSWORD=N'MyLogin', DEFAULT_DATABASE=[my_klient], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF END
GO
------ FOR SERVER DB My_server
use My_server
GO
CREATE CERTIFICATE My_klientCert_1 AUTHORIZATION My_klient_User FROM FILE ='\\mssql\certifikacesql\My_klientCert_1.cer'
GO
DECLARE @i uniqueidentifier
SELECT @i = service_broker_guid FROM sys.databases WHERE database_id = DB_ID();
SELECT 'CREATE ROUTE My_server_route_1
WITH SERVICE_NAME = ''//SC/My_klientService'',
ADDRESS = ''TCP://10.20.80.103:4040'',
BROKER_INSTANCE = '''+CAST(@i as varchar(100))+''''
GO
December 16, 2008 at 5:55 pm
OK, this is good, but what I was really hoping for is the T-SQL code that is SEND-ing and RECEIVE-ing your messages.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 16, 2008 at 11:29 pm
-- From klient
DECLARE @Conv_Handler uniqueidentifier
DECLARE @OrderMsg xml
BEGIN DIALOG CONVERSATION @Conv_Handler
FROM SERVICE [My_klientService]
TO SERVICE 'My_server_Service'
ON CONTRACT [My_klientContract];
SET @OrderMsg =' ';
SEND ON CONVERSATION @Conv_Handler
MESSAGE TYPE [SendCampaignLog] (@OrderMsg);
GO
----------------------------------------
--In the server
SELECT CAST(message_body as xml),* FROM dbo.My_server_Queue
-- result is ok
SELECT * FROM sys.conversation_endpoints
-- result is ok - I use the handle (copy to clipboard
SEND ON CONVERSATION '9DB22970-81CB-DD11-9307-001FBC000611' -- The guid is from clipboard
MESSAGE TYPE [CampaignReceived] ('');
GO
-----------------------------------------
--In front end
SELECT * FROM my_klientQueue
-- In first front end - ok
-- in other front ends - empty
-------------------------------------------------------------------------------
I have this deferences between front ends
every where in past article (i sent) you can find "_1" and replace the number - for example "_2",......
-- you have to change IP adres and port for next routes as well
---- because of the queue in second and others front end dosnt work - I didnt send the code what i have in stored procedure (where are recievied, processing the message type, end conversation,...).
December 17, 2008 at 9:28 am
David Mrazek (12/16/2008)
---- because of the queue in second and others front end dosnt work - I didnt send the code what i have in stored procedure (where are recievied, processing the message type, end conversation,...).
OK, I am a little confused. You originally said that the problem was that the answers back to the second front end were not working. If so then this is exactly what I need to see.
Please post the procedure that does the RECEIVE, any answering SEND's and the END CONVERSATION. Please post the whole procedure, as is. You can remove anything that has to do with writing to or reading from you app tables, etc. But, I need enough of your procedure intact that I can run it and try to duplicate the problem.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 17, 2008 at 12:22 pm
the problem has been solved:-)
the problem was here (with certficates and klient_user) every instance must have own user, and this user must have grants to other objects.
In my code has been just one user for everyone, and when i created the certificate for "my_klient" from file - I used them. This was wrong!
Thank you for your help.
Kind regards
David
December 17, 2008 at 4:58 pm
Heh. Well, glad I could help, though it seems like you were able to do that without me. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply