Home > SQL, SQL Azure > SQL Azure Database Take Offline

SQL Azure Database Take Offline

As everyone knows that database administrator can take SQL Server Database offline for maintenance purpose with following command


EXEC sp_dboption N'yourDBName', N'offline', N'true'

But how we can achieve this with SQL Azure? Database administration in SQL Azure differs most from SQL Server in terms of physical administration. SQL Azure databases cannot be controlled by physical level administration as you don’t know physical resources used in SQL Azure. This means we cannot take SQL Azure database offline.

To deal with this issues here is small workaround using SQL Azure Firewall. Actually we can block SQL Azure Firewall rules.

Basically, the firewall settings of your SQL Azure Server can be listed with the help of SQL query against your master database.

SELECT * FROM sys.firewall_rules

But we cannot fire INSERT, UPDATE, and DELETE (I/U/D) Statements against your system catalogs. It will give error “Ad hoc updates to system catalogs are not allowed.”

We can use following SQL Azure stored procedures. These stored procedure is only available in the master database to the server-level principal login. (not supported in on-premise SQL Server)

· sp_set_firewall_rule

Creates or updates the firewall settings for your SQL Azure server and takes three parameters rule name, start_ip_address & end_ip_address

· sp_delete_firewall_rule

Removes firewall settings from your SQL Azure server and only one parameter that is rule name.

-- Create new firewall rule for only IP
exec sp_set_firewall_rule N'BigData Rule1','',''
-- Update firewall rule to also allow IP
exec sp_set_firewall_rule N'BigData Rule1','',''
-- Remove example firewall rule
exec sp_delete_firewall_rule N'BigData Rule1'

Simply to take your SQL Azure Database offline you need to remove all firewall rules first with sp_delete_firewall_rule. But this will take all database offline from your server as these Firewall settings applicable for your server level & not taking care of transactions which is in progress. 😦


Sandip Shinde

  1. no body
    November 13, 2020 at 3:26 pm

    this is just plain stupid, thnx microsoft

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: