SQL in Cloud

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

Pages

  • Alter database in SQL V12
  • Changing MAX_DOP setting in SQL Azure DB V12
  • Connection Pooling and Isolation level reset
  • Contained users in SQL Azure DB V12
  • Full-text search in SQL Azure
  • Large Index Rebuild in SQL Azure V12
  • Row versioning in SQL Database version (V12)
  • Sql Azure db – database offline

Archives

  • May 2016
  • December 2015
  • July 2015
  • May 2015
  • April 2015

Categories

  • SQL Azure (8)
  • Uncategorized (3)

WordPress

  • Log in
  • WordPress

Subscribe

  • Entries (RSS)
  • Comments (RSS)
  • Alter database in SQL V12
  • Changing MAX_DOP setting in SQL Azure DB V12
  • Connection Pooling and Isolation level reset
  • Contained users in SQL Azure DB V12
  • Full-text search in SQL Azure
  • Large Index Rebuild in SQL Azure V12
  • Row versioning in SQL Database version (V12)
  • Sql Azure db – database offline