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.

Author: Łukasz Bodziony

Website: Windows VPS

Łukasz Bodziony is the CEO and founder of NETCLOUD24, a global VPS hosting brand proudly originating from Poland. With extensive experience in cloud computing, virtualization, and server management, he delivers high-performance Windows VPS and Remote Desktop Services (RDS) solutions to clients across Europe, North America, and beyond.

His expertise covers a wide range of technologies, including Microsoft Azure, Proxmox VE, Amazon Web Services (AWS), and numerous other virtualization and cloud platforms.

Beyond running his hosting business, Łukasz also provides professional paid server configuration and optimization services for companies and individuals. Outside of work, he is dedicated to caring for his children and building a secure future for them.

If you are interested in working with him or need expert assistance with your hosting, cloud environment, or server setup, feel free to reach out via Windows VPS.