Share via

Versioning Windows Azure SQL Database Schemas by Using Federations

  • Article

Traditionally database schema upgrades have been an “all or nothing” affair. A production database had a single schema, which all applications used.

Simple schema changes such as the following could be implemented without breaking backward compatibility of existing applications:

  • Adding a new table;
  • Adding a column to an existing table;
  • Adding new stored procedures and functions;
  • Adding new foreign key relationships.

However more complex schema changes would break backwards compatibility:

  • Changing the data type of a column used in a key;
  • Deleting a column;
  • Deleting a table;
  • Changing foreign key relationships.

A number of methods evolved for dealing with schema upgrades, but however well they worked, they all required varying amounts of planning and effort to implement.

Now, Windows Azure SQL Database Federations greatly simplifies schema upgrades. Since each Federation member is a separate database, they need not all have the same schema. This enables applications to run against different versions of a database schema, and enables rolling out changes initially to only a subset of federation members.

Sample Scenarios

We discuss two application scenarios: multi-tenant and single-tenant.

In both cases, we assume that an application is using a database similar to the well-known Adventure Works database sample, and that we want to implement a new feature: suggesting purchase recommendations based on a customer’s sales history.

Note that in both scenarios, the application layer will need to be able to detect which version of the schema it is using, and decide whether to implement new features accordingly.

Scenario: Adding New Features to a Multi-Tenant Application

In this scenario the database is federated on TenantID. All tables with data specific to a given tenant have primary keys that contain TenantID. Each tenant may or may not occupy a separate federation member, depending on application security requirements.

To implement the new feature (suggesting purchase recommendations based on a customer’s sales history) we design a new schema, which includes a new Recommendations table, which contains a foreign key to an existing Customers table.

Not all tenants are equally ready to implement the feature, and some may not wish to implement it at all. So initially we apply the schema changes to federation members that contain those tenants ready to be upgraded. And as new tenants become ready, we then apply the schema changes to them.

Application Layer Changes

The applications using the database need to understand which database schema is being used, for any given tenant. One way to do this is to check for the presence of the new table, and depending on its presence, implement the new feature’s logic, or not.

Implementation Steps

To carry out this process, do the following steps.

  1. Decide on a versioning strategy and if necessary, federate your database accordingly. In this scenario the database is already federated on tenant id.
  2. Design the new schema and create a script that upgrades the existing database schema.
  3. If federation members can contain more than one tenant, then perform SQL Database SPLIT operations so that the tenants to be upgraded with the new schema are all in separate federation members. If each federation member already contains only a single tenant, this step in not needed.
  4. Apply the upgrade script to each federation member that contains tenants who are to be upgraded to the new schema.

Scenario: Single Tenant

This scenario is similar to the preceding one. However we do not have a TenantID field that neatly separates database schema versions. Instead, we must use some other data value to separate the schema versions. Let’s assume that the new feature, and therefore the new schema, is being rolled out on a Customer basis, so we will upgrade selected customers to the new schema. If we can easily separate ranges of CustomerIDs that are to be upgraded, then we can use the same strategy as the preceding scenario.

Let’s assume that customers cannot be separated into neat ranges, so the preceding solution won’t be practical. In this case we create a new federation based on SchemaVersion. We add a one-row table that contains a SchemaVersion column, and add this column to the primary keys of Customers and related tables that we will federate on.

In this example there will be two federation members: one with the existing database schema, and one with the new schema.

Application Layer Changes

To ensure forward and backward compatibility, the application layer code must be able to identify what schema to use for a given customer, and on that basis decide whether to apply the new feature’s logic. In this example you can do this by testing the Customer’s SchemaVersion column.

Implementation Steps

To carry out this process, do the following steps.

  1. Decide on a versioning strategy and federate your database accordingly. In this scenario the database is federated on SchemaVersion.
  2. Design the new schema and create a script that upgrades the existing database schema.
  3. Perform a SQL Database SPLIT operation to create a new federation member which will hold customers with the new schema.
  4. Apply the upgrade script to the new federation member.
  5. Migrate selected customers to the new federation member.