Big Data, Data Analytics and Data Science | BI-BIGDATA.com

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

</pre>
<pre>

EXEC sp_dboption N'yourDBName', N'offline', N'true'
 OR
ALTER DATABASE N'yourDBName' SET OFFLINE

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 192.145.1.200
exec sp_set_firewall_rule N'BigData Rule1','192.145.1.200','192.145.1.200'
-- Update firewall rule to also allow IP 192.145.1.200
exec sp_set_firewall_rule N'BigData Rule1','192.145.1.200','192.145.1.250'
-- 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. 😦

Thanks,

Sandip Shinde