locked
Recommended Architecture for One-Many RRS feed

  • Question

  •  

    In a situation where one may have a single master SQL Server that ultimately needs to communicate information back down to 1000's of downstream servers, what is the recommended architectural approach?

     

    It doesn't feel right to have to add 1K-5K routes to the master SQL Server. Is there a way to have the dowstream servers "broadcast" their existence to the master, so that new servers can be added and updates can happen seamlessly? Does this fall into a pub-sub scenario or is there a better way? And, if so, how to ensure an open conversation (so that one server doesn't miss information that all the other servers received)? Should the master dynamically create routes or better to rely on an open conversation initiated by the downstream server?

    Tuesday, June 5, 2007 4:24 PM

Answers

  • A TRANSPORT route is a special type of route created like this: create route [AutoRouteTransport] with address = 'TRANSPORT'; This route is create on master and it enables the self-routing names. The service named [tcp://downstream-host:4022/service] is created on the downstream machines, not on the master. So a new service is able to join w/o any modification on the master, which is your goal.

    A related topic is anonymous dialog security, see http://msdn2.microsoft.com/en-us/library/ms166036(SQL.90).aspx, it also necessary to allow secure dialogs in an environment like you describe (new services are added and master does not need to be reconfigured).

    Here is step by step how to configure, using certificates for endpoint security and anonymous dialog security:

     

    1) Create the service on master. Grant SEND to [Public] on it.

    2) Create the TRANSPORT route on master.

    3) Create a certificate for the master service owner (dbo?)

    4) backup the public key part of the cert created at 4 as \\master\share\service.cer

    5) create certificate for endpoint (in [master] database on master)

    6) backup the public key part of the cert created at 5 as \\master\share\endpoint.cer

    7) create a broker endpoint with authentication = certificate [the cert created at step 5])

    8) grant CONNECT to [public] on the broker endpoint

     

    With these steps, the master is now ready to accept downstream services. To add a new downstream machine, these are the steps (for each machine). All steps are performed on the newly added machine, nothing is modified on the master machine:

    1) create the downstream service

    2) create a normal route to the master service

    3) create a 'proxy-user' w/p login for the master service

    4) import the certificate \\master\share\service.cer, with authorization [master-proxy-user]

    5) create a remote service binding for master service, with user [master-proxy-user] and anonymous=on

    6) create a certificate for endpoint (in [master] database on new downstream machine)

    7) create an broker endpoint with authentication = certificate [the cert created at step 6])

    8) create a proxy-login for master's endpoint cert

    9) create a proxy-user in [master] database for the proxy-login

    10) import the certificate \\master\share\endpoint.cer authorization proxy-user

    11) grant CONNECT on the broker endpoint to [master's proxy login]

     

    This configuration allows the downstream services to begin dialogs with the master, w/o any aditional configuration on the master. Traffic is encrypted at dialog level (anonymous security) but the master service cannot distinguish (from a security point of view) between the downstream services (hence the granting of SEND to [public], i.e. any service will be allowed to send to master service)

    Some variations you can do on this:

    - don't use dialog security at all. Skip steps 3 and 4 on master, 3,4 and 5 on downstream. Traffic will still be encrypted by the endpoint security.

    - use Windows authentication on the endpoints. Skip steps 6 and 8 on master, use Windows instead at step 7. Skip steps 6,7,8,9 and 11 on downstream, use Windows instead on step 10. To authorize the endpoints to connect, use windows domain groups. That is, create a domain group [domain\downstreammachines] and on master grant CONNECT permission on the broker endpoint to this group, then add the downstream SQL instances service account to this group (if running as localsystem add the machine account domain\downstreammachinename$ )

     

     

     

     

    Tuesday, June 12, 2007 4:52 PM

All replies

  • In this scenario, I don't think I would use Service Broker.  First, because of the security that would be involved in setting the servers up so messages could be sent from environment to environment.  Second, it sounds like a situation where replication is the way to go.  It sounds like your setup is a heirarchy, which suggests traditional transactional replication rather than peer-to-peer replication.  Hope this helps.

    Tim

    Friday, June 8, 2007 9:18 PM
  •  

    I don't have a need for security because I am not crossing environments in the hierarchy. Everything is behind a firewall and I control the network. So, I am more interested in how to manage the transfer of data down the hierarchy. I think I am settling on some type of pub/sub design, but I wanted to hear other ideas or recommendations first.

    Monday, June 11, 2007 4:16 PM
  • I would use replication
    Monday, June 11, 2007 6:35 PM
  •  

    Maybe I should describe in more detail what is being transferred. This is not a situation where I am replicating database schema or data from one server to many others. Its a situation where decisions are being made at the top level and the results distributed to the lowest level (many servers). These servers are not and should not be equipped to contain the amount of data needed for decisions, so only the incremental changes will be sent to them, so they can update their operational status. Think of turning things on or off or modifying how a web server works, etc. Maybe I am missing something in your suggestion, but I don't see replication being a good solution at all in this situation.

    Monday, June 11, 2007 7:35 PM
  • You could use Service Broker Dynamic Routing (see http://technet.microsoft.com/en-us/library/ms166054(SQL.90).aspx) to automate the creation and maitenance of the 1k-5k routes. The dynamic routing algorithm could use something like Active Directory to locate the location of the 'downstream' services.

    Another alternative is to have a TRANSPORT route on the master and name the 'downstream' services with route-like names that are self-routable using a TRANSPORT route. I.e. a downstream service is created like this: create service [tcp://downstream-client-213:4022/downstreamservice] on queue [queuename]; then the TRANSPORT route on the master will allow messages to this service to self-route to 'downstream-client-123:4022'.

    Tuesday, June 12, 2007 12:39 AM
  •  

    This seems closer to what I need. However, I'm not sure I understand the TRANSPORT route method and what you mean by "route-like names that are self-routable using a TRANSPORT route".  Are you sayng that simply creating the name in that fashion will create a self-routing mechanism? Because I'm not sure I see the difference between creating a self-routing service on the master and creating a route on the master, either way I need to have thousands of entries (some kind of entry) on the master, correct?

    What I would like to do is be able to add a new downstream client and do minimal configuration at the master. The new downstream client should just "join the group", so to speak, and begin participating in those updates coming from master. I realize that something must be done to announce the presence of the client to the master; I'm just not sure what the best way to accomplish it using SSB might be. I do have one - possibly two - tiers between master and the downstream clients for intermediate processing, so I was thinking they might be used to consolidate routes somehow, but then the problem becomes "how do I guarantee that each downstream client receives an update once and only once, and not one time from each midserver?"

    Tuesday, June 12, 2007 4:08 PM
  • A TRANSPORT route is a special type of route created like this: create route [AutoRouteTransport] with address = 'TRANSPORT'; This route is create on master and it enables the self-routing names. The service named [tcp://downstream-host:4022/service] is created on the downstream machines, not on the master. So a new service is able to join w/o any modification on the master, which is your goal.

    A related topic is anonymous dialog security, see http://msdn2.microsoft.com/en-us/library/ms166036(SQL.90).aspx, it also necessary to allow secure dialogs in an environment like you describe (new services are added and master does not need to be reconfigured).

    Here is step by step how to configure, using certificates for endpoint security and anonymous dialog security:

     

    1) Create the service on master. Grant SEND to [Public] on it.

    2) Create the TRANSPORT route on master.

    3) Create a certificate for the master service owner (dbo?)

    4) backup the public key part of the cert created at 4 as \\master\share\service.cer

    5) create certificate for endpoint (in [master] database on master)

    6) backup the public key part of the cert created at 5 as \\master\share\endpoint.cer

    7) create a broker endpoint with authentication = certificate [the cert created at step 5])

    8) grant CONNECT to [public] on the broker endpoint

     

    With these steps, the master is now ready to accept downstream services. To add a new downstream machine, these are the steps (for each machine). All steps are performed on the newly added machine, nothing is modified on the master machine:

    1) create the downstream service

    2) create a normal route to the master service

    3) create a 'proxy-user' w/p login for the master service

    4) import the certificate \\master\share\service.cer, with authorization [master-proxy-user]

    5) create a remote service binding for master service, with user [master-proxy-user] and anonymous=on

    6) create a certificate for endpoint (in [master] database on new downstream machine)

    7) create an broker endpoint with authentication = certificate [the cert created at step 6])

    8) create a proxy-login for master's endpoint cert

    9) create a proxy-user in [master] database for the proxy-login

    10) import the certificate \\master\share\endpoint.cer authorization proxy-user

    11) grant CONNECT on the broker endpoint to [master's proxy login]

     

    This configuration allows the downstream services to begin dialogs with the master, w/o any aditional configuration on the master. Traffic is encrypted at dialog level (anonymous security) but the master service cannot distinguish (from a security point of view) between the downstream services (hence the granting of SEND to [public], i.e. any service will be allowed to send to master service)

    Some variations you can do on this:

    - don't use dialog security at all. Skip steps 3 and 4 on master, 3,4 and 5 on downstream. Traffic will still be encrypted by the endpoint security.

    - use Windows authentication on the endpoints. Skip steps 6 and 8 on master, use Windows instead at step 7. Skip steps 6,7,8,9 and 11 on downstream, use Windows instead on step 10. To authorize the endpoints to connect, use windows domain groups. That is, create a domain group [domain\downstreammachines] and on master grant CONNECT permission on the broker endpoint to this group, then add the downstream SQL instances service account to this group (if running as localsystem add the machine account domain\downstreammachinename$ )

     

     

     

     

    Tuesday, June 12, 2007 4:52 PM
  •  

    This is exactly what I am looking for. So does this mean that each downstream service will need to have a different name in order for the master to distinguish or can I also have the same service names on the downstream machines (making the configuration even easier) and service broker will automatically know at the master where the dialog response should go based on conversation? Security issues aside (I am using anonymous security across the board and my endpoints are already configured), is this the correct configuration:

     

    USE [MasterDatabase];

    GO

     

    CREATE QUEUE [tcp://MASTER/NodeSinkQueue];

    CREATE SERVICE [tcp://MASTER/NodeSinkService]

        ON QUEUE [tcp://MASTER/NodeSinkQueue];

     

    GRANT SEND ON SERVICE :: [tcp://MASTER/NodeSinkService] TO PUBLIC

    GO

     

    CREATE ROUTE [tcp://NODE/NodeSinkTransport]

    WITH

    ADDRESS = N'TRANSPORT'

    GO

     

     

    USE [NodeDatabase]

     

    CREATE QUEUE [tcp://NODE/NodeSinkQueue];

    CREATE SERVICE [tcp://NODE/NodeSinkService]

        ON QUEUE [tcp://NODE/NodeSinkQueue];

     

    CREATE ROUTE [tcp://MASTER/NodeSinkRoute]

    WITH SERVICE_NAME = 'tcp://MASTER/NodeSinkService',

    BROKER_INSTANCE = N'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXX',

    ADDRESS = N'TCP://XXX.XX.XX.XXX:4022'

    GO

     

    Thanks!

    Tuesday, June 12, 2007 9:14 PM
  • Each node must have a different service name, corresponding to the machine name. There is no pixie dust here, the master cannot simply guess the location of a service. The routing algorithm is specified here: http://technet.microsoft.com/en-us/library/ms166052(SQL.90).aspx
    Wednesday, June 13, 2007 4:42 AM
  •  

    I was really counting on the pixie dust... :-) Thanks for the enlightenment - implementing today.

    Wednesday, June 13, 2007 3:26 PM
  •  

    Well, I set this up according to the instructions and my message gets to the master successfully (and is processed by my activated stored procedure on the master), but the response never makes it back to the downstream machine and I don't see any failures in the transmission_queue or anything peculiar in the trace. When I receive the message on the master, I am receiving top 1 and using that conversation handle to send the processed response back to the downstream machine. Is this correct? Any other ideas?

     

    The service on the master is defined like this:

     

    CREATE SERVICE [tcp://MASTER/RequestService]

    AUTHORIZATION [dbo]

    ON QUEUE [dbo].[tcp://MASTER/RequestQueue]

    (

    [tcp://NODE/RequestContract/v1.0]

    )

     

    CREATE ROUTE [tcp://NODE/RequestTransport]

    WITH

    ADDRESS = N'TRANSPORT'

    GO

     

    This is how I have my downstream service defined:

     

    CREATE SERVICE [tcp://000.00.00.00:4022/RequestService]

    ON QUEUE [tcp://NODE/RequestQueue]

    (

    [tcp://NODE/RequestContract/v1.0]

    );

    GO

     

    CREATE ROUTE [tcp://MASTER/RequestRoute]

    AUTHORIZATION [dbo]

    WITH SERVICE_NAME = N'tcp://MASTER/RequestService' ,

    BROKER_INSTANCE = N'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX' ,

    ADDRESS = N'TCP://000.01.01.001:4022'

     

    I know everything makes it to the master ok, because I am auditing the information received. It just seems as though the message gets dropped into no where when I try to send back the response using the TRANSPORT route.

    Thursday, June 28, 2007 3:09 PM
  • So reply messages sit on master.appdatabase.sys.transmissions_queue? If so, what is their transmission_status?
    Thursday, June 28, 2007 4:04 PM
  •  

    Nope...the reply messages are nowhere to be found. Everything looks as though it worked, except I never get the reply message on the downstream box.

     

    This is what I am using to send the message on master, once I do some processing (minus some error handling). This is called from the activated stored procedure after I do some inserts with the received data. The conversation handle passed here is the one obtained from the request message.

     

    CREATE PROCEDURE [dbo].[usp_SendResponseToNode]

    (

    @conversationHandle AS UNIQUEIDENTIFIER,

    @messageBody AS VARBINARY(MAX)

    )

    AS

    BEGIN

    SET NOCOUNT ON;

    BEGIN TRANSACTION;

    BEGIN

    SEND ON CONVERSATION @conversationHandle

    MESSAGE TYPE [tcp://NODE/RequestMessage] (@messageBody)

    END

    COMMIT TRANSACTION;

    END

     

    Thursday, June 28, 2007 4:28 PM
  • If the message 'vanishes' from the transmissions_queue it means that it was either succesfully delivered (therefore is in the recipient's queue) or the conversation was intentionally ended. Please ensure that you don't inadvertely RECEIVE the message in the initiatort queue and ommit to process it (e.g. do you use assignment in RECEIVE @handle = conversation_handle but you omit a TOP (1) ?). Also make sure you do not end the conversation on the initiator side before you have a chance to RECEIVE this reply.
    Thursday, June 28, 2007 5:17 PM
  •  

    I suspect it has something to do with the TRANSPORT route. What should I be looking for on the master to feel confident that it has the information needed to reply back to the downstream server? Is there something I can check once the request message arrives on the master (or while I'm processing it)?

     

    I'm not sure what could be happening because - as I said - I do receive the message successfully on the master. However, when I try to use the conversation_handle to reply, I never see anything. And I only see that the server is CONVERSING in a trace. How can I verify that things are correct using a TRANSPORT route before I try to reply? Maybe I have my service named incorrecty? I am using the following format [tcp://machine-ip:4022/MyService]. Is this all that's needed on the downstream server for the master to know where to deliver the reply?

    Thursday, June 28, 2007 6:16 PM
  •  

     

    By the way, I DO see these occuring at regular intervals on the master, but I'm not sure why service broker would think the messages are duplicate. Again, could this be something related to the way I am using the TRANSPORT route?

     

    "Broker:Message Undeliverable This message could not be delivered because it is a duplicate.   sa      16 2007-06-28 09:30:20.793"

     

    Would the result of this error be "nothing" in the transmission_queue and target queue?

    Thursday, June 28, 2007 6:46 PM
  • That is an indication that acks don't go back to the sender, so he retries again and again. Acks folow the same routing rules as normal messages, so indeed would point that there is a routing problem. Which machine traces this event? The server (that has the TRANSPORT route) or the initiators?

    In which database had you created the TRANSPORT route? Is there any other route that has the same service_name and would pre-empt the TRANSPORT route?

     

    The Message Classify event in profiler will tell for each message classification (including acks) what address was selected. Just make sure you enable all columns in the profiler trace properties, usually if you start from any template it hides most columns, make sure you start from the 'blank' template.

    Thursday, June 28, 2007 9:38 PM
  • It was the initiator that was tracing this event (not the one with the TRANSPORT route).

     

    I am not longer getting this ack error problem, but I still don't see the reply anywhere (the message still makes it successfully to the target - the one with the TRANSPORT route). I removed AutoCreatedLocal on both databases and removed any services that were similar that could be interfering. So, at this point, nothing should be pre-empting the TRANSPORT. I have show all events and show all columns selected.

     

    This is what I see for the trace regarding message classify on the original initiator:

     

    Broker Remote Message Ack: 1- Message with Acknowledgement Sent

    Message Classify: 1- Local

    Broker Remote Message Ack: 4- Acknowledgement Received

     

    From the trace on the initiator, it looks like it views the actual target machine (the one with the TRANSPORT) as the Initiator. I'm not sure whats going on here. I should be able to use the same conversation handle for the reply, correct?

     

    Any other suggestions?

    Thursday, June 28, 2007 11:41 PM
  •  

     

    OK, case closed. There was an application logic bug in my stored procedure that was preventing the reply. Sorry for the time waste and thanks for the help.

    Friday, June 29, 2007 12:22 AM