Prashanth Jayaram

Installation of SQL Server vNext CTP on Linux Distribution CentOS 7

May 10, 2017 by

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.

See Also

SQL Server vNext – Does Microsoft love Linux?

References


Prashanth Jayaram

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

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
Prashanth Jayaram
484 Views