SQL Server Installation & Configuration

Pre-Install Planning: Instance Naming

Before you launch setup.exe, decide whether this will be a default instance or a named instance. A default instance is addressable by the server's network name alone and is limited to one per host — MSSQLSERVER is its internal name. A named instance is addressable as SERVERNAME\InstanceName, and a single host can run many of them side by side, each with its own version, service pack level, collation, service account, and port.

The practical reason to default to named instances, even when you're only installing one SQL Server today: you cannot retroactively turn a default instance into a named one, and you cannot add a second default instance later if a new application or team needs an isolated engine on the same box. Consolidation requests — "can we just put this small reporting database on the existing SQL box" — are common, and if the existing installation is a default instance, the only way to isolate the new workload is a named instance anyway, which means you're now running a mixed setup with inconsistent naming conventions across your estate. Standardizing on named instances from the start (for example, SERVERNAME\PROD01) keeps every instance addressed the same way and leaves room for SERVERNAME\PROD02 without any migration.

Named instances rely on the SQL Server Browser service to resolve the dynamic TCP port a client should connect to, since only default instances get the well-known port 1433 by convention. If you go the named-instance route, either leave Browser running and open UDP 1434 on the firewall, or assign the instance a static TCP port and turn Browser off — the latter is generally preferred in production because it removes a service and a port from the attack surface and gives you a predictable port for firewall rules and connection strings.

Service Accounts and Least Privilege

Every SQL Server service — the Database Engine, SQL Server Agent, and any others you enable — runs under a Windows identity, and that identity's rights determine what the service can touch on the host and on the network. Setup defaults to virtual accounts or built-in identities like NT AUTHORITY\NetworkService, which are convenient but wrong for a production-adjacent server for two reasons. First, a built-in account is shared conceptually across whatever else on the box might use it, so you lose the ability to audit "what did the SQL Server service specifically do" versus "what did something else running as NetworkService do." Second, built-in accounts carry broad implicit network privileges (NetworkService authenticates to the network as the machine account), which is more access than the Database Engine actually needs.

The production pattern is a dedicated domain account per service — one for the Database Engine, a separate one for SQL Server Agent — each granted only what that service requires and nothing else. Better still, use a Group Managed Service Account (gMSA) where your Active Directory environment supports it: a gMSA has its password managed and rotated automatically by AD, so you get the isolation of a dedicated identity without the operational burden of manual password rotation (and the outage risk of a service account password expiring unnoticed).

Whichever account type you use, set it through SQL Server Configuration Manager, not the Windows Services control panel applet. Configuration Manager knows how to grant the account the specific local rights the Database Engine needs (log on as a service, and — if you want instant file initialization — SE_MANAGE_VOLUME_NAME via the "Perform Volume Maintenance Tasks" privilege), and it correctly registers the Service Principal Names (SPNs) required for Kerberos authentication. Changing the account through the Services applet instead leaves SPN registration and some ACLs untouched, which surfaces later as NTLM fallback or "double-hop" authentication failures that are painful to diagnose after the fact.

Do not use the same account across multiple SQL Server services or multiple instances unless you have a specific reason to. Separate accounts mean a compromise of the Agent account, for example, doesn't automatically hand over Database Engine rights, and it keeps audit logs attributable to a single service.

Choosing the Right Collation Before You Install

Collation determines sort order, case sensitivity, and accent sensitivity for character data, and it's set at the server level during setup, then inherited by tempdb and every new user database unless explicitly overridden. This is the setting installers most often accept the default on without thinking, and it's also one of the two or three settings on this list that is genuinely difficult to change afterward — treat it as a decision, not a default.

The problem with changing it later is scope. The server collation governs the system databases and any database created without an explicit COLLATE clause. Changing it after the fact means either rebuilding the system databases with Setup.exe /ACTION=REBUILDDATABASE and a new /SQLCOLLATION value — which resets master, model, msdb, and tempdb, wiping out logins, jobs, linked servers, and any server-level configuration you'd already applied — or standing up a new instance with the correct collation and migrating every database into it. Individual user databases can carry their own collation independent of the server default, but mismatched collations across databases cause comparison and join errors ("Cannot resolve the collation conflict") whenever a cross-database query compares string columns with different collations, so a server-wide inconsistency becomes a recurring application bug rather than a one-time fix.

Pick collation based on what the application actually requires, not the SQL Server default. SQL_Latin1_General_CP1_CI_AS (case-insensitive, accent-sensitive) is the common default for US English deployments and is what many older or third-party applications expect, since it matches historical Windows codepage behavior. If the application needs case-sensitive comparisons — common for systems migrated from Oracle or Linux-hosted databases — you need a _CS collation from the start; retrofitting case sensitivity onto an existing case-insensitive database means every string comparison in the application layer that assumed case-insensitivity is now a potential bug. If the application or its data will handle characters outside the Basic Multilingual Plane (certain emoji, some CJK extension characters), you need a collation with supplementary character support, such as one of the _100_...SC collations, because the older Windows collations will silently mishandle those code points. Check the vendor's installation documentation for a required collation before you run setup — most enterprise applications (SharePoint, many ERP products) state one explicitly, and installing with the wrong one is a day-one mistake you'll be living with for years.

Configuring Max Server Memory

This is the single most consequential post-install setting and the one most frequently skipped. Out of the box, max server memory is set to 2147483647 MB — effectively unlimited. SQL Server's buffer pool will grow to consume nearly all physical memory on the host as workload demands it, and by default it does not release that memory back to the OS under pressure. On a dedicated database server this eventually starves the operating system itself, along with SQL Server's own non-buffer-pool memory needs (thread stacks, the plan cache, extended events, linked server providers), producing symptoms like OS-level paging, monitoring agents timing out, or the box becoming unresponsive to RDP under load — well before you'd expect any actual capacity problem.

Set it explicitly right after install, via sp_configure:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory', 24576; -- value in MB
RECONFIGURE;

The value to choose depends on total host RAM and what else runs on the box. A widely used starting formula for a dedicated SQL Server host: reserve 4 GB or 10% of total RAM for the OS, whichever is larger, then reserve additional headroom for non-buffer-pool SQL Server memory (thread stacks scale with max worker threads, and each thread stack is roughly 2 MB on 64-bit; linked servers, CLR, and extended events sessions all add overhead outside the buffer pool). On a 64 GB dedicated database server, a reasonable starting point is leaving 8-10 GB for the OS and non-buffer-pool SQL memory, setting max server memory to roughly 54-56 GB, then watching Memory: Available MBytes and the Memory Manager: Total Server Memory vs. Target Server Memory counters over the first weeks of real load and adjusting. If the box also runs SSIS, SSRS, SSAS, or is a virtualization host sharing RAM with other guests, reserve considerably more — those workloads compete for the same physical memory and don't show up in SQL Server's own memory accounting.

min server memory (also set via sp_configure) is worth setting too, though it solves a different problem: it prevents the buffer pool from being trimmed below a floor under external memory pressure, which matters most on hosts running multiple SQL instances or other memory-hungry services that might otherwise cause SQL Server to shrink its buffer pool repeatedly.

TempDB File Configuration

By default, a fresh install of SQL Server creates tempdb with a single data file. Every session that spills to tempdb — sorts, hash joins, version store for read committed snapshot isolation, temp tables, index rebuilds with SORT_IN_TEMPDB — competes for allocation pages (PFS/GAM/SGAM) within that one file, and under concurrent load that shows up as PAGELATCH contention on those allocation pages, a well-documented and easily avoidable bottleneck.

The fix is multiple tempdb data files, which the SQL Server Setup UI has let you configure directly on the tempdb page since SQL Server 2016 — use it. The commonly cited starting guideline is one data file per logical core, up to 8 files; beyond 8 logical cores, start at 8 and only add more (in increments, monitoring contention) if you still observe allocation-page waits, since more files past a certain point add file-management overhead without proportional benefit. This isn't a hard rule for every workload, but it's a sound default for a general-purpose OLTP instance and far better than the single-file default.

Two details matter more than the file count itself. All tempdb data files must be created at the same initial size and with the same autogrowth increment — tempdb uses a proportional-fill algorithm that distributes writes across files based on free space, and unequal file sizes defeat it, funneling activity back into whichever file has the most free space. And autogrowth should be set as a fixed MB value, not a percentage, so growth events stay predictable in size as the files get larger:

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev,  SIZE = 8192MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb MODIFY FILE (NAME = temp2,    SIZE = 8192MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb MODIFY FILE (NAME = temp3,    SIZE = 8192MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb MODIFY FILE (NAME = temp4,    SIZE = 8192MB, FILEGROWTH = 512MB);

Size the files generously up front — sized correctly at creation, in equal increments — rather than relying on autogrowth events during production load, since each growth event is a brief stall while the file extends (mitigated substantially if the service account has Perform Volume Maintenance Tasks for instant file initialization, which applies to data files but not the log file). Put tempdb on its own fast storage separate from user database files where possible, since it's write-heavy and shared by every concurrent session on the instance.

Basic Hardening: Protocols and Firewall Rules

SQL Server Configuration Manager exposes which network protocols are enabled under SQL Server Network Configuration. A default install typically enables Shared Memory and TCP/IP, with Named Pipes and VIA available but often disabled already; confirm this rather than assuming it. For a server accessed only over the network, disable any protocol you don't use — Named Pipes in particular has historically been a vector worth closing off if nothing in your environment depends on it, and VIA is obsolete and should be off. Shared Memory only serves local connections from the same machine and is low-risk to leave enabled for local administrative access.

Set a static TCP port for the instance rather than relying on dynamic ports, especially for named instances — this makes firewall rules and client connection strings predictable and lets you disable SQL Server Browser (UDP 1434) entirely if only one instance is on the host, removing a service that otherwise exists solely to answer "which port is this named instance listening on" to anyone who asks.

On the Windows Firewall side, add an explicit inbound rule scoped to the TCP port SQL Server listens on (1433 for a default instance or your chosen static port for a named one), restricted to the specific source subnets or hosts that legitimately need to connect — application servers, jump hosts, monitoring systems — rather than opening the port to the entire network. If SQL Server Browser stays enabled, add the corresponding UDP 1434 rule with the same source restrictions. Leave the port closed to anything outside those explicit sources, and don't rely on the firewall as your only control — pair it with SQL Server authentication mode (prefer Windows/AD authentication over mixed mode where the application allows it) and, if mixed mode is required, a strong, rotated password on the sa login or a disabled/renamed sa account entirely.