Zurück zur DokumentationSQL Server is Microsoft's most used relational database on Windows Server. This guide covers installation, initial configuration and most common operations.
Available Editions
| Edition | Recommended Use |
|---|---|
| Express | Free, max 10 GB per DB: development and small projects |
| Developer | Free, full features: development/test only |
| Standard | Production, up to 24 cores and 128 GB RAM |
| Enterprise | Production unlimited, advanced features |
Standard or Express is used on typical VPS.
Installation
Download
Download installer from microsoft.com/sql-server/sql-server-downloads.
For production environments without GUI, use silent installation via PowerShell:
# Mount ISO and run setup
# Minimal SQL Server Express installation (no GUI)
.\setup.exe /Q /ACTION=Install /FEATURES=SQLEngine `
/INSTANCENAME=MSSQLSERVER `
/SQLSVCACCOUNT="NT AUTHORITY\NETWORK SERVICE" `
/SQLSYSADMINACCOUNTS="BUILTIN\Administrators" `
/TCPENABLED=1 `
/IACCEPTSQLSERVERLICENSETERMS
Install SQL Server Management Studio (SSMS)
SSMS is the GUI to manage SQL Server: necessary for most operations.
# Download and install SSMS silently
$ssmsUrl = "https://aka.ms/ssmsfullsetup"
Invoke-WebRequest -Uri $ssmsUrl -OutFile "C:\ssms-setup.exe"
Start-Process -FilePath "C:\ssms-setup.exe" -ArgumentList "/install /quiet" -Wait
Initial Configuration
Enable Remote Connections
By default SQL Server accepts only local connections. To enable remote:
# Enable TCP/IP protocol via registry
Import-Module SQLPS -DisableNameChecking
# Or use SQL Server Configuration Manager (GUI):
# Start → SQL Server Configuration Manager → SQL Server Network Configuration
# → Protocols for MSSQLSERVER → TCP/IP → Enable
Then restart service:
Restart-Service MSSQLSERVER
Open Port in Firewall
New-NetFirewallRule -DisplayName "SQL Server" `
-Direction Inbound -Protocol TCP -LocalPort 1433 -Action Allow
Don't expose port 1433 on internet without IP whitelist. SQL Server is frequent target of brute-force attacks. Limit to your application IP.
Enable SQL Authentication (sa)
By default SQL Server uses Windows authentication only. To enable sa account:
-- Run in SSMS as sysadmin
ALTER LOGIN sa ENABLE;
ALTER LOGIN sa WITH PASSWORD = 'YourSecurePassword123!';
GO
-- Enable mixed mode (Windows + SQL Authentication)
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'LoginMode', REG_DWORD, 2;
GO
Then restart SQL Server service.
Database Management
Create a Database
CREATE DATABASE MyDatabase
ON PRIMARY (
NAME = MyDatabase,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\MyDatabase.mdf',
SIZE = 100MB,
MAXSIZE = 10GB,
FILEGROWTH = 100MB
)
LOG ON (
NAME = MyDatabase_log,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\MyDatabase_log.ldf',
SIZE = 20MB,
MAXSIZE = 2GB,
FILEGROWTH = 50MB
);
GO
Create Dedicated User
-- Create SQL login
CREATE LOGIN my_user WITH PASSWORD = 'Password123!';
-- Create user in database
USE MyDatabase;
CREATE USER my_user FOR LOGIN my_user;
-- Assign permissions (db_owner = all permissions on DB)
ALTER ROLE db_owner ADD MEMBER my_user;
-- Or limited permissions:
ALTER ROLE db_datareader ADD MEMBER my_user;
ALTER ROLE db_datawriter ADD MEMBER my_user;
GO
Backup and Restore
Full Backup
BACKUP DATABASE MyDatabase
TO DISK = 'C:\Backup\MyDatabase_full.bak'
WITH FORMAT, COMPRESSION, STATS = 10;
GO
Scheduled Backup via SQL Agent
SQL Agent is included in Standard/Enterprise. For Express, use Task Scheduler (see dedicated guide).
-- Create nightly backup job (via SSMS: SQL Server Agent → Jobs → New Job)
USE msdb;
EXEC sp_add_job @job_name = 'Nightly Backup MyDatabase';
EXEC sp_add_jobstep @job_name = 'Nightly Backup MyDatabase',
@step_name = 'Backup',
@command = 'BACKUP DATABASE MyDatabase TO DISK = ''C:\Backup\MyDatabase.bak'' WITH FORMAT, COMPRESSION';
EXEC sp_add_schedule @schedule_name = 'Every night at 2',
@freq_type = 4, @freq_interval = 1,
@active_start_time = 020000;
EXEC sp_attach_schedule @job_name = 'Nightly Backup MyDatabase',
@schedule_name = 'Every night at 2';
EXEC sp_add_jobserver @job_name = 'Nightly Backup MyDatabase';
GO
Restore from Backup
-- First take DB offline if exists
ALTER DATABASE MyDatabase SET OFFLINE WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE MyDatabase
FROM DISK = 'C:\Backup\MyDatabase_full.bak'
WITH REPLACE, RECOVERY, STATS = 10;
GO
Useful PowerShell Commands
# SQL Server service status
Get-Service MSSQLSERVER
# Restart
Restart-Service MSSQLSERVER
# Size of all databases
Invoke-Sqlcmd -Query "SELECT name, size*8/1024 AS size_MB FROM sys.master_files WHERE type=0" -ServerInstance "localhost"
# List databases
Invoke-Sqlcmd -Query "SELECT name, state_desc FROM sys.databases" -ServerInstance "localhost"
# Active connections
Invoke-Sqlcmd -Query "SELECT * FROM sys.dm_exec_sessions WHERE is_user_process=1" -ServerInstance "localhost"
Periodic Maintenance
-- Rebuild all fragmented indexes (run weekly)
EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=ON)';
-- Update statistics
EXEC sp_updatestats;
-- Check database integrity
DBCC CHECKDB (MyDatabase) WITH NO_INFOMSGS;
GO
To automate maintenance on Express (no SQL Agent), use SSMS Maintenance Plans or create PowerShell scripts scheduled via Task Scheduler.
Verwandte Artikel
Windows VPS: Performance Tweaks
Optimizations and tweaks to improve Windows Server VPS performance
Change Language to English on Windows Server
How to set English as primary language on Windows Server VPS
RDP: Access, Port, Multi-User and Issues
Complete guide to Remote Desktop on Windows Server - connection, port change, multiple simultaneous users and troubleshooting
DeluxHost, gegründet 2023, bietet hochwertige Hosting-Lösungen für verschiedene digitale Anforderungen. Wir bieten Shared Hosting, VPS und dedizierte Server mit erweiterter Sicherheit und globalen Rechenzentren.
