Quickly setup SQL Server on Linux using an Azure SQL Server VM template


By:   |   Updated: 2017-05-26   |   Comments   |   Related: More > SQL Server on Linux

Problem

Are there any existing built-in templates on Azure that run SQL Server on Linux which can be quickly setup to test SQL Server features on Linux? Can we connect to the new SQL Server on Linux locally from my desktop using SQL Server Management Studio (SSMS)?

Solution

There are already some templates that are available in Azure that run SQL Server on Linux. This tip describes the step by step process to quickly set it up.

Setup a Linux VM on Azure for SQL Server

First, log on to the Azure portal: https://portal.azure.com/.

If you don’t have an account, you can set up a trial edition https://azure.microsoft.com/en-us/free/ and get a $200 credit for 30 days. Once you login, follow the sequence of steps as shown below. The available SQL Server VMs on Linux appear as shown below.

Steps to follow to search for SQL Server Linux template

Just click on one of the templates. In this tip, we will try the first one, that is, SQL Server 2017 CTP 2.0 on Red Hat Enterprise Linux 7.3. Once you click on this image, you will get this screen, click on ‘Create’.

Click on Create to set up the virtual machine

Once you click on ‘Create’, you will need to complete the process for creating the new VM. In the first screen, you will see the option to enter details for the ‘Basic Configuration Setting’. Enter the details as shown below. In the VM Disk type section, you can provide either SSD or HDD, then provide a complex password and for location, provide a location closest to you.

Enter Basic Configuration Settings for the Virtual Machine

In the next screen, you will get the option to choose the virtual machine size. Review the list of machine sizes available and select the one that best suits your requirement.

The next screen is the section to configure optional features. You can leave it to the default settings as shown below.

Configure optional features in Microsoft Azure Virtual Machine

In the final summary screen, review the parameters and click on ‘OK’ which will create the new virtual machine.

Summary section for Microsoft Azure Virtual Machine

Steps to create the inbound security rules for the new VM

Once the VM is created, make sure to create the inbound security rules on the Azure portal. Refer to the below image and follow the sequence as shown to setup the inbound rules. (note I have indicated various steps in the images)

create the inbound security rules on the Azure portal

Once you click on the Network interfaces name as shown (step 4), you will get this screen.

Network Security Group in Microsoft Azure

Click on the Network Security group (step 2 above) to get to the next screen. In the next screen, you will see the option to add the inbound security rules. Make sure to add the inbound security rules as shown. One for SSH and the other for the default SQL port.

Inbound security rules

Steps to connect to the new VM

Go to the section to connect to the VM. You will get this message to connect to the new virtual machine using SSH.

Steps to connect to the new VM

You can download a compatible SSH version from this link: http://www.putty.org/.

Enter the IP address details as shown and click on Open.

SSH putty Login Screen

Once you click on Open a pop window appears where you need to input your user name and password. Refer to the image below. Once you key in the correct credentials, you will get connected to the VM.

ssh login

In order to set up SQL Server on this template, run this command at the prompt.

sudo /opt/mssql/bin/mssql-conf setup
   

The below screen shows the sequence of prompts that we get when we install SQL Server.

Install SQL Server Tools on Linux

Now, when you try to connect to SQL server using SQLCMD, you will get this error message.

Error message while connecting to SQL Server

This is because, the SQL Server tools are not yet installed. In order to install the SQL Server tools on the Red Hat Enterprise Linux (RHEL), refer to these sequence of steps.

Step 1: Enter super user mode

sudo su
   

Step 2: Download the Red Hat configuration file

curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/msprod.repo
   

Step 3:: Exit super user mode

Enter in super user mode to download config file

Run the below command at the prompt in order to install the SQL Server tools:

sudo yum update
   

Accept the terms and conditions when you are prompted. In the final step, run below command to make the SQLCMD utility accessible.

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

Once this command is run, you will be able to start using SQLCMD utility to connect to the installed SQL Server as shown. This shows that a connection is made to SQL Server on Linux.

SQLCMD connection

Steps to connect to the SQL Server on Linux locally using SSMS

In this section, we will try to connect to this SQL Server on Linux locally using SSMS installed on a windows machine. To get the full DNS name or IP address, refer to the below image from the Azure portal.

Get DNS name for Microsoft Azure Virtual Machine

On SSMS installed locally, provide the details as shown with the correct credentials.

SSMS Login Screen to Connect to Microsoft Azure Virtual Machine

With this, you will be able login successfully to a SQL Server installed on Linux.

Login success on SSMS
Next Steps
  • Try this tip using your own Azure subscription and test connectivity locally from SSMS.
  • To get familiar with Azure, refer to these SQL Server Azure Tips
  • To get familiar with SQL Server on Linux, refer to these SQL Server on Linux Tips


Last Updated: 2017-05-26


get scripts

next tip button



About the author
MSSQLTips author Mohammed Moinudheen Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools