Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Installation of SQL Server 2017 on Oracle VM VirtualBox for Red Hat - Part 2


By:   |   Last Updated: 2019-02-01   |   Comments   |   Related Tips: More > SQL Server on Linux

Problem

SQL Server 2017 works on both Windows and Linux operating systems. Database administrators should have hands-on experience for both Windows and Linux for SQL Server.  In this tip, we will install the SQL Server database engine and SQL Server Agent on an Oracle VM VirtualBox running Red Hat Linux. You can read the first part of this tip series we created an Oracle VM VirtualBox and installed RHEL.

Solution

Before proceeding with this tip you should check out this tip to install Oracle VM VirtualBox or build your own Linux server using Red Hat Linux.

In the previous tip we created a virtual server named 'AlphaV'. To start the Oracle VM VirtualBox, click on Start > Normal Start.

Oracle VM VirtualBox Manager

We can see the below screen after logging into the machine with the root user.

The VM VirtualBox

I am going to use PuTTY to install SQL Server on this VM. PuTTY is a free and open-source terminal emulator, serial console and network file transfer application. It supports several network protocols, including SCP, SSH, Telnet and raw socket connections.

We can connect to the server using putty with the hostname or IP address. Launch Terminal from the Applications.

launch terminal from the applications

We can get the IP address using the ifconfig command as shown below.  We can see below it is 192.168.1.101.

Get IP address using ifconfig command

Enter the Hostname or IP address and connect to the server using PuTTY.

Connect to the server using putty

Once connected, we get the below screen. In the top bar, it shows we are connected as [email protected].

Connected Server

Note: You may get the below message in Linux when installing SQL Server.

This system is registered with an entitlement server but is not receiving updates. You can use subscription-manager to assign subscriptions.

To resolve the message, we need to run the below commands and register the server to get updates from Red Hat. Provide the username and password by which we have registered with Red Hat.

# subscription-manager register
Subscription Manager to assign subscriptions.

After registering, run the below command to subscribe to the entitlement.

# subscription-manager attach --auto
Subscribe to the entitlement

Before installing SQL Server, it is always best to check for any SQL Server service status.  Although we installed a fresh OS and it is not running any applications, however, it is recommended to check the services before the installation of SQL Server.

# systemctl status mssql-server

As expected, we can see that mssql-server.service could not be found.

Check status of mssql-server.service

In the next step, we need to check for the SQL Server repository if it is already there. Use the below script to check.

# sudo ls -lrt /etc/yum.repos.d

We did not find anything related to the SQL Server related repository. It should be available after downloading from the Microsoft site. 

Check SQL Server related repository

When you install SQL Server on Linux, we need to configure a Microsoft repository to acquire the database engine package, mssql-server, and related SQL Server packages.

Repository Name Description
CU mssql-server-2017 SQL Server 2017 Cumulative Update (CU) repository.

We will execute curl commands to download the installation packages from the Microsoft website to this Linux server. Curl is a tool to transfer data from or to a server, using one of the supported protocols (HTTP, HTTPS, FTP, FTPS, SCP, SFTP, TELNET, LDAP). The command works without user interaction.

# sudo curl -o/etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo
Download the installation packages

Now we should be able to see the SQL Server related repository.

# sudo ls -lrt /etc/yum.repos.d
Check SQL Server related repository

The mssql-server.repo file contains packages for the base SQL Server release and bug fixes or improvements since the release of SQL Server 2017.

To view the contents of this file, use the below command.

# cat /etc/yum.repos.d/mssql-server.repo

We can see that the repository contains the below contents.

  • Name: Name of the package
  • Baseurl: URL for the installable setup.
  • Gpgheck: gpgcheck stands for signature verification from its central database if it is 1 then it asks for signature verification else it doesn’t.
  • Gpgkey: It contains a URL to download the appropriate keys
view content of mssql-server.repo


Before installing SQL Server, it is better to check any preinstalled packages related to Microsoft SQL Server.

# yum list ms*
Check any preinstalled package related to Microsoft SQL Server.

Now, the next step is to install SQL Server. The below command will install the latest SQL Server version available. It will also install SQL Server Agent.

Note: Before SQL Server 2017 CU4, we needed to install SQL Server Agent separately.

This command uses the yum package to install SQL Server 2017. YUM (Yellowdog Updater Modified) is an open source command-line tool as well as a graphical based package management tool for RPM (Red Hat Package Manager) based Linux systems. It allows users and system administrators to easily install, update, remove or search software packages on systems.

# sudo yum install -y mssql-server
install SQL Server 2017

It downloads the required packages and imports GPG keys.

We can see below that the SQL Server installation is complete, however, it says, "Please run /opt/mssql/bin/mssql-conf setup to complete the setup of Microsoft SQL Server".

We also need to restart SQL Server to apply this setting using the below command.

# systemctl restart mssql-server-service
Install SQL Server

We can get the information of installed features using the below command.

# rpm -ql mssql-server
get the information of installed features
Get the information of installed features
Get the information of installed features
Get the information of installed features

Now we need configure the setup. It will install the necessary edition, default system database locations. A couple of inputs required for this configuration.

# sudo /opt/mssql/bin/mssql-conf setup

First, we need to 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)
  7.  Enterprise Core (Paid)

8- I bought a license through a retails sales channel and have a product key to enter.

Choose an edition of SQL Server

In this demo, I want to install SQL Server 2017 Developer edition so provide the value 2 and press Enter.

Now it asks to accept the License term and we need to provide Y or Yes as an input.

If we want to review the privacy statement, go to the Privacy Statement link.

Accept Privacy Statement

In the next step, enter the SQL Server administrator (SA) password.

Once we give the SA password, it configures SQL Server. In the end, it gives the below message.

"Set up has completed successfully, SQL Server is now starting."

Enter SQL Server administrator (SA) password

If we want to check the version number of the SQL Server package, use the below command.

# yum list mssql-server
Check the version number of the SQL Server package

Enable Firewall to connect SQL Server remotely

We need to enable TCP port 1433 in the firewall to connect remotely.

First, check the status of the firewall daemon.

# systemctl status firewalld
check the status of the firewall daemon

TCP port 1433 is the default SQL Server connecting port, and we can add a firewall rule to allow incoming connections. To make the effect permanent, we need to add the - - permanent parameter, so that rule work even after a reboot.

#sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent
Add firewall Rule

Now reload the firewall configuration using the below command

# sudo firewall-cmd  --reload
Add firewall Rule Reload

To confirm the status of the port in the firewall, run the below command, we can see the list of enabled ports.

# firewall-cmd  --list-ports
Status of  firewall Rule

We can see that default port 1433 is now enabled to allow connections in the firewall.

Enable SQL Server Agent

By default, SQL Server Agent is disabled. Let's first check the existence of SQL Agent components.

# /opt/mssql/bin/mssql-conf list
Check SQL Serveragent

SQL Server Agent is present, run the below command to enable it. We need to restart the SQL Server service after enabling it.

# /opt/mssql/bin/mssql-conf  set sqlagent.enabled true
Enable SQLServer Agent

Restart SQL Server service using the below command.

# systemctl restart mssql-server.service
restart the SQL Server service

Once we have restarted the service, we can see that the SQL Agent is showing True in the mssql.conf file.

# cd /var/opt/mssql
# cat mssql.conf
Check Configuration

Verify that SQL Server is up and running.

# systemctl status mssql-server.service
Check SQL Server status

Connect to SQL Server using sqlcmd utility

By default, the SQLCMD utility is not available in the Microsoft SQL Server Linux version and requires the mssql-tools package.

Run the below command to download the Microsoft Red Hat repository configuration file.

# curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/msprod.repo
Download the Microsoft Red Hat repository Configuration

Once the package is downloaded, run the below to install mssql-tool. You need to accept a few conditions during the installation. Enter 'Y' to accept it.

# sudo yum install mssql-tools unixODBC-devel
install the mssql-tool
Install the mssql-tool
Install the mssql-tool
Install the mssql-tool

Mssql-tools installation is complete now and we can connect to SQL Server remotely by PuTTY and test the connection.

You may still get "sqlcmd: command not found" error when you try to run sqlcmd if you have no proper symlinks created.

Run the below to create symlinks.

# ln -sfn /opt/mssql-tools/bin/sqlcmd /usr/bin/sqlcmd
Create symlinks

Try to connect to the server via sqlcmd using:

# sqlcmd -S 192.168.1.101 -U SA

Or

# sqlcmd -S localhost -U SA
Connect to the server using sqlcmd

The default location of the data files, log file and error logs

In SQL Server 2017 on Linux, the default location of the data files and log files are /var/opt/mssql/data.

We can see below the content under the /var/opt/mssql/data.

Use the below commands.

# cd /var/opt/mssql
# ls -lahrt
Default location of the data files and log files

Similarly, the location of the error log file and default trace files are: /var/opt/mssql/log.

# cd /var/opt/mssql/log
# ls -lahrt
default location of the error logs

To read the error log file, run the below command.

# cat errorlog
Read the error log file
Next Steps


Last Updated: 2019-02-01


get scripts

next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools