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

 

---

Learn more about Azure SQL Database: Enroll to our Online Course!

Check our online course on Udemy titled “Introduction to Azure SQL Database for Beginners”
(special limited-time discount included in link).

Our course, will help you get started with Azure SQL Database and SQL Server on Azure Virtual Machines fast and easy. Many live demos included.

Learn More

---

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.

 

Step 1c: Set the Size of the VM

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

 

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.

 

 

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.

 

 

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:

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.

 

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

 

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.

 

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.

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.

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:

 

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.

 

See More! Enroll to the Online Course!

Learn more about Azure SQL Database and get started fast and easy! Through our online course “Introduction to Azure SQL Database for Beginners“, you will get lifetime access to a comprehensive set of lectures and live demonstrations that will get you started with Azure SQL Database in no time! The course is regularly updated with additional educational material!

 

Learn More

 

 

Learn More

• Get started with Azure
• Tutorial: Design your first Azure SQL database using SSMS
• Create a storage account on Azure
• Back up SQL Server databases to Azure
• Azure Virtual Machines

 

Featured Online Courses:

Check all my courses on Udemy and enroll with an exclusive discount coupon:

• AI Demystified: A 1-Hour Beginner’s Guide (Suitable for Non-Technical People)
• AI Essentials: A Beginner’s Guide to Artificial Intelligence
• Human-AI Synergy: Teams and Collaborative Intelligence
• SQL Server 2022: What’s New – New and Enhanced Features
• Working with Python on Windows and SQL Server Databases
• Introduction to Azure Database for MySQL
• Boost SQL Server Database Performance with In-Memory OLTP
• Introduction to Azure SQL Database for Beginners
• Essential SQL Server Administration Tips
• SQL Server Fundamentals – SQL Database for Beginners
• Essential SQL Server Development Tips for SQL Developers
• Introduction to Computer Programming for Beginners
• .NET Programming for Beginners – Windows Forms with C#
  
• SQL Server 2019: What’s New – New and Enhanced Features
• Entity Framework: Getting Started – Complete Beginners Guide
  
• Data Management for Beginners – Main Principles
  
• A Guide on How to Start and Monetize a Successful Blog

 

Check Also:

• How to Reset Your SQL Server Admin Password on Azure
• Comparison of SQL Server Deployment Options on the Azure Cloud Platform
• Learn Azure SQL Database: Creating your First Database
• How to Backup a SQL Server Database from On-Premises to Azure Storage
• What’s New in SQL Server 2019

 

Subscribe to the GnoelixiAI Hub newsletter on LinkedIn and stay up to date with the latest AI news and trends.

Subscribe to the SQLNetHub YouTube channel (SQLNetHub TV).

Subscribe to my personal YouTube channel.

 

Rate this article: (2 votes, average: 5.00 out of 5)

Loading...

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

© SQLNetHub