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.