...

*Cube-Host– full cloud services!!

Installing MS SQL Server on Windows VPS

Installing MS SQL Server on Windows VPS

What you will need

  • Windows VPS (recommended: separate disk for data and backups).
  • Local/domain administrator rights, ISO/distribution of SQL Server 2019/2022.
  • (Preferable) service account for SQL services (MSA/gMSA or domain).

Running the installer

Run setup.exe → InstallationNew SQL Server stand-alone installation.

Windows VPS 1
Windows VPS 2

Components (Feature Selection)

For the server role, Database Engine Services is the minimum requirement. Depending on the tasks, Replication, Full-Text, SSIS, etc. are added.

Instance

Leave Default instance (MSSQLSERVER) or create a Named instance (e.g., DEV).

Windows VPS 4

Services and Collation (Service Accounts / Collation)

  • On the Service Accounts tab, set up accounts for services (preferably MSA/gMSA/domain), SQL Server Agent — Automatic.
  • On the Collation tab, Cyrillic_General_CI_AS is often selected for data.
Windows VPS 5
Windows VPS 6

The wizard has an option called Grant Perform Volume Maintenance Task… — this is Instant File Initialization (IFI), which speeds up the creation/growth of data files (with security nuances). Recommendations and details can be found in Microsoft documentation.

Database Engine Configuration

Authentication mode and administrators

Mixed Mode (Windows + sa login for compatibility with external applications) is often chosen. Set a strong sa password and add your SQL administrators.

Windows VPS 7

Data/log/backup directories

It is good practice to distribute data, logs, and backups across different directories (if possible, on different disks/volumes).

Windows VPS 8

TempDB

Specify the number of files, sizes, and growth. In newer versions, the installer adds the tempdb file(s) itself, but it is better to check the settings. Microsoft’s general recommendation: up to 8 logical processors — the same number of tempdb data files; if there are more than 8 logical processors — start with 8 files and increase if necessary, monitoring allocation competition.

Windows VPS 9

Microsoft’s tempdb guide: recommendations on the number of files and initial values.

Finish the installation and install the client

Wait for the Complete step. Install SQL Server Management Studio (SSMS) — in the installation center, there is an item called Install SQL Server Management Tools (leads to the SSMS downloader).

Enable network access (TCP/IP) and set the port

Open SQL Server Configuration Manager → SQL Server Network Configuration → Protocols for <INSTANCE> and enable TCP/IP.

Windows VPS 10

Set a static port (usually 1433, but a non-standard port is also possible) in the IP Addresses tab → IPAll section: clear TCP Dynamic Ports (empty) and specify TCP Port.

Windows VPS 11
Windows VPS 12

Restart the SQL Server instance service.

Windows VPS 13

If you leave a dynamic port for a named instance, clients will need the SQL Server Browser service (via UDP/1434), otherwise connect with an explicit port. For a detailed analysis, see WinITPro.

Windows firewall rules

Open incoming connections for your instance’s TCP port (example — 1433). If you are using Browser, add UDP/1434.

  # Port of the copy (replace if necessary)
New-NetFirewallRule -DisplayName "SQL Server (TCP 1433)" -Direction Inbound -Protocol TCP -LocalPort 1433 -Action Allow

# For SQL Server Browser (if needed for dynamic ports)
New-NetFirewallRule -DisplayName "SQL Server Browser (UDP 1434)" -Direction Inbound -Protocol UDP -LocalPort 1434 -Action Allow  

Current Microsoft recommendations regarding ports and rules can be found in the official guide.

Connection and quick diagnostics

From the client/jump host:

  # default instance (standard port)
Test-NetConnection <vps-hostname> -Port 1433

# named instance with its own port
Test-NetConnection <vps-hostname> -Port <your_port>  

In SSMS: for a non-standard port, format the string as <host>\<instance>,<port>.

Check the version and basic parameters:

  SELECT @@VERSION AS version;
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure;  -- review current settings  

MAXDOP (example): Follow Microsoft’s recommendations and load, then record the value:

  EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'max degree of parallelism', 8; RECONFIGURE;
  

Microsoft guide on configuring MAXDOP and recommendations.

Common installation issues

  • Error “Could not find the Database Engine startup handle” — often related to TLS 1.2 being disabled. Enable TLS 1.2 support in Windows and repeat the installation.
  • The installer “freezes” (Generating native images, etc.) — there are proven steps to resolve this from WinITPro.

Mini checklist after installation

  • Mixed Mode is enabled, the sa password is complex, logins/roles are issued.
  • TCP/IP is active, the port is static and documented.
  • Firewall rules are created, access is restricted by networks/addresses where possible.
  • tempdb is configured (number of files/growth/location).
  • IFI is enabled (in accordance with company security policy).