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.

 

↑ Return to Top


2. Requirement

  • RedHat Server 7.3 or higher
  • SQL Server vNext CTP1 or higher
  • SQL Server Management Studio (SSMS) 16.5 or higher

 

↑ Return to Top


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.

 

↑ Return to Top


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.

 

↑ Return to Top


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' ;

 

 

↑ Return to Top


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

 

 

↑ Return to Top


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.

 

↑ Return to Top


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' ;

 

 

↑ Return to Top


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

 

 

↑ Return to Top


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.

 

↑ Return to Top


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 ;

 

 

↑ Return to Top


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

 

 

↑ Return to Top


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.

 

↑ Return to Top


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' ;

 

 

↑ Return to Top


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

 

 

↑ Return to Top


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.

 

↑ Return to Top


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' ;

 

 

↑ Return to Top


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

 

 

↑ Return to Top


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.

 

↑ Return to Top


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 ;

 

 

↑ Return to Top


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"

 

 

↑ Return to Top


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' ;

 

 

↑ Return to Top


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.

 

 

↑ Return to Top


5. Reference

 

↑ Return to Top


6. See Also

 

↑ Return to Top