Azure seamless upgrade when database schema changes

Question

Let's say I have a production and staging deployment both using their own (SQL Azure) databases. If the schema in staging has changed and needs to be deployed to production is there a defined way of achieving the database upgrade on the production database (without downtime)?

e.g. If I swap VIP staging <-> production (and at same time automate changing connection strings somehow) what is the best process to automate upgrading the sql azure database.

My thought would be to spot the environment change in RoleEnvironmentChanging (though not sure that VIP swap even fires RoleEnvironmentChanginng) and run the sql script against the to-be database (i.e. prod) at that point, however I need to make sure that script is only run once and there will be multiple instances transitioning.

Answer 1

I have been looking on best practices for this all over the place and have found none. So far this is what I do:

• Deploy to staging (Production is already running)
• Copy app_offline.htm file to the web root on Production. This way I block users from using the application, thus blocking changes to the database. I am using only one instance.
• Backup the database.
• Run DDL, DML and SP scripts. This updates the production database to the latest schema.
• Test application on Staging.
• Swap VIP. This brings the application back online since the app_offline.htm file is not present on Staging (new Production).
• If something goes wrong, swap VIP again, restore database and delete app_offline.htm.

With this approach I have a downtime of ~5 minutes approximately; my database is small, which is better than waiting for the Vm to be created and users getting errors.

Answer 2

So you have production deployment which has its own SQL Azure database and staging deployment which has its own SQL Azure database. In this situation both the application have their connection string pointing to two different databases.

Your first requirement is to change the Database schema on fly when you swap the deployment or do something and I have the following concern with that design:

1. If you write any code inside the role to do "ONCE and only ONCE" action, there is no guarantee that that this will happen only ONCE. It will happen multiple time depend on several scenario such as

   1.1 In any situation you VM needs to be reimage by the system and this CODE will do the exactly same what it did during last reimage

   1.2 You might protect it to not happen at role start or VM start by some registry method of some external key but there is full proof mechanism that not to happen.

2. Because of it I would suggest when you are ready to SWAP your deployments you can:

   2.1 Run the script to update to the production related SQL Azure schema (This will have no impact on application download because it is not touched but while your database schema is updated, you may know better how it impact your application)

   2.2 Change the configuration in staging deployment to point to production SQL Azure (This will not have any production application downtime at all)

   2.3 SWAP the deployment (This will also have no application downtime)

So even when you manually update the DB Schema and then SWAP the deployment there is no significant downtime besides the time take by DB to update the schema.