Databases

SQL Server 2019 Express User Guide

| Product: SQL Server 2019 Express

Overview

This guide covers the deployment and configuration of SQL Server 2019 Express on Linux using cloudimg AMIs from the AWS Marketplace. SQL Server 2019 Express is a free, lightweight edition of SQL Server ideal for small databases, lightweight web applications, and learning SQL Server fundamentals.

What's included in this AMI:

  • SQL Server 2019 Express edition installed at /opt/mssql
  • SQL Server database port 1433 preconfigured and listening on boot
  • SA (System Administrator) account with a randomly generated password
  • sqlcmd command line tool for database interaction
  • OS package update script for keeping the system current
  • AWS CLI v2 for AWS service integration
  • Systems Manager Agent (SSM) for remote management
  • CloudWatch Agent for monitoring
  • Latest security patches applied at build time
  • 24/7 cloudimg support with guaranteed 24 hour response SLA

Edition Notes: SQL Server 2019 Express is free to use in production but has the following resource limits: maximum database size of 10 GB per database, maximum of 1 GB RAM utilisation for the buffer pool, and limited to the lesser of 1 socket or 4 cores. These limits make Express suitable for small applications, embedded databases, and learning environments. If your workload exceeds these limits, consider upgrading to Standard or Enterprise edition.

Prerequisites

Before launching this AMI, ensure you have:

  1. An active AWS account
  2. An active subscription to the SQL Server 2019 Express listing on AWS Marketplace
  3. An EC2 key pair for SSH access
  4. Familiarity with EC2 instance management and SSH

Recommended Instance Type: t3.small (2 vCPU, 2 GB RAM). Since Express edition is limited to 1 GB buffer pool RAM and 4 cores, a smaller instance is cost effective.

Minimum Requirements: 1 vCPU, 2 GB RAM, and 6 GB disk space.

Step 1: Launch the AMI

  1. Navigate to the AWS Marketplace and search for "SQL Server 2019 Express cloudimg"
  2. Click Continue to Subscribe, accept the terms, then Continue to Configuration
  3. Select your preferred Region and Software Version
  4. Click Continue to Launch
  5. Choose Launch through EC2 for full control over instance configuration
  6. Select your instance type (t3.small recommended)
  7. Configure storage: ensure at least 6 GB disk space. Remember that individual databases are limited to 10 GB in Express edition
  8. Configure your Security Group with the following inbound rules:
Port Protocol Source Purpose
22 TCP Your IP SSH access
1433 TCP Your IP SQL Server database port

Important: Restrict port 1433 to trusted application servers or your IP only. Never expose the SQL Server port to the public internet.

  1. Select your EC2 key pair and launch the instance

Step 2: Connect via SSH

Once your instance is running and has passed both status checks (2/2), connect using SSH:

ssh -i your-key.pem ec2-user@<public-ip-address>

To switch to the root user:

sudo su -

Important: Wait for the EC2 instance to reach 2/2 successful status checks before connecting. Early connection attempts may produce "Permission denied" errors as the instance is still initialising.

Step 3: Retrieve the SA Password

The SA (System Administrator) password is randomly generated at build time and stored in a log file on the instance. Retrieve it by running:

cat /stage/scripts/mssql_admin_password.log

Save this password securely. You will need it to connect to SQL Server.

Step 4: Connect to SQL Server

Use the sqlcmd command line tool to connect to the local SQL Server instance:

sqlcmd -S localhost -U SA

Enter the SA password retrieved in Step 3 when prompted.

Expected output:

1>

You are now connected to SQL Server and can run T-SQL queries.

Step 5: Create a Database and Table

Create a new database:

CREATE DATABASE MyAppDB;
GO

Switch to the new database:

USE MyAppDB;
GO

Create a table:

CREATE TABLE Users (
    UserID INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100),
    Email NVARCHAR(255),
    CreatedAt DATETIME DEFAULT GETDATE()
);
GO

Insert data:

INSERT INTO Users (Name, Email) VALUES ('John Doe', 'john@example.com');
GO

Query data:

SELECT * FROM Users;
GO

Type QUIT to exit the sqlcmd shell.

Note: Each database in Express edition is limited to a maximum size of 10 GB. Monitor database size with:

SELECT name, size * 8 / 1024 AS SizeMB FROM sys.master_files WHERE type = 0;
GO

Server Components

Component Software Home
SQL Server 2019 Express /opt/mssql

Note: Component versions may be updated on first boot by the automatic OS package update script finding new versions in the system package repositories.

Filesystem Layout

Mount Point Size Description
/ 38 GB Root filesystem
/boot 2 GB Operating system kernel files
/opt/mssql 9.8 GB SQL Server installation directory

Managing the SQL Server Service

SQL Server is managed via systemd and starts automatically on boot.

Check service status:

systemctl status mssql-server

Stop SQL Server:

systemctl stop mssql-server

Start SQL Server:

systemctl start mssql-server

Restart SQL Server:

systemctl restart mssql-server

Scripts and Log Files

Script/Log Path Description
initial_boot_update.sh /stage/scripts Updates the OS with the latest packages on first boot
initial_boot_update.log /stage/scripts Output log for the boot update script
mssql_admin_password.log /stage/scripts Contains the randomly generated SA password

On Startup

An OS package update script runs on first boot to ensure the image is fully up to date. You can disable this by removing the script and its crontab entry:

rm -f /stage/scripts/initial_boot_update.sh

crontab -e
# Delete the following line, save and exit:
@reboot /stage/scripts/initial_boot_update.sh

Troubleshooting

Cannot connect via SSH

  1. Wait for the EC2 instance to reach 2/2 status checks
  2. Verify your security group allows port 22 from your IP
  3. Confirm you are using the correct key pair and connecting as ec2-user
  4. Check the instance system log in the AWS Console for boot errors

SQL Server service not running

  1. Check service status: systemctl status mssql-server
  2. Review the SQL Server error log: cat /var/opt/mssql/log/errorlog
  3. Verify sufficient disk space: df -h /opt/mssql
  4. Ensure the instance has at least 2 GB RAM

Cannot connect to SQL Server on port 1433

  1. Verify the service is running: systemctl status mssql-server
  2. Check the security group allows port 1433 from your IP
  3. Test local connectivity: sqlcmd -S localhost -U SA
  4. Check SQL Server is listening: ss -tlnp | grep 1433

SA password not working

  1. Retrieve the password again: cat /stage/scripts/mssql_admin_password.log
  2. Ensure you are copying the entire password without extra whitespace
  3. If needed, reset the SA password using mssql-conf:
sudo systemctl stop mssql-server
sudo /opt/mssql/bin/mssql-conf set-sa-password
sudo systemctl start mssql-server

Database size limit reached (10 GB)

  1. Check current database sizes:
SELECT name, size * 8 / 1024 AS SizeMB FROM sys.master_files WHERE type = 0;
GO
  1. Archive or purge old data to free space
  2. If 10 GB is insufficient, consider upgrading to Standard or Enterprise edition

Security Recommendations

  • Restrict port access: Only allow SQL Server port 1433 from trusted application servers or specific IP addresses
  • Change the SA password: Replace the default generated password with a strong, unique password after first login
  • Create application specific logins: Avoid using the SA account for application connections; create dedicated logins with minimal required permissions
  • Enable encrypted connections: Configure SQL Server to use TLS/SSL for client connections
  • Keep SQL Server updated: Apply cumulative updates and security patches when available
  • Monitor access: Review SQL Server error logs regularly for failed login attempts
  • Use AWS security features: Leverage VPC security groups, NACLs, and private subnets to restrict network access
  • Secure the password file: After retrieving the SA password, consider removing or restricting access to /stage/scripts/mssql_admin_password.log
  • Monitor database sizes: Express edition has a 10 GB per database limit; set up alerts before reaching the cap

Support

If you encounter any issues with this product, contact cloudimg support:

  • Email: support@cloudimg.co.uk
  • Website: www.cloudimg.co.uk
  • Support hours: 24/7 with guaranteed 24 hour response SLA