SQL Persistence

Stores NServiceBus data in various relational database engines, without the need for an intermediate ORM, using only a configured DbConnection.

Persistence at a glance

For a description of each feature, see the persistence at a glance legend.

Feature
Storage Types	Sagas, Outbox, Subscriptions, Timeouts
Transactions	Local database transactions or distributed transactions when available
Concurrency control	Optimistic concurrency for correctness + pessimistic concurrency for performance
Scripted deployment	SQL scripts generated at compile time, can be promoted outside build directory.
Installers	Installers execute the generated scripts, which can be enabled in development.

Highlights

Supports multiple database engines.
No ORM dependency: can be used with Entity Framework, Dapper, etc.
Independent tables for each endpoint. No "noisy neighbor" problems.
Generates DDL scripts at compile time in the build output directory.
Generated scripts can be promoted outside the build directory and:
- Added to source control.
- Compared using a diff viewer.
- Inspected by DBAs.
- Treated as first-class citizens in operations workflows for installation and deployment.
Sagas are:
- Stored using Json.NET to serialize complex data structures, with no need to manage complex table structures.
- Built to be version-aware with support for data evolution.
- Built to allow changing the CorrelationId over time.

Supported SQL implementations

NuGet Packages

The SQL Persister consists of several NuGet packages.

NServiceBus.Persistence.Sql.MsBuild

This packages adds to the MSBuild pipeline and generates the required SQL installation scripts at compile time. It does this by interrogating types (in the target assembly) and attributes (from the NServiceBus.Persistence.Sql NuGet package) to infer what scripts to create. It is required for any project where those SQL installation scripts are required. For saga scripts, this is any project that contains saga classes. For timeouts, subscriptions, and outbox scripts, it is the endpoint hosting project. This package has a dependency on the NServiceBus.Persistence.Sql NuGet package.

NServiceBus.Persistence.Sql

This package contains several parts:

APIs for manipulating EndpointConfiguration.
Runtime implementations of saga, timeouts, subscriptions, and outbox persisters.
Attributes used to define compile-time configuration settings. These attributes are interrogated by the NServiceBus.Persistence.Sql.MsBuild NuGet Package when generating SQL installation scripts.
Optionally runs SQL installation scripts at endpoint startup for development purposes. See Installer Workflow.

NServiceBus.Persistence.Sql.ScriptBuilder

This package contains the APIs that enable the generation of SQL installation scripts using code, i.e. without using the NServiceBus.Persistence.Sql.MsBuild NuGet package.

NServiceBus.Persistence.Sql.ScriptBuilder is not ready for general usage. It was made public and deployed to NuGet primarily to enable the generation of documentation in a repeatable way. For example, it is used to generate the SQL scripts in the MS SQL Server Scripts, MySql Scripts, Oracle Scripts, and PostgreSQL Scripts pages. In future releases, the API may evolve in ways that do not follow semantic versioning. This can be discussed in more detail in the Particular Software discussion forum.

Script creation

SQL installation scripts are created as a compile-time output alongside a project's binary outputs. Additionally, these scripts can be promoted to a directory under source control so that differences can be easily tracked and analyzed. To learn more, see Controlling Script Generation.

The scripts are generated by an MSBuild task at compile time. Any project containing either endpoint configuration or sagas must directly reference the SQL Persistence package. If endpoint configuration and sagas are contained in seperate projects, both projects must directly reference the SQL Persistence package and include the assembly: SqlPersistenceSettings attribute.

Troubleshooting

Looping & branching statements are not allowed

SqlPersistenceTask: Looping & branching statements are not allowed in a ConfigureHowToFindSaga method.

The ConfigureHowToFindSaga method can only contain statements that invoke ConfigureMapping<T>. Check if there is a null check or if the calling of ConfigureHowToFindSaga was done via the ?. Null-conditional operator and remove the ‘?.’ operator when present as the mapper will never be null.