Nisarg Upadhyay
Connect to database engine

Manage SQL Databases in CentOS: Install SQL Server on CentOS

August 24, 2022 by

In this article, we will understand how we can manage a SQL Database in CentOS 8.0. This article is the first article on the topic Manage SQL Server on CentOS.

  1. Step-by-step Installation of SQL Server and client tools in CentOS
  2. Create and connect to the SQL Database

I have created a virtual machine on a workstation and installed CentOS 8.0. First, let us install SQL Server on CentOS.

Install SQL Server on CentOS

To install a SQL Server on CentOS, perform the following steps.

Step 1: Update the CentOS system package

Before we start the installation, we should update the CentOS system packages and repositories. To do that, run the following command.

root@localhost ~]# sudo yum install -y mssql-server

Step 2: Add SQL Server repository

Once packages are updated, we must add Microsoft SQL Server 2019 repository in CentOS. To add the repository, run the following command.

root@localhost ~]# curl https://packages.microsoft.com/config/rhel/8/prod.repo > /etc/yum.repos.d/msprod.repo

Step 3: Install SQL Server 2019

Once the repository is installed, we can begin the installation of the SQL Server 2019. To do that, execute the below command.

[root@localhost ~]# sudo yum install -y mssql-server

Step 4: Configure the SQL Server instance

To configure the SQL Server instance, we can use the mysql-conf file. The mysql-conf is a configuration file that helps to install the SQL Server in a Linux environment. While configuring, you will be asked to enter appropriate values to configure the SQL Server; therefore specify the appropriate configuration parameters to complete the installation successfully. Let us begin the configuration process by running the following command.

[root@localhost ~]# sudo /opt/mssql/bin/mssql-conf setup

First, you must enter the edition of SQL Server 2019. The appropriate values are

  1. Evaluation (Free trial for 100 days)
  2. Developer (Free version, but you can use it in a non-production environment)
  3. Express (free)
  4. Web
  5. Standard (Paid license with resource cap. it is ideal for small and medium-sized organizations or applications)
  6. Enterprise (Paid license without any resource cap)
  7. I bought a license through a retail sales channel and have a product key to enter. (In this option, you must enter the product key to use the SQL Server)

In our setup, choose developer edition. And hit Enter key.

Select edition of SQL Server

Next, you are asked to accept or reject the license terms. Press Yes to accept the license terms.

Accept license terms

Next, provide the SQL Server system administrator password. Provide the desired password.

Specify password of SA user

Once the password is set, the SQL Server finishes the configuration, and the SQL Server services will be started.

SQL Server installed

You can view the status of the SQL Services by running the following command.

[root@localhost ~]# service mssql-server status

Output

SQL Server services are running

As you can see in the above image, the SQL Server services are running.

Now, we must set an environment variable to use the SQL Server executables and command-line tools without specifying the entire path. Add /opt/mssql-tools/bin directory to $PATH variable. To do that, run the following command.

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

Let us install the client tools to connect the database engine and access the SQL database. Run the following command.

[root@localhost ~]# sudo yum install mssql-tools unixODBC-devel

Following unixODBC packages will be installed.

  1. mssql-tools
  2. unixODBC-devel

Following dependencies will be added.

  1. msodbcsql17
  2. unixODBC

Screenshot:

install Client tools

If required, open the SQL Server default port in the firewall.

[root@localhost ~]# sudo firewall-cmd –add-port=1433/tcp –permanent
[root@localhost ~]# sudo firewall-cmd –reload

Once everything is set up, let’s connect to the SQL Server using SQL Server command-line tools. Launch the following command.

[root@localhost ~]# sqlcmd -U sa
Password:

Once connected, run @@version statement to view the SQL Server edition.

1> select @@version
2> Go

Command output

Version of SQL Server

As you can see, The SQL Server is installed successfully, and we are connected to the database engine.

Connect to SQL Server on CentOS using SQL Server management studio

Let’s connect to it using SQL Server management studio. As I have mentioned at the beginning of the article, I have configured the CentOS on my virtual machine and the SQL Server management studio on the machine. While connecting to the SQL Server, we will specify the IPAddress of the virtual machine. Launch SQL Server Management studio and in Connect to Database engine enter the connection parameters as shown below.

  1. Server type: Database Engine
  2. Server name: 192.168.1.2
  3. Authentication: SQL Server authentication
  4. Login: sa
  5. Password: Provide the appropriate password that you have set while installing SQL Server

Screenshot:

Connect to database engine

Again, run the @@version statement in the query editor window.

Select @@version

Query output

Populate the version using SSMS

Now, let us populate some basic information of SQL Databases and SQL Server instances.

Create a SQL Database

Let us create a SQL Database using SQL Server management studio. To do that, right-click on Databases 🡪 Select New Database…

New Database

In the New Database dialog box, enter the appropriate database name. In our case, the database name is Stackoverflow2010.

New Database screen

As you can see, the location of the SQL database is /var/opt/mssql/data/, which is the default location. We can change the settings of SQL Database by editing configuration files. I will explain it in my upcoming articles.

Summary

In this article, we understood how to install the SQL Server 2019 on CentOS. I have explained:

  1. The step-by-step installation process of SQL Server on CentOS.
  2. Connect to SQL Server using SQL Server management studio.
  3. Create a sample database and view the location of the database files.
Nisarg Upadhyay
408 Views