SecureAnyCloud
Deployment guide

SQL Server 2019 Web

Overview

SQL Server 2019 Web edition is designed for web hosting providers and websites. It is a low-cost database option for web-facing applications and services.

This guide covers deploying and configuring SQL Server 2019 Web on Linux with the SecureAnyCloud AMI from the AWS Marketplace.

What you get:

  • SQL Server 2019 Web edition installed at /opt/mssql
  • SQL Server database port 1433 preconfigured and listening on boot
  • An SA (System Administrator) account with a randomly generated password
  • The sqlcmd command-line tool for database interaction
  • An OS package update script that keeps 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 SecureAnyCloud support with guaranteed 24 hour response SLA

Edition notes: SQL Server 2019 Web is a low total-cost-of-ownership option for web hosting providers and web application developers. It supports up to 64 GB of buffer pool memory and the lesser of 4 sockets or 16 cores.

Web edition is licensed only for use as a backend database that supports public-facing web services, websites and web applications. Do not use it for internal line-of-business applications or general-purpose database workloads. For those scenarios, consider Standard or Enterprise edition.

Prerequisites

Before you launch the AMI, make sure you have:

  • An active AWS account
  • An active subscription to the SQL Server 2019 Web listing on AWS Marketplace
  • An EC2 key pair for SSH access
  • Familiarity with EC2 instance management and SSH

Recommended instance type: m5.large (2 vCPU, 8 GB RAM) or larger. Web edition workloads benefit from enough memory to cache frequently accessed data from web applications.

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

1

Launch the AMI

  • Open the AWS Marketplace and search for "SQL Server 2019 Web SecureAnyCloud".
  • Click Continue to Subscribe, accept the terms, then click Continue to Configuration.
  • Select your preferred Region and Software Version.
  • Click Continue to Launch.
  • Choose Launch through EC2 for full control over instance configuration.
  • Select your instance type (m5.large recommended).
  • Configure storage by provisioning gp3 volumes that are large enough for your web database workloads.
  • Configure your Security Group with the inbound rules below.
  • 22 TCP from your IP: SSH access
  • 1433 TCP from your application servers: SQL Server database port

Important: Restrict port 1433 to your web application servers or VPC CIDR range only. Never expose the database server directly to the public internet. Place the SQL Server instance in a private subnet and allow connections only from your web tier.

Select your EC2 key pair and launch the instance.

2

Connect via SSH

When your instance is running and has passed both status checks (2/2), connect over SSH as ec2-user:

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

Switch to the root user:

sudo su -

Important: Wait for the instance to reach 2/2 successful status checks before you connect. If you connect earlier, you may see "Permission denied" errors while the instance is still initialising.

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. Read it with:

cat /stage/scripts/mssql_admin_password.log

Store the password somewhere safe. You need it to connect to SQL Server.

4

Connect to SQL Server

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

sqlcmd -S localhost -U SA

When prompted, enter the SA password from Step 3. The prompt changes to:

1>

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

5

Create a database and table

Create a new database:

CREATE DATABASE WebAppDB; GO

Switch to the new database:

USE WebAppDB; 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 the data:

SELECT * FROM Users; GO

Type QUIT to exit the sqlcmd shell.

Server components

  • SQL Server 2019 Web, home directory /opt/mssql

Note: Component versions may be updated on first boot. The automatic OS package update script installs any newer versions it finds in the system package repositories.

Filesystem layout

  • /, 38 GB: root filesystem
  • /boot, 2 GB: operating system kernel files
  • /opt/mssql, 9.8 GB: SQL Server installation directory

Manage the SQL Server service

systemd manages SQL Server, and the service starts automatically on boot.

Check the 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

All three files live in /stage/scripts:

  • initial_boot_update.sh: updates the OS with the latest packages on first boot
  • initial_boot_update.log: output log for the boot update script
  • mssql_admin_password.log: contains the randomly generated SA password

On startup

An OS package update script runs on first boot to bring the image fully up to date. To disable it, remove 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

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

SQL Server service not running

  • Check the service status: systemctl status mssql-server
  • Review the SQL Server error log: cat /var/opt/mssql/log/errorlog
  • Verify that there is enough disk space: df -h /opt/mssql
  • Ensure the instance has at least 2 GB RAM.

Cannot connect to SQL Server on port 1433

  • Verify that the service is running: systemctl status mssql-server
  • Check that the security group allows port 1433 from your application servers.
  • Test local connectivity: sqlcmd -S localhost -U SA
  • Check that SQL Server is listening: ss -tlnp | grep 1433

SA password not working

  • Retrieve the password again: cat /stage/scripts/mssql_admin_password.log
  • Make sure you copy the entire password, without extra whitespace.
  • If needed, reset the SA password with mssql-conf:
sudo systemctl stop mssql-server
sudo /opt/mssql/bin/mssql-conf set-sa-password
sudo systemctl start mssql-server

High memory usage

  • SQL Server Web can use up to 64 GB of buffer pool memory by design.
  • Configure memory limits with mssql-conf:
sudo /opt/mssql/bin/mssql-conf set memory.memorylimitmb 2048
sudo systemctl restart mssql-server

Web application connection pooling issues

  • Make sure your web application uses connection pooling, so it does not exhaust connections.
  • Check active connections: sqlcmd -S localhost -U SA -Q "SELECT COUNT(*) FROM sys.dm_exec_connections"
  • Review and optimise slow queries so that they free connections faster.

Security recommendations

  • Restrict port access. Allow SQL Server port 1433 only from web application servers within your VPC.
  • Use private subnets. Place the SQL Server instance in a private subnet, accessible only from the web application tier.
  • Change the SA password. After your first login, replace the generated password with a strong, unique password.
  • Create application-specific logins. Avoid using the SA account for web application connections. Create dedicated logins with the minimum 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 they become available.
  • Monitor access. Review the SQL Server error logs regularly for failed login attempts.
  • Parameterise queries. Make sure your web application uses parameterised queries to prevent SQL injection attacks.
  • Use AWS security features. Use VPC security groups, NACLs and private subnets to restrict network access.
  • Secure the password file. After you retrieve the SA password, consider removing /stage/scripts/mssql_admin_password.log or restricting access to it.

Support

For help with this product, contact SecureAnyCloud support:

  • Email: sales@secureanycloud.com
  • Website: secureanycloud.com
  • Support hours: 24/7 with guaranteed 24 hour response SLA

Deploy on AWS

Launch SQL Server 2019 Web with 24/7 support from SecureAnyCloud.

View on Marketplace

Need Help?

Our support team is available 24/7.

sales@secureanycloud.com