Transaction when writing to database and create an database

Question

Hi we are building an multi tenancy solution where each tenant will have its own database. We will keep the information about the tenant in an index database. When creating an tenant the following steps will be made.

Create Tenant class, save it to the database where it will get an incremental number which will be used to name the database that we will create. Create the database with the incremental number. And then update the database schema.

This is not any problem at all, the problem is if some of the second to stages will throw an exception. Then we want to rollback the saving of the tenant. Can we do this in an bigger transaction. To be able to use the incremental number we are forced to commit the transaction to get it. Should we use any other naming strategies?

ideas?

Answer 1

From MSDN:

The CREATE DATABASE statement must run in autocommit mode (the default transaction management mode) and is not allowed in an explicit or implicit transaction.

(This applies to ALTER DATABASE as well.)

However, if your application logic decides that the operation should fail (note: move as much of that checking as you can as early in the logic as you can), you can simply DROP DATABASE instead (and rollback the index database transaction), because the next attempt should presumably recreate the whole thing anyway. Not an ideal solution by any means, but you'll have to work around the constraint I quoted above.