How to Install SQL Server 2014 on Windows Server 2012 R2

SQL Server 2014 is a natural pairing with Windows Server 2012 R2, offering AlwaysOn Availability Groups, in-memory OLTP (Hekaton), buffer pool extensions, and enhanced cloud integration with Microsoft Azure. Installing SQL Server 2014 correctly on Windows Server 2012 R2 requires attention to prerequisites, service account configuration, collation settings, storage layout, and security hardening. This guide provides a complete, production-ready SQL Server 2014 installation walkthrough for sysadmins deploying a standalone or clustered instance.

Prerequisites

– Windows Server 2012 R2 with all current Windows Updates applied
– SQL Server 2014 media (Standard, Enterprise, or Developer edition)
– Minimum hardware: 4 cores, 8 GB RAM (16 GB+ recommended), 50 GB OS drive
– Dedicated drives for SQL data files, log files, and TempDB (separate spindles or LUNs)
– .NET Framework 3.5 and 4.5 (both required by SQL Server 2014)
– Service accounts created in Active Directory (domain accounts, not local accounts)
– Windows Firewall rules planned for SQL traffic
– SQL Server 2014 requires no hotfixes on WS2012 R2 — it’s fully supported

Step 1: Prepare the Server

# Install .NET Framework 3.5 (required by SQL Server installer)
Install-WindowsFeature -Name NET-Framework-Core -Source "D:sourcessxs" -IncludeAllSubFeature

# Install .NET Framework 4.5 (already included in WS2012 R2, verify)
Get-WindowsFeature NET-Framework-45-Core | Select-Object DisplayName, Installed

# Configure power plan to High Performance
powercfg /setactive 8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c
powercfg /query scheme_current | findstr /i "power scheme"

# Disable unnecessary services for a SQL Server
Stop-Service -Name WSearch, SysMain -PassThru  # Windows Search, Superfetch
Set-Service -Name WSearch, SysMain -StartupType Disabled

# Configure virtual memory / page file
# For SQL Server, set to system-managed or fixed value = 1x RAM
# SQL Server manages its own buffer pool memory

# Verify disk layout
Get-Disk | Select-Object Number, FriendlyName, OperationalStatus | Format-Table -AutoSize
Get-Volume | Select-Object DriveLetter, FileSystemLabel, @{n='FreeGB';e={[math]::Round($_.SizeRemaining/1GB,1)}} | Format-Table -AutoSize

Step 2: Create SQL Server Service Accounts

Import-Module ActiveDirectory

# Create SQL Server service accounts in AD
$sqlAccounts = @(
    @{
        Sam = "svc_SQLEngine"
        Name = "SQL Server Database Engine Service"
        Desc = "SQL Server database engine service account for SQLPROD01"
    },
    @{
        Sam = "svc_SQLAgent"
        Name = "SQL Server Agent Service"
        Desc = "SQL Server Agent service account for SQLPROD01"
    },
    @{
        Sam = "svc_SQLBrowser"
        Name = "SQL Server Browser Service"
        Desc = "SQL Browser service account"
    }
)

$pwd = ConvertTo-SecureString "SQL$erv1ceP@ss!" -AsPlainText -Force
foreach ($acct in $sqlAccounts) {
    New-ADUser -SamAccountName $acct.Sam `
        -Name $acct.Name `
        -AccountPassword $pwd `
        -PasswordNeverExpires $true `
        -CannotChangePassword $true `
        -Enabled $true `
        -Description $acct.Desc `
        -Path "OU=ServiceAccounts,OU=Users,DC=corp,DC=local"
    Write-Host "Created: $($acct.Sam)" -ForegroundColor Green
}

# Grant "Log on as a service" right to SQL service accounts
# Configure via Group Policy:
# Computer Configuration > Windows Settings > Security Settings > 
# Local Policies > User Rights Assignment > "Log on as a service"
# Add: CORPsvc_SQLEngine, CORPsvc_SQLAgent

# Or via ntrights.exe (from Windows Server Resource Kit):
# ntrights +r SeServiceLogonRight -u "CORPsvc_SQLEngine"

Step 3: Configure Storage for SQL Server

# Create SQL Server directories on appropriate volumes
# Best practice: separate volumes for Data, Logs, TempDB, Backups

$sqlDirs = @(
    "D:SQLData",           # Data files (.mdf, .ndf)
    "E:SQLLogs",           # Log files (.ldf)
    "F:TempDB",            # TempDB files
    "G:SQLBackups",        # Backup files
    "C:SQLInstall"         # Installation temp
)

foreach ($dir in $sqlDirs) {
    New-Item -ItemType Directory -Path $dir -Force
    Write-Host "Created: $dir"
}

# Set NTFS allocation unit size to 64KB for SQL data volumes
# (Must be done BEFORE formatting — reconfigure existing volumes via diskpart or reformat)
# For existing volumes, SQL Server still works but 64KB AUS is optimal for I/O alignment

# Set permissions on SQL directories
$sqlEngine = "CORPsvc_SQLEngine"
$sqlAgent  = "CORPsvc_SQLAgent"

foreach ($dir in @("D:SQLData","E:SQLLogs","F:TempDB","G:SQLBackups")) {
    $acl = Get-Acl $dir
    $rule = New-Object System.Security.AccessControl.FileSystemAccessRule(
        $sqlEngine, "FullControl", "ContainerInherit,ObjectInherit", "None", "Allow")
    $acl.AddAccessRule($rule)
    Set-Acl $dir $acl
    Write-Host "Set permissions on $dir for $sqlEngine"
}

Step 4: Run SQL Server 2014 Unattended Installation

For repeatable, documented installations, use a configuration file rather than the GUI wizard:

# Create SQL Server 2014 configuration file
$configContent = @"
[OPTIONS]
ACTION="Install"
FEATURES=SQLENGINE,REPLICATION,FULLTEXT,SSMS,ADV_SSMS,CONN,BC,SDK
INSTANCENAME="MSSQLSERVER"
INSTANCEID="MSSQLSERVER"
QUIET="False"
QUIETSIMPLE="False"
INDICATEPROGRESS="True"
UPDATEENABLED="True"
UPDATESOURCE="MU"
HELP="False"
CONFIGURATIONFILE=""
INSTALLSHAREDDIR="C:Program FilesMicrosoft SQL Server"
INSTALLSHAREDWOWDIR="C:Program Files (x86)Microsoft SQL Server"
INSTANCEDIR="C:Program FilesMicrosoft SQL Server"
SQLSVCACCOUNT="CORPsvc_SQLEngine"
SQLSVCPASSWORD="SQL$erv1ceP@ss!"
SQLSVCSTARTUPTYPE="Automatic"
AGTSVCACCOUNT="CORPsvc_SQLAgent"
AGTSVCPASSWORD="SQL$erv1ceP@ss!"
AGTSVCSTARTUPTYPE="Automatic"
BROWSERSVCSTARTUPTYPE="Disabled"
SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"
SQLSYSADMINACCOUNTS="BUILTINAdministrators" "CORPSQL-Admins"
SQLTEMPDBDIR="F:TempDB"
SQLTEMPDBLOGDIR="F:TempDB"
SQLTEMPDBFILECOUNT="4"
SQLTEMPDBFILESIZE="1024"
SQLTEMPDBFILEGROWTH="512"
SQLTEMPDBLOGFILESIZE="256"
SQLTEMPDBLOGFILEGROWTH="128"
SQLUSERDBDIR="D:SQLData"
SQLUSERDBLOGDIR="E:SQLLogs"
SQLBACKUPDIR="G:SQLBackups"
SQLTEMPDBFILECOUNT="4"
SQLMAXDOP=""
NPENABLED="0"
TCPENABLED="1"
SECURITYMODE="SQL"
SAPWD=""
IACCEPTSQLSERVERLICENSETERMS="True"
"@

$configContent | Out-File "C:SQLInstallsql2014_config.ini" -Encoding ASCII

# Run the installation with the config file
$sqlMedia = "D:"  # SQL Server 2014 media drive letter
Start-Process -FilePath "$sqlMediaSetup.exe" `
    -ArgumentList "/Q /ConfigurationFile=C:SQLInstallsql2014_config.ini" `
    -Wait -PassThru

Step 5: Post-Installation Configuration

# Connect to SQL Server and perform post-installation configuration
# Using SQLPS module or Invoke-Sqlcmd

Import-Module SQLPS -DisableNameChecking

$serverName = "SQLPROD01"

# Configure max server memory (leave 10% for OS, maximum for SQL)
$totalRAMGB = [math]::Round((Get-WmiObject Win32_ComputerSystem).TotalPhysicalMemory / 1GB)
$maxMemoryMB = ($totalRAMGB - [math]::Max(1, [math]::Round($totalRAMGB * 0.1))) * 1024

Invoke-Sqlcmd -ServerInstance $serverName -Query @"
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'max server memory (MB)', $maxMemoryMB
RECONFIGURE
"@
Write-Host "Max server memory set to ${maxMemoryMB} MB"

# Configure max degree of parallelism (MAXDOP)
# General rule: number of physical cores per NUMA node, max 8
$physicalCores = (Get-WmiObject Win32_Processor).NumberOfCores
$maxdop = [math]::Min(8, $physicalCores)
Invoke-Sqlcmd -ServerInstance $serverName -Query @"
EXEC sp_configure 'max degree of parallelism', $maxdop
RECONFIGURE
"@

# Enable remote DAC (Dedicated Admin Connection)
Invoke-Sqlcmd -ServerInstance $serverName -Query @"
EXEC sp_configure 'remote admin connections', 1
RECONFIGURE
"@

# Disable SA account and create named admin account
Invoke-Sqlcmd -ServerInstance $serverName -Query @"
ALTER LOGIN [sa] DISABLE
ALTER LOGIN [sa] WITH PASSWORD = N'$(New-Guid)'
CREATE LOGIN [CORPSQL-Admins] FROM WINDOWS
ALTER SERVER ROLE [sysadmin] ADD MEMBER [CORPSQL-Admins]
"@

Step 6: Configure TempDB Optimally

# TempDB best practices: one file per logical processor (max 8)
$logicalCores = [math]::Min(8, (Get-WmiObject Win32_Processor).NumberOfLogicalProcessors)

$tempDbQuery = @"
-- Check current TempDB configuration
SELECT name, physical_name, size, max_size, growth, is_percent_growth
FROM sys.master_files
WHERE database_id = DB_ID('tempdb')

-- Add TempDB data files if fewer than $logicalCores
"@

$currentFiles = Invoke-Sqlcmd -ServerInstance $serverName -Query @"
SELECT COUNT(*) as FileCount FROM sys.master_files 
WHERE database_id = DB_ID('tempdb') AND type = 0
"@

Write-Host "Current TempDB files: $($currentFiles.FileCount) (Target: $logicalCores)"

# Add additional TempDB files to match core count
for ($i = 2; $i -le $logicalCores; $i++) {
    Invoke-Sqlcmd -ServerInstance $serverName -Query @"
ALTER DATABASE tempdb ADD FILE (
    NAME = N'tempdev$i',
    FILENAME = N'F:TempDBtempdev$i.ndf',
    SIZE = 1024MB, FILEGROWTH = 512MB
)
"@
    Write-Host "Added TempDB file $i"
}

Step 7: Configure SQL Server Firewall Rules

# Create firewall rules for SQL Server connectivity
New-NetFirewallRule -DisplayName "SQL Server Default Instance" `
    -Direction Inbound -Protocol TCP -LocalPort 1433 `
    -Action Allow -Profile Domain -Description "SQL Server default instance"

New-NetFirewallRule -DisplayName "SQL Server Browser Service" `
    -Direction Inbound -Protocol UDP -LocalPort 1434 `
    -Action Allow -Profile Domain -Description "SQL Server Browser for named instance discovery"

New-NetFirewallRule -DisplayName "SQL Server DAC" `
    -Direction Inbound -Protocol TCP -LocalPort 1434 `
    -Action Allow -Profile Domain -Description "SQL Server Dedicated Admin Connection"

# If using AlwaysOn AG
New-NetFirewallRule -DisplayName "SQL Server AG Endpoint" `
    -Direction Inbound -Protocol TCP -LocalPort 5022 `
    -Action Allow -Profile Domain -Description "SQL Server AlwaysOn AG mirroring endpoint"

# Verify SQL is listening
netstat -an | findstr ":1433"

Verification

Import-Module SQLPS -DisableNameChecking

$srv = "SQLPROD01"

# Verify SQL Server is running
Get-Service -Name MSSQLSERVER | Select-Object Status, StartType

# Verify version
Invoke-Sqlcmd -ServerInstance $srv -Query "SELECT @@VERSION AS SQLVersion, @@SERVERNAME AS ServerName"

# Verify configuration
Invoke-Sqlcmd -ServerInstance $srv -Query @"
SELECT name, value_in_use 
FROM sys.configurations 
WHERE name IN ('max server memory (MB)','max degree of parallelism','remote admin connections')
ORDER BY name
"@

# Check TempDB files
Invoke-Sqlcmd -ServerInstance $srv -Query @"
SELECT name, physical_name, size*8/1024 as SizeMB
FROM sys.master_files WHERE database_id = 2
"@

# Check disk space on SQL volumes
Get-Volume | Where-Object { $_.DriveLetter -in @('D','E','F','G') } |
    Select-Object DriveLetter, FileSystemLabel, 
        @{n='SizeGB';e={[math]::Round($_.Size/1GB,1)}},
        @{n='FreeGB';e={[math]::Round($_.SizeRemaining/1GB,1)}} |
    Format-Table -AutoSize

Summary

Installing SQL Server 2014 on Windows Server 2012 R2 properly requires more than running Setup.exe. Dedicated service accounts with minimal privileges, separate storage volumes for data, logs, TempDB, and backups with 64KB NTFS allocation units, an unattended configuration file for repeatable deployments, optimal memory settings leaving adequate RAM for the OS, MAXDOP tuned to core count, multiple TempDB files matching logical processors, and appropriate firewall rules together create a production-ready SQL Server platform. The combination of SQL Server 2014’s in-memory OLTP, enhanced AlwaysOn, and buffer pool extensions with Windows Server 2012 R2’s Storage Spaces, Failover Clustering, and enhanced networking delivers the highest-performing Windows database platform in the 2012-2014 era.

Links

Newsletter

Contact