Install SQL Server on Windows Server VPS

Microsoft SQL Server is a enterprise relational database platform used by countless applications. This guide covers installing SQL Server 2022 on your Windows Server VPS, configuring it for production use, and setting up backups, security, and performance monitoring.

Download and Install SQL Server 2022

# Download SQL Server 2022 Express (free) or Developer Edition
# Using PowerShell to download the installer
Invoke-WebRequest -Uri "https://go.microsoft.com/fwlink/?linkid=2215158" `
    -OutFile "C:\temp\sql-setup.exe"

# Run silent installation — Developer Edition
C:\temp\sql-setup.exe /ACTION=Install `
    /FEATURES=SQLEngine,FullText `
    /INSTANCENAME=MSSQLSERVER `
    /SQLSYSADMINACCOUNTS="BUILTIN\Administrators" `
    /SECURITYMODE=SQL `
    /SAPWD="YourStr0ngP@ssword!" `
    /TCPENABLED=1 `
    /SQLSVCSTARTUPTYPE=Automatic `
    /AGTSVCSTARTUPTYPE=Automatic `
    /IACCEPTSQLSERVERLICENSETERMS `
    /SQLTEMPDBFILECOUNT=4 `
    /SQLTEMPDBFILESIZE=256 `
    /SQLTEMPDBFILEGROWTH=128 `
    /QUIET

# Install SQL Server Management Studio (SSMS)
Invoke-WebRequest -Uri "https://aka.ms/ssmsfullsetup" -OutFile "C:\temp\SSMS-Setup.exe"
Start-Process "C:\temp\SSMS-Setup.exe" -ArgumentList "/install /quiet /norestart" -Wait

Post-Installation Configuration

# Import SQL Server module
Import-Module SqlServer

# Enable TCP/IP protocol
$smo = 'Microsoft.SqlServer.Management.Smo.'
$wmi = New-Object ($smo + 'Wmi.ManagedComputer')
$tcp = $wmi.GetSmoObject("ManagedComputer[@Name='$env:COMPUTERNAME']/ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']")
$tcp.IsEnabled = $true
$tcp.Alter()

# Set TCP port to 1433
$ipAll = $tcp.IPAddresses | Where-Object { $_.Name -eq "IPAll" }
$ipAll.IPAddressProperties["TcpPort"].Value = "1433"
$ipAll.IPAddressProperties["TcpDynamicPorts"].Value = ""
$tcp.Alter()

# Restart SQL Server
Restart-Service MSSQLSERVER

# Configure firewall
New-NetFirewallRule -DisplayName "SQL Server" `
    -Direction Inbound -Protocol TCP -LocalPort 1433 -Action Allow

# Set max memory (leave 4GB for OS)
$totalRAM = (Get-CimInstance Win32_PhysicalMemory | Measure-Object Capacity -Sum).Sum / 1MB
$sqlMaxMemory = [math]::Max($totalRAM - 4096, 2048)

Invoke-Sqlcmd -Query "
    EXEC sp_configure 'show advanced options', 1;
    RECONFIGURE;
    EXEC sp_configure 'max server memory (MB)', $sqlMaxMemory;
    RECONFIGURE;
"

Create Databases and Users

# Create a production database
Invoke-Sqlcmd -Query "
    CREATE DATABASE MyAppDB
    ON PRIMARY (
        NAME = 'MyAppDB_Data',
        FILENAME = 'D:\SQLData\MyAppDB.mdf',
        SIZE = 1GB,
        FILEGROWTH = 256MB
    )
    LOG ON (
        NAME = 'MyAppDB_Log',
        FILENAME = 'D:\SQLLogs\MyAppDB_log.ldf',
        SIZE = 256MB,
        FILEGROWTH = 128MB
    );
"

# Create application login and user
Invoke-Sqlcmd -Query "
    CREATE LOGIN AppUser WITH PASSWORD = 'SecureAppP@ss2026!';
    USE MyAppDB;
    CREATE USER AppUser FOR LOGIN AppUser;
    ALTER ROLE db_datareader ADD MEMBER AppUser;
    ALTER ROLE db_datawriter ADD MEMBER AppUser;
    GRANT EXECUTE TO AppUser;
"

# Create read-only login for reporting
Invoke-Sqlcmd -Query "
    CREATE LOGIN ReportUser WITH PASSWORD = 'Rep0rtP@ss2026!';
    USE MyAppDB;
    CREATE USER ReportUser FOR LOGIN ReportUser;
    ALTER ROLE db_datareader ADD MEMBER ReportUser;
"

Backup Configuration

# Create backup directories
New-Item -ItemType Directory -Path "D:\SQLBackups\Full", "D:\SQLBackups\Diff", "D:\SQLBackups\Log" -Force

# Full backup script
$BackupScript = @'
$databases = Invoke-Sqlcmd -Query "SELECT name FROM sys.databases WHERE database_id > 4 AND state = 0"
$date = Get-Date -Format "yyyyMMdd_HHmmss"
foreach ($db in $databases) {
    $backupFile = "D:\SQLBackups\Full\$($db.name)_FULL_$date.bak"
    Invoke-Sqlcmd -Query "
        BACKUP DATABASE [$($db.name)]
        TO DISK = '$backupFile'
        WITH COMPRESSION, CHECKSUM, INIT;
    "
    Write-Host "Backed up: $($db.name) -> $backupFile"
}
# Clean up backups older than 7 days
Get-ChildItem "D:\SQLBackups\Full" -Filter "*.bak" |
    Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-7) } |
    Remove-Item
'@

# Schedule backups
$Action = New-ScheduledTaskAction -Execute "PowerShell.exe" `
    -Argument "-ExecutionPolicy Bypass -Command $BackupScript"
$Trigger = New-ScheduledTaskTrigger -Daily -At "2:00AM"
Register-ScheduledTask -TaskName "SQL Full Backup" `
    -Action $Action -Trigger $Trigger -User "SYSTEM" -RunLevel Highest

Performance Monitoring

# Check top wait statistics
Invoke-Sqlcmd -Query "
    SELECT TOP 10
        wait_type,
        waiting_tasks_count,
        wait_time_ms / 1000.0 AS wait_time_seconds,
        signal_wait_time_ms / 1000.0 AS signal_wait_seconds
    FROM sys.dm_os_wait_stats
    WHERE wait_type NOT LIKE '%SLEEP%'
    AND wait_type NOT LIKE '%IDLE%'
    AND wait_type NOT LIKE '%QUEUE%'
    ORDER BY wait_time_ms DESC;
"

# Find slow queries
Invoke-Sqlcmd -Query "
    SELECT TOP 10
        qs.total_elapsed_time / qs.execution_count / 1000 AS avg_ms,
        qs.execution_count,
        SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
            ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS query_text
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
    ORDER BY qs.total_elapsed_time / qs.execution_count DESC;
"

Best Practices

• Separate data and log files onto different disks for better performance
• Set max memory to prevent SQL Server from consuming all available RAM
• Configure TempDB with multiple files equal to the number of CPU cores (up to 8)
• Use Windows Authentication when possible, mixed mode only when needed
• Regular backups: Full daily, differential every 4 hours, log every 15 minutes
• Monitor wait statistics and slow queries for performance tuning
• Keep SQL Server patched with the latest cumulative updates