Microsoft in the year 2016 announced their plans to bring MS SQL Server to Linux. Microsoft SQL is an open-source relational database system available for production workloads in RHEL and other Linux distributions. Ms SQL server 2019 builds on previous releases of grow SQL Server as a platform. This gives one choices of data types, development languages, on-premises or cloud environments, and operating systems.
Miscrosoft SQL server 2019 has the following features:
- It has an intelligent database built on previous innovations on previous versions.
- It has an Intelligent Query Processing.
- It has an intelligent performance that ensures is runs faster. These improvement helps provide predictable performance across all workloads.
- Improvement in monitoring that enables unlock performance insights over a database workload
- It provides a world class developer experience with improvements in graph and spatial data types and UTF-8 support
- Has unicode support feature.
- Highly available
- Has resumable operations
- Can be deployed in containers in a safer manner and with more functionality
- Offers MS SQL server Machine Learning Services
This article illustrates how to install and configure Microsoft SQL Server 2019 on Rocky Linux 8.
Before installation, ensure the following requirements are met:
- Minimum memory of 3 GB
- Minimum of 10 GB of available hard-disk space
- CPU processor with a minimum speed of 1.4 GHz. But the recommended is >= 2 GHz
Having met the requirements, we can proceed to install and comfigure Microsoft SQL Server 2019 on Rocky Linux 8 with the below steps.
Step 1: Add MS SQL Server 2019 repository
Ms SQL server is available in Red Hat repositories which are added manually as below:
sudo curl https://packages.microsoft.com/config/rhel/8/mssql-server-2019.repo -o /etc/yum.repos.d/mssql-server-2019.repo
sudo curl https://packages.microsoft.com/config/rhel/8/prod.repo -o /etc/yum.repos.d/msprod.repoLastly, verify the list of available repositories on your system via the following dnf command. You should get the MS SQL Server 2019 repository added to your Rocky Linux server.
sudo dnf repolistSample Output:
$ sudo dnf repolist
repo id repo name
appstream Rocky Linux 9 - AppStream
baseos Rocky Linux 9 - BaseOS
extras Rocky Linux 9 - Extras
packages-microsoft-com-mssql-server-2019 packages-microsoft-com-mssql-server-2019
packages-microsoft-com-prod Step 2: Install Microsoft SQL server
With the required repositories added, proceed and install Ms SQL server on Rocky Linux 8.
sudo dnf -y install mssql-serverSample Output:
...
Transaction Summary
======================================================================================================================================================================================================
Install 10 Packages
Upgrade 1 Package
Total download size: 262 MStep 3: Install MS SQL Server tools
Then we proceed and install MS SQL tools as below.
sudo yum -y install mssql-tools unixODBC-devel Agree to the License prompt:
The license terms for this product can be downloaded from
https://aka.ms/odbc17eula and found in
/usr/share/doc/msodbcsql17/LICENSE.txt . By entering 'YES',
you indicate that you accept the license terms.
Do you accept the license terms? (Enter YES or NO)
YES
The license terms for this product can be downloaded from
http://go.microsoft.com/fwlink/?LinkId=746949 and found in
/usr/share/doc/mssql-tools/LICENSE.txt . By entering 'YES',
you indicate that you accept the license terms.
Do you accept the license terms? (Enter YES or NO)
YESConfirm your installtion with:
rpm -qi mssql-server
rpm -qi mssql-tools
....Step 4: Start MS SQL Database Engine
With a successful installation, we now run our Database Engine. Follow the below steps:
Set a password on prompt:
sudo /opt/mssql/bin/mssql-conf setupThen you are required to select an edition. I selected option 2_Developer (free, no production use rights):
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=2109348&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): 2Accept The License terms:
The privacy statement can be viewed at:
https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409
Do you accept the license terms? [Yes/No]: YesThen set your server system administrator password:
Enter the SQL Server system administrator password:
Confirm the SQL Server system administrator password:
Configuring SQL Server...
ForceFlush is enabled for this instance.
ForceFlush feature is enabled for log durability.
Created symlink /etc/systemd/system/multi-user.target.wants/mssql-server.service → /usr/lib/systemd/system/mssql-server.service.
Setup has completed successfully. SQL Server is now starting.Enable MS-SQL server and set to run on boot:
$ systemctl status mssql-server.service
mssql-server.service - Microsoft SQL Server Database Engine
Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendo>
Active: active (running) since Tue 2023-05-17 10:20:12 UTC; 2min 55s ago
Docs: https://docs.microsoft.com/en-us/sql/linux
Main PID: 2746 (sqlservr)
Tasks: 117
Memory: 659.2M
CGroup: /system.slice/mssql-server.service
├─2746 /opt/mssql/bin/sqlservr
└─2772 /opt/mssql/bin/sqlservr
$ systemctl is-enabled mssql-server.service
enabledThen export your path /opt/mssql/bin/ as below:
echo 'export PATH=$PATH:/opt/mssql/bin:/opt/mssql-tools/bin' | sudo tee /etc/profile.d/mssql.shThen source the file to use MS SQL binaries:
source /etc/profile.d/mssql.shAllow SQL server ports for remote hosts:
$ sudo firewall-cmd --add-port=1433/tcp --permanent
success
$ sudo firewall-cmd --reload
successStep 5: Test MS SQL server 2019
With all the configurations set. We will now test our SQL server. Connect to the server as below:
$ sqlcmd -S localhost -U SAAuthenticate with the password you set earlier and proceed as below:
1> select name from sysusers;
2> go
name
--------------------------------------------------------------------------------------------------------------------------------
##MS_AgentSigningCertificate##
##MS_PolicyEventProcessingLogin##
db_accessadmin
db_backupoperator
db_datareader
db_datawriter
db_ddladmin
db_denydatareader
db_denydatawriter
db_owner
db_securityadmin
dbo
guest
INFORMATION_SCHEMA
public
sys
(16 rows affected)Create a database in MS SQL 2019
This is an example of how to a database in MS SQL 2019:
> CREATE DATABASE mytestDB
2> SELECT Name from sys.Databases
3> GO
Name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
mytestDB
(5 rows affected)
1> USE mytestDB
2> CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT)
3> VALUES (1, 'banana', 150); INSERT INTO Inventory VALUES (2, 'orange', 154);
4> GO
Changed database context to 'mytestDB'.
(1 rows affected)Show databases in MS SQL as below:
1> select name,database_id from sys.databases;
2> GO
name database_id
------------- -----------
master 1
tempdb 2
model 3
msdb 4
testDB 5
(5 rows affected) To drop a database in MS SQL use the command:
1> DROP DATABASE mytestDB;
2> GOUse Exit to terminate MS SQL database server
1> exitConclusion
In this article, I have demonstrated how to install, configure, create and drop databases in Microsoft SQL server 2019 on Rocky / AlmaLinux 8. I hope this helped you.
Check out our other articles on Database servers:
