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:
- Creating/deleting a Windows Azure SQL database server in our subscription (the SQL Server instance equivalent in on-prem)
- Defining firewall rules to allow access at the server or database level (SQL database provides two firewall layers - server level and database level)
- 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:
- First they will create a Server in Windows Azure SQL database.
- Then they will create a database instance on that server.
- After that firewall rules definitions will need to be set in order for the application to get access to the databases.
- 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;
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
# 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()
# Create the database
$command.CommandText = "CREATE DATABASE [database_name]"
# 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).
Shay Yannay is a Senior Software Developer and Windows Azure Domain Expert.
He is experienced with designing and developing highly scalable, distributed, 24x7 availability complex system. Shay also specializes in performance management & diagnostics of multi-tier applications.
He is passionate about the cloud technologies and trends, specifically with Microsoft Azure.
He currently works for Dell as an Azure Specialist.
Shay holds a B.Sc in Communication Systems Engineering from the Ben-Gurion university.
Personal Blog: http://shayyannay.wordpress.com