Install SQL Server Express on a Windows VPS

SQL Server Express is Microsoft's free edition of SQL Server. It suits small websites, line-of-business apps and development, within the database size and resource limits Microsoft sets for Express. Check Microsoft's edition comparison for the limits of the version you install.

1. Download and install

1. On the server, open Microsoft's SQL Server downloads page and download Express.
2. Run the installer and choose Basic for a standard setup with sensible defaults, or Custom to choose the install folder and features.
3. Accept the licence terms and wait for the install to finish. Note the instance name it shows, usually SQLEXPRESS.

2. Install SQL Server Management Studio

The installer's final screen links to SQL Server Management Studio (SSMS), a separate free download. Install it, then connect to localhost\SQLEXPRESS with Windows Authentication.

3. Create a database and a login for your app

In SSMS, open a New Query window and run (change the names and use a strong password):

CREATE DATABASE ShopDb;
GO
CREATE LOGIN shop_app WITH PASSWORD = 'Use-A-Long-Unique-Passphrase-1!';
GO
USE ShopDb;
CREATE USER shop_app FOR LOGIN shop_app;
ALTER ROLE db_owner ADD MEMBER shop_app;

SQL logins need SQL Server and Windows Authentication mode: right-click the server in SSMS, open Properties → Security, choose that mode, then restart the SQL Server service.

4. Local apps only? You are done

If your website or app runs on the same server, connect with Server=localhost\SQLEXPRESS;Database=ShopDb;... and do not open any ports.

5. Remote access (only if you need it)

1. Open SQL Server Configuration Manager → SQL Server Network Configuration → Protocols for SQLEXPRESS and enable TCP/IP.
2. In TCP/IP properties, on the IP Addresses tab under IPAll, clear TCP Dynamic Ports and set TCP Port to 1433.
3. Restart the SQL Server (SQLEXPRESS) service.
4. Allow the port only from your own IP address:

New-NetFirewallRule -DisplayName "SQL 1433 from office" -Direction Inbound -Protocol TCP -LocalPort 1433 -RemoteAddress 198.51.100.10 -Action Allow

Troubleshooting

• "A network-related or instance-specific error": check the instance name, that the service is running (Get-Service *SQL*), and for remote connections that TCP/IP is enabled and the firewall allows your IP.
• "Login failed for user": confirm mixed-mode authentication is enabled and the service was restarted.

Next steps

• Back up a Windows VPS (include your .bak files).
• Check CPU, RAM and disk use on Windows