Sql Azure db – database offline

Testing application behavior while taking database down is a very common scenario in the test environment. In Sql standalone, this is very easy as one can take the service offline / execute alter statement to take database offline. This is not the same case with SQL Azure as the alter statement to take database offline is not supported. Here are a few work around to solve this.

There are multiple options to achieve similar behavior in SQL DB:

  1. Rename database to a different name (alter database rename statement)
  2. Delete the database and restore after testing using restore feature in SQL DB
  3. Remove the server level firewall rules so no one from the Internet can access the database. More info here

Problems with Rename

  1. We may not rename the database if in Geo DR relationship
  2. Point in time restore chain is broken because now we have to restore the database with a different name

Problems with Deleting database

  1. There is no soft delete and restore can take hours, in the worst case up to RTO
  2. Delete / restore can cause data loss up to the provided RPO value
  3. Need to rename the database to the original name post restore
  4. From the service perspective this will be a different database

Problems with firewall rules

  1. If we have multiple databases, we loose connectivity to all of them by default. However this can be mitigated if database firewall rules set
  2. From the service perspective there is no loss of continuity with this approach and is preferred