SQL Server 2019 on Linux - Step by step process to install MSSQL on CentOS and Ubuntu






Are you trying to install SQL Server 2019 on Linux?

This guide is for you.


SQL Server is supported on Red Hat Enterprise Linux (RHEL), SUSE Linux Enterprise Server (SLES), and Ubuntu. It is also supported as a Docker image, which can run on Docker Engine on Linux or Docker for Windows/Mac.

Linux system administrators will need to be knowledgeable on MS SQL Server if they want to stay at the top of their game.

Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to perform Linux Related queries and Software Installation tasks.

In this context, we shall look into how to install SQL Server 2019 on Linux.


More about Microsoft SQL Server ?

A relational database management system, Microsoft SQL Server might be the right choice for an RDBMS.

Open-source MySQL and PostgreSQL are typically synonymous with Linux distributions. However, working with MSSQL on Linux is also supported.

Here, let us see how to install MSSQL Server 2019 on CentOS 7 and Ubuntu 16.04.


How to install SQL Server 2019 on Linux ?

To start with this setup, ensure that you have a Server with at least 2GB of memory along with CentOS 7 and Ubuntu 16.04 servers with root access.


1. Install MSSQL Server 2019 in CentOS 7

i. Add MSSQL 2019 Preview Repository

Make sure that all packages are up to date by running the command below:

root@centos ~]# yum update -y

Then, we tell the package manager yum where to look for the mssql-server package by adding the appropriate repo by executing the command:

root@centos ~]# curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-preview.repo


ii. Install SQL Server

Now that yum is aware of the MSSQL repo, we can use it to install the package:

root@centos ~]# yum install -y mssql-server


iii. Configure MSSQL Server

Then, we need to configure SQL with a system administrator password and confirm the edition. 

a. Here we use the Developer edition, choice 2, as it is free:

root@centos ~]# /opt/mssql/bin/mssql-conf setup
usermod: no changes
Choose an edition of SQL Server:
1) Evaluation (free, no production use rights, 180-day limit)
2) Developer (free, no production use rights)
3) Express (free)
4) Web (PAID)
5) Standard (PAID)
6) Enterprise (PAID) – CPU Core utilization restricted to 20 physical/40 hyperthreaded
7) Enterprise Core (PAID) – CPU Core utilization up to Operating System Maximum
8) I bought a license through a retail sales channel and have a product key to enter.
Details about editions can be found at
https://go.microsoft.com/fwlink/?LinkId=852748&clcid=0x409
Use of PAID editions of this software requires separate licensing through a
Microsoft Volume Licensing program.
By choosing a PAID edition, you are verifying that you have the appropriate
number of licenses in place to install and run this software.
Enter your edition(1-8): 2
The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from:
https://go.microsoft.com/fwlink/?LinkId=855862&clcid=0x409
The privacy statement can be viewed at:
https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409
Do you accept the license terms? [Yes/No]:Yes
Enter the SQL Server system administrator password:
Confirm the SQL Server system administrator password:
Configuring SQL Server…
This is an evaluation version. There are [116] days left in the evaluation period.
ForceFlush is enabled for this instance.
ForceFlush feature is enabled for log durability.
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /usr/lib/systemd/system/mssql-server.service.
Setup has completed successfully. SQL Server is now starting.


b. Eventually, we verify that the mssql service is running:

root@centos ~]# systemctl status mssql-server

Our output will be similar to this:

mssql-server.service – Microsoft SQL Server Database Engine
Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor preset: disabled)
Active: active (running) since Wed 2019-10-23 20:18:03 EDT; 2min 45s ago
Docs: https://docs.microsoft.com/en-us/sql/linux
Main PID: 61529 (sqlservr)
CGroup: /system.slice/mssql-server.service
├─61529 /opt/mssql/bin/sqlservr
└─61549 /opt/mssql/bin/sqlservr


iv. Allow Remote Connections (Optional)

If we want to access the SQL server remotely, we have to open up the SQL Server port.

Firewalls are in place to keep the server safe by limiting access to it:

root@centos ~]# firewall-cmd –zone=public –add-port=1433/tcp –permanent

Once done, we need to reload our firewall rules and verify the port is open:

[root@centos ~]# firewall-cmd –reload
success
root@centos ~]# firewall-cmd –list-ports
1433/tcp


v. Add Microsoft Red Hat repository

We need a way to interact with our SQL server. 

First, let us add another repo so we can use yum to install SQL Server command-line tools:

root@centos ~]# curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo


vi. Install and setup MSSQL Server command-line tools

During the installation of these packages, there will be a couple of interactive prompts to accept the license terms:

root@centos ~]# yum install -y mssql-tools unixODBC-devel

For ease of use we can add the path /opt/mssql-tools/bin/ to the PATH variable on the server so that we can execute SQL commands from any location on the server:

root@centos ~]# echo ‘export PATH=”$PATH:/opt/mssql-tools/bin”‘ >> ~/.bash_profile
root@centos ~]# source ~/.bashrc

Finally, to verify that we can make a connection to SQL Server, run:

root@centos ~]# sqlcmd -S localhost -U SA
Password:
1>


[Couldn't complete SQL Server installation? Contact us now. ]


2. How to install MSSQL Server 2019 in Ubuntu 16.04 ?

i. Add MSSQL Server Ubuntu 2019 preview repository.

a. Initially, we update the server packages:

root@ubuntu1604:~# apt-get update -y

b. Once done, we add the GPG keys for the repository we want to add. 

It is a way for Linux users to verify the validity of files and confirm they come from trusted sources:

root@ubuntu1604:~# wget -qO- https://packages.microsoft.com/keys/microsoft.asc | apt-key add –

c. After that we can add the repository:

root@ubuntu1604:~# add-apt-repository “$(wget -qO- https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-preview.list)”

d. The repository requires an HTTPS connection. To ensure that apt can connect to the repo, we need to be certain it can connect over https:

root@ubuntu1604:~# apt-get install -y apt-transport-https


ii. Install MSSQL Server

Now all that is left to do is to make sure apt knows about the new repo and install MSSQL Server:

root@ubuntu1604:~# apt-get update -y
root@ubuntu1604:~# apt-get install -y mssql-server

iii. Configure MSSQL Server

The configuration step is the same on both CentOS 7 and Ubuntu 16.04. 

a. During the process, there will be interactive prompts:

root@ubuntu1604:~# /opt/mssql/bin/mssql-conf setup
usermod: no changes
Choose an edition of SQL Server:
1) Evaluation (free, no production use rights, 180-day limit)
2) Developer (free, no production use rights)
3) Express (free)
4) Web (PAID)
5) Standard (PAID)
6) Enterprise (PAID) – CPU Core utilization restricted to 20 physical/40 hyperthreaded
7) Enterprise Core (PAID) – CPU Core utilization up to Operating System Maximum
8) I bought a license through a retail sales channel and have a product key to enter.
Details about editions can be found at
https://go.microsoft.com/fwlink/?LinkId=852748&clcid=0x409
Use of PAID editions of this software requires separate licensing through a
Microsoft Volume Licensing program.
By choosing a PAID edition, you are verifying that you have the appropriate
number of licenses in place to install and run this software.
Enter your edition(1-8): 2
The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from:
https://go.microsoft.com/fwlink/?LinkId=855862&clcid=0x409
The privacy statement can be viewed at:
https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409
Do you accept the license terms? [Yes/No]:Yes
Enter the SQL Server system administrator password:
Confirm the SQL Server system administrator password:
Configuring SQL Server…
This is an evaluation version. There are [116] days left in the evaluation period.
ForceFlush is enabled for this instance.
ForceFlush feature is enabled for log durability.
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /lib/systemd/system/mssql-server.service.
Setup has completed successfully. SQL Server is now starting.


b. MSSQL Server should now be running and enabled. In order to verify, we can run:

root@ubuntu1604:~# systemctl status mssql-server –no-pager
* mssql-server.service – Microsoft SQL Server Database Engine
Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor preset: enabled)
Active: active (running) since Thu 2019-10-24 00:24:23 EDT; 3min 45s ago
Docs: https://docs.microsoft.com/en-us/sql/linux
Main PID: 19446 (sqlservr)
Tasks: 135
Memory: 548.5M
CPU: 12.499s
CGroup: /system.slice/mssql-server.service
|-19446 /opt/mssql/bin/sqlservr
`-19485 /opt/mssql/bin/sqlservr


iv. Allow Remote Connections

If we intend to utilize a remote connection to the new SQL Server, it is necessary to open up the SQL Server port.

a. To keep our firewall interactions succinct, we install ufw:

root@ubuntu1604:~# apt-get install -y ufw

b. Once done, we have to enable it. We will receive a warning indicating that the SSH connection might be interrupted. If it is disconnected, log back in and continue:

root@ubuntu1604:~# ufw enable
Command may disrupt existing ssh connections. Proceed with operation (y|n)? y
firewall is active and enabled on system startup

c. Now it is time to allow traffic through to port 1433:

root@ubuntu1604:~# ufw allow 1433
Rule added
Rule added (v6)

v. Install and setup MSSQL Server command-line tools

a. First, we need to add new GPG keys for the repo that contains the MSSQL command-line tools:

root@ubuntu1604:~# curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add –


b. Then we can add the repository:

root@ubuntu1604:~# curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | tee /etc/apt/sources.list.d/msprod.list

c. After that, we update apt and install the command line tools:

root@ubuntu1604:~# apt-get update -y
root@ubuntu1604:~# apt-get install -y mssql-tools unixodbc-dev

There should be one or two interactive prompts to accept licenses during installation like this:

d. Let us make it easy to execute sqlcmd anywhere on the server:

root@ubuntu1604:~# echo ‘export PATH=”$PATH:/opt/mssql-tools/bin”‘ >> ~/.bash_profile
root@ubuntu1604:~# echo ‘export PATH=”$PATH:/opt/mssql-tools/bin”‘ >> ~/.bashrc
root@ubuntu1604:~# source ~/.bashrc

e. Finally, we verify that we can connect to MSSQL Server locally:

root@ubuntu1604:~# sqlcmd -S localhost -U SA
Password:
1>


[Need urgent help with the SQL Server installation? We'd be happy to assist. ]


Common errors encountered while we Install SQL Server 2019 on Linux 

1. Error: ” invalid

While registering the Microsoft SQL server ubuntu repository:

root@server:~# add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2019.list)"

We may receive the error:

Error: '' invalid

In order to solve this error we add the following:

root@server:~# wget -qO- https://packages.microsoft.com/keys/microsoft.asc | apt-key add -OK
root@server:~# add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2019.list)"

In addition, we can try using curl and adding the repository manually:

root@server:~# curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2019.list | tee /etc/apt/sources.list.d/mssql-prod.list
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 103 100 103 0 0 306 0 --:--:-- --:--:-- --:--:-- 307
deb [arch=amd64,arm64,armhf] https://packages.microsoft.com/ubuntu/16.04/mssql-server-2019 xenial main


2. sqlcmd: command not found

If we get an error while connecting to the instance using SQL CMD, it might be due to a problem in the installation of the SQL Command Line Tool.

For Example, connecting to SQL Server Instance by:

sqlcmd -S localhost -U SA

and getting the error:

sqlcmd: command not found

To solve this we need to link SQL CMD utilities with Ubuntu accessible directory using:

sudo ln -sfn /opt/mssql-tools/bin/sqlcmd /usr/bin/sqlcmd

The ln command is to create links between files. The parameters of this command serve different purposes:

s – symbolic: It makes symbolic links instead of hard links.

f – force: It removes existing destination files.

n – no-dereference: It treats LINK_NAME as a normal file if it is a symbolic link to a directory.


Finally, connect to SQL Server Instance again using sql cmd and perform SQL Server commands in the Ubuntu terminal.


[Facing any SQL Server Connection error? We are here for you. ]


Conclusion

This article will guide you the process to install #MSSQL Server 2019 on #CentOS 7 and Ubuntu 16.04.

SQL Server 2019 #Developer is a full-featured free edition, licensed for use as a development and test database in a non-production environment.

SQL Server 2019 allows us to integrate data from structured and unstructured data sources. 

We can now process diverse big data and relational data sources using Transact-SQL from SQL Server using PolyBase. 

We can see below PolyBase support external databases.

SQL Server Express can be used in production (beware of the limitations, like the 10GB cap), but according to this link Express is available for Linux. SQL Server Express is available to use in Production.

Starting with SQL Server 2017, SQL Server runs on Linux. It's the same SQL Server database engine, with many similar features and services regardless of your operating system. It's the same SQL Server database engine, with many similar features and services regardless of your operating system.

To run a SQL script in #Linux terminal:

1. Open Terminal and type mysql -u to Open the MySQL command line.

2. Type the path of your #mysql bin directory and press Enter.

3. Paste your #SQL file inside the bin folder of mysql server.

4. Create a #database in MySQL.

5. Use that particular database where you want to import the SQL file.



Related Post


Keep In Touch

We hope to hear from you.

Accept File Type: jpg,jpeg,png,txt,pdf,doc,docx