How to create an Azure SQL Virtual Machine How to setup and configure a SQL Virtual Machine on MS Azure: a highly configurable way to manage SQL Databases using a PaaS approach

How to Migrate SQL Server DB to Azure SQL Server DB

Some weeks ago we've published a post showing how to create an Azure SQL Managed Instance on the Azure Cloud Portal: a great way to create, host and mantain SQL Databases in the cloud using an Infastructure-as-a-Service (IaaS) approach. In this post we'll explain how to configure a SQL Virtual Machine, an alternative (and most powerful) way to manage SQL Databases in the cloud using a Platform-as-a-Service (PaaS) approach.

As we most likely already know, putting our DBMS on Azure gives us the chance to have our SQL Server workloads running as either a hosted infrastructure (IaaS) or a hosted service (PaaS): the first option is great if we want to handle the database maintenance tasks by ourselves - such as apply patches, take backups, and so on; the second option is preferable if we do want to delegate such operations to Azure. However, regardless of the chosen path, we're going to have a scalable database service with full redundancy and no single point of failure guarantees, plus a lot of other performance and data security benefits. The downsides, as we can easily guess, are mostly two: the additional cost and the fact that we're going to have our data located elsewhere, which can be a major issue in terms of privacy and data protection in certain scenarios.

Introduction

The SQL Virtual Machine is one of the three main approaches available on Azure for hosting SQL databases: the other two options are SQL Databases and SQL Managed Instances. As we can easily expect, all of these options have their pros and cons in terms of costs, performance, security, customization and complexity. Let's briefly summarize how each one of them works and their benefits:

Azure, a scalable database service providing near 100% compatibility with a standard SQL Server instance, featuring a Infrastructure as a Service (IaaS) usage and billing model. This option provides all of the PaaS benefits of the previous one (SQL Database) but adds some additional infrastructure-related capabilities, such as: native virtual network (VNet), custom private IP addresses, multiple databases with shared resources, and so on.

  • SQL Database: A fully-managed SQL database engine based on SQL Server Enterprise Edition. This option allows us to setup and manage one or more single relational databases hosted in the Azure cloud with a Platform-as-a-Service (PaaS) usage and billing model: more specifically, we can define it a Database-as-a-Service (DBaaS) approach. This option provides built-in high availability, intelligence, and management, hence being great for those who wants a versatile solution without the hassle of having to configure, manage and pay a whole server host.
  • SQL Managed Instance: A dedicated SQL Managed Instance on Azure, a scalable database service providing near 100% compatibility with a standard SQL Server instance, featuring a Infrastructure as a Service (IaaS) usage and billing model. This option provides all of the PaaS benefits of the previous one (SQL Database) but adds some additional features and capabilities, such as: Linked Servers, Service Brokers, Database Mail, full Azure Virtual Network support, multiple databases with shared resources, and so on.
  • SQL Virtual Machine: A fully-managed SQL Server consisting of a Windows or Linux Virtual Machine with a SQL Server instance installed on top of it. This approach, which also adopts a Infrastructure as a Service (IaaS) usage and billing model, offer full administrative control over the whole SQL Server instance and the underlying OS, thus being the most complex and customizable one. The most significant difference from the other two options (SQL Database and SQL Managed Instance) is that SQL Server VMs also allow full control over the database engine: we can choose when to start maintenance/patching, change the recovery model, pause/start the service, and so on.

For further info regarding the pros and cons of the Azure options described above, we strongly suggest to read this guide.

How to create an Azure SQL Virtual Machine

All of these options are good enough and, although very different in terms of overall costs, can be activated free of charge: SQL Database is arguably the cheapest one because it's free for 12 months, thanks to the trial subscription plan offered by Azure, as long as we keep its size under 250GB; either SQL Managed Instance and SQL Virtual Machine are rather expensive, since they both provide a virtualized IaaS, but they can be activated for free (at least for few weeks) with the 200€ provided by that same Azure trial subscription plan.

SQL Virtual Machine

Let's see how we can setup a SQL Virtual Machine.

Let's start by visiting the following URL:

Which will bring us to the web page that will allow us to create an Azure SQL Virtual Machine:

How to create an Azure SQL Virtual Machine

Click the Start free button and create a new account.

If you already have a valid MS account you can definitely use it, but you should only do that if you are sure you want to use the free Azure trial on it: if that's not the case, consider creating a new one.

After a brief registration form (and/or login phase) you'll be redirected to the Azure portal.

It goes without saying that, if the account we've logged in with has already spent its free period or has an active paid subscription plan, we'll be gracefully bounced back:

How to create an Azure SQL Virtual Machine

Eventually, after having sorted everything out, we should be able to access the Azure portal (portal.azure.com) in all of its glory:

How to create an Azure SQL Virtual Machine

Once there, type SQL Virtual Machine in the top-level search box to locate the feature with the same name: click on in, then click again to the Create SQL virtual machine button to start the configuration wizard.

How to create an Azure SQL Virtual Machine

Once done, we'll be asked to select a SQL deployment option: choose SQL virtual machines, then select a viable OS and a SQL Server edition: each one comes with a different license plan, depending on how you plan to use it. For the purpose of this example post, we'll pick Microsoft Windows Server 2019 and SQL Server Developer Edition (which is provided at free cost), as shown in the screenshot below:

How to create an Azure SQL Virtual Machine

For additional info regarding the available SQL Server editions, as well as their license plans, read this post.

During the process we'll be also asked to create our very first Azure Tenant (unless we already have one): that's basically a virtual organization that owns and manages a specific set of Microsoft cloud services. Tenants are identified by unique URIs with the following format: <TenantName>.onmicrosoft.com: just give it a suitable name and go ahead.

Configuring the VM

As soon as we click the Create button we'll be asked to configure our SQL Virtual Machine with a wizard-like interface splitted into the following tabs:

  • Basics: Subscription type, instance name, admin username and password, and so on
  • Disks: The number, type and capacity of HDDs/SDDs to provide the VM with
  • Networking: Network-related configuration settings
  • Management: Monitoring features, auto-shutdown capabilities, backup, and more
  • Advanced: Additional configuration, agents, scripts, extensions, and the like
  • SQL Server Settings:
  • Tags: These allow some name-value pairs that can be useful in categorizing the various MS Azure resources to be set
  • Review + Create: Review and confirm all of the above

Most of these settings are self-explanatory and don't require a guidance: just be sure to choose a VM plan with enough capacity and hardware resources for the workload you reasonably expect: however, keep in mind that you can change it after deployment without issues. In our example, we've picked a Standard_B1s  size, which is among the cheapest ones (9,85 EUR/months).

How to create an Azure SQL Virtual Machine

When you reach the Networking tab be sure to pick a Public IP address, unless you don't want your VM (and hence your SQL Database) to be accessible from the Internet:

How to create an Azure SQL Virtual Machine

Keep in mind, though, that having a public IP Address doesn't automatically mean that your SQL Server instance will accept incoming connections from the Internet. To make it do that we'll also need to explicitly accept connections from the public Internet by selecting the corresponding options in the SQL Server settings tab, as shown in the screenshot below:

How to create an Azure SQL Virtual Machine

Wait a minute: isn't that a major security issue? What if our databases contains personal or sensitive data?

As a matter of fact, it actually is: allowing public access from the Internet is something we should always avoid, unless we're playing with open data for testing, demonstrative or tutorial purposes... Which is precisely what we're doing right now.

Once you've set up all of these options, go to the Review + Create table to check that everything has been properly set.

If everything looks good, click the Create button to start provisioning the Virtual Machine.

The deployment task can take some minutes, therefore you'll have to be patient: once it's done, you'll be able to access the Virtual Machine (and its SQL Server instance) and configure it further, OR directly connect to it using the public IP address (screenshot below, near the top-right corner) that MS Azure will assign to it.

How to create an Azure SQL Virtual Machine

However, before connecting to the SQL Server, we should definitely spend a couple more minutes to understand how we can do that from any external machine (outside MS Azure) without giving up too much in terms of security.

Connecting to a SQL Virtual Machine

Azure SQL Virtual Machine provides a private endpoint to allow connectivity from inside its virtual network: if you need to connect to your VM from within any MS Azure service - including Azure-hosted VMs, Web Applications published on App Services, and so on - this is the best option in terms of security, as it provides maximum isolation. As we can see from the screenshot above , the private endpoint assigned to our sample instance corresponds to the Private IP Address 10.0.0.5.

However, there are scenarios where you need to provide a public endpoint connection, for example when you need to connect to your Virual Machine from external VMs or web farms: that's precisely why we've setup a public endpoint in the previous paragraph: since we've done that, MS Azure provided our instance with a public IP Address that we can use to connect from outside. As we can see from the screenshot above , the public endpoint assigned to our sample instance corresponds to the Public IP Address 20.103.254.198.

Let's try to summarize what we've just said:

  • If we plan to connect to our newly-created SQL Virtual Machine only from MS Azure-hosted apps, machine and/or services, the best thing we can do is to disable the public endpoint (20.103.254.198) and only allow connection using the private endpoint (10.0.0.5).
  • If we plan to connect to our SQL Virtual Machine from external machines, we need to keep the public endpoint enabled and use the corresponding IP address (20.103.254.198) to actually perform the connection.

It goes without saying that, should you go for the public endpoint route, you'll open your VM to external threats: for that very reason, you'll have to carefully configure the inbound connection rules to minimize the risk of unauthorized access to your data.

This task can be done by selecting the Settings > Networking option from the left menu and properly configure the public endpoint by setting an inbound rule for TCP port 1433, as shown in the screenshot below:

How to create an Azure SQL Virtual Machine

Once done, we'll be able to connect to SQL Server using SSMS or a standard connection string from the Internet, providing the username/password credentials we've previously set up during the Virtual Machine configuration wizard.

Again, keep in mind that opening a SQL Server Instance to the public for non-demonstrative purposes is a major security issue and should be avoided at all costs: instead of doing that, you should restrict the connection using one of the many secure approaches supported by MS Azure: IP address/mask restrictions, SSH, Azure VPN Gateway, Just-in-Time Access, Azure Bastion, and so on.

Conclusion

That's it, at least for now: we hope that this post will help both ASP.NET developers and System Administrators to create a SQL Virtual Machine on MS Azure and connect to it from any external client, server or web application.

If you want to know more about .NET Core and Angular check out the ASP.NET Core 5 and Angular book, available as paperback and e-book.

About Ryan

IT Project Manager, Web Interface Architect and Lead Developer for many high-traffic web sites & services hosted in Italy and Europe. Since 2010 it's also a lead designer for many App and games for Android, iOS and Windows Phone mobile devices for a number of italian companies. Microsoft MVP for Development Technologies since 2018.

View all posts by Ryan

Leave a Reply

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


The reCAPTCHA verification period has expired. Please reload the page.

This site uses Akismet to reduce spam. Learn how your comment data is processed.