It was a paradigm shift in December 2016, when Microsoft made their SQL Server database available for Linux; it was the first time in history that Microsoft ever designed SQL Server to run on a non-Windows operating system. SQL Server vNext was released for public preview so the user community could test and deploy SQL Server on a Linux operating system. Microsoft took a major step in diversifying the database technology into the non-windows platform for the first time.
Introduction
The close integration of various data sources under one umbrella called Hadoop – Big Data enabled DBAs to handle data and its data structures on the available platforms. It’s a great opportunity for the SQL community members to try MS SQL on the non-windows platform.
SQL Server on Linux can potentially provide customers with even more flexibility in their data solution. SQL Server’s proven enterprise experience and capabilities is a valuable asset to Enterprise Linux customers around the world. It’s expanding the database market to meet the needs and be on par with the changing trends in technology.
This article is an effort to detail the instructions for the installation of SQL Server vNext on Linux. The article also includes several basic Linux commands, thereby being helpful in understanding the process of installation and configuration.
This article is a comprehensive guide for the installation and configuration of MSSQL; it includes:
- Introduction and understanding of how SQL Server run on Linux
- Challenges and complications in building and managing SQL Server on Linux
- Curating the MSSQL Server installation using Linux commands
- Detailed installation procedure
Pre-requisites
- Basic understanding of Linux
- Access to CentOS/RHEL Software Libraries
- At least 4 GB RAM
- At least 8 GB of hard disk space
How Does SQL Server Run on Linux?
Microsoft has provided great insight on how they were able to port SQL Server to run natively on Linux introducing what is known as Platform Abstraction Layer (“PAL”). The Microsoft Research Team set out to bring full functionality, performance, and scale value of the SQL Server RDBMS to Linux. The Microsoft Research Drawbridge acts as an abstraction layer between the OS and the application layers. Drawbridge explored a new approach to process virtualization and isolation. It’s a form of virtualization, specifically for application sandboxing. In fact, it’s very hard to provide a capable SQL version outside of windows within the stipulated time, hence MSR (Microsoft Research) team decides to integrate SQL Server’s existing platform layer SOS (SQL Server Operating System) with Drawbridge to create what we call as the SQLPAL. The SOS provides robust memory management, thread scheduling, and IO services. Creating SQLPAL enabled the full functional SQL version to run on Linux. Please read the reference section for more information.
Basic Linux Commands
Below are the few basic Linux commands which will help SQL administrator understand the process of installation of SQL Server and to troubleshoot unforeseen issues during the configuration.
Linux Commands
| Command | Usage | Description |
| uname | uname -a | Linux find out the current running kernel version |
| ls | ls /directory_path | List files |
| cp | cp src_file_path /destination_directory_path | Copy files |
| mv |
mv src _file_path /destination_directory_path |
Renames a file or moves it from one directory to another directory |
| rm | rm file_path | Delete files |
| ln | ln file_path /destination_directory_path | Link files |
| cd | cd /Directory_path | Change directory |
| pwd | pwd | Print current directory name or Present Working Directory |
| mkdir | mkdir /new_directory_path | Create directory |
| rmdir | rmdir /directory_path | Delete directory |
| cat | cat /file_path | View files |
| chown | chown user_name file_path | Change file owner |
| chgrp | chgrp group_name /file_path | Change file group |
| chattr | sudo chattr +i/-i /file_path | Change advanced file attributes |
| ifconfig | ifconfig | Set/display network information/ IP address |
| systemctl |
systemctl start/stop/reload/mask/unmask <servicename> systemctl status mssql-server |
If you are running as a non-root user, you will have to use sudo since this will affect the state of the operating system |
| lscpu | lscpu | CPU/hardware information |
| lspci | lspci | List all PCI devices |
| fdisk | fdisk -l | List installed hard disk and size |
| free | free -m | free and used memory in the system |
| wget | wget https://packages.microsoft.com/config/rhel/7/mssql-server.repo |
wget is a free utility for non-interactive download of files from the web The simplest way to use wget is to simply provide it with the location of a file to download over HTTP. |
| yum | yum install/remove/update mssql-server -y | The yum command is the primary tool for getting, installing, deleting, querying, and otherwise managing Red Hat Enterprise Linux RPM software packages |
Install MSSQL Server on CentOS 7
CentOS is an Enterprise-class Linux Distribution derived from sources freely provided to the public by Red Harm Inc. for Red Hat Enterprise Linux.
This section covers how to install and configure MSSQL in the RHEL based Linux distribution CentOS.
- List the available repositories: mssql-server.rep and prod.repo
- Install wget utility to download the files
- Download the repositories
- Move the repositories /etc/yum.repos.d/
- Install the MSSQL database service using yum, a package management tool for RPM-based Linux systems
- Configure the MSSQL instance by accepting the license agreement
- Check the MSSQL service status
- Setup the firewall configuration to listen TCP port 1433
- List the IP address
- Access the instance using another Windows machines using SSMS
Microsoft has created a repository to download the SQL Server libraries. You can access it at here.
Let’s get starteded by installing wget, a free utility to non-interactively download files from the Web. The simplest way to use wget is to simply provide it with the location of the file to download over HTTP.
If you’re running as a non-root user, you will have to use sudo, since this will affect the state of the operating system.
sudo yum -y install wget
OR
yum -y install wget
Download mssql-server.repo using wget
wget https://packages.microsoft.com/config/rhel/7/mssql-server.repo
Download prod.repo using wget
wget https://packages.microsoft.com/config/rhel/7/prod.repo
Now, move the files to the /etc/yum.repos.d/ directory so that it can be used with yum command
mv *.repo /etc/yum.repos.d/
ls /etc/yum.repos.d/
OR
wget –directory-prefix=/etc/yum.repos.d/
https://packages.microsoft.com/config/rhel/7/mssql-server.repo
ls /etc/yum.repos.d/
The libraries have now been downloaded and moved the respective directories. We’re ready for the installation procedures
yum –y install mssql-server
The installation process will perform the basic checks such as for dependencies, memory, disk space, etc., followed by the download of the required packages. The installation will start automatically, once the download completes.
[root@localhost]# /opt/mssql/bin/mssql-conf setup
That would complete the setup; it’s to accept the license agreement and set up the SA password.
Make sure to specify a strong password for the SA account (such as it being at least 8 characters long, has uppercase and lowercase letters, numeric characters and/or other non-alphanumeric symbols).
Run the following command to ensure the SQL Server service is running:
[root@localhost]# systemctl status mssql-server
Firewall configuration
Here’s how we enable the firewall to listen to the TCP port
- Run setup using the command, setup
- Select System Services
- Click Run Tool
- Choose firewalld.service
- Click OK
- Open the SQL Server port on the firewall
[root@localhost]# firewall-cmd –zone=public –add-port=1433/tcp –permanent
[root@localhost]# firewall-cmd –reload
Get the IP address
Fetch the IP address of the server using the ifconfig command.
[root@localhost]# ifconfig
Ping both, the Windows and the Linux servers.
Connect Linux MSSQL instance using SQLCMD or SSMS installed on another Windows machine
Connect the MSSQL instance using SQLCMD.
The sqlcmd utility is a command line utility used for interactive execution of Transact-SQL statements.
- The server option (-S) identifies the instance of Microsoft SQL Server to which sqlcmd connects.
- Authentication options (-U and -P) specify the credentials that sqlcmd uses to connect to the instance of SQL Server.
Connecting using SSMS
When using SQL Server Authentication, logins are created in the SQL Server and are not based on Windows user accounts. Both, the username and the password, are created on the SQL Server and are stored in the SQL Server. Users connecting using SQL Server Authentication must provide their credentials (username and password) every time they connect to the instance.
Querying MSSQL Instance from SSMS
SQL Server Management Studio (SSMS) remains a Windows-only feature. Although, we can connect to a Linux SQL Server instance from the Windows SSMS. Developers can also use the database tools in Visual Studio, that allow queries, table design, stored procedure editing, and more.
Summary
According to reports, we SQL DBAs have to wait until mid of 2017 to get our hands on the official version of SQL Server on Linux. Meanwhile, we can catch the free preview of SQL Server on Linux. In this article, I have tried to include every step needed to setup MSSQL on Linux. This is certainly a great initiative from Microsoft, and I strongly encourage you to try SQL Server on Linux.
My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.
View all posts by Prashanth Jayaram
- Stairway to SQL essentials - April 7, 2021
- A quick overview of database audit in SQL - January 28, 2021
- How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021