Weekend Project: SQL Server 2022 on Ubuntu

There’s something I’ll have to admit to: I don’t (didn’t) have an SQL Server test instance to play with. Gasp! I broke apart my Windows desktop earlier this year, sold all the parts and then remembered how am I supposed to play with SQL Server without it? Well it did save some desk space…

This weekend it came to the top of my task list to build another so for this brief tutorial I’m going to show you how to install SQL Server 2022 onto an Ubuntu Server. Yes that’s right; no Windows involved.

Pre-requisites

• A fully setup Ubuntu Server 20.04 running on either a spare PC/server, Virtual Machine, cloud service, basically somewhere. Hell they even run Doom on pregnancy tests these days so I’m sure you can find something to run it on.
• SQL Server 2022 requires a minimum of 2 GB of system memory so spec/configure as appropriate.

Once you have your shiny new Ubuntu Server you should then use SSH to connect into the environment and let the fun begin.

Step 1: Import the GPG keys for the Microsoft repository. This means that you can trust the repo that you’ll download SQL Server 2022 from:

curl https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc

Step 2: Register the Microsoft repository with apt. This means that you add the address of the Microsoft repository into apt’s list it uses to check for and download software & updates. This is where you’ll actually download SQL Server 2022 from:

sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2022.list)"

Step 3: Update apt’s list of available updates then instruct apt to install SQL Server 2022. Note the -y switch after the install argument means that you are telling apt to do all this automatically i.e.: yes to all prompts. This will trigger a download around 1.3 GB in total so warm up that internet connection:

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

Step 4: Run mssql-conf setup to configure your newly installed SQL Server 2022 instance:

sudo /opt/mssql/bin/mssql-conf setup

1. You’ll first get asked for the edition. I chose number 2 for Developer as I want to use this build as a test/training server. Whatever edition you choose do make sure you are using an edition you are appropriately licenced for. You’ve been warned.
2. Following choosing an edition you’ll get asked to accept the licence terms. I know you’ll read them fully and very carefully but make sure you type “Yes” to accept.
3. Next will be the language. I chose 1 for English but as you require for this.
4. After that you’ll get prompted to specify an sa password. Make sure you choose something secure and record it securely especially if this is production.

Step 5: Confirm that the service is running. It would be most definitely disappointing if it weren’t:

systemctl status mssql-server --no-pager

You’ll get something resembling this which confirms that SQL Server 2022 is running:

If you’re new to SQL Server on Linux then something you need to be aware of is that you can’t run SQL Server Management Studio (SSMS) on Linux. As an alternative you can go try Azure Data Studio which has the Admin Pack for SQL Server extension pack available that includes tools for Profiler, SQL Server Agent, Import and for working with .dacpac files.

Once you are connected to the SQL Server instance then one thing to note if you run the T-SQL command @@VERSION you’ll get something like this:

Microsoft SQL Server 2022 (RTM-CU7) (KB5028743) - 16.0.4065.3 (X64)  	Jul 25 2023 18:03:43  	Copyright (C) 2022 Microsoft Corporation 	Developer Edition (64-bit) on Linux (Ubuntu 20.04.6 LTS) <X64>

Specifically the point here is that Cumulative Updates (CUs) in SQL Server on Linux are handled differently than Windows. On Ubuntu Linux to take an example CUs are delivered using apt. This is unlike Windows where you have to download and install the cumulative update that you want or accidentally let Windows Update install one if there’s a security fix that needs applying. This means that the release you get by default from the repository is the latest one.

So, congratulations. You’ve made it this far. If you don’t like reading this blog (why?) then you can always follow Microsoft’s direct instructions on the setup above but then you’ll have to admit I made you scroll all the way down here to find this link.

Bonus Round! Restoring AdventureWorks Sample Data

Now you have your newly installed Ubuntu Server with an SQL Server 2022 instance you’re going to need some test data in there (unless you’re straight into production in which case bon voyage!).

To do this you can use wget to bring in the SQL Server 2022 edition of the venerable AdventureWorks databases.

--OLTP
wget https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2022.bak

--Data Warehouse
wget https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorksDW2022.bak

--Lightweight
wget https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorksLT2022.bak

This should download the databases into your Home directory if you haven’t done a cd out of there.

You can then use Azure Data Studio to restore the databases if you have preview features turned on. If you don’t then you can restore using the following T-SQL:

--OLTP
USE [master]
RESTORE DATABASE [AdventureWorks2022] FROM DISK = N'/home/<user>/AdventureWorks2022.bak' WITH  FILE = 1, MOVE N'AdventureWorks2022' TO N'/var/opt/mssql/data/AdventureWorks2022.mdf', MOVE N'AdventureWorks2022_log' TO N'/var/opt/mssql/data/AdventureWorks2022_log.ldf', NOUNLOAD,  STATS = 10

--Data Warehouse
USE [master]
RESTORE DATABASE [AdventureWorksDW2022] FROM  DISK = N'/home/<user>/AdventureWorksDW2022.bak' WITH  FILE = 1, MOVE N'AdventureWorksDW2022' TO N'/var/opt/mssql/data/AdventureWorksDW2022.mdf',  MOVE N'AdventureWorksDW2022_log' TO N'/var/opt/mssql/data/AdventureWorksDW2022_log.ldf', NOUNLOAD, STATS = 10

--Lightweight
USE [master]
RESTORE DATABASE [AdventureWorksLT2022] FROM DISK = N'/home/<user>/AdventureWorksLT2022.bak' WITH  FILE = 1, MOVE N'AdventureWorksLT2022_Data' TO N'/var/opt/mssql/data/AdventureWorksLT2022.mdf', MOVE N'AdventureWorksLT2022_Log' TO N'/var/opt/mssql/data/AdventureWorksLT2022_log.ldf', NOUNLOAD, STATS = 10