How to create a SQL Server test instance
- Set up a fresh 2GB VPS on Digital Ocean
free -mto make sure you have at least 2GB of RAM- 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