How to deploy an Azure SQL Database

Nowadays Azure SQL Database has nearly full parity with its on-premises counterpart, SQL Server. Learn how to deploy a virtual server in the Microsoft Azure cloud and stand up a SQL Server database.

Contents

Deploy a virtual server
Deploy an Azure SQL Database
Connecting to the Azure SQL Database
For further learning

Author
Recent Posts

Timothy Warner

Timothy Warner is a Microsoft Cloud and Datacenter Management Most Valuable Professional (MVP) who is based in Nashville, TN. Check out his Azure and Windows Server video training at Pluralsight, and feel free to reach out to Tim via Twitter.

Latest posts by Timothy Warner (see all)

Understanding Azure service accounts - Fri, Mar 31 2023
Azure PowerShell vs. Azure CLI - Wed, Mar 15 2023
Use Azure Storage for backup - Thu, Feb 23 2023

Here's the situation: your company plans to "lift and shift" its line-of-business workloads to their Azure tenant in the Microsoft cloud. Specifically, your task is to deploy a test Azure SQL Database instance that the developers and DBAs can play with prior to performing any actual application migrations. Where do you start?

You've logged into the Azure Portal (portal.azure.com) with your Azure AD administrative user account. Let's do this!

Deploy a virtual server

The first step to deploy an Azure SQL Database is to build a virtual SQL Server virtual machine (VM). Recall that we're dealing with database as a service (DBaaS) and not infrastructure as a service (IaaS). That is, we're surrendering deep access to the cloud-based VM in the name of easier administration of our database environment.

In the Azure portal, open the search box, type SQL server, and press ENTER. This universal search box is great; if you're not using it every day, please start doing so. I show you the interface in the next figure.

Azure universal search tool

In the SQL servers blade, click Add. You'll need to specify the following information to create an Azure-based virtual SQL Server:

Server name: This hostname needs to be globally unique in the Microsoft-owned database.windows.net DNS domain. You can use your own DNS domain as well, but it's a little hacky to do so.
Server admin login: The default administrator. Don't use sa, admin, administrator, or any obvious choice for security reasons.
Password: Standard best practices apply--the longer and more complex, the better.
Subscription: Self-explanatory.
Resource group: You might want to put all Azure artifacts associated with your application in the same resource group for simpler management and tracking.
Location: To minimize latency, choose an Azure region that is geographically closest to the people who will use the database.

After the virtual server deployment succeeds, we're ready to move onto database creation.

Deploy an Azure SQL Database

Return to the global search, type sql database and press ENTER. Click Add to initiate the database creation process. Here your database name doesn't have to be unique outside of the resource group. Also, make sure to use the same subscription as the virtual server or this won't work!

The Select source option provides three choices for what kind of database you want:

Blank database
AdventureWorksLT sample database
Restore from backup

In this tutorial, we'll select the AdventureWorksLT option so we actually have data to play with. In a production environment, you can migrate your on-premises SQL Server database to Azure SQL Database by performing the following steps:

Back up the database to .BACPAC format with SQL Server Management Studio (SSMS) or another tool of your choice.
Copy the BACPAC archive to an Azure storage account.
Either create a new Azure SQL Database specifying the Backup option, or restore the backed-up database directly to your virtual server with SSMS or another tool.

For the Server option, ensure that you're mounting the database to the virtual server we created earlier in the process.

SQL elastic pool is a cost-saving feature that allows you to share Database Transaction Units (DTUs) among several databases. We won't use this feature today.

As is the case with any Azure service, the pricing tier is a crucial choice for you. Here you can balance database performance (expressed in DTUs) and storage capacity against your budget. In the following screenshot, I'm choosing the Standard pricing tier set to the default DTU and storage thresholds.

Choosing a pricing tier

Finally, choose a default database collation and submit the deployment job to get it done!

Connecting to the Azure SQL Database

Click on your new Azure SQL Database to open its configuration blade. Look at my annotated screenshot, and then I'll point out some important stuff:

Azure SQL Database configuration blade

A: Roll back to the database from a previously created restore point.
B: Export the database and its metadata to an Azure storage account (perhaps you want to move from the cloud to on-prem).
C: Define access rules.

The Set server firewall option is particularly important for obvious reasons--nobody will get into the database otherwise. In the following screenshot I ensure that Allow access to Azure services is enabled, and I've created an entry for my management workstation, specifying its public IPv4 address.

Configuring database firewall rules

Now fire up SSMS (2014 or 2016 will do) and let's authenticate! Use the fully qualified domain name (FQDN) of your virtual server as the server name, and supply the admin credentials you defined during deployment.

Logging into Azure SQL Database

From here, actually working with the Azure SQL Database is strikingly similar to working on an on-prem instance. Check out the following screenshot for proof:

Working with Azure SQL Database in SSMS

Of course, one giant difference between Azure SQL Database and on-prem is the absence of server-level configuration options. You'll note in the image above that there is no Properties option when I right-click the virtual server.

Instead, you do your virtual server configuration from within the Azure Portal. Check out the following annotated screen capture, and I'll describe some of the more interesting options.

Configuring the virtual server

A: Role-based access control (RBAC) provides granular authorization for your databases.
B: Set access rules at the virtual server level. A virtual server can host more than one Azure SQL Database.
C: Azure's many advantages include its built-in intelligence engines. This one automatically tunes your virtual server based on the behavior of your workloads.
D: Cycle the default admin password.
E: Move the database to another resource group or even a different Azure subscription.

For further learning

I hope that this brief tutorial was enough to get you started with Azure SQL Database. I've been a DBA for a long time, and I'm proud of Microsoft in how rapidly they matured this product to bring it into (largely) feature parity with SQL Server on-premises.

As usual, I'll leave you with some supplemental references to help you climb the learning curve even faster.