How to create a SQL Server test instance

  1. Set up a fresh 2GB VPS on Digital Ocean
  2. free -m to make sure you have at least 2GB of RAM
  3. Use Ubuntu 20.04 LTS for this documentation

Microsoft’s documentation

This is just my abbreviation of the official Microsoft Documentation for running SQL Server on Ubuntu: https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-ubuntu?view=sql-server-ver15

You may want to check that if this becomes out of date.

Get Microsoft’s Repo Keys

SQL Server is not in the official Ubuntu / Debian software app store (repository).

You will need to add Microsoft’s app store to your Ubuntu Linux instance, like so:

wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2019.list)"
sudo apt-get update

Install Microsoft SQL Server

Now that it’s in the app store (repository), you can istall it by name:

sudo apt-get install -y mssql-server

You’ll need a new password. May I suggest a nice, long, 192-bit secure random password?

xxd -l24 -ps /dev/urandom | xxd -r -ps | base64 \
    | tr -d = | tr + - | tr / _

IMPORTANT: You’re best to avoid special characters, otherwise you’ll need to URL-escape them later.

Then select a license and set the password to finish setting up the server:

sudo /opt/mssql/bin/mssql-conf setup
# Developer Edition
systemctl status mssql-server --no-pager

If the server failed to start, it’s probably because you don’t have 2GB of RAM.

SQL Server CLI Tools

In most cases gnupg2 will already be installed, but just in case:

sudo apt install -y gnupg2

Again, add the Microsoft repository and signing keys:

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
sudo apt-get update

Then you can install the tools:

sudo apt-get install -y mssql-tools unixodbc-dev

And add it to your path:

curl -sS https://webinstall.dev/pathman | bash
export PATH="$HOME/.local/bin:$PATH"

pathman add /opt/mssql-tools/bin
export PATH="/opt/mssql-tools/bin:$PATH"

Or if you prefer your rc files a little on the sloppy side:

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.zshrc
mkdir -p ~/.config/fish
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.config/fish/config.fish

export PATH="$PATH:/opt/mssql-tools/bin"

Testing that it all worked

Connect to the server (use the password you created above):

sqlcmd -S localhost -U SA

Do a basic check that you can create some data:

SELECT "Hello, World!";

GO
CREATE DATABASE TestDB;
SELECT Name from sys.Databases;
USE TestDB;

GO

CREATE TABLE TestTable1 (id CHAR(36), name VARCHAR(255), attr VARCHAR(255));
INSERT INTO TestTable1 VALUES ('xyz', 'banana', 'tasty');
INSERT INTO TestTable1 VALUES ('abc', 'orange', 'sweet');

GO

QUIT

If you want to read from a file you do so like this:

sqlcmd -S localhost -U SA -i fixtures.sql

Credentials & Connection String

Here’s what a SQL Server connection string looks like:

sqlserver://MY_USER@MY_PASS:MY_HOST/MY_INSTANCE?database=MY_CATALOG&Encrypt=disable

Note: SQL Server 2008 has some janky TLS that doesn’t play well with some database drivers.

It’s broken down into these components:

# This is the IP Address
MSSQL_SERVER=localhost

# This is the default port
MSSQL_PORT=1433

# SA is the default admin. Don't use it. :p
MSSQL_USERNAME=SA

# This may need to be URL-escaped if it has special characters
MSSQL_PASSWORD=Password1

# I think you leave this empty for TCP/IP connections
MSSQL_INSTANCE=""

# This is the database name
MSSQL_CATALOG="TestDB"

# If you need them
MSSQL_PARAMS="Encrypt=true"

Strange Errors and How to Solve Them

Usually gnupg2 is pre-installed, but if not you’ll get this error:

sudo: setrlimit(RLIMIT_CORE): Operation not permitted
E: gnupg, gnupg2 and gnupg1 do not seem to be installed, but one of them is required for this operation

It can be solved by installing gnupg2:

sudo apt install -y gnupg2