SQL Server Express is Microsoft’s free edition of SQL Server. It’s the natural database for small .NET and ASP.NET applications, accounting and line-of-business software that require SQL Server, and development environments. This guide installs it on a Windows Server VPS and configures it for secure use.

Know the Express limits

Express is free for production use, with limits per instance (check the current version’s documentation for exact numbers):

  • Database size: 10 GB per database (data files).
  • Memory: the buffer pool uses a limited amount of RAM (about 1.4 GB).
  • CPU: the lesser of 1 socket or 4 cores.
  • No SQL Server Agent — so no built-in scheduled jobs (backups are scheduled another way, below).

When you outgrow these, Standard edition (licensed) lifts them. For development, the free Developer edition has all features but isn’t licensed for production.

1. Download and install

Download the SQL Server Express installer from Microsoft’s website on the server, run it and choose:

  • Basic — quick defaults, or
  • Custom — choose features and the instance name (recommended so you understand the settings).

During a custom install:

  • Instance name: the default for Express is SQLEXPRESS (named instance). Connections then use SERVERNAME\SQLEXPRESS.
  • Authentication mode: choose Mixed Mode if applications will log in with SQL logins; set a strong sa password. Windows-only authentication is more secure if all clients are on the same machine or domain.
  • Add your Windows admin account as a SQL Server administrator.

2. Install SQL Server Management Studio (SSMS)

SSMS is a separate free download from Microsoft — the main tool for managing databases. Connect to localhost\SQLEXPRESS with Windows authentication.

Command-line alternative: sqlcmd -S localhost\SQLEXPRESS -E.

3. Create a database and an application login

CREATE DATABASE AppDb;
GO
CREATE LOGIN app_user WITH PASSWORD = 'A-Long-Random-Password!2026', CHECK_POLICY = ON;
GO
USE AppDb;
CREATE USER app_user FOR LOGIN app_user;
ALTER ROLE db_datareader ADD MEMBER app_user;
ALTER ROLE db_datawriter ADD MEMBER app_user;
-- add db_ddladmin only if the app runs its own migrations
GO

Applications should never connect as sa. Consider disabling sa entirely once you have another admin login:

ALTER LOGIN sa DISABLE;

4. Local apps vs remote access

If your application runs on the same VPS, keep it simple: connect via localhost\SQLEXPRESS and leave network access closed.

If another server must connect, configure TCP/IP — carefully.

Enable TCP/IP and a fixed port

Express disables network protocols by default and uses a dynamic port for named instances.

  1. Open SQL Server Configuration Manager → SQL Server Network Configuration → Protocols for SQLEXPRESS → enable TCP/IP.
  2. TCP/IP properties → IP Addresses → IPAll: clear TCP Dynamic Ports, set TCP Port to 1433 (or another fixed port).
  3. Restart the SQL Server (SQLEXPRESS) service.

Firewall: one source IP only

New-NetFirewallRule -DisplayName "SQL Server from app" -Direction Inbound -Protocol TCP `
  -LocalPort 1433 -RemoteAddress 203.0.113.5 -Action Allow

Never expose 1433 to the whole internet. SQL Server ports are scanned and brute-forced constantly. For admin access from your PC, use a VPN or an SSH tunnel through the built-in OpenSSH server instead. See Windows Firewall with PowerShell.

Connection string example:

Server=203.0.113.10,1433;Database=AppDb;User Id=app_user;Password=…;Encrypt=True;TrustServerCertificate=False;

Encryption requires a certificate the client trusts; for testing, TrustServerCertificate=True skips validation (not for production over the internet).

5. Backups without SQL Agent

Create a backup script, C:\Scripts\backup.sql:

DECLARE @file NVARCHAR(260) = N'D:\Backups\AppDb_' + FORMAT(GETDATE(), 'yyyyMMdd_HHmm') + N'.bak';
BACKUP DATABASE AppDb TO DISK = @file WITH COMPRESSION, CHECKSUM, INIT;

(Backup compression may not be supported on Express in all versions; remove COMPRESSION if it errors.)

Schedule it with Task Scheduler:

$action  = New-ScheduledTaskAction -Execute "sqlcmd.exe" -Argument '-S localhost\SQLEXPRESS -E -i C:\Scripts\backup.sql'
$trigger = New-ScheduledTaskTrigger -Daily -At 2:30am
Register-ScheduledTask -TaskName "SQL Backup AppDb" -Action $action -Trigger $trigger -User "SYSTEM" -RunLevel Highest

Delete old backups with a second task, and copy backups off the server (to object storage or another machine). See Windows Task Scheduler.

Test restores in SSMS (Restore Database) or with RESTORE VERIFYONLY.

6. Keep it patched

Install SQL Server cumulative updates (via Microsoft Update or manual download) along with Windows updates — see Windows Update with PowerShell.

Memory tip

On a small VPS, limit SQL Server’s memory so Windows and your application keep enough (Server Properties → Memory → Maximum server memory). Express already caps the buffer pool, but setting it explicitly avoids surprises.

Alternatives

If your application supports it, PostgreSQL or MySQL on Linux is often cheaper and lighter — see MySQL vs PostgreSQL. For apps that require SQL Server, a Windows VPS with Express is a cost-effective start.

  • SQL Server
  • Windows Server
  • Databases