Standard Settings for a New SQL Server Install

The installer finishing is only the start of a usable server. Standard settings for a new SQL Server install turn a fresh engine into something you can support at 2 a.m.

Record the Starting Point Before Standard Settings

Before tuning anything, record the instance name, build, edition, service accounts, processor layout, physical memory, storage volumes, and intended workload. The same recommended value does not fit every host. A dedicated database server and a server sharing memory with other applications require different limits. Your standard settings document should tell the next DBA why each value was chosen.

I keep the first snapshot before any change. It makes rollback and later comparison possible. A fresh installation can already carry settings from an image or automated setup. Trust the query over the setup checklist. The checklist describes intent; the engine shows current reality.

SELECT @@SERVERNAME AS instance_name,
       SERVERPROPERTY(N'ProductVersion') AS product_version,
       SERVERPROPERTY(N'Edition') AS edition,
       SERVERPROPERTY(N'IsClustered') AS is_clustered;

Set a Memory Boundary

SQL Server needs room for its buffer pool, but Windows and other services need memory too. Configure max server memory from the host’s actual capacity and workload. Leave room for the operating system, SQL Server components outside that limit, backup tools, monitoring, and any colocated service. A copied cap from another machine can produce either waste or pressure.

I check whether the host is dedicated before choosing the cap. Document the calculation and revisit it after the workload settles. A value in sys.configurations is easy to read, so make it part of routine checks. Do not use min server memory as a substitute for capacity planning. The first setting protects the host; the second does not magically allocate physical RAM.

SELECT name, value, value_in_use
FROM sys.configurations
WHERE name IN
(
    N'max server memory (MB)',
    N'min server memory (MB)'
);

Review Parallelism Standard Settings Together

MAXDOP and cost threshold for parallelism work together. MAXDOP limits the number of processors a parallel plan can use. Cost threshold influences when the optimizer considers parallelism. Hardware topology, workload, and application behavior decide their values. Set a reviewed starting point, then observe actual plans and waits. A single number taken from an old blog post is not a design.

I revisit parallelism after representative workload testing. If a server runs a reporting system, its pattern differs from a small transaction system. Record the before and after values when you change them. A plan shift after a deployment is easier to explain when the server settings have a dated history. The setting should serve the workload, not the desire for matching screenshots.

Lay Out tempdb

Check tempdb data-file count, sizes, growth increments, and storage path. Equal-sized data files support balanced allocation. Do not add a pile of files without wait evidence or a version-aware plan. One log file is normal. Make sure the volume has space for expected work, including spills, temporary objects, and version store activity.

The query below shows the actual layout after setup. A template can say four files while the instance has one because setup parameters were missed. Verify sizes and growth units, then test startup behavior. Tempdb is recreated at restart. The layout that exists after a reboot matters more than the one visible immediately after a manual file operation.

SELECT name,
       type_desc,
       physical_name,
       size * 8.0 / 1024 AS size_mb,
       growth,
       is_percent_growth
FROM tempdb.sys.database_files
ORDER BY type_desc, file_id;

Establish Backups Before Data Arrives

A new instance needs a backup destination, schedule, retention plan, and restore test before it hosts important data. Decide recovery objectives with the application owner. Configure full, differential, and log backups as required by the recovery model. Protect encryption keys and backup credentials where they apply. An empty backup folder is not a backup policy.

I ask for a successful restore to a separate environment before calling the server ready. Backup job success only proves that a job ran. It does not prove the files are accessible or that the restore sequence meets the target. Include system databases and server-level configuration in the recovery plan. When an instance fails, the application database is only part of what you need to rebuild.

Put Alerts on a Real Route

Configure Database Mail or the team’s supported notification channel, then test it end to end. SQL Agent alerts for serious errors and job failures need an operator who actually receives messages. A message sent to an abandoned mailbox is an impressive demonstration of silent failure. Include storage capacity, backup failures, corruption signals, and service availability in the monitoring plan.

I test one harmless alert before handing the server to users. Check that the recipient can tell which instance sent it and what action is expected. An alert without context sends the next person into detective work. Document how to silence it during approved maintenance and how to restore it afterward. Monitoring that stays disabled after a change is a common new-server defect.

Check Security and Access

Review authentication mode, enabled logins, sysadmin membership, service account rights, network exposure, and connection encryption. Grant application accounts only the database permissions they require. Disable or remove temporary setup access after testing. Confirm that operations staff have a supported emergency path, with credentials stored according to policy.

I do this before the first application deployment. Retrofitting least privilege after a month of production use is harder because every accidental dependency has had time to settle. Ask who owns the application login and how it will be rotated. An account without an owner eventually becomes everybody’s problem, which is a poor security model.

Finish Standard Settings With a Readiness Check

Reboot or fail over where the change plan requires it, then recheck effective settings, service startup, tempdb layout, backups, and alerts. Run a small application connection test with the intended identity. Save the final snapshot of the standard settings and the restore-test evidence. A server is ready when its controls work together, not when each box was clicked once.

Which failure would leave your team guessing tomorrow? Add one verification step for that risk today. A short readiness record saves hours during the first incident. It also gives future reviewers a reason for every nondefault choice. Setup should leave a server that a different DBA can understand without calling the person who installed it.

Related reading on this blog: Why 'Max Server Memory' Isn’t Always the Limit and 3 Ways to Configure MAXDOP: SQL in Sixty Seconds #166.

A finished installer is not a finished server, it is the starting point for an operational system.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.