The rule of three, SQL Server on Linux edition

When it comes to Microsoft products, the rule of three — at least as far as I’m concerned — is where you can accomplish the same task in three different ways. The go-to example is Microsoft Word, where you can use the ribbon toolbar, a keyboard shortcut, or the context menu to perform the same common formatting options.

As some of you may know, I’m preparing for the upcoming PASS Virtual Summit, where I’m presenting a pre-conference session today titled “SQL Server on Linux from A to Z.” As I was preparing my demo scripts, I noticed that this rule of three extends to Linux as well. I’ve been using Linux (and FreeBSD) since 1996, and have learned in that time that there are many (many!) ways to do the same things.

So, it came as no surprise to find that you can set up the encryption keys for the Microsoft repository on Ubuntu (and I presume the other distributions as well) in at least three different ways. I figured that this can be confusing for the Linux beginner, so I wanted to draw attention to them here and explain the differences — if any — between them.

Let’s start with wget, which is a tool designed to download the contents of the URL you point it to. In the example from the SQL Server quickstart guide on Microsoft Docs, you do the following to register the encryption keys:

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

Now we move on to curl, which outputs the contents of the URL you point it to. In the SQL Server tools guide on Microsoft Docs, you do the following, which does exactly the same thing as the previous example:

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

In both examples, the contents of the microsoft.asc file are piped to the command apt-key add, being run in the context of the superuser (sudo). The trailing hyphen tells apt-key add to use the output generated before the pipe.

In case you’re wondering: in the first example the -q switch tells wget to run the command quietly. The trailing O- in the same switch tells wget to redirect all output to a single file, and then instead of a file to use the standard output (the console) instead. The command after the pipe (apt-key) is then able to use that output.

With curl, the output by default goes to the console, so there are no shenanigans needed to redirect it to the command after the pipe.

Finally, we get what I call the PowerShell method, which is different again. In fairness to the people who wrote the documentation, it’s different for a good reason. Installing Microsoft PowerShell on Linux comes with a number of dependencies, so in order to reduce the amount of effort involved, Microsoft has made available a Debian package (Ubuntu is based on Debian). Inside this zip file you will find the same microsoft.asc file, along with a list of all the packages available on the Microsoft repository.

This is a two-step process, because the .deb file needs to be downloaded locally before it can be used.

wget -q https://packages.microsoft.com/config/ubuntu/18.04/packages-microsoft-prod.deb


sudo dpkg -i packages-microsoft-prod.deb

In the first step, again using the -q switch to hide any output from the command, we download the file containing all the information for the Microsoft repository. Then, we use a command called dpkg which installs the package into the repository registry on the local machine, to let the Ubuntu package manager know that the third-party packages from Microsoft can be downloaded.

For the more perceptive readers, you’ll note that the PowerShell example listed last does a lot more than the original wget and curl commands. For those two, we need to register the package repository separately.

If we’re installing SQL Server, we use:

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

If we’re installing the SQL Server tools, we use the same product list from the PowerShell example (which you’ll find if you inspect the .deb file), and use yet another tool (tee) to process it into the local machine’s package registry.

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

This post is a great example of having the experience to know that there are myriad ways to perform the same task, and that it’s OK if you don’t know them all, or have a different way of doing things.

Share your thoughts in the comments below.