Daniel Calbimonte

How to work handle SQL Azure Databases with PowerShell

May 5, 2015 by

Introduction

PowerShell is a shell used specially to automate administrative tasks.

It is an incredible tool used to programmatically automate tasks like SQL Server tasks, Exchange tasks, Windows tasks, etc. It is very useful to integrate different Microsoft and sometimes non-Microsoft programs.

In this new tutorial, we will show how to install PowerShell for Azure and then how to use it. We will create some databases, edit database properties and retrieve database information using PowerShell.


Figure 1. PowerShell and Azure

Requirements

  • An Azure subscription.
  • A local machine with Windows installed.

Getting started

  1. In your local machine, we will first install Microsoft Azure PowerShell. You can download the installer here.


    Figure 2. Downloading the installer.

  2. Once you have the installer, a configuration Windows will be displayed.


    Figure 3. Time remaining to configure the Microsoft Web Installer.

  3. The Web Platform Installer will initiate the Microsoft Azure PowerShell wizard.


    Figure 4. The PowerShell installer for Azure SDK.

  4. As any installer, it requires you to accept the terms and conditions.


    Figure 5. Microsoft Agreement.

  5. Once it is installed, it will ask to restart the machine. You can restart immediately or later.


    Figure 6. The restart message.

  6. On the Windows Start Menu, open the Microsoft Azure PowerShell. This is the command line used to automate some tasks.


    Figure 7. The Microsoft Azure PowerShell

  7. The Microsoft Azure PowerShell will be similar to this screenshot.


    Figure 8. The PowerShell Command line

    The PowerShell for Azure is different from the PowerShell installed by default in Windows 7, 8 or 2008 or 2012. It has exclusive commands (cmdlets) for Azure activities.

  8. Once the Microsoft Azure PowerShell is installed, you can connect to your Azure Subscription using the Get-AzurePublishSettingsFile.
    Get-AzurePublishSettingsFile

    This command is used to create a file to connect to your Azure Portal and automate tasks there.


    Figure 9. The Get-AzurePublishSettingsFile command.

  9. The command will prompt your browser in the Azure Portal. Choose the account than you want to use.


    Figure 10. The Windows to the Azure Portal section to select an account

  10. Specify your login and password and Sign in.


    Figure 11. The Login and Password to Sign in

  11. A subscription file will be downloaded. This file will be used to connect to Azure.


    Figure 12. The downloading window of the bublish profile.

  12. The subscription file generated is an xml file similar to this one.


    Figure 13. The Published File.

  13. Once the file is generated, you can import the file using the Import-AzurePublishSettingsFile command like this:

    >Import-AzurePublishSettingsFile –PublishSettingsFile
    c:\yourpath\yourpublishfilename.publishsettings


    Figure 14. The message after importing the file.

    With the import command you can now connect to your Azure Subscription.
    You just need to specify the path of the subscription file.

  14. One good command to start it the get-azuresqldatabaseserver

    >get-azuresqldatabaseserver

    With this command you can obtain the SQL Server Name.


    Figure 15. The Database Servers.

    In this example, you have 2 Server names, the location and the administrator login names.

  15. Another important command is the Get-AzureSqlDatabase. The command lists the databases of a specific server.

    Get-AzureSqlDatabase -ServerName "kadhym5ybh"


    Figure 16. The list of Azure Databases

    The command will display all of the databases of the Server named kadhym5ybh. It will also display the CollationName, Edition, MaxSize in Gb, Bytes, the creating date, Service Objective Name and RecoveryPeriodStartDate.

  16. The other command is the New-AzureSqlDatabase. This command is used to create a New Database. You can also especify the Maximum Size, the Edition of the Database and the Collation. The following example creates a Database named Database1 in the kadhym5ybh Server. The database has a maximum size of 5 GB and it is a Web edition with the SQL_Latin1_General collation. The editions can be Web, Business, Basic, Standard, or Premium. The default edition is the Web edition.

    New-AzureSqlDatabase -ServerName "kadhym5ybh" -DatabaseName "sqlshack" -Edition "Web" -MaxSizeGB 5 -Collation "SQL_Latin1_General_CP1_CI_AS"


    Figure 17. The new database created

  17. You can also verify if the database sqlshack was created with 5 GB in the Azure Portal in the Databases section.


    Figure 18. The Database in the Azure Portal

  18. Another PowerShell command is the set-AzureSqlDatabase. With this command you can modify the database properties.

    To do this, first you need to store the database information in a variable. In this example the $db1 variable.

    PS C:\> $db1 = Get-AzureSqlDatabase -ServerName "kadhym5ybh" -DatabaseName "sqlshack”

  19. The next step is to use the Set-AzureSqlDatabase command to change a database property. In this example, we are modifying the Maximum size of the Database1 from 50 to 20.

    PS C:\> Set-AzureSqlDatabase -ServerName "kadhym5ybh" -Database $db1 -MaxSizeGB 1


    Figure 19. The Database size

  20. You can also store the information using the out-file cmdlet. The following example shows how to store the list of database servers in a text file named PS_Results.txt in the scripts folder:

    get-azuresqldatabaseserver | out-file c:\scripts\PS_Results.txt


    Figure 20. The text file with the Servers

  21. You can also export your values to a csv file using the export-csv cmdlet.

    get-azuresqldatabaseserver | export-csv -path C:\scripts\csvresults.csv


    Figure 21. The csv results in MS Excel

  22. Or maybe in you will like the results in xml format using the Export-Clixml cmdlet:

    get-azuresqldatabaseserver | Export-Clixml c:\scripts\results.xml


    Figure 22. The results in xml format

  23. It is also possible to store the results in html format using the ConvertTo-HTML cmdlet combined with the Out-File.

    get-azuresqldatabaseserver | ConvertTo-HTML | Out-File C:\Scripts\results.html


    Figure 23. The results in html format

  24. If you want to filter some columns, you can use the select command. The following example shows the use of the select and also set the format of the results as a list instead of a table.

    get-azuresqldatabaseserver | select ServerName, Location | Format-List


    Figure 24. The results of the select in a Format of a list.

  25. In order to get a list of cmdlets available, you can use the Get-Command

    Get-Command


    Figure 25. The list of cmdlets of Azure PowerShell

  26. If you want an example of a specific command, you can use the get help cmdlet.

    The following example shows how to use the the Remove-AzureSqlDatabase cmdlet.

    get-help Remove-AzureSqlDatabase –detailed


    Figure 26. The help of the Remove-AzureSqlDatabase

    Conclusion

    In this article, we installed the Microsoft Azure PowerShell and then we used some PowerShell commands to create, modify Azure Databases from our local machine using PowerShell. We also stored the results in different formats like html, xml, csv and txt.

    Daniel Calbimonte
    Latest posts by Daniel Calbimonte (see all)
PowerShell, SQL Azure

About Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams. He also helps with translating SQLShack articles to Spanish View all posts by Daniel Calbimonte

168 Views