Evolving NServiceBus persistence

While we’ve been working hard on supporting .NET Core lately, you may have noticed that we also released a brand new (and dare we say better?) persistence library for NServiceBus called SQL Persistence. The new persister supports multiple database engines and uses raw ADO.NET and native SQL queries, without the need for an intermediate ORM.

We dreamed up some powerful new features that would take NServiceBus persistence to the next level. Up until now, our primary method of persisting data in relational databases used NHibernate, which was making it impossible to realize those dreams. We decided it was time for NServiceBus to make an evolutionary leap forward in its persistence capability.

That leap is our new SQL Persistence, and we think you’re going to like it. We believe it’s better for you as a developer, better for DevOps, and better for the future evolution of your NServiceBus systems.

🔗Better for you

The headline feature of SQL Persistence is its support for multiple engines, which currently includes Microsoft SQL Server, Oracle, and MySQL. We’re evaluating support for additional engines, including PostgreSQL, MariaDB, and Amazon Aurora. (Update Dec 5, 2017: SQL Persistence now also supports PostgreSQL too.) This gives you the freedom to use the database engine you feel most comfortable supporting, whether on-premises or with cloud databases through Microsoft Azure or Amazon RDS. Each database engine supported by SQL Persistence is fully documented and backed by a suite of acceptance tests that is run for every engine type on each release.

Additionally, SQL Persistence is fully async from top to bottom, the same as NServiceBus 6. Since it only references the System.Data namespace, it has no blockers to full support for .NET Core 2.0, and we’ll be including it in our plans to support .NET Core going forward¹. It doesn’t have a dependency on any ORM library, so you can easily use it with Entity Framework, Dapper, or the ORM of your choice to manage your business data. All it uses is a DbConnection.

Using SQL Persistence will also save you a lot of time if you’re working with sagas. In SQL Persistence, each saga will get its own table, but it will store saga data as a JSON blob rather than as multiple interrelated tables. This means that changes to a saga will not require an update to the saga table schema or a migration script to fill in missing data. As sagas are meant to represent complex, always-changing business processes, this tends to be a frequent occurrence.

You and your company’s database administrators will definitely appreciate not needing to modify the database schema every time business rules are updated. In fact, there are a lot of other features in SQL Persistence that DBAs will enjoy.

🔗Better for DevOps

In a DevOps context, DBAs are going to want to be able to inspect, audit, and manage the SQL used by an application. SQL Persistence was created with the DBA and DevOps in mind.

SQL Persistence uses an MSBuild task to write table creation DDL scripts to the bin directory at compile time. During development time, you can still have these scripts executed when an endpoint starts up. But at deploy time, you can pass these scripts off to your DBA to inspect and execute as part of a DevOps process with properly elevated permissions. You can even elect to elevate the generated scripts outside the bin directory so that they can be added to source control. This way, when schema changes are necessary, the diff will be visible in the changelog along with your code changes.

Your DBAs can also audit all the queries executed by the library. In fact, we use the library to generate the scripts and automatically include them in our documentation. There, you can view all the DDL and DML scripts for Microsoft SQL Server, Oracle, and MySQL.

These features, focused on DevOps and DBAs, bring a level of visibility that is hard to achieve when using an ORM.

🔗Data evolution as a feature

We wanted to make it easier to evolve your NServiceBus system over the long term. Business requirements change all the time, and we wanted to help you react to those changes while respecting the “data at rest” already stored in the database.

To start, every entity stored by SQL Persistence will include the version of the NServiceBus.Persistence.Sql.dll assembly that created it. In the event of breaking changes between versions of the persistence library, having the version attached to every row allows any migration scripts we create to be that much more intelligent. Instead of making educated guesses about how to migrate data based solely on the shape of the data present, we’ll know exactly what version of the persistence was responsible and how to address it.

We’re also extending the ability to be version-aware to your saga data by including the version of the saga data’s assembly with each saga entity. So, for example, if you were forced to make a breaking change to one of your saga data classes between versions 1.0 and 2.0, you would be able to detect the version while the saga was being loaded and take corrective action on the JSON data before it’s deserialized into the new structure. With this ability, you can make gradual changes to each saga entity as it’s loaded, rather than requiring downtime to manually update all existing saga instances.

Along with changing business requirements, sagas can change in other ways. SQL Persistence introduces a new feature called a transitional correlation ID to help. Imagine your company is acquired and you need to transition from the older company’s OrderId to a new GlobalOrderId that contains a different value. The transitional correlation ID allows you to store both order values as table columns, either of which can be used to find the saga during a transitional period. Once all “in-flight” sagas have been updated and the older ID is not needed, it can be dropped, and the transitional ID becomes the new correlation ID.

With these new features, we believe that it will be much easier to evolve your business systems, even in the face of breaking changes and shifting business requirements.

🔗Summary

We’re very proud of the new SQL Persistence. We believe it’s an evolutionary leap forward for persistence in NServiceBus, and we plan to invest heavily in it in the future. Although we remain committed to supporting our other persistence libraries, in most cases, we believe that SQL Persistence should be your first choice for new development and that the only choice you should have to make is which database engine you’re most comfortable using. If you start using it now, you’ll get all the benefits described above, and you’ll be better prepared for a potential move to .NET Core in the future.

So go ahead and take the new SQL Persistence for a spin. First, start with the Simple SQL Persistence Sample, and then read some more about how it all works in the SQL Persistence documentation. It’s worth it.

About the author: David Boike is a developer at Particular Software who always prefers to write his own artisanal, handcrafted SQL and claims this qualifies him as old-school.

🔗Footnotes

¹ Support for .NET Core will depend upon support from the underlying client library. At the time of this writing, .NET Core is supported by Microsoft SQL Server and a prerelease build of the MySQL client, but a compliant Oracle package does not yet exist.