title: How to use Flyway in a multi-schema MS SQL Server environment?
body: <p>We have a multi-tenant SaaS application using the "multi-schema" strategy i.e. every customer has dedicated schema in the same database instance. We are using MS SQL Server as the database which switches between schema's through the "default schema" setting for SQL Server "users". For example customers A, B and C are configured in SQL Server as follows:</p>
  
  <ul>
  <li>Customer A: <code>user_A</code> with default schema <code>schema_A</code> </li>
  <li>Customer B: <code>user_B</code> with default schema <code>schema_B</code> </li>
  <li>Customer C: <code>user_C</code> with default schema <code>schema_C</code> ... and so on.</li>
  </ul>
  
  <p>In our application, we switch DataSource connection to point to correct schema for each customer by setting the SQL Server "user" on the Connection by executing following SQL before every query: </p>
  
  <pre><code>EXECUTE AS USER = 'user_A';
  </code></pre>
  
  <p>This poses some problems for us when trying to use Flyway for managing state of schema versions - in a global manner. Since flyway's schema support only takes in a list of schema names this does not work for MS SQL Server. Flyway performs the migrations on the default schema of the user provided with the DataSource configuration; which in SQL Server's case, the "user" needs to vary per Customer/schema. </p>
  
  <p>Ideally we would have a callback like <code>FlywayCallback.beforeEachSchemaMigrate(Connection)</code> that will let us set the desired User Context per schema by executing the "Execute as User" statement before each migration per schema. Not sure why that hook isn't there? </p>
  
  <p>Another short coming in flyway is the convention of using first schema in list of schema's, as the one holding the <code>schema_version</code> table. This is not desired in a SQL Server based multi-tenant environment. As we cannot assume that the schema containing the <code>schema_version</code> table is also a real Customer schema. Keep in mind in a SaaS application like ours, schema's per tenant/customer are created/destroyed on the fly. When a user sign's up, part of the provisioning process creates the new schema based on some conventions. So the list of schema's is dynamic for us.</p>
  
  <p>Ideally we can tell Flyway to use a given schema to create the <code>schema_version</code> table without trying to run the migrations on that schema. Typically this would be the <code>dbo</code> schema (which is default schema in SQL Server). We use dbo schema to hold tables that are global in nature across all tenants, schema_version would be considered a global table.</p>
  
  <p>So in the end after a successful migration, our database should look like the following:</p>
  
  <pre><code> - dbo.schema_version
   - schema_A.my_tables
   - schema_B.my_tables
   - schema_C.my_tables
  </code></pre>
  
  <p>All of the above schema's being in the same "State", dictated and controlled by <code>dbo.schema_version</code> table.</p>
  
  <p>Is this currently possible?</p>