On November 16, 2016 Microsoft announced the public preview of the next release of SQL Server on Linux and Windows, which brings the power of SQL Server to both Windows – and for the first time ever – Linux. SQL Server enables developers and organizations to build intelligent applications with industry-leading performance and security technologies using their preferred language and environment. With the next release of SQL Server, we can develop applications with SQL Server on Linux, Windows, Docker, or macOS (via Docker) and then deploy to Linux, Windows, or Docker, on-premises or in the cloud.

But only today I have had the time to install it and try it!

So this article provides a walkthrough of how to install SQL Server vNext CTP 1.1 on Ubuntu 16.04 and 16.10. (Note: doesn't work on 14.04 because the OpenSSL-package is outdated).

Note: You need at least 3.25GB of memory to run SQL Server on Linux.

Install SQL Server

• 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. Make sure to specify a strong password for the SA account (Minimum length 8 characters, including uppercase and lowercase letters, base 10 digits and/or non-alphanumeric symbols).

sudo /opt/mssql/bin/sqlservr-setup

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

systemctl status mssql-server

Upgrade SQL Server on Ubuntu

In order to upgrade the mssql-server package, follow these steps:

• Update the apt-get repository lists:

sudo apt-get update

• Re-run the installation command, this will upgrade the specific mssql-server package:

sudo apt-get install mssql-server

These commands will download the newest package and replace the binaries located under /opt/mssql/ . The user generated databases and system databases will not be affected by this operation.

Uninstall SQL Server on Ubuntu

In order to remove the mssql-server package, follow these steps:

• Run the remove command. This will delete the package and remove the files under /opt/mssql/. However, this command will not affect user-generated and system databases.

sudo apt-get remove mssql-server

• Removing the package will not delete the generated database files. If you want to delete the database files use the following command:

sudo rm -rf /var/opt/mssql/

Next steps

If you want to check whether it works or not, do not forget to switch off the firewall

iptables -F 
iptables -P INPUT ACCEPT

And to permanently open port 1433 (sql-server default-port)

iptables -A INPUT -p tcp --dport 1433 -j ACCEPT
sudo apt-get install iptables-persistent
sudo netfilter-persistent save
sudo netfilter-persistent reload

or if you use ufw, you can do the same by typing less with

ufw allow 1433/tcp

You can stop, start, or restart the SQL Server service as needed using the following commands:

sudo systemctl stop mssql-server
sudo systemctl start mssql-server
sudo systemctl restart mssql-server

To start sql-server at boot-time:

systemctl enable mssql-server

To disable SQL-Server-start at boot-time:

systemctl disable mssql-server

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

• Run the following commands to install SQL Server tools:

sudo apt-get update 
sudo apt-get install mssql-tools unixodbc-dev-utf16

To update to the latest version of 'mssql-tools' run the following commands:

apt-get refresh 
apt-get update mssql-tools

Connect and query SQL Server on Linux with sqlcmd

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 <YourPassword>

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

sqlcmd -S <ServerIP> -U SA -P <YourPassword>

Query SQL Server

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 testDatabase; 
GO

Create a table in the current database:

CREATE TABLE Table1 (id INT, name NVARCHAR(50), quantity INT);
GO

Insert data into the new table:

INSERT INTO Table1 VALUES (1, 'Item1', 10);
INSERT INTO Table1 VALUES (2, 'Item2', 15);
GO

Select from the table:

SELECT * FROM Table1 WHERE quantity > 12;
GO

To end your sqlcmd session, type

QUIT

or

Exit

Connect and query from Windows

It is important to note that SQL Server tools on Windows connect to SQL Server instances on Linux in the same way they would connect to any remote SQL Server instance. So, you can follow the same steps in this topic running sqlcmd.exe from a remote Windows machine using :

• SQL Server Management Studio (SSMS)
• Windows PowerShell
• SQL Server Data Tools (SSDT)