How to Install and Use MS SQL Server on Linux
Earlier this year, Microsoft surprised the IT world with the announcement of their plans to bring MS SQL Server to Linux.
Under Satya Nadella’s leadership, the Redmond giant has made significant progress towards taking advantage of the areas where Linux dominates the industry (such as the technologies that power the cloud). The move to make SQL Server available in Linux is yet another indicative of this approach.
Whatever the company’s motivations behind this initiative, Linux system administrators are likely to need to learn how to install, maintain, and use MS SQL Server – specially considering that the preview version packages are already available for Red Hat Enterprise Linux 7 (includes CentOS 7 as well) and Ubuntu Server 16.04 64 bits (sorry – no 32-bit version available!).
The only “fancy” system requirement of the preview version is that the system where it is installed must have at least 3.25 GB of RAM.
Installing MS SQL Server on Linux
To begin, we’ll need to add two repositories to our software sources list.
On RHEL / CentOS:
1. Insert the following lines into /etc/yum.repos.d/sql-server.repo:
[packages-microsoft-com-mssql-server] name=packages-microsoft-com-mssql-server baseurl=https://packages.microsoft.com/rhel/7/mssql-server/ enabled=1 gpgcheck=1 gpgkey=https://packages.microsoft.com/keys/microsoft.asc
To install the MS SQL Server command-line tools, create /etc/yum.repos.d/msprod.repo with these contents:
[packages-microsoft-com-prod] name=packages-microsoft-com-prod baseurl=https://packages.microsoft.com/rhel/7/prod/ enabled=1 gpgcheck=1 gpgkey=https://packages.microsoft.com/keys/microsoft.asc
2. Then install the packages using yum package manager, as usual:
# yum install -y mssql-server mssql-tools
When the installation is complete, you will be reminded to run the configuration script (/opt/mssql/bin/sqlservr-setup) to accept the license terms, set the password for the SA user, and start the service. Additionally, you can choose to enable it to start automatically on boot.
3. Open port 1433/tcp on your firewall in order to allow external clients to communicate with the database server:
If you’re using firewalld:
# firewall-cmd --add-port=1433/tcp --permanent # firewall-cmd --reload
Otherwise (using iptables):
# iptables -A INPUT -p tcp --dport 1433 -j ACCEPT # iptables-save > /etc/sysconfig/iptables
On Ubuntu Server, do:
1. In order for Ubuntu to trust the packages from the MS SQL Server repositories, import the GPG keys:
$ sudo sh -c "curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -"
2. Add the repositories to /etc/apt/sources.list.d/sql-server.list:
$ sudo sh -c "echo deb [arch=amd64] https://packages.microsoft.com/ubuntu/16.04/mssql-server xenial main > /etc/apt/sources.list.d/sql-server.list" $ sudo sh -c "echo deb [arch=amd64] https://packages.microsoft.com/ubuntu/16.04/prod xenial main >> /etc/apt/sources.list.d/sql-server.list"
3. Resynchronize the package index files and update the core package and additional tools:
$ sudo apt-get update $ sudo apt-get install mssql-server mssql-tools -y
4. Run the configuration script as in the previous case:
$ sudo /opt/mssql/bin/sqlservr-setup
5. Choose “Yes” when prompted to accept the license terms for MS SQL Tools:
Testing MS SQL Server on Linux
We will login to the server and create a database named Fabrics. The
-P switch must be followed by the password you chose when you installed the package previously:
$ sqlcmd -S localhost -U SA -P 'YourPasswordHere' CREATE DATABASE Fabrics exit
If you’re using Linux, you can continue using the command-line as shown above. Otherwise, install SQL Server Management Studio Express if you’re on Windows.
Once done, enter the IP of the database server (192.168.0.200 in this case) and the login credentials (username=sa, password=YourPasswordHere):
Upon successful login, the Fabrics database should appear at the left hand side:
Next, click New Query to open a new query window where you’ll insert the contents of the Fabrics script from Codeproject.com, then click Execute.
If successful, you will see the script created 5 tables and the number of records in each:
To wrap up, run the following query to retrieve the first 5 records from the Clients table:
USE Fabrics SELECT TOP 5 FirstName, LastName, DateOfBirth FROM Client GO
The results should be identical to the output in the following image:
Congratulations! You have successfully installed and tested MS SQL Server on Linux!
In this article we have explained how to install MS SQL Server on RHEL / CentOS and Ubuntu Server.
Due to the newfound closeness of Microsoft and Linux, Linux system administrators will need to be knowledgeable on MS SQL Server if they want to stay at the top of their game.
By mid 2017, the same SQL Server editions will be offered on Linux as today on Windows: Enterprise, Standard, Web, Express, and Developer. The last two are free but only the Express edition will be licensed for production use (but with resource limits).
As always, feel free to use the comment form below to drop us a note if you have any questions. We look forward to hearing from you!