Hello, friends. In this post, you will learn how to install Microsoft SQL Server on Ubuntu 20.04 / Debian 10 Although in Linux we have many OpenSource options this database manager is quite good and is still used by many companies today.
In terms of proprietary database managers perhaps Oracle Database, IBM DB2, and Microsoft SQL Server are the most used and common options by many companies. They have the great advantage of having large companies behind them that provide a high level of support to their operations.
Also, there are other quite competent open-source options such as MySQL, PostgreSQL, or Percona. However, many companies still trust their data to proprietary options and the fact that we can install them in Ubuntu speaks well of our system. So let’s go for it.
Install Microsoft SQL Server on Ubuntu 20.04 / Debian 10
As you can imagine, SQL Server is not available in the official Ubuntu repositories but it is available through the repository offered by Microsoft.
So, open a terminal or connect via SSH and update the system:
sudo apt update sudo apt upgrade sudo apt install software-properties-common
Then import the GPG key from the Microsoft repository.
sudo wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
After that, add the SQL Server repository as such.
sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/18.04/mssql-server-2019.list)"
Next, refresh APT.
sudo apt update
And now you can install Microsoft SQL Server by executing the following command:
sudo apt install mssql-server Reading package lists... Done Building dependency tree Reading state information... Done The following additional packages will be installed: gawk gdb libbabeltrace1 libc++1 libc++1-10 libc++abi1-10 libdw1 libmpfr6 libpython2-stdlib libpython2.7-minimal libpython2.7-stdlib libsasl2-modules libsasl2-modules-gssapi-mit libsigsegv2 libsss-nss-idmap0 python-is-python2 python2 python2-minimal python2.7 python2.7-minimal Suggested packages: gawk-doc gdb-doc clang libsasl2-modules-ldap libsasl2-modules-otp libsasl2-modules-sql python2-doc python-tk python2.7-doc binutils binfmt-support Recommended packages: libc-dbg libcc1-0 gdbserver The following NEW packages will be installed: gawk gdb libbabeltrace1 libc++1 libc++1-10 libc++abi1-10 libdw1 libmpfr6 libpython2-stdlib libpython2.7-minimal libpython2.7-stdlib libsasl2-modules libsasl2-modules-gssapi-mit libsigsegv2 libsss-nss-idmap0 mssql-server python-is-python2 python2 python2-minimal python2.7 python2.7-minimal 0 upgraded, 21 newly installed, 0 to remove and 29 not upgraded. Need to get 235 MB of archives. After this operation, 1,102 MB of additional disk space will be used. Do you want to continue? [Y/n]
This way it will be installed.
Preparing Microsoft SQL Server for the first use
During the installation process, you will notice that at the end, you will get an output screen similar to this one:
Setting up python-is-python2 (2.7.17-4) ... Setting up libc++1:amd64 (1:10.0-50~exp1) ... Setting up mssql-server (15.0.4102.2-4) ... +--------------------------------------------------------------+ Please run 'sudo /opt/mssql/bin/mssql-conf setup' to complete the setup of Microsoft SQL Server +--------------------------------------------------------------+ Processing triggers for man-db (2.9.1-1) ... Processing triggers for mime-support (3.64ubuntu1) ... Processing triggers for libc-bin (2.31-0ubuntu9.2) ...
In it, we are told that it is necessary to configure the application before using it and it even tells us the command to start the configuration.
sudo /opt/mssql/bin/mssql-conf setup usermod: no changes Choose an edition of 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) - CPU Core utilization restricted to 20 physical/40 hyperthreaded 7) Enterprise Core (PAID) - CPU Core utilization up to Operating System Maximum 8) I bought a license through a retail sales channel and have a product key to enter. Details about editions can be found at https://go.microsoft.com/fwlink/?LinkId=2109348&clcid=0x409 Use of PAID editions of this software requires separate licensing through a Microsoft Volume Licensing program. By choosing a PAID edition, you are verifying that you have the appropriate number of licenses in place to install and run this software. Enter your edition(1-8):
The main configuration we have to do is to enter the license. In this case, I have used the Developer which is free and we can use it for the post.
Also, you have to accept the license terms and create the administrator key which has to be complex.
With this SQL Server will be installed but we are missing one more detail.
For now, you can check the status of the service to see if everything is in order:
sudo systemctl status mssql-server
● mssql-server.service - Microsoft SQL Server Database Engine
Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor preset: enabled)
Active: active (running) since Thu 2021-03-25 20:39:29 CET; 3min 59s ago
Docs: https://docs.microsoft.com/en-us/sql/linux
Main PID: 3175 (sqlservr)
Tasks: 136
Memory: 587.6M
CGroup: /system.slice/mssql-server.service
├─3175 /opt/mssql/bin/sqlservr
└─3218 /opt/mssql/bin/sqlservr
Install SQL Server tools and UnixODBC
Although the Server is ready, we have to install other components like the Tools and fundamentally the UnixODBC connector without this we will not be able to make a connection with the server.
So, add the GPG key and the repository:
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add - OK wget https://packages.microsoft.com/config/ubuntu/19.10/prod.list sudo mv prod.list /etc/apt/sources.list.d/mssql-release.list
After this, refresh APT to apply the change:
sudo apt update
And finally, install both the UnixODBC connector and the Mssql-tools
sudo ACCEPT_EULA=Y apt install mssql-tools unixodbc-dev Reading package lists... Done Building dependency tree Reading state information... Done The following additional packages will be installed: autoconf automake autotools-dev libgdbm-compat4 libltdl-dev libodbc1 libperl5.30 m4 msodbcsql17 odbcinst odbcinst1debian2 perl perl-modules-5.30 unixodbc Suggested packages: autoconf-archive gnu-standards autoconf-doc libtool gettext libtool-doc unixodbc-bin m4-doc perl-doc libterm-readline-gnu-perl | libterm-readline-perl-perl make libb-debug-perl liblocale-codes-perl Recommended packages: libtool The following NEW packages will be installed: autoconf automake autotools-dev libgdbm-compat4 libltdl-dev libodbc1 libperl5.30 m4 msodbcsql17 mssql-tools odbcinst odbcinst1debian2 perl perl-modules-5.30 unixodbc unixodbc-dev 0 upgraded, 16 newly installed, 0 to remove and 29 not upgraded. Need to get 9,845 kB of archives. After this operation, 53.9 MB of additional disk space will be used. Do you want to continue? [Y/n]
After the installation, add the directory to the bash_profile so that the command is available from the whole system.
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
In the end, you can connect to the shell by running:
sqlcmd -S 127.0.0.1 -U SA
Note: if you cannot execute the command, log out and try again.
Now you will be able to use Microsoft SQL Server on Ubuntu 20.04 / Debian 10
Conclusion
Microsoft SQL Server is pretty good at what it does and that is why many companies have it as their database manager. Having it available on Ubuntu 20.04 / Debian 10 is not a difficult task and brings us even closer to the integration between Microsoft and Linux products.
