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 → Installation → New SQL Server stand-alone installation.
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).
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.
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.
Data/log/backup directories
It is good practice to distribute data, logs, and backups across different directories (if possible, on different disks/volumes).
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.
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.
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.
Restart the SQL Server instance service.
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).
