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.
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.
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:
- 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.
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.
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:
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.
- 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.
- Azure SQL Database documentation
- Marketing page
- SQLDatabase PowerShell module
- Migrate SQL Server database from on-prem to Azure