1. Introduction
Microsoft SQL Server is a powerful relational database management system. This guide covers the installation, configuration, database creation, security settings, and backup automation on Linux.
2. System Requirements
- Ubuntu 20.04 or later / CentOS 7 or later
- At least 2GB RAM and 10GB free disk space
- Administrator (sudo) access
3. Installing Microsoft SQL Server
3.1 Adding the Microsoft Repository
sudo wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2019.list)"
sudo apt update
3.2 Installing SQL Server
sudo apt install -y mssql-server
sudo /opt/mssql/bin/mssql-conf setup
sudo systemctl enable --now mssql-server
Follow the on-screen instructions to configure SQL Server settings.
4. Configuring SQL Server
4.1 Installing SQL Server Command-Line Tools
sudo apt install -y mssql-tools unixodbc-dev
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
4.2 Connecting to SQL Server
sqlcmd -S localhost -U SA -P 'YourStrongPassword'
5. Creating and Managing Databases
CREATE DATABASE TestDB;
GO
USE TestDB;
CREATE TABLE Users (ID INT PRIMARY KEY, Name NVARCHAR(50));
GO
INSERT INTO Users (ID, Name) VALUES (1, 'Alice');
SELECT * FROM Users;
GO
6. Configuring Remote Access
sudo ufw allow 1433/tcp
sudo systemctl restart mssql-server
Ensure SQL Server is set to allow remote connections by modifying the configuration file.
7. Setting Up Automated Backups
7.1 Creating a Backup Script
#!/bin/bash
TIMESTAMP=$(date +"%Y-%m-%d_%H-%M-%S")
BACKUP_DIR="/var/opt/mssql/backups"
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'YourStrongPassword' -Q \
"BACKUP DATABASE TestDB TO DISK = '$BACKUP_DIR/TestDB_$TIMESTAMP.bak'"
7.2 Automating Backups with Cron
crontab -e
0 2 * * * /path/to/backup_script.sh
This schedules a daily backup at 2 AM.
8. Conclusion
Following this guide, you should now have a fully operational Microsoft SQL Server on Linux, with remote access and automated backups configured.
