SQL Server on Linux on Azure (part 2 – setup)
- SQL Server on Linux on Azure (part 1 – deployment)
- SQL Server on Linux on Azure (part 2 – setup)
While looking for an image on the Azure Marketplace, I stumbled across a new image which I just had to try out: SQL Server vNext on Red Hat
In the first article I showed the deployment of the Marketplace item and the configuration of the related Network Security Group. This time we will finish the setup and get SQL Server vNext up and running on our Linux VM.
First we make sure our VM is running and then connect to the VM with the SSH client of our choice (SSH = Secure SHell). From Windows I prefer using either Putty or Royal TS, which I use as a RDP manager as well. Depending on which authentication mechanism you chose during deployment, the username and either the private key or the password is needed.
After logging in you’ll be greeted by a nice and simplistic Bash (Bourne-again shell) console window.
SQL server is already installed but not setup on the system. As well we do not have any SQL tools installed, like sqlcmd. The SQL tools are not a prerequisite to run SQL Server on Linux, but they come in handy later, when we actually want to connect to the SQL instance. That being said we will start with getting SQL up and running and follow that by installing the SQL tools.
We start the SQL server setup by entering following command
You will have to accept the SQL Server vNext license terms by typing YES and you can select if you want to start the SQL Server service now and wether this should be started automatically upon starting the VM. As this is the sole purpose for this machine I chose “y” on both questions.
If we now check the state of the service we’ll see it is running. To do so we enter following command:
systemctl status mssql-server
So our SQL server is running. Woohoo! Oh, wait… how do we get to that box, to actually do something with it?
Looks like we are not done yet. Actually two more steps have to be completed:
- Installing the SQL tools, to be able to locally work with the SQL Server instance
- Configuring the VMs firewall, to be able to work remotely and connect from SQL Server Management Studio
So let’s get that done with!
We will start by installing the SQL tools. To be able to do so we need to add Microsofts Red Hat package repository to the VMs repo store. We have to do this as the actual root user, so we will enter superuser mode and use curl to download the repo config files:
sudo su curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/msprod.repo exit
When the download has completed, we’ll install the tools as follows.
sudo yum install mssql-tools
This command first checks for some prerequisites and asks for confirmation to install them. Afterwards we’ll be asked to accept the licenses for MS SQL ODBC drivers and the SQL tools by again typing YES. It should finish saying “Completed!”.
Afterwards we can use sqlcmd to connect to the running SQL instance:
sqlcmd -S localhost -U SA
From sqlcmd we query SQL Server for its version:
select @@VERSION go
The output tells us we are running SQL Server vNext CTP1 version 18.104.22.168 on Linux (Red Hat Enterprise Linux Server 7.2). How cool is that?
Let’s see how to make that even cooler by opening the VMs internal firewall and connecting our SQL Server Management Studio. To do so you have to install SSMS vNext CTP1 or newer from here. So let’s start with the firewall:
sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent sudo firewall-cmd --reload
And now let’s see what happens if we try to connect with Management Studio.
Since I thought this pretty useless I imported the good old Northwind Trading demo database from Codeplex. First you have to create a new database Northwind and then you need to run the SQL script. You can do this either by importing the SQL script with sqlcmd locally or by loading the script into SSMS and pressing Execute! or F5.