Nov 20, 2016

How to Install SQL Server on Red Hat Linux 7.2 and Ubuntu 16.04


Microsoft SQL Server now runs on Linux. The latest release, SQL Server vNext CTP1, runs on Linux and is in many ways simply SQL Server. It’s the same SQL Server database engine, with many similar features and services regardless of your operating system.






SQL Server vNext CTP1 is supported on Red Hat Enterprise Linux and Ubuntu. It is also available as a Docker image which can run on Docker Engine on Linux or Docker for Windows/Mac.

In this article, we'll walk you through the steps to install Microsoft SQL Server on Red Hat Linux and Ubuntu 16.04. This guide also covers how to run the SQL Server Docker image on Linux, Mac, or Windows. In the end of article, we will be connecting to SQL Server with sqlcmd, creating and populating a database.

Supported platforms

SQL Server vNext CTP1 is supported on the following platforms:
PlatformSupported versionGet
Red Hat Enterprise Linux7.2Get RHEL 7.2
Ubuntu16.04Get Ubuntu 16.04
Docker Engine1.8+Get Docker

In this section of the article, we will walk you through the steps to install SQL Server vNext CTP1 on Red Hat Enterprise Linux (RHEL) 7.2.

How to Install SQL Server on Red Hat Enterprise Linux

To install the mssql-server package on RHEL, follow these steps:

Enter superuser mode.

sudo su

Download the Microsoft SQL Server Red Hat repository configuration file:

curl https://packages.microsoft.com/config/rhel/7/mssql-server.repo > /etc/yum.repos.d/mssql-server.repo

Exit superuser mode.

exit

Run the following commands to install SQL Server:

sudo yum install -y mssql-server

After the package installation finishes, run the configuration script and follow the prompts.

sudo /opt/mssql/bin/sqlservr-setup

Once the configuration is done, verify that the service is running:

systemctl status mssql-server

You may need to open a port on the firewall on RHEL. If you are using FirewallD for your firewall, you can use the following commands.

sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent
sudo firewall-cmd --reload


How to Install SQL Server on Ubuntu

In this section of the article, we will show you how to install SQL Server vNext CTP1 on Ubuntu 16.04.

To install the mssql-server Package on Ubuntu, follow these steps:

Import the public repository GPG keys:

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

Register the Microsoft SQL Server Ubuntu repository:

curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list | sudo tee /etc/apt/sources.list.d/mssql-server.list

Run the following commands to install SQL Server:

sudo apt-get update
sudo apt-get install -y mssql-server

After the package installation finishes, run the configuration script and follow the prompts.

sudo /opt/mssql/bin/sqlservr-setup

Once the configuration is done, verify that the service is running:

systemctl status mssql-server

In this section of the article, we will explain how to pull and run the mssql-server Docker image. This image can be used with the Docker Engine 1.8 on Linux or on Docker for Mac/Windows.


How to Run the SQL Server Docker image on Linux, Mac, or Windows

Requirements for Docker
  • Docker Engine 1.8+ on any supported Linux distribution or Docker for Mac/Windows.
  • Minimum of 4 GB of disk space
  • Minimum of 4 GB of RAM
Important
The default on Docker for Mac and Docker for Windows is 2 GB for the Moby VM, so you will need to change it to 4 GB. The following sections explain how.

Docker for Mac

  1. Click on the Docker logo on the top status bar.
  2. Select Preferences.
  3. Move the memory indicator to 4GB or more.
  4. Click the restart button at the button of the screen.


For Windows users:

  1. Right-click on the Docker icon from the task bar.
  2. Click Settings under that menu.
  3. Click on the Advanced Tab.
  4. Move the memory indicator to 4GB or more.
  5. Click the Apply button.


Pull and run the Docker image

Pull the Docker image from Docker Hub.

sudo docker pull microsoft/mssql-server-linux

To run the Docker image, you can use the following commands:

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=' -p 1433:1433 -d microsoft/mssql-server-linux

To persist the data generated from your Docker container, you must map volume to the host machine. To do that, use the run command with the -v :/var/opt/mssql flag. This will allow the data to be restored between container executions.

sudo docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=' -p 1433:1433 -v :/var/opt/mssql -d microsoft/mssql-server-linux

Note
The ACCEPT_EULA and SA_PASSWORD environment variables are required to run the image.

Important
Volume mapping for Docker-machine on Mac with the SQL Server on Linux image is not supported at this time.


How to Provision a Linux SQL Server VM in Azure

Azure provides Linux virtual machine images that have SQL Server vNext CTP1 installed. This section of the article provides a short walk through on how to use the Azure portal to create a Linux SQL Server virtual machine.

Create a Linux VM with SQL Server installed

Open the Azure portal.
  1. Click New on the left.
  2. In the New blade, click Compute.
  3. Click See All next to the Featured Apps heading.


In the search box, type SQL Server vNext, and press Enter to start the search.


Tip
This filter shows the available Linux virtual machine image(s) for SQL Server vNext. Over time, SQL Server vNext images for other supported Linux distributions will be listed. You can also click this link to go directly to the search results for SQL Server vNext.

Select a SQL Server vNext image from the search results.
Click Create.
On the Basics blade, fill in the details for your Linux VM.


Note
You have the choice of using an SSH public key or a Password for authentication. SSH is more secure.

Click OK.
On the Size blade, choose a machine size. For development and functional testing, we recommend a VM size of DS2 or higher. For performance testing, use DS13 or higher. For instructions on how to generate an SSH key, see Create SSH keys on Linux and Mac for Linux VMs in Azure.







To see other sizes, select View all.

Click Select.
On the Settings blade, you can make changes to the settings or keep the default settings.
Click OK.
On the Summary page, click OK to create the VM.

Note
The Azure VM pre-configures the firewall to open the SQL Server port 1433 for remote connections. But to remotely connect, you also need to add a network security group rule as described in the next section.


Configure for remote connections

To be able to remotely connect to SQL Server on an Azure VM, you must configure an inbound rule on the network security group. The rule allows traffic on the port on which SQL Server listens (default of 1433). The following steps show how to use the Azure portal for this step.

In the portal, select Virtual machines, and then select your SQL Server VM.
In the list of properties, select Network interfaces.
Then select the Network Interface for your VM.


Click the Network security group link.


In the properties of the Network Security Group, selct Inbound security rules.

Click the +Add button.
Provide a Name of "SQLServerRemoteConnections".
In the Service list, select MS SQL.


Click OK to save the rule for your VM.


Connect to the Linux VM

If you already use a BASH shell, connect to the Azure VM using the ssh command. In the following command, replace the VM user name and IP address to connect to your Linux VM.

ssh -l AzureAdmin 100.55.555.555

You can find the IP address of your VM in the Azure portal.


If you are running on Windows and do not have a BASH shell, you can install an SSH client, such as PuTTY.

  1. Run PuTTY.
  2. On the PuTTY configuration screen enter your VM's public IP address.
  3. Click Open and enter your username and password at the prompts.


Configure SQL Server

After connecting to your Linux VM, open a new command terminal.
Set up SQL Server with the following command.

sudo /opt/mssql/bin/sqlservr-setup

Accept the License and enter a password for the system administrator account. You can start the server when prompted.


How to Install SQL Server tools on Linux

In this section of the artice, we will show you the steps to install the command-line tools, Microsoft ODBC drivers, and their dependencies. 

The mssql-tools package contains:
  • sqlcmd: Command-line query utility.
  • bcp: Bulk import-export utility.

Install the tools for your platform:
  • Red Hat Enterprise Linux
  • Ubuntu
  • macOS


Install tools on RHEL

Enter superuser mode.

sudo su

Download the Microsoft Red Hat repository configuration file.

curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/msprod.repo

Exit superuser mode.

exit

Run the following commands to install mssql-tools.

sudo yum install mssql-tools


Install tools on Ubuntu

Import the public repository GPG keys:

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

Register the Microsoft Ubuntu repository:

curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list

Update the sources list and run the installation command:

sudo apt-get update 
sudo apt-get install mssql-tools


Install tools on macOS

Sqlcmd and bcp are not available on macOS.

Use sql-cli from macOS.


Connect and query SQL Server on Linux with sqlcmd

This section of the article provides connection requirements and guidance for SQL Server vNext CTP1 running on Linux. In most cases, the connection requirements and processes do not differ across platforms. This section approaches the subject in the context of Linux and then points to other resources.



In the following steps, you will use sqlcmd to connect to SQL Server vNext on Linux. After successfully connecting, you will use Transact-SQL (T-SQL) queries to create and populate a small database.


Install the SQL Server command-line tools

Sqlcmd is part of the SQL Server command-line tools, which are not installed automatically with SQL Server on Linux. If you have not already installed the SQL Server command-line tools on your Linux machine, you must install them. For more information on how to install the tools, follow the instructions for your Linux distribution:



  • Red Hat Enterprise Linux
  • Ubuntu
  • MacOS


Connection requirements

To connect to SQL Server on Linux, you must use SQL Authentication (username and password). To connect remotely, you must ensure that the port SQL Server listens on is open. By default, SQL Server listens on TCP port 1433. Depending on your Linux distribution and configuration, you might have to open this port in the firewall.

Connect to SQL Server on Linux

In the following steps, connect to SQL Server vNext on Linux with sqlcmd.
  1. On your Linux box, open a command terminal.
  2. Run sqlcmd with parameters for your SQL Server name (-S), the user name (-U), and the password (-P).

The following command connects to the local SQL Server instance (localhost) on Linux.

sqlcmd -S localhost -U SA -P ''

To connect to a remote instance, specify the machine name or IP address for the -S parameter.

sqlcmd -S 192.555.5.555 -U SA -P ''


Query SQL Server

After you connect to SQL Server you can run queries to return information or create database objects. In the following steps, you will use sqlcmd to:

  1. Query SQL Server for a list of the databases.
  2. Use Transact SQL to create a database.
  3. Create and populate a table in the new database.
  4. Query the table.
To to complete each of these tasks, copy the Transact-SQL from the examples below into the sqlcmd session that you created in the previous step.

For example, this query returns the name of all of the databases.

SELECT Name from sys.Databases;
GO
Create a database using the SQL Server default settings.

CREATE DATABASE testdb;
GO
Use the database:

USE testdb;
GO
Create a table in the current database:

CREATE TABLE inventory (id INT, name NVARCHAR(50), quantity INT);
GO
Insert data into the new table:

INSERT INTO inventory VALUES (1, 'banana', 150);
INSERT INTO inventory VALUES (2, 'orange', 154);
GO
Select from the table:

SELECT * FROM inventory WHERE quantity > 152;
GO
To end your sqlcmd session, type QUIT.

QUIT







Conclusion

In this article, you installed SQL Server on Red Hat Linux, Ubuntu, macOS including provisioning of Azure VM. And in the end you have connected to SQL Server with sqlcmd, and created and populated a database.

Post a Comment

 
TECH SUPPORT © 2012 - Designed by INFOSBIRD