This is a step-by-step procedure to help a Microsoft Azure user to create Azure Virtual Linux Machine with SQL Server onboard with no charge for license.
- Microsoft Azure account;
- SSH terminal.
How to Deploy Azure Linux Virtual Machine with SQL Server
The following is a step-by-step procedure.
Step 0. Virtual Machine Selection
Get into your Microsoft Azure account.
Click on “New” as shown above, then see a menu as shown below.
As shown above click on “Compute” section and look for “SQL Server Linux”.
You should see a list of available SQL servers, as shown below (it may be a long list, so be patient).
Click on “Free SQL Server License: SQL Server 2017 Developer on Ubuntu Server 16.04 LTS“.
You should see something like the following:
Change “Resource Manager” to “Classic” (see the red arrow that points out to the place on the picture above), this is just to make your life a little bit easier.
To finish the preparations click on the “Create” button meaning “Create Azure SQL Server 2017 Developer on Ubuntu” and start the following procedure of creation.
Step 1. Starting the Creation Procedure with Basics
Fill in the fields Name, User name, Password, etc.
Step 2. Choosing the Size of Your Linux Virtual Machine
Choose the size of your SQL server on Ubuntu.
Pricing here is not for the case, it is for other cases.
Click on “Select” button.
Step 3. Configure Optional Features
See the parameters there and change them, if you want, then click on “OK” button.
Step 4. See the Summary and check the options, names, etc
Click on “OK” button.
Note: refreshing the screen sometimes helps when you wait too long for finishing the procedure
Now you should have Microsoft Azure SQL server on Ubuntu at your disposal (see the Overview of the SQL server on Ubuntu below).
How to Connect to Azure SQL Server on Linux (Ubuntu)
Connect SSH Terminal to Ubuntu Server (Ubuntu Virtual Machine)
Take parameters for the SSH terminal connection to Linux (Ubuntu) server from the parameters list of your Ubuntu virtual machine (Ubuntu server) created according to the creation procedure described above.
For example, take “DNS name” from your Azure “SQL Server on Ubuntu” screen shown at the very last picture of the creation procedure and copy it to the “Host” field in the Xshell New Session Properties window, if you use Xshell terminal.
Configure SQL Server on Ubuntu using the SSH Terminal
Type the following Microsoft instructions into your SSH terminal window:
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add - sudo add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list)" sudo apt-get update
After the last command (“update”) there should be a long list like the following:
sudo apt-get install -y mssql-server sudo /opt/mssql/bin/mssql-conf setup systemctl status mssql-server curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add - sudo add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list)" sudo apt-get update
After entering the last command (“update”) there should be a long list similar to one from the above black-and-white output window.
sudo apt-get install -y mssql-tools unixodbc-dev echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc source ~/.bashrc sudo /opt/mssql/bin/mssql-conf setup
After entering the last (“setup”) command from the list you will be asked for two things.
The first thing: choose an edition of SQL server.
Choose your option (Developer for this case).
The second thing: enter the SQL Server system administrator password.
Type in the password (you will use it later).
Note: although it doesn’t say it explicitly, the password here must contain both uppercase and lowercase letters, numbers, and other (non-alphanumeric) symbols, 8 characters is the minimal length for the password.
Now the connection to the SQL server on Ubuntu should work.
Check its version just in case to make sure that the server is really up and running.
As a result, you should see a text like this:
From now on the SQL server on Linux (Ubuntu) should be ready to use.
Test the SQL Server on Linux (Ubuntu)
Use sqlcmd command-line tool to use Microsoft Azure SQL Server on Linux (Ubuntu).
Note: sqlcmd tool has been installed during configuring the SQL Server.
sqlcmd -S localhost -U SA -P '<SQL Server system administrator password>'
Note: <SQL Server system administrator password> in the line above is the same “SQL Server system administrator password” that you defined above when answering about “the second thing”.
If successful, you should get to a sqlcmd command prompt from the Microsoft Azure SQL Server on Ubuntu:
Note: the prompt changes (incrementing) each time after entering a command, and the counting (incrementing) stops after entering “GO” (after that the counting starts from “1>” again).
Microsoft site contains a set of instructions any user can use for testing purposes, let’s use it:
CREATE DATABASE TestDB SELECT Name from sys.Databases GO
After this you should see a text like this:
Let’s proceed further:
USE TestDB CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT) INSERT INTO Inventory VALUES (1, 'banana', 150); INSERT INTO Inventory VALUES (2, 'orange', 154); SELECT * FROM Inventory WHERE quantity > 152; GO
Note: DON’T FORGET ABOUT “GO” AT THE END.
As a result, you should see a text like this:
Have a good time using the Microsoft Azure SQL Server on Linux (Ubuntu) 🙂