Question: Handling Schema Updates

[MrMDavidson]

I just opened #49 and was thinking it'd be good to open up a discussion about this as it's something that's affected us before and I'm sure we're not the only one.

We're running Rebus in a cloud scenario with auto-scaling. At this very moment in time we have 12 web servers running Rebus as a one way queue to communicate with 5 back end workers. (Those numbers change throughout the day/week/month). This is all using the SQL Transport and running under a tightly controlled environment where the application runs with the least amount of privileges possible for our application. As such we're faced with two issues:

1. The application cannot make schema modifications at run time - it's simply going to throw because the permissions aren't there. This is a hard requirement.
2. When we deploy the changes are progressively rolled out. So when we click the "Deploy!" button it might be that N web servers and Y backend servers are taken out, upgraded, and put back in. This means we then have a mix of some old code and some new code running in production. This continues until eventually all code is new code. But we have a measurable moment in time (tens of minutes) where code is running side by side. Again, this is a hard requirement.

As a result of these we need to carefully plan and roll out database changes to be backwards compatible with the "old" version of the code (Eg. We'd not mark a column as not-null without it also having a default or else old code would fail to insert) as well as revertable should we need to roll back the application.

The move to table-per-queue, for instance, has been a headache for us. I wonder if there's some way we can come up with that makes these schema modifications easier to manage. Maybe it's something as simple as change log documentation for releases of Rebus.SqlServer that indicates any schema modifications required. Or an embedded resource into the schema progressively builds the "now" version of the table.
Eg.

IF NOT EXISTS (SELECT 1 FROM sys.Tables WHERE Name = '$TableName$' AND schema_id = SCHEMA_ID('$SchemaName$'))
BEGIN
  CREATE $Schema$.$TableName$ (... )
END

-- In V2 we added the Foo column
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = 'Foo' AND object_id = OBJECT_ID('$SchemaName$.$TableName$'))
BEGIN
  ALTER TABLE $SchemaName$.$TableName$ ADD Foo VARCHAR(MAX) NULL
END

-- In V3 we did X

There could then be a static method IList<string> SqlServerTransport.GetMigrationScripts(string schema, string table) that returns all of these snippets and could be executed. The existing EnsureTableNameIsCreated() method would just be a wrapper around this. But for people that need to manage these migrations externally they could pull in the list of migrations and execute those themselves. There are libraries that manage this for you (eg. FluentMigrator) but bringing in a whole new library for it feels a bit excessive.

Thoughts?

[DouglasHammon]

@MrMDavidson I'm curious if you found a way to handle running the Rebus schema modifications outside of the main application and run them in a separate application designed to just run database migrations (similar to DbUp or FluentMIgrator). We have a similar requirement where the main application does not have the permissions to make schema modifications, but we have a separate application that does that is in charge of database migrations.

[MrMDavidson]

@MrMDavidson I'm curious if you found a way to handle running the Rebus schema modifications outside of the main application and run them in a separate application designed to just run database migrations (similar to DbUp or FluentMIgrator). We have a similar requirement where the main application does not have the permissions to make schema modifications, but we have a separate application that does that is in charge of database migrations.

We had a manual process of effectively copy/pasting of rolling custom migrations for FluentMigrator whenever we bumped Rebus versions. It was a fairly time consuming process. The investigation I did at the time to introduce something like;

IList<string> SqlServerTransport.GetMigrationScripts(string schema, string table) => new string[] {
$@"IF NOT EXISTS (SELECT 1 FROM sys.Tables WHERE Name = '{table}' AND schema_id = SCHEMA_ID('{schema}'))
BEGIN
  CREATE {schema}.{table} (... )
END",

$@"-- In V2 we added the Foo column
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = 'Foo' AND object_id = OBJECT_ID('{schema}.{table}'))
BEGIN
  ALTER TABLE {schema}.{table} ADD Foo VARCHAR(MAX) NULL
END",

$@"-- In V3 we did X..."
};

Seems like it'd be fairly easy to implement without breaking the external APIs, but I never got around to a PR for it. Consumers of the Rebus could then have their own migration plans that effectively just call this method and run it. A further enhancement would be to have the "version" of the table externally tracked... so you'd pass in what version of the table you want (eg. 2) and it'd return only the appropriate migration scripts for that version (in this example, omitting the 2nd). That'd mean that the migration plans should be the same for a given DB version even if the library has been upgraded since (eg. Someone runs your migration on a fresh DB but the library has now been upgraded to V+1)

[DouglasHammon]

@MrMDavidson Thanks!