Easily Create a SQL Database on Microsoft Azure

PowerShell ISE Azure SQL DatabaseDo you want to learn about Microsoft SQL Server, or need to develop a brand new application that leverages SQL Server? If so, then the Microsoft Azure public cloud can certainly help get you started! One of the great things about Microsoft Azure is that many of its services offer a free tier that doesn’t cost you anything, until you have high enough performance demands requiring the need to scale up. The Azure SQL Database service is no exception to that rule!

Although I had trouble finding documentation on it, there’s a free tier of the Azure SQL Database service. It provides a 32MB database, that was intended to work with the Azure App Service offering, but through a loophole in the Azure PowerShell module, you can create one on its own! Keep reading, and we’ll explore how to provision a SQL Database in seconds (literally) using PowerShell.

If you’re interested in learning more about Microsoft Azure, check out Art of Shell’s training courses that are available for download!

SQL Database Tiers

There are quite a few different pricing tiers available for Microsoft Azure SQL Database. This allows you to select the performance and storage capacity metrics that are relevant for your application, without paying for more than what you need. Switching between service plans is also quite straightforward. If you have short periods of high performance demand, you can scale up your Azure SQL database to meet those demands. Later on, after the demand diminishes, you can scale the SQL database back down to a lower pricing tier, to save costs!

You can choose between creating a stand-alone SQL database, or creating a database on top of an Elastic Pool. Elastic Pool enables you to share resources with multiple databases. In other words, you can co-locate databases with high performance demands, and low performance demands, to help balance the usage of resources in your Azure subscription.

To help you understand the relationship of objects, pertaining to the Azure SQL Database service, here is a simple diagram. Your Microsoft Azure Subscription is the top-level entity that’s essentially a billing boundary. Next, you create a Resource Group, which is simply a management container for all types of cloud resources in Microsoft Azure. Next, you provision a SQL Server, which has a globally-unique name, and a username / password for high-level administration. For the sake of clarity, the SQL Server that you provision in Microsoft Azure is not an infrastructure virtual machine that you can manage yourself. It’s a platform service, with SQL Server pre-installed, that is host to an Elastic Pool, or if you don’t want to use that feature, it hosts SQL Databases directly.

Microsoft Azure SQL Database Architecture

Create a SQL Database using PowerShell

Microsoft Azure provides a PowerShell module that enables automated interaction with your Azure subscription! The module is named AzureRM, and is available on the PowerShell Gallery. Once you’ve installed this module, you can easily provision Resource Groups, SQL Servers, and SQL Databases. In the following sections, we’re going to explore exactly how to do this!

Install the Azure PowerShell Module

First, we need to install the Azure Resource Manager (ARM) PowerShell module. To do that, make sure that you’re running PowerShell version 5.0, which includes the PowerShellGet module. To verify that you have the PowerShellGet module, run the command: Get-Module -ListAvailable -Name PowerShellGet. After you’ve verified that you have PowerShellGet installed, use the following command to install the Azure Resource Manager PowerShell module.

Install-Module -Name AzureRM -Scope CurrentUser -Force;

This command will install the ARM PowerShell module to your ~\Documents\WindowsPowerShell\Modules directory. This avoids the need for administrative rights to install the module to the all-users directory, which is the default.

Authenticate to Microsoft Azure

In any Azure PowerShell automation script, the first thing you need to do is authenticate to the Microsoft Azure service. To do that, we obtain an authentication token from Azure Active Directory (AAD). We call the Add-AzureRmAccount command in order to authenticate. To speed up the authentication process, and avoid the use of the clunky web browser control pop-up, we call the built-in Get-Credential PowerShell command, and pass that into the -Credential parameter of Add-AzureRmAccount.

Add-AzureRmAccount -Credential (Get-Credential -Message 'Please enter your Microsoft Azure username and password.');

Create an Azure Resource Manager Resource Group

In the Microsoft Azure Resource Manager (ARM) interface, we need to provision every cloud resource into a management container called a Resource Group. In order to provision a SQL Server, we need to first create an empty Resource Group, that will contain it. To do this, we use the logically-named New-AzureRmResourceGroup command. The -Name parameter specifies a unique (for the subscription) name for the Resource Group. The -Location parameter specifies the Azure Region where the Resource Group will be created. To find a list of supported Azure Regions, call the Get-AzureRmLocation command.

In case you’re unfamiliar with the syntax of the command invocation below, we’re using a special technique called PowerShell Splatting. Watch this video for more information about what PowerShell Splatting is, and how to use it. Trust me, it’s easy, and you’ll write much nicer-looking code once you’re familiar with it!

### Use PowerShell Splatting to create a Resource Group
$ResourceGroup = @{
  Name = 'ArtofShell-SQL';
  Location = 'Central US';
  }
New-AzureRmResourceGroup @ResourceGroup;

Create an Azure SQL Server

Now that we’ve created an Azure Resource Manager (ARM) Resource Group inside our Azure Subscription, we can move on to create a SQL Server. The SQL Server is just an Azure platform service that abstracts the management of the underlying operating system from us. This helps us get up and running quickly, and not have to worry about infrastructure details that aren’t relevant to building our business applications.

The New-AzureRmSqlServer PowerShell command will create a new SQL Server. The -Name parameter specifies a globally-unique name for the SQL Server. We use the -ResourceGroupName parameter to specify the name of the Resource Group where we want to provision the SQL Server resource into. Of course, we will use the same Resource Group name that we just created in the last section. We use the -Location parameter to specify the Azure Region (aka. Location) where we want to provision this particular cloud resource. Again, use Get-AzureRmLocation to determine an authoritative list of supported Azure locations.

Next, we specify the administrative login for the SQL Server resource, using the -SqlAdministratorCredentials parameter. The Get-Credential command will return the username and password combination for this purpose. We’ll use this credential to login to the SQL Server later on, in order to manage the databases running on top of it.

Finally, in order to ensure that we are using the latest version of SQL Server, we’ll specify the -ServerVersion parameter, and set it to 12.0. For more information about this parameter, run Get-Help -Name New-AzureRmSqlServer -Parameter ServerVersion.

$SqlServer = @{
  ResourceGroupName = 'ArtofShell-SQL';
  Name = 'contososql';
  Location = 'Central US';
  SqlAdministratorCredentials = (Get-Credential);
  ServerVersion = '12.0';
  }
New-AzureRmSqlServer @SqlServer;

If another Microsoft Azure customer has already taken the server name that you specified, you might receive an exception that sounds something like: “Server name ‘contososql’ has already been used.” In that case, simply change the name of your SQL Server resource, and re-run the command. Remember, the SQL Server name is globally unique.

Create an Azure SQL Database

After creating the SQL Server resource, we can now provision a SQL Database on top of it. To achieve this objective, we call the aptly-named New-AzureRmSqlDatabase command. Again, we need to specify the -ResourceGroupName parameter, where our SQL Server resource resides. We also tack on the -ServerName parameter to specify which SQL Server resource we want to provision the database onto. The -DatabaseName parameter enables us to give our new database a unique, custom name.

You also have the optional ability to specify a custom SQL collation using the -CollationName parameter. The -Edition parameter is also optional, and partially determines which pricing tier the SQL Database will be billed at. To use the entry-level pricing tier, we would specify a value of Basic.

$SqlDatabase = @{
 ResourceGroupName = 'ArtofShell-SQL';
 ServerName = 'contososql';
 DatabaseName = 'tweetstream';
 Edition = 'Basic';
 };
New-AzureRmSqlDatabase @SqlDatabase;

Enable a SQL Firewall Rule

Because the Azure SQL Database service is accessible publicly, it’s locked down pretty well by default. You’ll need to create a SQL firewall rule that explicitly enables access to your IP address, where you’re going to manage the database from. First, go visit What is my IP, and get your IPv4 address. Once you’ve determined your public IPv4 address, run the New-AzureRmSqlServerFirewallRule command to enable access to your SQL Database from your system.

We specify the -ResourceGroupName parameter, where our SQL Server resource resides. We also add on the -ServerName parameter, and pass in the name of our SQL Server resource, that we want to create the firewall rule on.

The -FirewallRuleName parameter simply specifies a custom name for the firewall rule. Finally, the -StartIpAddress and -EndIpAddress parameters determine the allowed start and ending ranges of IPv4 addresses. As a good rule of thumb, you should open up as few IP addresses as possible, but for non-production testing, you can open up a larger range for simplicity (eg. 67.0.0.0 – 67.255.255.255). In a production application, you should only open the individual IP addresses that need access to the database.

$FirewallRule = @{
 ResourceGroupName = 'ArtofShell-SQL';
 ServerName = 'contososql234';
 FirewallRuleName = 'DevWorkstation';
 StartIpAddress = '67.0.0.0';
 EndIpAddress = '67.255.255.255';
 }
New-AzureRmSqlServerFirewallRule @FirewallRule;

Connect to the SQL Database

Provisioning the SQL Server and database wasn’t all that hard, right? Now we’re finally at the point where we can connect to our SQL Server and access the databases on it. Install the SQL Server Management Studio (SSMS) and the SQL Server Data Tools (SSDT) packages, if you’re using Visual Studio 2015. SQL Server Management Studio is a nice, GUI application that enables us to connect to the remote SQL Server. The name of your remote Azure SQL Server will be yoursqlservername.database.windows.net.

Connect to SQL Database with SSMS

Forgot your SQL Server Administrator Login?

If you forget the administrative login for your Microsoft Azure SQL Server resource, that’s okay. Using the ARM PowerShell module, as long as you have access to manage your SQL Server resource, you can easily reset your password to a new one. This will restore your access to your SQL Server resource, so you can manage the databases on it. By simply running the Set-AzureRmSqlServer command, and tacking on the -ResourceGroupName, -ServerName, and -SqlAdministratorPassword, we can reset the password for the administrator account.

$SqlPasswordReset = @{
 ResourceGroupName = 'ArtofShell-SQL';
 ServerName = 'contososql234';
 SqlAdministratorPassword = Read-Host -AsSecureString -Prompt 'Please enter a new password.';
 }
Set-AzureRmSqlServer @SqlPasswordReset;

Conclusion

In this article, we’ve introduced the Microsoft Azure SQL Database service, and taken a look at how to provision a database using the Azure Resource Manager (ARM) PowerShell module. We also used the ARM PowerShell module to open a port in the SQL Server firewall, so that we could connect to it remotely. Finally, we took a look at how to reset our Azure SQL Server administrator password, in case we get locked out of the system.

This article originally appeared on Art of Shell.

Leave a Reply

Your email address will not be published. Required fields are marked *