Windows Azure SQL Database Management

Introduction 

Windows Azure SQL database provides a very handy management commands which are exposed either by REST API or PowerShell Cmdlets. 

Looking closely we can find various management operations such as: 

1. Creating/deleting a Windows Azure SQL database server in our subscription (the SQL Server instance equivalent in on-prem) 
2. Defining firewall rules to allow access at the server or database level (SQL database provides two firewall layers - server level and database level)
3. Updating the server main password. 

The management operations above can well be performed from the Azure management portal, so you probably ask yourself why are those commands exposed in the first place? The answer is simple - Automation.

Here are a few steps that probably every company does in order to set-up a Windows Azure cloud service:

1. First they will create a Server in Windows Azure SQL database.
2. Then they will create a database instance on that server.
3. After that firewall rules definitions will need to be set in order for the application to get access to the databases.
4. Finally the cloud service package will be uploaded to Windows Azure Cloud. 

Using the Code

Automating that process can really speed things up when setting a new environment. Puling our sleeves up, let's create a PowerShell script that accommodates the configuration process above: 

# Create a new server
New-AzureSqlDatabaseServer -AdministratorLogin [user_name] 
  -AdministratorLoginPassword [password] -Location [data_center_name]

# Create server firewall rule
New-AzureSqlDatabaseServerFirewallRule –ServerName "[server_name]" 
   -RuleName "allowAzureServices" -StartIpAddress 0.0.0.0 –EndIpAddress 0.0.0.0

# Setup a new database
$connectionString = "Server=tcp:[server_name].database.windows.net;
  Database=master;User ID=[user_name]@[server_name];
  Password=[password];Trusted_Connection=False;Encrypt=True;" 
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

# Verify the existence of the desired database
$command = New-Object System.Data.SQLClient.SQLCommand
$command.Connection = $connection
$command.CommandText = "select name from sys.databases where name='[database_name]'"
$reader = $Command.ExecuteReader()

if(!$reader.HasRows){
# Create the database
$command.CommandText = "CREATE DATABASE [database_name]"
$command.ExecuteNonQuery()
}
$reader.Close
$connection.Close

# Create a cloud service
$packagePath = "[.cspkg path]" 
$configPath = "[.cscfg path]"
New-AzureService -ServiceName "[service_name]" 
  -Label "[service_label]" -Location "[data_center_location]"

# Upload an application package to the cloud service production slot
Set-AzureSubscription "[subscription_name]" -CurrentStorageAccount "[azure_storage_account_name]"
New-AzureDeployment -ServiceName "[service_name]" -Slot "Production" 
  -Package $packagePath -Configuration $configPath -Label "[deployment_label]"

Points of Interest 

The automation process above can be launched not only from your local computer but from the cloud service itself simply by adding a start-up script which will be launched when the role instance starts (of course, we need to take care that a single vm will handle the flow).