Traditionally, Microsoft SQL Server, being part of Microsoft’s back office platform, has always run on Windows operating systems. However, Microsoft has made a commitment to enable customers to choose any platform. As part of this commitment, for SQL Server 2017, Microsoft has released SQL Server on Linux. This version of SQL Server will run and host databases on all common distributions of Linux, including Ubuntu, SUSE, and Red Hat Enterprise Linux (RHEL).
I am going to build this lab on VMware Work station 14 with 4 GB of RAM and 4 Core Virtual CPUs.
The same VM will be used later in a cluster. In this lab I will walk through the single instance installation of SQL Server. In this lab there is no part of Linux based installation as its standard installation method I have followed for the lab setup, but you can install your Linux box as per your requirements on a production network.
· CentOS/RHEL Server
· SQL Server 2017 CTP 2.1 and SQL Server 2017 RC1
· SQL Server Management Studio (SSMS) 17.4
The National Institute of Standards and Technology (NIST) is a nonregulatory agency of the United States Department of Commerce. In 2015, NIST reported that SQL Server had the lowest number of security vulnerabilities of all the major database systems.
SQL Server 2016 and 2017 also support new security features, such as Always Encrypted, which ensures that data remains encrypted all the way to the client application, and Row-Level Security, which provides a fine-grained control over access to sensitive data.
After installing Linux system, you can access it through putty a tiny software for remoting Linux based servers.
You can download the software https://www.chiark.greenend.org.uk/~sgtatham/putty/latest.html
For my lab environment I am using https://sils.unc.edu/it-services/personal-computer-faq/using-ssh Secure SSH, you can use any remote SSH as per availability.
Let’s walk through all the steps in detail on a Linux box.
Wget is a computer program that retrieves content from web servers. It is part of the GNU Project. Its name derives from World Wide Web and get. It supports downloading via HTTP, HTTPS, and FTP. We will use wget command to download MSSQL repo.
[root@ms-sql-17-01 ~]# yum -y install wget
This will install the required package of Wget and we will use the same package to get SQL repository. Get the mssql-server.repo library for SQL database engine.
Get the prod.repo for SQL tools installation by following command.
We will move the repositories to the configuration folder, /etc/yum.repos.d/
This is the time to install SQL Server on Linux box by
[root@ms-sql-17-01 ~]# yum y install mssql-server
SQL server installation is completed at this point we will set the SA password for SQL
With the following command we will set the password for MS SQL SA account for remote administration.
# echo SA_PASSWORD=”P@ssw0rd″ | sudo tee –append /etc/environment
Let’s configure the SQL instance by accepting the license agreement by following command.# /opt/mssql/bin/mssql-conf setup
Press 1 to go for the evaluation non-production use
At this point we have successfully installed the MS SQL on Linux box, let’s check & verify it from SQL services by:-
[root@ms-sql-17-01 ~]# systemctl status mssql-server
Let’s check the Linux should listen on 1433 port for client connectivity.
You can fully disable the firewall in your lab environment or add the required ports in iptables if its in production.
You can download the SSMS latest version from https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms and install on your local machine. In my case I have install this on my Windows 10 Pro machine.
You can give your Linux VM ip address with SA password we have configured during installation.
If required ports are opened then it will immediately connect with Server.
Here we are connected with Linux box MS SQL Server and we can continue further administration on the Server through SSMS.
The mssql-server package installs a fully functional SQL Server and, after you have configured the SQL Server and opened the necessary firewall ports, you can begin to use it from other machines. For example, you can use Microsoft SQL Server Management Studio 17 installed on a Windows computer to connect to the SQL Server on the RHEL server and create databases. You also use client applications to connect and query data.
However, you do not yet have any tools installed on the RHEL server that you can use to connect to SQL Server and execute queries. To obtain such tools, install the SQL Server command-line tools package.
yum install -y mssql-tools unixODBC-devel
For convenience, you might wish to add the SQL command-line tools folder to the PATH environment variable. Use this to execute sqlcmd without specifying its full path.
Modify the PATH variable by using the following commands:
Adding SQL command-line tools to the PATH environment variable
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
Hope this will help you to start working on command line as well.