Table of Contents
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.
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:
Click the Start free button and create a new account.
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:
Eventually, after having sorted everything out, we should be able to access the Azure portal (portal.azure.com) in all of its glory:
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.
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:
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).
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:
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:
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.
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:
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.
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.
