How to Create an Azure SQL Server Virtual Machine

How to Create an Azure SQL Server Virtual Machine

How to Create an Azure SQL Server Virtual Machine

In Microsoft Azure, there are two main options for SQL Server services. The first option is Azure SQL Database (PaaS). The second option in SQL Server on Azure (laaS). In a previous article, I explained how you can create a SQL Server instance and database on Azure SQL Database. In this article, we are going to see step-by-step, how to create an Azure SQL Server Virtual Machine.

 

More specifically, for this article, we are going to create a simple VM with SQL Server 2019 CTP2.0 (learn what’s new).

Note: Since this is a demo, I will be following a simplified process, thus not taking into consideration all best practices.

 

Alright, let’s start!

 

Step 1: Create a new resource in Azure (SQL Server Virtual Machine)

Click the “Create a resource” link and search for “SQL Server 2019 on windows“.

Then, select the template “Free SQL Server License (CTP2.0)SQL 2019 Developer on Windows Server 2016″ and click on the “Create” bvtton.

Important note: Since this is a demo, I’m using the developer edition of SQL Server which can be used for testing/demo purposes. In case you want to create an Azure SQL Server Virtual Machine for Production purposes, please read here for pricing.

 

Step 1a: Choose a VM template

How to Create an Azure SQL Server Virtual Machine - Article on SQLNetHub

 

Step 1b: Configure Basic Settings

After clicking the “Create” button, you will need to configure the SQL Server VM.

In the below screen, I’m configuring some basic settings.

How to Create an Azure SQL Server Virtual Machine - Article on SQLNetHub

 

Step 1c: Set the Size of the VM

On the next screen, I choose the size of the VM:

How to Create an Azure SQL Server Virtual Machine - Article on SQLNetHub

 

Step 1d: Settings

On the next screen, I can set optional features but since this is a demo, I won’t set anything special such as high availability etc. The one thing I would like you to note at this step, is the “Select public inbound ports” option. For this option, for the time being, I selected “No public inbound ports” as I will set it up later, in a more controlled manner.

Security note: You need to carefully control which IPs can access your online resources on Azure. Therefore, you don’t just “enable” something. You need to have full control and always go through the advanced settings.

OK, cool. Let’s click on “OK” and proceed to the next screen of the basic configuration of our SQL Server VM.

How to Create an Azure SQL Server Virtual Machine (settings) - Article on SQLNetHub

 

 

Step 1e: SQL Server Settings

The next screen, is where you set SQL Server setting such as: SQL connectivity, port (always change the default port), SQL Authentication, sysadmin login, etc. A cool thing is that you can set SQL Automated Patching – no more headaches for DBAs! 🙂

Security Note: If you choose “Public” for the “SQL connectivity” option, you need to carefully set security in order to protect your SQL Server instance within the VM from potential unauthorized access from the Internet.

 

How to Create an Azure SQL Server Virtual Machine - Article on SQLNetHub

 

Step 1f: Summary

After you finish with SQL Server settings and click “OK”, you reach the last screen, where your options are being validated. If everything OK, you will get the below screen, which among other, displays a summary of all your options:

How to Create an Azure SQL Server Virtual Machine - Article on SQLNetHub

That’s it! After you click on the “Create” button, your Azure SQL Server VM will be created, based on the settings you’ve set.

 

Step 2: Access the Azure SQL Server Virtual Machine via RDP

Now that my VM with name “SQL2K19ST2Demo” has been deployed, I need to access it.

Let’s say I need to remotely access my VM. In order to do this, I need to add an inbound firewall rule as below:

Security Note: It is critical to be very careful when adding or modifying firewall rules for your Virtual Machines. Always work with specific IPs and Ports, especially in cases where you expose any resources to the Internet. Also, consider using VPN for site-to-site connections.

 

How to Create an Azure SQL Server Virtual Machine - Article on SQLNetHub

Now, as you can see in the below screenshot, I have managed to successfully connect.

How to Create an Azure SQL Server Virtual Machine - Article on SQLNetHub

 

Step 3: Access the SQL Server Instance on Azure VM via SSMS

In order to connect to SQL Server on the Azure VM using SSMS, again, like in the case of the RDP connection, you need to manage your firewall rules.

If you haven’t added a firewall rule during the VM creation, if you want, you need to add an inbound firewall rule in order to allow SQL access.

Take for example the below screenshot, where I have added an additional inbound firewall rule for my VM.

In this rule, I have set the below:

  • Source IP
  • Source port range
  • Destination IP addresses
  • Destination port ranges
  • Priority

 

Security Note: It is critical to be very careful when adding or modifying firewall rules for your Virtual Machines. Always work with specific IPs and Ports, especially in cases where you expose any resources to the Internet. Also, consider using VPN for site-to-site connections.

 

As you can see, I’m only allowing inbound traffic for my IP (source IP) to one specific port (SQL) on my VM.

How to Create an Azure SQL Server Virtual Machine - Article on SQLNetHub

 

Now, I will try to connect to my Azure VM’s SQL Server instance using SSMS.

In the “Server name” field I entered the public IP of my VM, as well as entered my credentials.

How to Create an Azure SQL Server Virtual Machine - Article on SQLNetHub

If you try to connect like the above, there is a chance that you get an error similar to the below:

 

.TITLE: Connect to Server
——————————

Cannot connect to ……..

——————————
ADDITIONAL INFORMATION:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 – The certificate chain was issued by an authority that is not trusted.) (Microsoft SQL Server, Error: -2146893019)

 

Therefore, before clicking on the “Connect” button, in the “Connection Properties” tab, you need to check the “Trust server certificate” option.

How to Create an Azure SQL Server Virtual Machine - Article on SQLNetHub

OK, now after I click on the “Connect” button, you can see that I have managed to connect to the SQL Server instance on my Azure VM:

How to Create an Azure SQL Server Virtual Machine - Article on SQLNetHub

 

Conclusion

Azure Virtual Machines is a very fast and handy way to create, among other, SQL Server machines. Microsoft Azure provides a large set of virtual machine templates, which make the whole process easier and faster. Moreover, you can fully customize the VM during its creation, and later.

In this article and demo, we created an Azure SQL Server Virtual Machine with SQL Server 2019 CTP2.0, and we managed to connect using RDP and SQL access via SSMS.

As with creating any resource on Azure, when creating Azure Virtual Machines, you need to be very careful with the network/firewall permissions you set, especially in the case where the VM is exposed to the Internet. Moreover, consider using VPN for site-to-site connections.

 

Learn More

 

Check Also:

 

Subscribe to our newsletter and stay up to date!

Check out our latest software releases!

Easily generate SQL code snippets with Snippets Generator!

Convert static T-SQL to dynamic and vice versa with Dynamic SQL Generator.

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© 2018 SQLNetHub

How to Create an Azure SQL Server Virtual Machine Click to Tweet

 

Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server and Software Architect, Author, and a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Artemakis's official website can be found at aartemiou.com.