PowerShell is Microsoft’s modern scripting language for system administration that supports a wide variety of tasks by executing commands (known as cmdlets, pronounced command-lets) from the PowerShell command line. Microsoft has also developed PowerShell cmdlets for managing Microsoft Azure, including a number of useful SQL Database commands. In this post, I’ll show you how to get started using these cmdlets.

Even if you already have PowerShell installed, these special cmdlets for Microsoft Azure need to be installed separately. The following instructions describe how to download and install the cmdlets.

Installing the Microsoft Azure PowerShell cmdlets

To install the Microsoft Azure PowerShell cmdlets, follow these steps:

1. Navigate your web browser to https://www.windowsazure.com/en-us/downloads.
2. Scroll down to Command-Line Tools, and click the Install link beneath Windows PowerShell.
   
3. When prompted to run or save, click Run.
4. If the User Account Control dialog appears, click Yes.
5. In the Web Platform Installer dialog, click Install.
   
6. Click I Accept to start the installation.
7. When installation completes, click Finish.
8. Click Exit to close the Web Platform Installer dialog.

Using the PowerShell Integrated Scripting Environment

Although you can use a simple text editor (even Notepad) to write PowerShell scripts, the PowerShell Integrated Scripting Environment (ISE) is a much more productive environment. As you’ll see, it offers some nice features, such as syntax highlighting and IntelliSense-style auto-completion.

PowerShell can be launched from the command line. To start the PowerShell ISE and view help information for the Azure SQL Database cmdlets, follow these steps:

1. Open a command prompt.
2. At the command prompt, type powershell_ise.
3. At the PowerShell ISE prompt, type get-help get-azuresql, and then pause. In a moment, a popup window appears showing all the cmdlets that start with get-azuresql.
   
4. Double-click on any of the cmdlets to complete the command, and then press Enter to view help for the selected cmdlet.

Configuring PowerShell for your Microsoft account

Before you can start using PowerShell to manage Azure, you need to configure it for your account. This is simply a matter of logging in to the portal, and then running a few PowerShell commands to retrieve your account information from Azure and import it into PowerShell. This section walks you through the process.

To configure PowerShell for your Microsoft account, follow these steps:

1. Log in to the Microsoft Azure portal at https://manage.windowsazure.com. This step is necessary for PowerShell to identify your account. If you don’t log in first, you will be prompted to log in when you try to retrieve your account settings in PowerShell.
2. Start the PowerShell ISE, as explained in the previous procedure.
3. Type Get-AzurePublishSettingsFile to retrieve your account settings. Internet Explorer will open up automatically and download a .publishsettings file with your account information.
4. Click Save to save the .publishsettings file to your default Downloads folder.
   Important: The .publishsettings file should be kept safe and private, because it effectively provides access to the Azure subscriptions on your Microsoft account.
5. Back in the PowerShell ISE, type Import-AzurePublishSettingsFile <.publishsettings file>, where <.publishsettings file> is the complete file name (with path) of the account settings file you just saved to your default Downloads folder. (This is typically C:\Users\<username>\Downloads\<subscription-name>.publishsettings.)

The PowerShell ISE doesn’t boast with a message when the settings are imported successfully. You’ll only get an error message if it fails. Otherwise, you’ll know that all went well if you are silently returned back to the PowerShell command-line prompt.

You are now ready to create a server, and then you can create a database on that server.

Creating a new server

First, create a new server and add a firewall rule for your IP address so that the server will allow you to connect to it using PowerShell. To do this, follow these steps in the PowerShell ISE:

1. Type New-AzureSqlDatabaseServer –Location “East US” –AdministratorLogin “<new-login>” –AdministratorLoginPassword “<new-password>“, where <new-login> and <new-password> are the credentials you want to assign for the new server. The server is created, and PowerShell responds by displaying the new server name.
   
2. Type New-AzureSqlDatabaseServerFirewallRule –ServerName <server-name> –RuleName <any-name> –StartIpAddress <your-ip-address> –EndIpAddress <your-ip-address>, where <server-name> is the name of the new server created in step 1, <any-name> is an arbitrary name for the new rule (no spaces permitted), and <your-ip-address> is the IP address of your machine. This command creates a new firewall rule to allow PowerShell access to the server from your IP address. If you don’t know your IP address, you can find out what it is by visiting whatismyipaddress.com.
   

Creating a new database

The New-AzureSqlDatabase cmdlet creates a new database. Before you can use this cmdlet, you must first create an object with your credentials, and then you use those credentials to create a context associated with the server that you want to create the new database on. You store the server context in a variable, and then you specify the server context variable with the New-AzureSqlDatabase cmdlet to create the database (as well as all other cmdlets you might run for that particular server).

To create a new database now, follow these steps in the PowerShell ISE:

1. Type $creds = new-object System.Management.Automation.PSCredential(“<login-name>“, (“<login-password>” | ConvertTo-SecureString –asPlainText –Force)), where <login-name> and <login-password> are the administrator login and password you assigned when you created the server in the previous procedure. This stores those administrator credentials in a secure string named $creds.
2. Type $context = New-AzureSqlDatabaseServerContext –ServerName <server-name> –Credential $creds, where <server-name> is the name of the server you created in the previous procedure. (The server name is displayed when you create the server) This creates a context associated with the credentials you created in step 1 and the server you created in the previous procedure, and it stores that context in an object named $context.
3. Type New-AzureSqlDatabase –Context $context –DatabaseName MyNewDb. This creates a new database named MyNewDb. The database is created on the server associated with $context, using the credentials associated with $creds. When the database is created, PowerShell displays information about the new database.
   
4. It’s often useful to view all the databases that exist on the server. To do so, type Get-AzureSqlDatabase –Context $context. As shown by this cmdlet’s output below, the server includes a master database, just as an on-premises SQL Server does.
   

The database you just created with New-AzureSqlDatabase is, by default, a Web edition database with a maximum size of 1 GB and the default collation. This is the same type of database that gets created when you use Quick Create in the Microsoft Azure management portal. To override these defaults, specify the –Edition, –MaxSizeGb, and –Collation switches with an edition, maximum size, and collation of your own choosing. For example, the following statement creates a Business edition database with a maximum size of 150 GB (the largest possible):

New-AzureSqlDatabase –Context $context –DatabaseName MyBigDb -Edition Business -MaxSizeGB 150

You can also change the edition and maximum size (but not the collation) of an existing database by using the Set-AzureSqlDatabase cmdlet with the –Edition and –MaxSizeGb switches. For example, you can use the following command to reconfigure the MyNewDb database you just created as a Business edition database with a maximum size of 20 GB:

Set-AzureSqlDatabase -Context $context -DatabaseName MyNewDb -Edition Business -MaxSizeGB 20

Deleting a database

The Remove-AzureSqlDatabase cmdlet deletes a SQL Database. To delete the MyNewDb database you just created in the previous section, follow these steps:

1. Type Remove-AzureSqlDatabase –Context $context –DatabaseName MyNewDb.
2. When prompted to confirm, click Yes.

If you are using Remove-AzureSqlDatabase to write scripts you intend to run with no user intervention, you can include the –Force switch. This switch causes the database to be deleted immediately, without being prompted to confirm.

There’s a lot more you can do with these PowerShell cmdlets of course, so be sure to explore all the help topics to learn about all the other supported functionality!

In my previous post, I showed you how to sign up for a Windows Azure subscription. Once you’ve got your subscription, it’s easy to create a SQL Database, which is very much like an ordinary SQL Server database hosted in the cloud. In this post, I’ll show you how. Essentially, you first need to create a server on Windows Azure, and then you can create the database on that server.

Creating a server

It’s easy to create a server, which is akin to an instance of SQL Server in the sense that it can host multiple databases. All you need to do is create an administrator account user name with a strong password, and specify the geographical region where the server should be located physically. To achieve the best performance, you should choose the region closest to your consumers. You will also want to be sure that any Windows Azure cloud Web sites and services you create are hosted in the same region as the SQL Database servers they communicate with. By locating both in the same region, you will avoid the bandwidth-based fee that gets incurred when your cloud sites, services, and databases communicate across different Azure regions. You will also reduce latency, which results in perceivably better performance.

SQL Database also has special firewall rules you can set to control exactly which computer or computers can access your database server in the cloud. Minimally, you’ll need to add a rule granting access to the IP address of your computer so that you can access the server from your local machine. For production, you might need to add rules granting access to blocks of IP addresses.

Follow these steps to create a new server:

1. Log in to the Windows Azure portal at https://manage.windowsazure.com. This brings you to the main portal page showing ALL ITEMS.
   
2. Click SQL DATABASES in the vertical navigation pane on the left, then click SERVERS at the top of the page, and then click CREATE A SQL DATABASE SERVER.
   
3. Provide a new server login name—for example, saz.
4. Supply a password for the new server, and then reenter it to confirm. Typical strong password guidelines apply, which require you to use a combination of mixed case, numbers, and symbols.
5. Choose a region from the drop-down list—for example, East US. For best performance, pick the region you are located in or nearest to.
6. Be sure to leave the ALLOW WINDOWS AZURE SERVICES TO ACCESS THE SERVER  check box selected. This makes the server accessible to other Windows Azure cloud services that you can create.
   
7. Click the checkmark icon on the lower-right side of the dialog to complete the settings. After just a few moments, the new server is provisioned and ready to use.
   

If you’ve ever prepared a new server from scratch yourself, you can really appreciate the time and effort you just saved. This server is now available and ready to host databases in the cloud, and SQL Database has automatically assigned a randomly unique (but relatively short) name by which it can be accessed. But before access is granted, the server firewall must be configured. So the next step is to add a firewall rule so that you can connect to the server from your local machine.

The check box mentioned in step 6 added the special IP address 0.0.0.0, which allows cloud services running on Windws Azure to access the SQL Database server. However, you still need to add the IP address of your local machine to access the server from the SQL Database management portal and other tools (such as SQL Server Management Studio and SQL Server Data Tools in Microsoft Visual Studio).

To add a firewall rule for the IP address of your local machine, follow these steps:

1. Click the server name, and then click the CONFIGURE link at the top of the page.
2. To the right of your current detected IP address, click ADD TO THE ALLOWED IP ADDRESSES. A new firewall rule for your IP address is added.
   
3. Click SAVE at the bottom of the page.
4. Click the back icon (the large back-pointing arrow) to return to the SQL DATABASES page for the new server.

You might need to wait a few moments for the new firewall rule to take effect, although typically it happens very quickly (often within five to ten seconds). If you don’t wait long enough, however, and the rule has not yet taken effect, you can be quite certain that you will not be able to connect to the server from your local machine until it does.

Creating a SQL Database instance

It will be just about as easy to create a database as it was to create the server. You simply need to choose a name for the new database, an edition, a database size, a default collation, and of course, the server to host the database on.

These settings can be easily changed later on. As part of the elastic scaling provided by SQL Database, you can freely switch back and forth between the Web and Business editions. You can also switch up and down between the sizes (1 GB or 5 GB for the Web edition, or 10 GB through 150 GB for the Business edition) as your changing needs dictate. And if 150 GB is still too small for you, you can partition your database using special sharding techniques.

Follow these steps to create a new SQL Database:

1. In the Windows Azure portal, click the DATABASES link at the top of the page.
2. Click CREATE A SQL DATABASE. This opens the NEW SQL DATABASE dialog.
3. Type the name for the new database.
4. Leave the default settings to create a Web edition database up to 1 GB in size using the SQL_Lating1_GeneralCP1_CI_AS collation.
5. Choose the server you created in the previous procedure from the drop-down list.
   
6. Click the checkmark icon in the lower right of the dialog to complete the settings.

After a few more moments, the new database is created and ready to use.

And that’s all there is to creating a new SQL Database on Windows Azure!