The Shift to SQL Server on Linux
Microsoft SQL Server used to be a Windows-only affair. If you wanted to run a high-performance instance, you were locked into that specific ecosystem. That barrier fell in 2017. Today, running SQL Server on Linux is a standard move for DevOps teams who want to slash licensing costs and simplify their CI/CD pipelines.
I’ve been migrating production workloads from Windows to Linux for several years now. The performance is nearly identical. However, the real win is the workflow. Managing a database using native Linux tools feels much more natural in a modern infrastructure stack. You get the power of MSSQL without the overhead of a heavy Windows Server GUI.
Installing SQL Server on Ubuntu 22.04
I usually stick with Ubuntu for these deployments. Its package management is predictable, and the community support is massive. Before you start, check your hardware. Microsoft says you need at least 2GB of RAM, but in my experience, 4GB is the real-world minimum for a stable production environment.
Step 1: Import the Repository GPG Keys
Your system needs to trust the Microsoft packages. Grab the public GPG keys and store them in your keyrings directory.
wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo gpg --dearmor -o /usr/share/keyrings/microsoft-prod.gpgStep 2: Register the Ubuntu Repository
Next, point your system to the SQL Server 2022 repository. This ensures you get the latest updates directly from the source.
curl https://packages.microsoft.com/config/ubuntu/22.04/mssql-server-2022.list | sudo tee /etc/apt/sources.list.d/mssql-server-2022.listStep 3: Install the SQL Server Package
Now, update your local index and pull down the binaries. This step handles the heavy lifting but won’t configure your database just yet.
sudo apt-get update
sudo apt-get install -y mssql-serverStep 4: Run the Configuration Script
With the binaries installed, trigger the setup script. This is where you choose your edition and set the System Administrator (SA) password.
sudo /opt/mssql/bin/mssql-conf setupPick the ‘Developer’ edition for local testing or ‘Express’ for small, free-tier apps. Make that SA password a beast. SQL Server is a constant target for automated brute-force scripts.
Security Hardening: Don’t Leave the Door Open
A default installation is a vulnerable installation. I never leave an instance running with out-of-the-box settings. You need to lock it down immediately after the setup script finishes.
Changing the Default Port
SQL Server lives on port 1433 by default. Every bot on the internet scans for this port. Moving your instance to a non-standard port, like 15000, can drop the number of automated login attempts in your logs by over 90%.
sudo /opt/mssql/bin/mssql-conf set network.tcpport 15000
sudo systemctl restart mssql-serverConfiguring the Firewall
Use UFW (Uncomplicated Firewall) to restrict access. Never expose your SQL port to the entire internet. Only allow traffic from your specific application server or your office IP range.
sudo ufw allow from 192.168.1.0/24 to any port 15000
sudo ufw enableHandling Data Imports
Migrations often involve messy data. If you are stuck with raw CSV files that need to become JSON for a seeding script, try toolcraft.app/en/tools/data/csv-to-json. It processes everything in your browser. This means your sensitive data never actually leaves your machine, which is a huge plus for compliance.
Advanced Management with mssql-cli
The old sqlcmd utility works, but it feels dated. I prefer mssql-cli. It provides modern features like auto-completion and syntax highlighting, which are essential when you’re troubleshooting queries at 2 AM.
sudo apt-get install -y python3-pip
pip3 install mssql-cliConnect to your instance using the new port you configured earlier:
mssql-cli -S localhost,15000 -U SA -P 'YourStrongPassword'Automating Backups with Cron
The Express edition lacks the SQL Server Agent for scheduling. On Linux, this isn’t an issue because we have cron. It’s more reliable and easier to script anyway.
Creating the Backup Script
Create a file named backup_mssql.sh. This script handles the backup and cleans up files older than a week to save disk space.
#!/bin/bash
BACKUP_DIR="/var/opt/mssql/backups"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="MyDatabase"
mkdir -p $BACKUP_DIR
# Trigger the full backup
mssql-cli -S localhost,15000 -U SA -P 'YourStrongPassword' -Q "BACKUP DATABASE [$DB_NAME] TO DISK = N'$BACKUP_DIR/${DB_NAME}_$DATE.bak' WITH NOFORMAT, NOINIT, STATS = 10"
# Purge backups older than 7 days
find $BACKUP_DIR -type f -name "*.bak" -mtime +7 -deleteScheduling the Task
Make the script executable and set it to run every night at 2 AM. This ensures you always have a fresh recovery point.
chmod +x ~/backup_mssql.sh
(crontab -l 2>/dev/null; echo "0 2 * * * /home/user/backup_mssql.sh") | crontab -Monitoring and Logs
Always keep an eye on your service health. If things get sluggish, the SQL Server error log is the first place you should look. It reveals memory pressure issues and failed login attempts in real-time.
To watch the logs as they happen, use tail:
sudo tail -f /var/opt/mssql/log/errorlogIf you see errors regarding “Memory limit has been reached,” you may need to cap the memory usage. Use mssql-conf to set memory.memorylimitmb. This prevents the database from starving the Linux OS of resources.
Running SQL Server on Linux is a game-changer for cost-conscious engineering teams. By mastering the CLI tools, you gain a level of control that’s simply impossible through a traditional point-and-click interface.
