If you are a non-techy or do not want the headache, I can offer to install it for you. You can just put a comment on this post or reach me out on Twitter at @ghulamostafa.
For a long time, whoever was building a Web App with a SQL Server database, had to lookup for Windows Hosting providers, which were (and still are) not cheap. That’s true the prices have dropped but if you do compare the prices with a Linux based server, Windows servers are almost two times the price.
Well, the good news is that Microsoft started switching and supporting to the Open Source cause. Among those changes, they have started supporting their software and tools to be able to run on a non-Windows environment. Among which at the top are the development tools. But today’s topic is about the hosting environment for a .NET application on a Ubuntu (I will be using a droplet at DigitalOcean for this article).
To start with, you would need an a server running Ubuntu 18.04 or 20.04. Below are the steps to install the required libraries and dependencies.
- Import the public repository GPG keys:
wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
- Register the Microsoft SQL Server Ubuntu repository for SQL Server 2019:
sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/18.04/mssql-server-2019.list)"
- Run the following commands to install SQL Server:
sudo apt-get update sudo apt-get install -y mssql-server
It depends on which version you want to install, but mostly for development and small projects, we prefer using SQL Server Express Edition.
- After the package installation finishes, run mssql-conf setup and follow the prompts to set the SA password and choose your edition.
sudo /opt/mssql/bin/mssql-conf setup
- Once the configuration is done, verify that the service is running:
systemctl status mssql-server --no-pager
- If you plan to connect remotely, you might also need to open the SQL Server TCP port (default 1433) on your firewall.
The SQL server should be running on the server by now. You can connect to your server using SQL Server Management Studio (SSMS) using the address (or IP) and the credentials you’ve set.
But if you prefer to use the command line, you need to follow the steps below:
- Import the public repository GPG keys.
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
- Register the Microsoft Ubuntu repository.
curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
- Update the sources list and run the installation command with the unixODBC developer package. For more information, see Install the Microsoft ODBC driver for SQL Server (Linux).
sudo apt-get update sudo apt-get install mssql-tools unixodbc-dev
To update to the latest version of mssql-tools run the following commands:
sudo apt-get update sudo apt-get install mssql-tools
- Optional: Add /opt/mssql-tools/bin/ to your PATH environment variable in a bash shell.
To make sqlcmd/bcp accessible from the bash shell for login sessions, modify your PATH in the ~/.bash_profile file with the following command:
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
To make sqlcmd/bcp accessible from the bash shell for interactive/non-login sessions, modify the PATH in the ~/.bashrc file with the following command:
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc source ~/.bashrc
- Run sqlcmd with parameters for your SQL Server name (-S), the user name (-U), and the password (-P). In this tutorial, you are connecting locally, so the server name is localhost. The user name is SA and the password is the one you provided for the SA account during setup.
sqlcmd -S localhost -U SA -P ''
There you have your MS SQL Server running on a Ubuntu Server.