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:
- Rename database to a different name (alter database rename statement)
- Delete the database and restore after testing using restore feature in SQL DB
- Remove the server level firewall rules so no one from the Internet can access the database. More info here
Problems with Rename
- We may not rename the database if in Geo DR relationship
- Point in time restore chain is broken because now we have to restore the database with a different name
Problems with Deleting database
- There is no soft delete and restore can take hours, in the worst case up to RTO
- Delete / restore can cause data loss up to the provided RPO value
- Need to rename the database to the original name post restore
- From the service perspective this will be a different database
Problems with firewall rules
- If we have multiple databases, we loose connectivity to all of them by default. However this can be mitigated if database firewall rules set
- From the service perspective there is no loss of continuity with this approach and is preferred