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 a 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 0with 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="" | 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' ;
# Initiate 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
# Initiate 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.
3.6.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 Tools package and query the version using sqlcmd Bash command.
# 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.
5. Reference
- Microsoft SQL Server on Linux Public Preview Technical Whitepaper
- Microsoft Docs - SQL Server on Linux Documentation
- Microsoft Docs - Install SQL Server on Red Hat Enterprise Linux by Jason Roth
- Microsoft Docs - Install SQL Server tools on Linux by Jason Roth
- Microsoft Docs - Use SSMS to Manage SQL Server on Linux by Sanjay Nagamangalam
- GitHub - PoSH-SSH by Carlos Perez
6. See Also