The SQL Server Transport offers several deployment options for the queue tables.
Default
In the default mode all tables for all endpoints exist in a single schema of a single catalog. Given two endpoints, Sales and Billing, here's the mapping of the endpoint names to the queue table names:
| Endpoint | Queue table name |
|---|---|
| Sales | [sql_server_instance_01].[nsb_database].[nsb_schema].[Sales] |
| Billing | [sql_server_instance_01].[nsb_database].[nsb_schema].[Billing] |
This mode does not require any transport-specific addressing configuration so it is easy to set up. It also does not require Distributed Transaction Coordinator (DTC) to ensure exactly-once message processing.
The snapshot (backup) of the entire system state can be done by backing up a single database. It is especially useful if business data is also stored in the same database.
Multi-instance
In the multi-instance mode queue tables exist in multiple instances of SQL Server. Given two endpoints, Sales and Billing, here's the mapping of the endpoint names to the queue table names:
| Endpoint | Queue table name |
|---|---|
| Sales | [sql_server_instance_01].[nsb_database].[nsb_schema].[Sales] |
| Billing | [sql_server_instance_02].[nsb_database].[nsb_schema].[Billing] |
In order to use this mode the connection information has to be provided for each instance.
To ensure exactly-once message processing the transport must be set to TransactionScope transaction mode and Distributed Transaction Coordinator (DTC) has to be set up to allow distributed transaction that span multiple SQL Server instances.
Although the multi-catalog and multi-schema modes are not supported explicitly, they but can be simulated by configuring all connections to refer to a single instance of SQL Server but with different query string properties:
initial catalogfor multi-catalog deploymentsqueue schemafor multi-schema deployments
queue schema is an NServiceBus-specific extension to the SQL Server connection string syntax and can only be used in connection strings used by SQL Server Transport.