So that day have come now, MSFT has to port their software to Linux in order to stay relevant. MS SQL Server preview have come out and it is supported on Ubuntu, CentOS, RHEL and Docker container. In this article, we are going to walk through how to install Microsoft SQL Server on linux platforms versions (CentOS / RHEL 7 and Ubuntu 16.04 LTS). The installation of the software is straightforward so let's start.
Installing MS SQL Server on CentOS or RHEL
As root, issue this command to add Microsoft repository
curl https://packages.microsoft.com/config/rhel/7/mssql-server.repo > /etc/yum.repos.d/mssql-server.repo
After the repository have been added, log in as non-root user with sudo rights. I will do it like this, you change for your username:
su miki
Next we will install the MS SQL Server
sudo yum install mssql-server
After the yum have finished installing the package, we need to run the script which is similar to mysql_secure_install script.
sudo /opt/mssql/bin/sqlservr-setup
Complete the prompt like this:
If firewalld is not installed and enabled by default (in minimal install sometimes isn't) lets enable it:
sudo yum install firewalld
Enable it to start at boot
sudo systemctl enable firewalld
Start it for this session
sudo systemctl start firewalld
And add rules so SQL server can work
sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent
sudo firewall-cmd --reload
Check if SQL server is running
systemctl status mssql-server
Installing on Ubuntu
If you are using Ubuntu, here is how to install it. First lets enter superuser mode
sudo su
Lets add the key for the repository
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
Then add repository
curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list > /etc/apt/sources.list.d/mssql-server.list
And update sources list
apt update
After this we can switch to non-root user either with su username or by exiting
exit
Next we install the MS SQL server by following command
miki@ubuntu-1:~$ sudo apt-get install -y mssql-server
Same as on centos, we need to run the script
sudo /opt/mssql/bin/sqlservr-setup
And check if it is running
systemctl status mssql-server
Docker image
A third way to use Microsoft SQL Server is with docker image. If you have working docker installation, you can run this on any Linux distribution. For instruction how to install docker on your distribution, you can visit official docker site.
When you have docker installed we can proceed to pulling the docker image
sudo docker pull microsoft/mssql-server-linux
We will need directory for persistent volume for the database
mkdir ~/mssql
This command will start the docker container with this image and with /home/miki/mssql as data dir. You change this path for your data dir
sudo docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<YourStrong!Passw0rd>' -p 1433:1433 -v /home/miki/mssql:/var/opt/mssql -d microsoft/mssql-server-linux
Connecting to the MS SQL Server
In order to connect to the server, you need mssql tools which are not part of mssql server install. Here is how to install them
On Ubuntu
As super user add key for new repository (yes, it is another repo, not same as mssql)
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
Add repository
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/msprod.list
Update sources list
sudo apt-get update
Log in as non-root
su miki
Install the tools
sudo apt-get install mssql-tools
On CentOS
As root run run this command to add repo:
sudo curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/msprod.repo
Switch to normal user
su miki
Install the tools
sudo yum install mssql-tools
On both CentOS and Ubuntu, you would need to accept EULA while install is in the process.
To connect the DB server, you run following command
sqlcmd -S localhost -U SA -P 'YourPassword'
Where YourPassword is the password you entered when you ran sqlservr-setup script. This should give you the mssql prompt
Working with SQL server
Now that we done with the install and have accessed the server, lets use it. For example, this commands will create database linoxide and change usage to it.
1> CREATE DATABASE linoxide;
2> GO
1> USE linoxide;
2> GO
Changed database context to 'linoxide'.
1>
In order to execute the command, after command you need to type GO as next line. Showing all databases is done with following command
SELECT Name from sys.Databases;
Conclusion
We have successfully installed Microsoft SQL server on Ubuntu 16.04, RHEL and CentOS 7 and Docker container. In my opinion, MariaDB and PostgreSQL are still a better choices for Linux server, but if you just have to use MSFT software, now it is possible on Linux too. Thank you for reading, this is all for today.
This is helpful for when you're in a "Microsoft shop" with applications (e. g. RSA Archer or BMC Remedy) that require MS SQL Server, but you'd prefer to introduce and use a better platform like GNU/Linux for reliability reasons. You can now do so and get that F/OSS foot in the door. Since the client has already decided that They Want That Specific Application, the alternative would be no introduction of F/OSS.
For this reason, in the grander scheme of things, I do consider this a good thing. Remember that RMS himself used UNIX to write GNU and replicated UNIX, piece by piece, including the C compiler (GCC). Today we have GNU/Linux and the BSD's, which can build themselves, as a result, which is a very good thing.
I view running MS SQL Server on GNU/Linux in similar fashion; it's a way to start the migration of Microsoft shops to at least a mixed environment. Any progress in this direction is a win. Once the MCSE's on staff learn that F/OSS platforms are actually really good, some of them start looking into them more. Having been an MCSE for quite a few years, I'm an example of this type of person. Now I prefer F/OSS platforms like GNU/Linux and BSD.
This is the sort of long-term thinking that helps result in more F/OSS usage, as I suspect most visitors to Linoxide believe is a good thing, as do I.
--SYG
Very interesting, is possible to use the Microsoft GUI?