How to Install and Configure MSSQL Server

How to Install and Configure MSSQL Server

Microsoft SQL Server is a powerful relational database management system commonly used with Windows Server environments. This guide covers installing SQL Server on your Kazepute Breeze, performing initial configuration, and setting up remote access for your applications.

Prerequisites

• A Kazepute Breeze running Windows Server 2019 or 2022
• At least 4 GB of RAM (8 GB or more recommended)
• Administrator access
• .NET Framework 4.6 or later (included in Windows Server)

Download SQL Server

Download SQL Server from the official Microsoft website. For testing and development, SQL Server Express or Developer edition are free. For production workloads, use Standard or Enterprise edition with an appropriate license.

Install SQL Server via Command Line

For automated or unattended installations, use the command-line installer:

# Run the SQL Server installer in unattended mode
.\Setup.exe /Q /IACCEPTSQLSERVERLICENSETERMS /ACTION=Install `
    /FEATURES=SQLENGINE,FULLTEXT `
    /INSTANCENAME=MSSQLSERVER `
    /SQLSVCACCOUNT="NT Service\MSSQLSERVER" `
    /SQLSYSADMINACCOUNTS="BUILTIN\Administrators" `
    /SECURITYMODE=SQL `
    /SAPWD="YourStrongSAPassword123!" `
    /TCPENABLED=1 `
    /SQLBACKUPDIR="D:\SQLBackups" `
    /SQLUSERDBDIR="D:\SQLData" `
    /SQLUSERDBLOGDIR="D:\SQLLogs"

Enable TCP/IP for Remote Connections

By default, SQL Server only accepts local connections. Enable TCP/IP access:

# Import the SQL Server module
Import-Module SQLPS -DisableNameChecking

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

# Restart SQL Server service
Restart-Service -Name MSSQLSERVER

Configure the Firewall

Allow SQL Server traffic through Windows Firewall:

# Allow SQL Server default port
New-NetFirewallRule -DisplayName "SQL Server" -Direction Inbound -Protocol TCP -LocalPort 1433 -Action Allow

# Allow SQL Server Browser (for named instances)
New-NetFirewallRule -DisplayName "SQL Server Browser" -Direction Inbound -Protocol UDP -LocalPort 1434 -Action Allow

Basic SQL Server Configuration

Connect to your SQL Server instance using sqlcmd or SQL Server Management Studio (SSMS):

# Connect via sqlcmd
sqlcmd -S localhost -U sa -P "YourStrongSAPassword123!"

# Set max memory (leave some for the OS)
EXEC sp_configure 'max server memory', 6144;  -- 6 GB for an 8 GB Breeze
RECONFIGURE;
GO

# Enable backup compression by default
EXEC sp_configure 'backup compression default', 1;
RECONFIGURE;
GO

# Set cost threshold for parallelism
EXEC sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE;
GO

Create a Database and User

# Create a new database
CREATE DATABASE MyAppDB;
GO

# Create a login and user
CREATE LOGIN appuser WITH PASSWORD = 'AppUserPass456!';
GO

USE MyAppDB;
CREATE USER appuser FOR LOGIN appuser;
ALTER ROLE db_owner ADD MEMBER appuser;
GO

Configure Automated Backups

# Full backup
BACKUP DATABASE MyAppDB TO DISK = 'D:\SQLBackups\MyAppDB_Full.bak' WITH COMPRESSION, INIT;
GO

# Schedule via SQL Server Agent or Windows Task Scheduler
# Using sqlcmd in a scheduled task:
# sqlcmd -S localhost -U sa -P "Password" -Q "BACKUP DATABASE MyAppDB TO DISK='D:\SQLBackups\MyAppDB.bak' WITH COMPRESSION"

Best Practices

• Place data files, log files, and backups on separate disks for best performance.
• Set the maximum server memory to leave at least 2 GB for the OS.
• Use SQL Server Authentication mode only when needed; prefer Windows Authentication.
• Disable the SA account or rename it for production servers.
• Enable transparent data encryption (TDE) for sensitive databases.
• Schedule regular full, differential, and transaction log backups.
• Monitor the SQL Server error log and set up alerts for critical events.