Since the previous blog post on SQL Server: Getting Started with MS SQL Server on Ubuntu server TechNet Wiki article, I have received quite alot of positive feedbacks on the walkthrough and decided to work on creating a SQL Server: Getting Started with MS SQL Server on Red Hat Enterprise Linux (RHEL) TechNet Wiki article walkthrough with Yellowdog Updater, Modified (YUM).
You can either view this article from the Microsoft TechNet Wiki which may have any improvement updates by the TechNet community on the link below:
Or carry on reading this page on the original article which I have noted in my engineering journal with some explanations on the process.
The Original TechNet Wiki Article on SQL Server: Getting Started with MS SQL Server on Red Hat Enterprise Linux
1. Introduction
With Microsoft ❤ Linux ever so closely every day, Microsoft has released SQL Server vNext for public preview where you can deploy a SQL Server on a Linux operating system and still be able to be managed by SQL Server Management Studio (SSMS) remotely. In this article, it will demonstrate how you can deploy the newly public preview release of SQL Server vNext on a RedHat Server to get you started.
2. Requirement
- RedHat Server 7.3 or higher
- SQL Server vNext CTP1 or higher
- SQL Server Management Studio (SSMS) 16.5 or higher
3. Getting Started with Microsoft SQL Server on Red Hat
To get started, you will have to deploy a RedHat Server with OpenSSH Server for SSH remote connectivity and the article will not be demonstrating or covering on how to deploy an RedHat Server nor OpenSSH Server into the server.
In general, you will be able to install SQL Server vNext using Bash shell commands on the terminal console and you may not require the OpenSSH Server but for this article it will provide examples on how to install the SQL Server vNext using Bash shell commands and PowerShell commands remotely through SSH as it is common practice for managing large enterprise environment.
So let us get started.
3.1. How to establishing SSH connectivity with Red Hat Server?
In order to allow remote management of the RedHat server through SSH, you will need to install and setup OpenSSH Server on the RedHat server, and with that you will be able to establish SSH connectivity to issue Bash commands remotely.
3.1.1. Using PowerShell
In this example, it demonstrates the use of PosH-SSH PowerShell module to establish a SSH session with the RedHat server.
# Import Posh-SSH PowerShell Module Import-Module ` -Global Posh-SSH ;
# Establish a SSH Session with the remote # Linux RedHat server New-SSHSession ` -ComputerName 192.168.150.237 ` -Credential (New-Object ` -TypeName System.Management.Automation.PSCredential ` -ArgumentList 'usr-ryen', ` (ConvertTo-SecureString ` -String 'myRHELPassword' ` -AsPlainText ` -Force) ` ) ` -Force ;
# Display the remote Linux RedHat server's # Operating System description Invoke-SSHCommand ` -SessionId 0 ` -Command 'cat /etc/redhat-release' ;
3.1.2. Using PuTTY
In this example, it demonstrates the use of PuTTY to establish a SSH session with the RedHat server to issue the Bash shell commands.
# Download PuTTY to C:\Temp Invoke-WebRequest ` -Uri 'https://the.earth.li/~sgtatham/putty/latest/x86/putty.exe' ` -OutFile 'C:\Temp\putty.exe' ;
# Launch PuTTY and connect to # the RedHat server using SSH Start-Process ` -FilePath 'C:\Temp\putty.exe' ` -ArgumentList '192.168.150.238' ;
# Display the remote Linux RedHat server's # Operating System description cat /etc/redhat-release
3.2. How to add the Microsoft SQL Server RedHat Repository?
In order to access the Microsoft SQL Server RedHat repository, you will need to obtain the Microsoft SQL Server RedHat Repository configuration file and have a valid RedHat Subscription for downloading any dependencies.
3.2.1. Using PowerShell
In this example, it demonstrates the use of Invoke-SSHCommand PowerShell cmdlet to issue Bash shell command remotely to obtain the Microsoft SQL Server RedHat Repository configuration file.
# Download the Microsoft SQL Server # RedHat Repository configuration file Invoke-SSHCommand ` -SessionId 0 ` -Command 'echo myRHELPassword | sudo -S curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server.repo' ;
3.2.2. Using PuTTY
In this example, it demonstrates the use of PuTTY to issue Bash shell command remotely to obtain Microsoft SQL Server RedHat Repository configuration file.
# Use Super-user do (sudo) to elevate your # current privilege to a superuser privilege sudo su
# Download the Microsoft SQL Server # RedHat Repository configuration file curl https://packages.microsoft.com/config/rhel/7/mssql-server.repo > /etc/yum.repos.d/mssql-server.repo
# Exit from your superuser privilege exit
3.3. How to install Microsoft SQL Server vNext on Red Hat?
Now that RedHat’s Yellowdog Updater, Modified (YUM) has been configured, in this article you will be shown the example of installing the Microsoft SQL Server package and execute the setup to configure, start and register SQL Server as a service.
3.3.1. Using PowerShell
In this example, it demonstrates the use of Invoke-SSHCommand PowerShell cmdlet to issue Bash shell command remotely to install the Microsoft SQL Server package and complete the setup.
# Use Yellowdog Updater, Modified (YUM) to # initialize an install of mssql-server # package Invoke-SSHCommand ` -SessionId 0 ` -Command 'echo myRHELPassword | sudo -S yum install -y mssql-server' ;
# Create a SA_PASSWORD system-wide environment variable Invoke-SSHCommand ` -SessionId 0 ` -Command "echo myRHELPassword | sudo -S bash -c 'echo SA_PASSWORD='''myMSSQLPa55w0rd''' >> /etc/environment'" ;
# Verify if the SA_PASSWORD system-wide environment # variable Invoke-SSHCommand ` -SessionId 0 ` -Command 'echo $SA_PASSWORD' ;
# Remove the existing SSH Session Remove-SSHSession ` -SessionId 0 ;
# Re-establish a SSH Session with the remote # Linux RedHat server so that $SA_PASSWORD # will be loaded in this new SSH session New-SSHSession ` -ComputerName 192.168.150.237 ` -Credential (New-Object ` -TypeName System.Management.Automation.PSCredential ` -ArgumentList 'usr-ryen', ` (ConvertTo-SecureString ` -String 'myRHELPassword' ` -AsPlainText ` -Force) ` ) ` -Force ;
# Re-verify if the SA_PASSWORD system-wide environment # variable Invoke-SSHCommand ` -SessionId 0 ` -Command 'echo $SA_PASSWORD' ;
# Use sqlservr-setup to initialize # the setup from the mssql-server # package Invoke-SSHCommand ` -SessionId 0 ` -Command 'echo myRHELPassword | sudo -S /opt/mssql/bin/sqlservr-setup --accept-eula --start-service --enable-service --set-sa-password' ;
# Use systemctl to verify the # mssql-server service status Invoke-SSHCommand ` -SessionId 0 ` -Command 'systemctl status mssql-server' | ` Select ` -ExpandProperty ` Output ;
# Use systemctl to start the # mssql-server service Invoke-SSHCommand ` -SessionId 0 ` -Command 'echo myRHELPassword | sudo -S systemctl start mssql-server' ;
# Use systemctl to re-verify the # mssql-server service status Invoke-SSHCommand ` -SessionId 0 ` -Command 'systemctl status mssql-server' | ` Select ` -ExpandProperty ` Output ;
3.3.2. Using PuTTY
In this example, it demonstrates the use of PuTTY to issue Bash shell command remotely to install the Microsoft SQL Server package and complete the setup.
# Use Yellowdog Updater, Modified (YUM) to # initialize an install of mssql-server # package sudo yum install -y mssql-server
# Create a SA_PASSWORD system-wide environment variable echo SA_PASSWORD="myMSSQLPa55w0rd" | sudo tee --append /etc/environment
# Verify if the SA_PASSWORD system-wide environment # variable echo $SA_PASSWORD
# Exit the current SSH Session in PuTTY # and reestablish a new SSH Session # using PuTTY exit
# Re-verify if the SA_PASSWORD system-wide environment # variable echo $SA_PASSWORD
# Use sqlservr-setup to initialize # the setup from the mssql-server # package sudo /opt/mssql/bin/sqlservr-setup --accept-eula --start-service --enable-service --set-sa-password
# Use systemctl to verify the # mssql-server service status systemctl status mssql-server
3.4. How to configure the Firewall on Red Hat Server?
By default, Red Hat Enterprise Linux will have a Firewall daemon running when the server is built and in order for any connection to the Microsoft SQL Server to be established remotely, you will need to add the firewall rule to allow TCP Port 1433 network traffic.
3.4.1. Using PowerShell
In this example, it demonstrates the use of Invoke-SSHCommand PowerShell cmdlet to issue Bash shell command remotely to configure the firewall to allow TCP Port 1433 on the RedHat server.
# Add a Firewall Rule to the Public zone # to allow TCP 1433 permanently Invoke-SSHCommand ` -SessionId 0 ` -Command 'echo myRHELPassword | sudo -S firewall-cmd --zone=public --add-port=1433/tcp --permanent' ;
# Inititate a reload of the Firewall # policy or rules Invoke-SSHCommand ` -SessionId 0 ` -Command 'echo myRHELPassword | sudo -S firewall-cmd --reload' ;
3.4.2. Using PuTTY
In this example, it demonstrates the use of PuTTY to issue Bash shell command remotely to configure the firewall to allow TCP Port 1433 on the RedHat server.
# Add a Firewall Rule to the Public zone # to allow TCP 1433 permanently sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent
# Inititate a reload of the Firewall # policy or rules sudo firewall-cmd --reload
3.5. How to add the Microsoft Red Hat repository?
With Microsoft SQL Server installed and configured on the RedHat server, you may want to know where you could obtain the Microsoft SQL Server tools for managing the database in Linux bash shell in RedHat server. In this section, you will need to add the Microsoft RedHat repository in order to obtain the Microsoft SQL Server tools.
3.5.1. Using PowerShell
In this example, it demonstrates the use of Invoke-SSHCommand PowerShell cmdlet to issue Bash shell command remotely to download the Microsoft RedHat repository configuration file.
# Download the Microsoft RedHat # Repository configuration file Invoke-SSHCommand ` -SessionId 0 ` -Command 'echo myRHELPassword | sudo -S curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo' ;
3.5.2. Using PuTTY
In this example, it demonstrates the use of PuTTY to issue Bash shell command remotely to download the Microsoft RedHat repository configuration file.
# Use Super-user do (sudo) to elevate your # current privilege to a superuser privilege sudo su
# Download the Microsoft RedHat # Repository configuration file curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/msprod.repo
# Exit from your superuser privilege exit
3.6. How to install Microsoft SQL Server Tools on Red Hat?
Now that RedHat’s Yellowdog Updater, Modified (YUM) has been configured, in this article you will be shown the example of installing the Microsoft SQL Server Tools package into the RedHat server.
# Use Yellowdog Updater, Modified (YUM) # to initialize an install of mssql-tools # package Invoke-SSHCommand ` -SessionId 0 ` -Command 'echo myRHELPassword | sudo -S ACCEPT_EULA=Y yum install mssql-tools -y -q' ;
# Use the sqlcmd Bash command from the # installed mssql-tools package to # establish a loopback connection to # the Microsoft SQL Server in RedHat # and query the version Invoke-SSHCommand ` -SessionId 0 ` -Command 'sqlcmd -S 127.0.0.1 -U sa -P myMSSQLPa55w0rd -Q "PRINT ''Hostname: '' + @@SERVERNAME ; PRINT @@Version" ' | Select ` -ExpandProperty Output ;
3.6.2. Using PuTTY
In this example, it demonstrates the use of PuTTY to issue Bash shell command remotely to install the Microsoft SQL Server Tools package and query the version using sqlcmd Bash command.
# Use Yellowdog Updater, Modified (YUM) # to initialize an install of mssql-tools # package sudo ACCEPT_EULA=Y yum install mssql-tools -y -q
# Use the sqlcmd Bash command from the # installed mssql-tools package to # establish a loopback connection to # the the Microsoft SQL Server in RedHat # and query the version sqlcmd -S 127.0.0.1 -U sa -P myMSSQLPa55w0rd -Q "PRINT 'Hostname: ' + @@SERVERNAME ; PRINT @@Version"
3.7 How to use Microsoft SQL Server Management Studio (SSMS) to manage remotely?
In this section, it will demonstrate how you can use Microsoft SQL Server Management Studio (SSMS) to manage your Microsoft SQL Server on RedHat remotely from a management server.
# Download SQL Server Management Studio (SSMS) 16.5 # to C:\Temp Invoke-WebRequest ` -Uri 'http://go.microsoft.com/fwlink/?linkid=832812' ` -OutFile 'C:\Temp\SSMS-Setup-ENU.exe' ;
# Install Microsoft SQL Server Management Studio (SSMS) 16.5 # on Management Server Start-Process ` -FilePath 'C:\Temp\SSMS-Setup-ENU.exe' ` -ArgumentList '/install /quiet /log C:\Temp\SSMS-Setup-ENU_Installation.log' ;
# Launch Microsoft SQL Server Management Studio (SSMS) 16.5 # on Management Server to connect to SQL Server on RedHat Start-Process ` -FilePath 'C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\Ssms.exe' ` -ArgumentList '-S 192.168.150.238 -U sa -P myMSSQLPa55w0rd' ;
4. Conclusion
There you have it. After establishing connectivity using Microsoft SQL Server Management Studio (SSMS) with the SA credential, you can see if it get connected to the server and you can issue your SQL Transact-SQL Syntax Query statement to determine the version that you deployed on Red Hat Enterprise Linux.