Windows Azure SQL Database Management with PowerShell

Editor’s Note: This post comes from Karthika Raman, Senior Technical Writer, Microsoft Data Platform team.

With the release of Windows Azure PowerShell cmdlets in November 2012, several PowerShell cmdlets were added for Windows Azure SQL Database.  With the added cmdlets, you can provision a database server, configure firewall rules and create a database with ease.

This blog posts gives you information to get started, followed by code examples for a step by step walkthrough starting from connecting to your Windows Azure subscription, to provisioning a server, configuring fire wall rules, and creating and configuring a database. It includes deleting the database and the server once the tasks have been completed.  To manage your Windows Azure SQL Database using Windows Azure SQL Database cmdlets, you must install the latest Windows Azure SDK.

An extended version of the script can be downloaded from the TechNet code gallery.

If you have never used Windows Azure PowerShell before, use the following steps to get started:

  1. Select a computer that is running Windows 8, Windows 7, Windows Server 2012, or Windows Server 2008 R2.
  2. Upload a management certificate to your Windows Azure Subscription. This enables client access to Windows Azure. 
  3. Install the Windows Azure module for Windows PowerShell: Install the Windows Azure module and then set the Window PowerShell execution policy. The Windows PowerShell execution policy determines the conditions under which configuration files and scripts are run. You can either run cmdlets by using the Windows Azure PowerShell command shell or by importing the Windows Azure cmdlets directly into Windows PowerShell.
  4. Configure connectivity between Windows Azure and your workstation: Configure the management certificate and subscription details automatically by downloading the PublishSettings file from Windows Azure and importing it. You can configure this manually with the Set-AzureSubscription cmdlet. Use the following sample code to configure the subscription and the certificate to be used when connecting to the subscription.  This should be done once for every subscription and on each new client machine.

$thumbprint = “24189B94425ECDF8536C547730A8DDE2B6DF3E5E”

$myCert = Get-Item cert:\\CurrentUser\My\$thumbprint

$subID = “e2f275b8-2739-4593-a5e8-6d9ee1539267″

Set-AzureSubscription -SubscriptionName “msdn” -SubscriptionId $subID -Certificate $myCert

Once you have completed the initial setup you can start managing your Windows Azure SQL Database cmdlets.

To see the list of Windows Azure SQL Database cmdlets, type “help sql”.

          

Following is a step by step walkthrough for provisioning a server, adding a firewall rule to the new server and creating a new database. It includes steps to remove the database and server once the tasks have been completed.  An extended version of the script can be downloaded from the TechNet code gallery.

  1. Select a subscription to use:Select-AzureSubscription -SubscriptionName “msdn”

    You can use Get-AzureSubscription to list all the subscriptions stored in the default subscription data file. See this topic for more information on using this cmdlet.

  2. List all the existing SQL Database Servers in the subscription you specified in the first step.

    Get-AzureSqlDatabaseServer
        

  3. Provision a new SQL Database Server: Use the New-AzureSqlDatabaseServer cmdlet to create a new database server. Provide the location, administratorLogin and password.

    New-AzureSqlDatabaseServer -location “West US” -AdministratorLogin “mylogin” -AdministratorLoginPassword “my@Password”
         

    Note: To update server properties, use the Set-AzureSqlDatabaseServer cmdlet.

  4. Create a Firewall Rule: To allow connections to the database server you must create a rule that specifies a range of IP addresses from which connections are allowed. Use the New-AzureSqlDatabaseServerFirewallRule to create a new firewall rule. Specify the Server Name, and the IP address range (Start and End)

    New-AzureSqlDatabaseServerFirewallRule -ServerName “sub9zmyalg” -RuleName “myrule” -StartIPAddress “111.111.111.222″ -EndIPAddress “222.222.222.222″

    Note: To get a list of rules for a server, use the Get-AzureSqlDatabaseServerFirewallRule cmdlet. 

  5. Create a connection to the server using Sql Authentication. This establishes a connection context to the server that you can then use to perform various tasks on the server.There are two ways to do this:
    • Using the Get-Credential cmdlet, where you will be prompted to provide a login and password information as seen below
       

    $cred = Get-Credential

     

    #create a new database server connection context using the credentials
     

    New-AzureSqlDatabaseServerContext  -ServerName “sub9myalg” –Credential $cred
     

    • By  creating a credential object that includes your login information:
       

    $servercredential = new-object
    System.Management.Automation.PSCredential(“mylogin”, (“my@Password”  |
    ConvertTo-SecureString -asPlainText -Force))

    #create a connection context

    $ctx = $server | New-AzureSqlDatabaseServerContext -Credential $serverCredential 
     

  6. Create a new database: Use the New-AzureSqlDatabase cmdlet to create a new database. If you specify just the database name, a database with the name you specified is created with default values for other properties: The resulting database is a web edition database with a MaxSize of 1 GB, and SQL_Latin1_General_CP1_CI_AS collation.

    New-AzureSqlDatabase $ctx –DatabaseName “mydatabase” 

     

    Note: To get a list of all the databases in the server, use the Get-AzureSqlDatabase cmdlet.

  7. Modify the database: Use the Set-AzureSqlDatabase cmdlet to change the database properties.  The following example illustrates modifying the max size of the database created in the previous step. The first cmdlet modifies the MaxSize property and the second cmdlet displays the properties so you can confirm the change.

    Set-AzureSqlDatabase $ctx –Databasename “mydatabase” –MaxSizeGB 20

    Get-AzureSqlDatabase $ctx –DatabaseName “mydatabase”

     

  8. Delete a database: Use the Remove-AzureSqlDatabase cmdlet to delete a specific database.  You will see a confirmation prompt as illustrated below

    Remove-AzureSqlDatabase $ctx –DatabaseName “mydatabase”

     

  9. Delete the database server: Use the Remove-AzureSqlDatabaseServer to delete the database server. You will see a confirmation prompt as illustrated below

    Remove-AzureSqlDatabaseserver –ServerName “sub9zmyalg”

Additional Reading

Windows Azure SQL Database cmdlets

http://michaelwasham.com/tag/sql-azure-cmdlets/

http://www.sqlskills.com/blogs/bobb/getting-windows-azure-sql-database-diagnostics-using-windows-azure-cmdlets/