Learn how to install SQL Server on Linux with this step-by-step guide. Follow our detailed instructions to set up SQL Server seamlessly on your Rocky Linux 8 system. #centlinux #linux #sqlserver
Table of Contents
What is SQL Server?
SQL Server is a relational database management system (RDBMS) developed by Microsoft. It’s a powerful and widely used platform for storing, managing, and retrieving data. SQL Server provides a comprehensive set of features for database management, data warehousing, business intelligence, and application development.
Key features of SQL Server include:
- Relational Database Management: SQL Server allows users to create, manage, and manipulate relational databases using Structured Query Language (SQL).
- Data Warehousing: It supports building and managing data warehouses for storing and analyzing large volumes of data efficiently.
- Business Intelligence: SQL Server includes tools for business intelligence (BI) such as reporting services, analysis services, and integration services, which enable users to extract insights from data and make informed decisions.
- High Availability and Disaster Recovery: SQL Server offers features like Always On Availability Groups and Failover Cluster Instances to ensure high availability and disaster recovery for critical databases.
- Security: It provides robust security features to protect data at rest and in transit, including encryption, authentication mechanisms, and role-based access control.
- Scalability and Performance: SQL Server is designed to scale from small single-server deployments to large-scale enterprise environments. It includes performance optimization features such as indexing, query optimization, and in-memory processing.
- Integration with Microsoft Ecosystem: SQL Server integrates seamlessly with other Microsoft products and services, such as Azure cloud services, Visual Studio development tools, and Windows Server operating systems.
- Cross-Platform Support: With the release of SQL Server 2017, Microsoft introduced support for running SQL Server on Linux, providing more flexibility for deployment options.
Overall, SQL Server is a versatile and robust database management system used by organizations of all sizes and industries for managing their data needs effectively.
PostgreSQL vs SQL Server
PostgreSQL and SQL Server are both powerful relational database management systems (RDBMS) commonly used for storing, managing, and querying data. While they share some similarities, they also have significant differences in terms of features, licensing, and ecosystem. Here’s a comparison between PostgreSQL and SQL Server:
PostgreSQL:
- Open Source: PostgreSQL is an open-source RDBMS released under the PostgreSQL License, which allows free use, modification, and distribution.
- Cross-Platform: PostgreSQL runs on multiple operating systems, including Linux, Windows, macOS, and various Unix-like systems.
- Community-Driven Development: PostgreSQL is developed and maintained by a global community of developers and contributors.
- Rich Feature Set: PostgreSQL offers a rich set of features, including advanced data types, full-text search, JSON support, and extensibility through custom functions and procedural languages like PL/pgSQL and PL/Python.
- Scalability: PostgreSQL is known for its scalability, supporting both horizontal scalability through sharding and vertical scalability through multi-core processing and optimized query execution.
- High Availability: PostgreSQL provides built-in features like streaming replication, synchronous replication, and automatic failover to ensure high availability and data redundancy.
- Extensions and Ecosystem: PostgreSQL has a vibrant ecosystem with a wide range of extensions, tools, and libraries available, enabling users to extend its functionality and integrate with other systems.
SQL Server:
- Commercial License: SQL Server is a commercial RDBMS developed and marketed by Microsoft. It offers various editions with different feature sets, including Enterprise, Standard, and Express editions.
- Integration with Microsoft Ecosystem: SQL Server integrates seamlessly with other Microsoft products and services, such as Azure cloud services, Visual Studio development tools, and Windows Server operating systems.
- Windows-Centric: While SQL Server is available for Linux since version 2017, it historically had a stronger focus on the Windows platform.
- Business Intelligence Tools: SQL Server includes a comprehensive set of business intelligence (BI) tools, such as Reporting Services (SSRS), Analysis Services (SSAS), and Integration Services (SSIS), for data analysis, reporting, and ETL (extract, transform, load) processes.
- Built-In Security Features: SQL Server offers robust security features, including encryption, auditing, row-level security, and dynamic data masking, to protect data at rest and in transit.
- Enterprise-Level Support: SQL Server provides enterprise-level support and services through Microsoft’s extensive network of partners and service providers.
- Proprietary Extensions: SQL Server offers proprietary extensions and features that may not be available in other RDBMS platforms, such as SQL Server-specific SQL syntax and stored procedure languages like T-SQL (Transact-SQL).
In summary, PostgreSQL is a feature-rich open-source RDBMS known for its extensibility, scalability, and community-driven development model, while SQL Server is a commercial RDBMS with strong integration with the Microsoft ecosystem, comprehensive business intelligence tools, and enterprise-level support. The choice between PostgreSQL and SQL Server often depends on factors such as licensing requirements, platform preferences, feature needs, and budget constraints.
Recommended Book: Exam Ref 70-761 Querying Data with Transact-SQL 1st Edition (PAID LINK)
Recommended Online Training: SQL Server Administration on Linux Basics
Environment Specification
We are using a minimal Rocky Linux 8 virtual machine with following specifications.
- CPU – 3.4 Ghz (2 cores)
- Memory – 2 GB
- Storage – 20 GB
- Operating System – Rocky Linux 8.6 (Green Obsidian)
- Hostname – mssql-server-01.centlinux.com
- IP Address – 192.168.116.128 /24
Update your Rocky Linux Server
By using a ssh client, connect with mssql-server-01.centlinux.com as root user.
Build cache for already installed yum repositories as follows.
# dnf makecache Rocky Linux 8 - AppStream 451 kB/s | 8.3 MB 00:18 Rocky Linux 8 - BaseOS 628 kB/s | 2.6 MB 00:04 Rocky Linux 8 - Extras 234 B/s | 11 kB 00:47 Metadata cache created.
Execute following dnf command to update your Rocky Linux server.
# dnf update -y
If the above command updates your Linux Kernel, then you should reboot your Linux operating system with newly installed Kernel.
# reboot
After reboot, verify the versions of Linux Operating System and Kernel.
# uname -r 4.18.0-372.9.1.el8.x86_64 # cat /etc/rocky-release Rocky Linux release 8.6 (Green Obsidian)
Install Python on Rocky Linux 8
MS SQL Server requires python2. Since, we are using a minimal installed Rocky Linux server. Therefore, you have to install Python2 before installing MS SQL Server.
Check if python interpreter is installed on your server.
# alternatives --config python There is 1 program that provides 'python'. Selection Command ----------------------------------------------- *+ 1 /usr/libexec/no-python Enter to keep the current selection[+], or type selection number:
Install python2 and openssl10 software packages by executing following command.
# dnf install -y python2 compat-openssl10
Configure the default Python interpreter by using following command.
# alternatives --config python There are 2 programs which provide 'python'. Selection Command ----------------------------------------------- *+ 1 /usr/libexec/no-python 2 /usr/bin/python2 Enter to keep the current selection[+], or type selection number: 2
Verify the Python version by executing following command at Linux bash prompt.
# python -V Python 2.7.18
Install SQL Server Yum Repository
Microsoft distributes the SQL Server software through their Official yum repository.
You can install SQL Server official yum repository as follows.
# curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/8/mssql-server-2019.repo
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 231 100 231 0 0 265 0 --:--:-- --:--:-- --:--:-- 265Build the cache for newly installed yum repositories.
# dnf makecache Rocky Linux 8 - AppStream 3.0 kB/s | 4.8 kB 00:01 Rocky Linux 8 - BaseOS 2.1 kB/s | 4.3 kB 00:02 Rocky Linux 8 - Extras 2.4 kB/s | 3.5 kB 00:01 packages-microsoft-com-mssql-server-2019 805 kB/s | 4.6 MB 00:05 Metadata cache created.
Install SQL Server on Linux
You have already installed Microsoft Official yum repository. Therefore, you can now easily install SQL Server on Linux by executing following dnf command.
# dnf install -y mssql-server
...
Running scriptlet: mssql-server-15.0.4223.1-2.x86_64 30/30
Installing : mssql-server-15.0.4223.1-2.x86_64 30/30
Running scriptlet: mssql-server-15.0.4223.1-2.x86_64 30/30
+--------------------------------------------------------------+
Please run 'sudo /opt/mssql/bin/mssql-conf setup'
to complete the setup of Microsoft SQL Server
+--------------------------------------------------------------+
Running scriptlet: guile-5:2.0.14-7.el8.x86_64 30/30
Running scriptlet: mssql-server-15.0.4223.1-2.x86_64 30/30
...
Invoke Microsoft SQL Server setup to configure your preferred edition.
# /opt/mssql/bin/mssql-conf setup 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): 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=2104294&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... 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.
The MS SQL Server setup is completed. The setup process also enable and start the mssql-server.service.
Execute the following command to check the status of mssql-server.service.
# 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 Sun 2022-05-29 08:41:05 PKT; 1min 31s ago
Docs: https://docs.microsoft.com/en-us/sql/linux
Main PID: 922 (sqlservr)
Tasks: 123
Memory: 1.4G
CGroup: /system.slice/mssql-server.service
├─ 922 /opt/mssql/bin/sqlservr
└─1154 /opt/mssql/bin/sqlservr
May 29 08:42:02 mssql-server-01.centlinux.com sqlservr[1154]: [156B blob data]
May 29 08:42:02 mssql-server-01.centlinux.com sqlservr[1154]: [160B blob data]
May 29 08:42:03 mssql-server-01.centlinux.com sqlservr[1154]: [61B blob data]
Configure Linux Firewall
To enable access to your MS SQL Server database server from across the network. You have to allow the default service port in Linux firewall.
# firewall-cmd --zone=public --add-port=1433/tcp --permanent success # firewall-cmd --reload success
Install Transact SQL Command-line Tools
To connect to a MS SQL Server and execute SQL commands, You need Transact SQL (T-SQL) command-line tools.
MS SQL Server command-line tools are provided with another official yum repository from Microsoft Corporation.
Use curl command to download and add yum repository in your Linux operating system.
# curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/8/prod.repo
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 192 100 192 0 0 203 0 --:--:-- --:--:-- --:--:-- 202You have to rebuild the yum cache because you have added a new repo on your Linux operating system.
# dnf makecache Rocky Linux 8 - AppStream 1.4 kB/s | 4.8 kB 00:03 Rocky Linux 8 - BaseOS 2.0 kB/s | 4.3 kB 00:02 Rocky Linux 8 - Extras 1.4 kB/s | 3.5 kB 00:02 packages-microsoft-com-prod 525 kB/s | 3.6 MB 00:07 packages-microsoft-com-mssql-server-2019 4.0 kB/s | 3.0 kB 00:00 Metadata cache created.
Now execute the following command to install MS SQL Command-line tools.
# dnf install -y mssql-tools unixODBC-devel
Add MS SQL Server command-line tools directory to PATH environment variable to make it executable from anywhere.
# echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile # echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc # source ~/.bashrc
Execute sqlcmd command to connect with your Microsoft SQL Server.
# sqlcmd -S localhost -U SA Password: 1>
Now you at the Transact SQL shell. you can execute SQL commands thereon.
Execute the following T-SQL command to get list of databases on your MS SQL Server.
1> SELECT Name from sys.Databases; 2> GO Name ------------------------------------------- master tempdb model msdb (4 rows affected)
Create a test database in SQL Server.
1> CREATE DATABASE Test 2> GO
Connect to test database and create a table thereon.
1> USE Test 2> GO Changed database context to 'Test'. 1> CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT) 2> GO
Insert some data in your table.
1> INSERT INTO Inventory VALUES (1, 'KEYBOARD', 800); 2> INSERT INTO Inventory VALUES (2, 'MOUSE', 800); 3> GO (1 rows affected) (1 rows affected)
Query your table to display its content.
1> SELECT * FROM Inventory;
2> GO
id name quantity
----------- -------------------------------------------------- -----------
1 KEYBOARD 800
2 MOUSE 800
(2 rows affected)Exit from T-SQL shell.
1> QUIT
Final Thoughts
Installing SQL Server on Linux 8 opens up a world of possibilities for robust database management and data-driven applications. Whether you’re setting up a development environment or deploying a production system, mastering this installation process is crucial.
If you prefer expert guidance or want to ensure a seamless installation, consider leveraging my services on Fiverr. With years of experience in database management and Linux administration, I offer comprehensive assistance to ensure your SQL Server installation on Linux 8 is smooth and efficient.