Introduction
SQL Server on Linux is a major step that Microsoft has taken with regards to open community support. This is very useful for SQL administrators who have to work on heterogeneous OS platforms where Oracle, MySQL, MariaDB, SAP ASE, PostgreSQL, DB2...etc. was the major databases supporting Linux platform was the only choice until now. Companies can now move to SQL Server on a Linux platform seamlessly using the SQL Server Migration Assistant from other supported databases and keep the operating system independent of the database choice.
This article will cover detailed installing instructions. It is expected that you understand basic Linux commands and operations and also the Linux operating system with RedHat / CentOS 7.2.
Requirements
- Basic installation knowledge of SQL Server and Linux
- Internet access should be open for TCP 443 to download the required packages for installing SQL Server.
- TCP port 22 for SSH connections should be open.
You need at least 3.25GB of memory to run SQL Server on Linux. The SQL Server Engine has only been tested up to 256GB of memory at this time.
SSH to the Linux console can be done using putty (https://the.earth.li/~sgtatham/putty/latest/x86/putty.exe). Using putty you can connect to the console as root user or regular user with sudo access
Commands used in this article
GDB - GDB is a package used to debug the programs written in various languages. (Refer to Step 1/2, in the this article for usage details) - More information can be found using this link: https://access.redhat.com/documentation/en-US/Red_Hat_Developer_Toolset/1/html/User_Guide/chap-GDB.html
YUM - YUM is a package in Linux used for installation in this article. (Refer to Step 1/2, in the this article for usage details) - More information can be found using this link: https://access.redhat.com/solutions/9934
CURL - CURL is a package to transfer the data (Refer to Step 3, in this article for usage details) - more information can be found using this link: https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Virtualization/3.2/html/Developer_Guide/Installing_cURL.html)
CAT - CAT in short is concatenate and is used to read a file and the output can be redirected to a new file or appended. (Refer to Step 3, in the this article for usage details) - More information can be found using this link: https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/3/html/Step_by_Step_Guide/s1-navigating-usingcat.html
Installing the dependent packages
The first step is to gdb with the following command:
sudo yum install gdb -y
You can see the results of this below:
Step 2: install bzip2.
sudo yum install bzip2 -y
Once the dependent packages are installed. Download the repository file (repo file) from Microsoft website. More information about the repo files can be found using this link: https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/6/html/Deployment_Guide/sec-Configuring_Yum_and_Yum_Repositories.html
Add the repository for SQL Server and Install
The next step is to get SQL Server with curl:
sudo curl https://packages.microsoft.com/config/rhel/7/mssql-server.repo > /etc/yum.repos.d/mssql-server.repo
This step will download the SQL Server binaries.
Once the reporistory is download and addded. Use YUM to install the SQL Server.
sudo yum install -y mssql-server
You will need to accept the License and defaults.
Just like on Windows, SQL Server is now installed (running the installation center). It is now time to accept SQL license, configure SQL Server service to auto run and give credntials for sa account.
sudo /opt/mssql/bin/sqlservr-setup
Accept the License and defaults and add an sa password according to security requirements. Once the installation is completed. Start the SQL Service and set the service option to automatic.
Note: All the input parameters requirements are marked in RED
Step 6: Check the status of the service :
sudo systemctl status mssql-server
Additional Commands:
sudo systemctl start mssql-server (Starting SQL Server Service)
sudo systemctl stop mssql-server (Stopping SQL Server Service)
Similar to SQL Server Service install. We need to download the repository to Install SQL Server client tools currently limited to sqlcmd and bcp utilities.
sudo curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/msprod.repo
Step 8:
sudo yum install mssql-tools -y
Add firewall rules exception for PROD and for just testing POC you can also stop firewalld service. You can refer to troubleshooting section:
Below is the sqlcmd manual and usage details
sqlcmd -H localhost -U sa Password <Enter the passsword>
You will notice that all the default databases has c:\ path before the Linux path however if you use TSQL to restore the database then it uses the absolute path without C:\ prefix. This is a known Issue:
The file browser is restricted to the “C:\” scope, which resolves to /var/opt/mssql/ on Linux. To use other paths, generate scripts of the UI operation and replace the C:\ paths with Linux paths. Then execute the script manually in SSMS.
Once you have the installation you can use SQL Server Management Studio and connect to the databases created / restored on Linux.
Troubleshooting:
If the installation steps are followed as per the document and there are still connection issues. This is due to the firewall configuration. It is a requirment to add the SQL Port, 1433 (default port), to the firewall configuration if firewalld is running. Please follow the steps below:
sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent
sudo firewall-cmd --reload
Once the port is added to firewall. Verify that the SQL Server service is listening on the port using the command below:
cat /etc/services | grep 1433
lsof -i | grep ms-sql-s
One other option is to turn off the firewalld service as below and then the SQL Server port will not be blocked. Also make sure to disable the auto start of the firewall service upon reboot.
NOTE - This option is not suggested in production environments.
service firewalld stop
systemctl disable firewalld
To re-enable the firewall service and auto start upon reboot. Follow the steps below:
service firewalld start
systemctl enable firewalld
Refer to the link below for more details: https://docs.microsoft.com/en-us/sql/linux/
For features that are supported / unsupported in SQL Server 2016 Linux, Refer to the link: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-release-notes
It is important to check the Known Issues section in the release notes before posting any bugs to Microsoft.
Hope this helps. If you need any further clarification or having trouble in installing SQL Server on Linux. Feel free to email me - cusvenus@hotmail.com
