Click here to Skip to main content
Click here to Skip to main content
Go to top

Windows Azure SQL Database Management

, 11 Dec 2012
Rate this:
Please Sign up or sign in to vote.
Find how Windows Azure SQL database and cloud service common set-up can be automated.

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).

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Shay Yannay
Software Developer Quest Software
Israel Israel
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
Follow on   Twitter

Comments and Discussions

 
GeneralMy vote of 1 PinmemberManjunath_x_s7-May-13 21:48 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140905.1 | Last Updated 11 Dec 2012
Article Copyright 2012 by Shay Yannay
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid