Running Microsoft SQL Server on Linux

Did you know that Microsoft SQL server can be run on Linux as well? Thanks to the fact that this is possible, who needs to use necessarily Microsoft SQL Server, can now get rid of the Windows operating system. How to do it? How to run and test MS SQL Server easily on Linux, I will describe in this article.

The ability to run Microsoft SQL Server (MSSQL) on the Linux operating system was first announced in March 2016, when the 2016 version of SQL Server was released. This version of MS SQL Server brought Linux support, which made it possible to install and run MS SQL on various Linux distributions.

So now you can easily replace Windows servers with Linux machines.

Installation and startup

The easiest way to try Microsoft SQL Server is to use Docker. If you have Docker installed. You won't need anything else, and getting it up and running and then deleting it is quick and easy.

Use the following command to start the MS SQL Server version 2022 docker container from a docker image. If the image has not been downloaded yet it will be downloaded first.

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=d0_n0t_be_l@zy_h3r3" -p 1433:1433 -d mcr.microsoft.com/mssql/server:2022-latest

Running it also confirms the license and sets the password to d0_n0t_be_l@zy_h3r3. The password must be sufficiently complex. If it is simple MS SQL server will not start and you will see an error in the console:

ERROR: Unable to set system administrator password: Password validation failed. The password does not meet SQL Server password policy requirements because it is too short. The password must be at least 8 characters..

Ideally, create a docker-compose file and run the MS SQL container using it.
Example docker-compose.yml with MS SQL server configuration to try:

version: '3.7'

services:
  
    mssql:
      image: mcr.microsoft.com/mssql/server:2022-latest

      environment:
          - ACCEPT_EULA=Y
          - SA_PASSWORD=d0_n0t_be_l@zy_h3r3

      volumes:
          - ./dump:/srv
      ports:
          - 1433:1433

Use the docker-compose up command to start the container configuration.

In both cases, Microsoft SQL Server is then available on the standard port 1433 on your machine.

The environment parameter SA_PASSWORD and MSSQL_SA_PASSWORD appear to be equivalent.

Of course, another option is to prepare a VPS with a supported Linux distribution and manually install MS SQL there. However, this procedure is more laborious and will also take up more disk space.
Moreover, as I looked at the installation procedure, it does not follow Unix and distribution standards. For example, only selected Linux distributions are supported. So rather than maintaining such a VPS, I prefer a pure VPS where the application in question runs in a container that I can delete or restore at any time. Here is a signpost installing MS SQL Server on Linux.

Connecting and Using DB

You will need to connect to the database not only programmatically, but also as a database administrator. For this, you can use a console client or a graphical client. Each method has its advantages so I will show you both options.

Graphical client

I use the DBeaver graphical client to connect to the Microsoft SQL server database. This program is written in the Java programming language, so it is multiplatform and allows connections to many types of database servers.

Console Client

Using the console client is essential. Especially if you want to manage servers efficiently. On Linux, the console utility sqlcmd is included with the MS SQL Server installation. If you are used to connecting to a MySQL/MariaDB database then the usage is similar.

We can connect to the database with this command:

/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'd0_n0t_be_l@zy_h3r3'

If you are using docker, you need to switch to docker first, see How to start using Docker properly. Or enter this command, which will run the above command directly in the docker container, which is called "app_mssql_1":

docker exec -it app_mssql_1 /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'd0_n0t_be_l@zy_h3r3'

Basic commands

Commands like SELECT, INSERT, DELETE and UPDATE work very similarly to other relational databases that use the SQL query language.

Creating a table:

CREATE TABLE table1 ( ID INT PRIMARY KEY, name NVARCHAR(50), surname NVARCHAR(50), age INT );

A single running server can contain visas of databases (also called schemas). Finding out what database we are currently using:

select DB_NAME()
go

List database:

select name from sys.databases
go

Command to change the currently used database:

use tempdb
go

Create database: "test":

CREATE DATABASE test
go

Delete database "test":

DROP DATABASE test
go

Dump all tables of the selected database :

SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE'
go

You can use several SQL queries to list users in MS SQL Server, that query system tables containing user information. One common way is to use the following query:

SELECT name, type_desc, create_date FROM sys.server_principals WHERE type IN ('S', 'U')
go

This query lists the user names, user types (S - SQL Login, U - Windows Login) and the date the user was created. You must have sufficient privileges to run the query.

Application connection from PHP

For how to connect to MS SQL Server from PHP see my article connecting to MS SQL using PHP from Linux.

Node.JS Application Connection

To connect an application written in NodeJS to an MS SQL Sever database server, you will need to install support. The so-called database driver. Here in the official documentation is a detailed guide on how to install MS SQL ODBC, that will enable communication between your NodeJS application and the MS SQL Server database server.

Backup and Restore Database

Microsoft SQL Server installed on Linux stores data in the /var/opt/mssql/data/ directory. So if you are using docker, you should have this directory located on volume which is persistent and not in a container. Because when you delete a container, all of its contents are deleted. You can back up this directory. But better than copying the files is to do a database dump.

For backups where you create a database dump, the console is great and you can automate the commands afterwards. To dump the "YourDatabaseName" database to the '/path/to/backupfile.bak' file, use the following command :

BACKUP DATABASE test TO DISK='/path/to/backupfile.bak'

To restore a database from a dump file to a database named "test", use the following command:

RESTORE DATABASE test FROM DISK = '/path/to/backupfile.bak' WITH REPLACE, RECOVERY

Restore the database if you got a copy of the database files from the server disk. To do this, use the following command:

CREATE DATABASE [newdb] ON (FILENAME ='/srv/newdb.mdf'),(FILENAME = '/srv/newdb_log.ldf') FOR ATTACH

All backups then need to be done automatically and stored on some geographically separated storage or backup solution. This is the only way to guarantee the safety of your data. Example How to write a script to back up MS SQL Server running in a Docker container

Video tutorial

This is a demonstration video on how to get Microsoft SQL Server up and running using Docker and how to connect to databases using the console and graphical client:

Licence

If you use free software, the licenses are simple and you don't have to worry about licensing. In the case of proprietary software, it's more complicated and sometimes very difficult. What about Microsoft SQL Server?
MS SQL Server is a proprietary solution, so you need to remember to think about licensing, because if you forget that, it will be a problem!

Microsoft offers the following options when it comes to licensing MS SQL Server:

1. Evaluation (free, no production use rights, 180-day limit)
2. Developer (free, no production use rights)
3. Express (free)
4. Web (PAID)
5. Standard (PAID)
6. Enterprise (PAID)
7. Enterprise Core (PAID)
8. I bought a license through a retail sales channel and have a product key to enter.

If you are running MS SQL Server using docker, you can use the MSSQL_PID parameter to specify what license you want to use. You have the following options:

• Developer : This will run the container using the Developer Edition
• Express : This will run the container using the Express Edition
• Standard : This will run the container using the Standard Edition
• Enterprise : This will run the container using the Enterprise Edition
• EnterpriseCore : This will run the container using the Enterprise Edition Core

If you don't specify a variable/license, the Developer license is set by default.

For operational purposes, you will need to find out what version of MS SQL Server you are using and what license you have set up.
To find out the MS SQL Server version, use the command:

SELECT @@VERSION

Find out what license you are using:

SELECT SERVERPROPERTY('Edition');

While the Express license is free, it has many significant limitations. The main limitations are in terms of database size and system resources used. The specific limits are:

• Maximum database size of 10 GB
• Maximum 1410 MB of memory
• Maximum compute of 1 socket or 4 cores
• Some features are not disabled...
• ect.

Official Microsoft SQL Server licensing page.

Of course if you have a choice I would recommend migrating the MS SQL database to for example MySQL/MariaDB. And if you are a more demanding user or use Oracle, migrate to PotgreSQL. This will solve your licensing problems and worries.
Here is more information about MariaDB (Mysql) and PostgreSQL

Master Database

The Microsoft SQL Server database system contains a database (schema) called "master". This is a special system database. This database is one of the default databases in MS SQL Server and contains important information and metadata that controls the entire MS SQL Server.
The following important information is stored in the "master" database:

• Data about all other databases created on the server, including their disk location, status, and configuration.
• Information about login names and user permissions for the entire MS SQL Server.
• Configuration options and settings for MS SQL Server, such as server parameters, authentication methods, network configuration, etc.
• Some internal tables and system views that provide access to important information about MS SQL Server operation.

The "master" database is critical to the proper functioning of MS SQL Server. If it were to become corrupted or fail, it could lead to problems with the entire SQL Server. Therefore, it is important to make sure that this database is backed up and protected from unauthorized access or risks of data loss.

Operating on Windows vs. Linux

What is the difference between the Windows version of MS SQL Server and the GNU/Linux version?

The core database engine for SQL Server is the same for both Windows and Linux. However, MS SQL Server on Linux does not currently support some features. If you are interested in more details check the documentation under Unsupported features & services. If you have any problems, take a look at the Known issues section.

Is the performance of MS SQL Server on Linux better than on Windows? It is said that MS SQL Server on Linux provides slightly better performance, in general this statement can be trusted, because at least Windows itself has significant demands on system resources. I have not personally tested the performance because I don't have the time and especially the nerve to deal with the Windows operating system. Therefore, I can't give specific shots.

More information here: MS SQL Server Linux FAQ

Links

There is plenty of information and documentation on the Internet about running MS SQL Server on Linux. Here I add some valuable links:

• Installing Microsoft SQL Server on Ubuntu
• Official MS SQL installation and usage page on Ubuntu.
• quickstart install connect docker
• mssql console documentation
• Backup & restore

Is it worth switching to Linux?

Yes it is!
By running MS SQL Server on Linux you save the cost of a Windows OS license and time (i.e. money) on maintenance, because managing a server with Linux OS is less time consuming and significantly less problematic.

Don't forget either that applications programmed in C# aka .NET can run on Linux! This finally allows you to easily migrate your application infrastructure from Windows to Linux without having to rewrite your application and change the database engine. After all, Microsoft itself has already migrated cloud Azure to GNU/Linux for the most part.