Router vs SqlServerTransport vs Schema

[valeriob]

Hi, i would like to isolate each queue in it's own schema, is it a good idea ? how can i do this on the router ? Does the router has to live in the same schema as the interface added ? (i have a routing problem doing that, because the router tries to send the message to it's own schema, i guess because it does not know the schema of the destination ?) What is the best practice to use the router inside an enterprise with many endpoint ? One single catalog and many schema ? If so do i need many routers as many schema are present ? Thanks for the help.

[valeriob]

I guess i partialy found my answer, to use transport.UseSchemaForQueue on the bridge. Anyway the questions about the best practices are still valid 😄

[SzymonPobiega]

Hi @valeriob

I am by no means a DB expert but my understanding of SQL Server schemas is that their purpose is mostly security and organization and they don't affect performance.

Given the fact that SQL Server can use native transactions across catalogs of the same SQL Server instance, the Router is only needed when using multiple instance of SQL Server. In such topology I would expect that each SQL Server instance follows one of the two general patterns:

• each endpoint has a separate catalog and, in that catalog, it has both queues and business data
• each endpoint has a separate catalog and there is a separate catalog for queues that stores queues of all endpoints In the first pattern it makes sense to group objects by purpose e.g. have queue schema for all queue tables. In the second pattern it makes sense to group queues in the queue catalog by their owning endpoint so the schema name could be derived from the endpoint (or service name)

The Router should just use whatever makes sense from the perspective of general architecture. As you mentioned, SchemaForQueue and CatalogForQueue APIs can be used to map queues to physical locations that make sense.

[valeriob]

Thanks @SzymonPobiega , that will help. A side question :D I'm hosting a router side by side an msmq endpoint (to be able to talk to another sql endpoint). Is there any way to get notified programmatically if the router have some problem ? I would like the process to crash if it does happen. Thanks Valerio