Microsoft SQL Server can now be run on Linux GNU.

Microsoft SQL Server ® on Linux GNU is here to stay. Get ready

Introduction

On December 28, 2016 – feast of the Holy Innocents, that special day when Spanish speakers make jokes throughout the day – it was announced the acquisition of a “Platinum” type membership by the company Microsoft ® in the Linux Foundation and the launch of Microsoft SQL Server ®. We took this news very seriously since for us, English-speaking people, we have April Fools’ Day on April 1st, that special day when we joke around. And if we had any doubts, it was officially published in the blog of the Microsoft ® technical team, which included a funny figure which showed a “Microsoft Linux” (sic) sign along with a penguin.

We have been using computers since 1989 with the famous MS-DOS® (Mr. Linus Torvalds wasn’t even studying at university at that point) we are still trying to conceive the idea that Microsoft SQL Server ® can be run on Linux. But nowadays, this is an unavoidable reality and those days of antagonism with former CEO Steve Ballmer are long gone. He left office in 2014 and his successor Mr. Satya Nadella led the company and he developed one of the largest computer infrastructures on the cloud computing network with the product “Microsoft Azure®” (formerly known as “Windows Azure®”).

This “Windows Azure®” product stands out because of its software development kit or “SDK” which is published as open-source (which is different from the free software license in Linux). To this day, artificial intelligence is already being developed in “Microsoft Azure®”, which is now known as “machine learning”.

This brief introduction leads us to our article today: Microsoft SQL Server ® on Linux GNU.

Microsoft SQL Server ® on Microsoft Windows®

Okay, we don’t want to fool you and yes, we are going to talk about GNU/Linux but first we need to know the framework of that popular database engine. Microsoft SQL Server ® was born in 1989 to be run on … IBM OS/2, yes, I am not joking. At that time, it was the “Ashton-Tate” company with its flagship product dBase that caused large volumes of data to flow through the local area network because each customer took a copy of the database to each computer where it was running. That’s why Microsoft bought the SQL Server ® license from Sybase which worked with the client/server model through simple commands and returned limited amounts of data (almost all the work is done separately and remotely on the server). Ashton-Tate provided its customer market and dBase worked as an interface for the user, while Microsoft and Sybase handled the work on the server side.

After some time (in 1994) the version 6 of SQL Server ® appeared, which was also run on the recent Windows NT® and that version was no longer made by Sybase. From version 7.0 onwards, it was massively rewritten in C ++ language and as of 2000, when it was time for OS/2 to be discontinued, SQL Server ® remained only for the Windows NT ® operating system. Then in 2001 its successor Windows XP® inherits the Windows NT® technology – by the way, “NT” means “New Technology” – and it was able to reach common users and their small and medium enterprises: the efficient management of relational data was no longer for exclusive use for large corporations.

In 2005, a dilemma emerged: the rise of 64-bit and multi-core processors, as well as a large amount of RAM for which Microsoft SQL Server ® was not prepared to take full advantage of all this potential. In reaction to this, it is decided to make a platform layer to contain “SQLOS layer”. A SQLOS is a “highly configurable user-level operating system with a powerful application-programming interface.” (You can have a look at the official and detailed explanation here) We will soon see why it was a very good choice, since it completely separates programming from the workloads and threads on a computer, on which Microsoft® SQL Server ® is installed.

Microsoft SQL Server ® on Linux GNU

In the world of web servers, GNU/Linux distributions have been the leading ones for many years. Debian has the Ubuntu Server, used by many companies of different sizes, to publish their sites on the Internet. But for the databases there are quite a few programmers who have chosen Microsoft SQL Server ® and because of this, companies must acquire a Microsoft Windows® operating system, this way creating additional work in the management of the local area network due to a mixed environment.

This concern gradually surfaced among Microsoft® customers and so they told the Redmond-based company (in Washington, USA) which put them on the spot because they did not know how to carry the millions of lines of code, accumulated during all these years, to the environment of the operating system of the penguin. The answer to this problem was found in a project dating back to 2011 called Drawbridge®, which was originally intended for virtualization of applications in a secure environment (called “sandbox”).

It was then necessary to “isolate” Microsoft SQL Server ®. Programmers coded in a carefree way as they worked in a well-known environment, which dealt with the various existing hardware. Once the Drawbridge processes were well understood and adapted, the following parameters were established:

• Quality and security must meet the same high bar we set for SQL Server ® on Windows
  Provide the same value, both in terms of functionality, performance, and scale
• Application compatibility between SQL Server ® on Windows and Linux
• Enable a continued fast pace of innovation in the SQL Server ® code base and make sure new features and fixes appear immediately across platforms
• Put in place a foundation for future SQL Server suite services (such as Integration Services) to come to Linux

To make SQL Server ® support multiple platforms, the engineering task is essentially to remove or abstract away its dependencies on Microsoft Windows®, as we can see, it wasn’t easy for the developers, who were being pushed out of their comfort zone!

This is how they created “SQL Platform Abstraction Layer” (SQL PAL) that merges the concepts of “SQLOS layer” and Drawbridge®. From now on the development team can work with a single base code and they do not need to worry about where the code will run (this includes the new Microsoft Azure® platform we talked about earlier). As we can see, so far, everything has come to fruition and the projects and resources of the computer giant, which seemed scattered, are now part of a whole.

Installing Microsoft SQL Server ®

As St Thomas once said: “Seeing is believing” that is why we took a virtual machine with 4 gigabytes of RAM (the minimum requirement is 3.25 gigabytes). Then, we downloaded Ubuntu Server 16.04 through Torrent technology and after installing it and doing the required security updates (just because it is a test server, we shouldn’t loose our good work habits!) and then we decided to install Microsoft® SQL Server ® on this “clean” machine. (Jimmy Olano, writer of this article, has made a 23 minutes recording with the complete installation process, you can watch it on YouTube by clicking on this link)

Essentially the instructions are to import the Microsoft® enabled security keys of its website so that we can certify that the content downloaded from that company has not been altered. Then we will add the link of the location of the repository in our file made for this, and then we need to update the list of components and give the order to download and install the packages. In fact, in the Microsoft® blog, where the instructions are published, it talks about the use of the “-y” parameter in order to accept the license immediately. We recommend installing without this option, so that we are always aware that we are using exclusive software and that we must stick to the license shown in the following figure of our authorship (Creative Commons Attribution-Share Alike 4.0 International):

Installing SQL Server ® command-line tools

Once we have installed our SQL Server ®, we must configure the “firewall” and establish the access policies to the computer which should be ready to start receiving data through other computers. But if we want to work directly on the console of our machine we must install the command-line tools with the command «sudo apt-get install mssql-tools unixodbc-dev» which, as expected, also contains questions for the acceptance – or denial – of accepting the conditions of use.
In our case, in that video we mentioned earlier, apart from installing the “command-line tools” we had the opportunity to create a database, as well as to create a table, and to introduce a few values ​​and to make a simple conditional query. But, we will not forget about this tool, which can offer a lot more.

Monitoring SQL Server ®

As a monitoring tool, the Microsoft® SQLCAT12 development team concluded that three tools are needed to monitor:

• collectd.
• InfluxDB.
• Grafana.

We will focus on collectd and we will talk briefly about InfluxDB and Grafana.

“collectd”

This open source software is written in C language and is a daemon or service which we will install and run on the server where Microsoft® SQL Server ® is hosted. It is very popular in routers that use OpenWrt, which is a well-known Linux distribution specially designed for these devices, which also has more than 100 plugins, which makes it easier to configure popular Linux applications such as Apache and MySQL (When writing this article, we reviewed that list and we did not find a specific one for Microsoft® SQL Server ®, given the novelty of the arrival of this software in the GNU world.
In order to deliver the “collectd” data, you can either write them in RDD file format (“RDDfile”) so that they can then be plotted with RDDtool or they can be collected using a plugin.
Currently Pandora FMS does not have a plugin for collectd but it has no problem connecting via SNMP.

To install collectd we must have Git and Docker Engine installed and then create an account in Microsoft® SQL Server ® using the following instructions:

USE master;
GO
CREATE LOGIN [collectd] WITH PASSWORD = N'mystrongpassword';
GO
GRANT VIEW SERVER STATE TO [collectd];
GO
GRANT VIEW ANY DEFINITION TO [collectd];
GO

The latter is very important in order for collectd to have proper access to our database server. Microsoft® invites you to clone your repository in GiHub, where you can find some very detailed instructions and we can contribute with any observation, correction or we can collaborate with an improvement if we request a pull request.

“InfluxDB”

It is responsible for communicating with collectd and then it saves and organises the data collected.

“Grafana”

Software that produces stunning graphics and drawings that represent the data collected by InfluxDB. It is strongly recommended that both InfluxDB and Grafana be installed on another computer different from the one running Microsoft® SQL Server ® or even each one on their own machine if the amount of systems to be monitored is large enough.

Conclusions

Microsoft® made sure to make a good deal with its application SQL Server ®: the code will not be able to run away towards Linux and it avoids compromising its reliability. It also has a base code ready to be improved upon the arrival of new hardware or to cover other operating systems.

All trademarks named herein are accompanied by the “®” symbol and are owned by Microsoft® Corporation and comply with the proper use of such trademarks.

About Pandora FMS

Pandora FMS is a flexible monitoring system, capable of monitoring devices, infrastructures, applications, services and business processes.
Of course, one of the things that Pandora FMS can control is the hard disks of your computers.
Would you like to know more about what Pandora FMS can offer you? Discover it by entering here: https://pandorafms.com

If you have more than 100 devices to monitor, you can contact us through the following form: https://pandorafms.com/en/contact/

Also, remember that if your monitoring needs are more limited you have at your disposal the OpenSource version of Pandora FMS. Find more information here: https://pandorafms.org