SQL Server Hosting and Configuration Standards #

Microsoft SQL Server often supports the systems a business depends on most, including accounting, manufacturing, document management, reporting, customer records, scheduling, inventory, and other line-of-business applications. When SQL Server is healthy, users may rarely think about it. When it is poorly designed, unsupported, undersized, insecure, or difficult to recover, the effects can reach far beyond the database itself.

This standard explains the business, technical, security, and operational expectations for hosting Microsoft SQL Server. It is written for business owners, executives, application owners, IT contacts, and technical teams. Readers do not need to be database administrators to understand the decisions and responsibilities described here.

The goal is not to make every SQL Server environment identical. The goal is to make each environment:

• Supported
• Secure
• Recoverable
• Properly sized
• Documented
• Monitored
• Understandable
• Aligned with the business impact of downtime and data loss

SQL Server is not simply another application installed on a Windows server. It is a business data platform that requires intentional ownership, capacity planning, access control, backup, recovery testing, maintenance, monitoring, and lifecycle management.

Policy Summary #

A healthy production SQL Server environment should meet the following expectations:

• SQL Server and the Windows operating system must remain within supported vendor lifecycles.
• The business application must support the installed SQL Server version, edition, and configuration.
• Every production database must have an identified business owner and application owner.
• SQL Server must be sized for the complete workload, including growth, reporting, backups, maintenance, integrations, and other applications sharing the server.
• SQL Server memory must be explicitly planned so Windows and other applications retain adequate resources.
• Production data must be protected by monitored backups and regularly tested restoration.
• SQL Server access must follow least privilege.
• Administrative access must be limited, documented, and separated from ordinary user access.
• Data files, transaction logs, TempDB, backups, and operating-system storage must be intentionally planned.
• Performance, capacity, backup, security, and service health must be monitored.
• Non-default settings must have a documented reason, owner, and review date.
• Changes must follow an approved change-management process.
• Production, development, test, and reporting databases must be clearly identified and appropriately protected.
• Unsupported, unmonitored, or undocumented systems may be limited to best-effort support until critical risks are corrected.

These requirements help reduce outages, strengthen security, improve recoverability, and make ongoing support more predictable.

Why This Standard Matters #

A database server can appear functional while carrying significant hidden risk.

Common examples include:

• The database is backed up, but no one has tested whether it can be restored.
• SQL Server is consuming most of the server’s memory and leaving Windows unstable.
• The environment is running an unsupported SQL Server or Windows Server version.
• The application vendor does not support the installed SQL Server version.
• Several users have unrestricted administrative access without a documented business need.
• Production, test, development, and copied databases are mixed together.
• Database files, transaction logs, backups, and the Windows pagefile compete for one storage path.
• Monitoring confirms that the server is online but does not detect failed backups or low storage capacity.
• A virtual machine has several drive letters, but every virtual disk depends on the same constrained storage pool.
• No one knows who may approve downtime, data retention, upgrades, or database retirement.
• A high-availability replica exists, but no independent, ransomware-resistant backup is available.
• Non-default SQL settings were changed years ago, and no one knows why.

These are not merely technical problems. They are governance, ownership, and business-continuity problems. A healthy SQL Server environment requires clear decisions, documented accountability, and investment appropriate to the impact of downtime and data loss.

1. Business Ownership and Accountability #

Every production SQL Server environment must have clearly identified ownership. Ownership and administration are not the same thing.

Business owner #

The business owner is accountable for the business process and information supported by the system.

The business owner should help answer:

• How important is the application?
• Which business functions stop when it is unavailable?
• How much downtime can the organization tolerate?
• How much recent data could the organization tolerate losing?
• What contractual, regulatory, or legal obligations apply?
• How long must the data be retained?
• Who may approve a major upgrade, outage, or retirement?
• What level of recovery and resilience is worth funding?

The business owner does not need to administer SQL Server. The business owner is responsible for the business decisions that technical teams cannot make alone.

Application owner #

The application owner understands how the organization uses the application and coordinates with its vendor.

The application owner should help answer:

• Which application depends on each database?
• Which SQL Server and Windows versions are supported?
• What maintenance windows are acceptable?
• How should the application be tested after a change?
• Which reports, integrations, services, and vendors depend on it?
• Who can validate that the application is functioning normally?
• Who should be contacted when the application vendor must participate?

Technical owner #

The technical owner maintains the server platform, SQL Server instance, backups, access, monitoring, documentation, and approved configuration.

Technical administration does not automatically grant authority to decide:

• Acceptable business downtime
• Acceptable data loss
• Data-retention periods
• Database deletion
• Risk acceptance
• Major application changes
• Whether an unsupported platform should remain in production

Required ownership record #

Every production SQL Server environment must document:

• Business owner
• Application owner
• Technical owner
• Application vendor
• Vendor support agreement or entitlement
• Change approver
• Emergency escalation path
• Authorized maintenance window
• Primary business contacts for outage communication

This is technology bookkeeping. It prevents urgent decisions from being made without business accountability.

2. Supported Software and Vendor Alignment #

Production SQL Server environments must use supported software whenever reasonably possible. A server may appear stable while an unsupported operating system, SQL Server version, or application dependency creates increasing security, recovery, compatibility, and support risk.

Review the complete technology stack #

The following components must be reviewed together:

• Microsoft SQL Server version
• SQL Server edition
• SQL Server cumulative-update level
• Windows Server version
• Database compatibility level
• Business application version
• Reporting components
• Backup software
• High-availability components
• Drivers and database connectors
• Security, monitoring, and management agents
• Hypervisor platform and integration drivers

Microsoft support is only part of supportability #

A supported SQL Server release is not sufficient when the business application vendor does not support it. Likewise, the application may be supported while the underlying Windows Server or SQL Server platform is not.

Application-vendor support #

Production line-of-business applications should maintain active vendor support or software maintenance whenever possible.

The vendor should be able to confirm:

• Supported SQL Server versions
• Supported Windows Server versions
• Required SQL Server edition
• Database compatibility-level requirements
• Collation requirements
• Supported authentication methods
• Required service accounts
• Supported high-availability designs
• Backup and restore requirements
• Query Store support
• Required antivirus or security exclusions
• Upgrade and migration procedures
• Required application testing

Patch-management standard #

SQL Server and Windows Server should be maintained at an approved and reasonably current security and servicing level. Updates must be evaluated and deployed through change management rather than ignored indefinitely or installed without planning.

SQL Server patching should include:

• Application-vendor compatibility review
• Backup confirmation
• Recovery and rollback planning
• Maintenance-window approval
• Service-impact communication
• Post-update database and application testing
• Documentation of the resulting build number
• Review of SQL Server and Windows event logs after completion

3. Dedicated Versus Shared SQL Server Hosting #

A major architectural decision is whether SQL Server should run on a dedicated server or share resources with the business application.

Dedicated SQL Server #

A dedicated SQL Server hosts the database engine separately from the application server.

This design generally provides:

• Clearer resource allocation
• Better workload isolation
• Easier troubleshooting
• Cleaner security boundaries
• Independent patching and lifecycle planning
• Reduced competition between SQL Server and application services
• Easier future scaling
• More predictable maintenance windows

A dedicated SQL Server is generally preferred when:

• The application is business critical.
• Downtime significantly affects operations or revenue.
• The workload is large, growing, or unpredictable.
• Reporting or indexing is resource intensive.
• Several applications share the SQL Server instance.
• Compliance or security requirements are stronger.
• Independent scaling is expected.
• The application vendor recommends or requires it.

Shared application and SQL Server #

A shared server runs SQL Server and one or more application services on the same Windows server.

This may be appropriate when:

• The workload is modest and predictable.
• The application vendor supports the design.
• Resource use is measured and monitored.
• Downtime tolerance is higher.
• The business accepts the shared failure domain.
• A documented growth and separation plan exists.

Risks of shared hosting #

SQL Server, Windows, application services, reporting tools, indexing services, security agents, monitoring systems, and backup jobs may compete for:

• Memory
• CPU
• Storage performance
• Network capacity
• Maintenance windows
• Restart authority

Standard #

Shared hosting is not automatically unacceptable, but it must be intentional and documented.

The design must document:

• Every significant workload sharing the server
• Expected and peak memory consumption
• SQL Server maximum memory
• Processor requirements
• Storage requirements
• Peak workload periods
• Backup, reporting, and indexing schedules
• Growth assumptions
• Conditions that would trigger separation

A shared server should not become a collection point for unrelated applications simply because unused capacity appears available.

4. SQL Server Design Levels #

Not every organization needs the same level of resilience or investment. These design levels help align the environment with the impact of downtime, data loss, security risk, and operational interruption.

Baseline #

• Supported SQL Server and Windows Server versions
• Application-vendor-supported configuration
• Clearly assigned ownership
• Explicit SQL Server memory planning
• Appropriate Windows pagefile configuration
• Monitored backups
• Periodic restore testing
• Least-privilege access
• Documented database and file inventory
• Service, capacity, and backup monitoring
• Documented maintenance and change process
• Documented lifecycle and upgrade plan

Recommended #

Includes the Baseline level, plus:

• Dedicated SQL Server when workload and business risk justify it
• Improved isolation of major storage workloads
• Stronger backup isolation
• Documented recovery point and recovery time objectives
• Query Store where supported and appropriate
• Routine performance and capacity trending
• Formal patch-management cadence
• Stronger separation of administrative duties
• Routine recovery exercises
• Separation of production from development and test

Resilient #

Includes the Recommended level, plus:

• High-availability or failover architecture
• Redundant compute, storage, power, and network components
• Failure-domain or geographic separation where appropriate
• Automated failover when justified and supported
• Documented failover and failback procedures
• Frequent recovery and continuity testing
• Capacity to withstand a host, storage, or database-node failure
• Business-approved downtime and data-loss objectives
• Formal disaster-recovery exercises

5. Server and Virtualization Standards #

SQL Server may run on physical hardware or inside a virtual machine. Both models can provide healthy results when intentionally designed and maintained.

Physical hosting #

Physical servers supporting production SQL Server should use business-class infrastructure appropriate to their criticality.

Expected controls may include:

• Error-correcting memory
• Redundant power supplies
• Appropriate storage redundancy
• Remote out-of-band management
• Active manufacturer warranty
• Environmental protection
• Monitored power protection
• Documented hardware lifecycle
• Replacement planning before end of support

Virtual hosting #

Virtualization does not remove hardware dependencies. It moves those dependencies to the hypervisor, host hardware, network, and storage platform.

A SQL Server virtual machine must be evaluated together with:

• Hypervisor host capacity
• Host memory pressure
• CPU scheduling and ready time
• Storage latency and contention
• Storage redundancy
• Virtual-switch design
• Host failover capabilities
• Backup integration
• Other virtual machines sharing the platform

Virtual machine standard #

A production SQL Server VM should have:

• Predictable access to its assigned memory
• Appropriate virtual CPU allocation
• Supported virtual storage controllers
• Supported virtual network adapters
• Current integration drivers and tools
• Accurate time synchronization
• Documented virtual disks and storage mappings
• Appropriate guest and host monitoring
• Supported backup integration
• Documented host placement and failover behavior

Resource overcommitment #

Virtualization platforms may allocate more theoretical resources than the physical host can provide simultaneously. This may be efficient for some workloads, but production SQL Server environments require caution.

A SQL VM may appear adequately sized inside Windows and still perform poorly when:

• The host is under memory pressure.
• CPU-ready or scheduling delay is elevated.
• Storage is oversubscribed.
• Another virtual machine creates heavy contention.
• Memory ballooning or dynamic-memory behavior affects the guest.
• A long-running hypervisor snapshot degrades performance.

Standard #

Production SQL Server resources must be based on reliable host capacity, not only on the values displayed inside the virtual machine. Changes to VM memory, processor count, storage, host placement, or high-availability configuration must trigger review of the SQL Server design.

6. Processor and Licensing Planning #

SQL Server performance, scalability, and licensing may all be affected by processor design.

Processor considerations #

SQL Server workloads may benefit from:

• Strong single-core performance
• An appropriate physical or virtual core count
• Balanced NUMA architecture
• Adequate CPU headroom
• Predictable hypervisor scheduling
• Application-vendor-supported processor design

More virtual processors do not always improve performance.

Over-allocating virtual CPUs may:

• Increase hypervisor scheduling delay
• Complicate NUMA alignment
• Create unnecessary licensing cost
• Make performance troubleshooting more difficult

Standard #

Processor sizing should consider:

• Application-vendor guidance
• Current and peak CPU use
• Concurrent user count
• Reporting and indexing activity
• SQL Server edition limits
• Physical and virtual NUMA design
• Virtualization overhead
• Future growth
• Licensing implications

Processor additions should address a measured need. They should not be the automatic response to a slow application before memory, storage, blocking, query design, and application behavior are evaluated.

7. Memory Configuration Standards #

SQL Server is designed to use memory aggressively because retrieving active data from memory is faster than repeatedly reading it from storage. High SQL Server memory use is not automatically a memory leak.

However, SQL Server must have a planned maximum-memory configuration that leaves adequate resources for:

• Windows Server
• Other applications
• Security and monitoring tools
• Backup software
• Reporting services
• SQL Server allocations outside the principal memory limit
• Drivers and system services
• Temporary workload spikes

Required standard #

Every production SQL Server instance must have:

• A documented maximum server memory value
• A documented reason for that value
• Adequate memory reserved for Windows
• Adequate memory reserved for shared applications and services
• Monitoring of available physical memory
• Monitoring of SQL Server memory use
• Review after any VM or physical-memory change
• Review when new applications or agents are added to the server

Maximum memory is a ceiling #

The SQL Server maximum-memory value is primarily a ceiling. It does not mean SQL Server will immediately reserve or consume the full amount. SQL Server grows its memory use as the workload benefits from additional caching and workspace memory.

Minimum server memory #

The SQL Server minimum-memory setting does not ordinarily force SQL Server to consume that amount at startup. It establishes a floor that SQL Server may retain after memory use has grown to meet workload demand. Unless a specific workload and documented design justify a different value, the default minimum is commonly left unchanged.

Database size is not memory size #

A 500 GB database does not automatically require 500 GB of RAM.

Memory requirements depend on:

• Frequently accessed data
• Query patterns
• Concurrent users
• Reporting activity
• Memory grants
• Execution-plan cache
• Application behavior
• Other workloads sharing the server
• Acceptable storage activity

8. Lock Pages in Memory #

Lock Pages in Memory, commonly called LPIM, allows eligible SQL Server memory allocations to remain in physical RAM rather than being handled through ordinary Windows paging.

When LPIM may help #

LPIM may be appropriate when there is evidence that Windows is trimming SQL Server’s working set or paging important SQL memory.

Possible evidence may include:

• SQL Server error 17890
• Sudden reductions in SQL Server memory
• Confirmed SQL Server working-set trimming
• Severe operating-system memory pressure affecting SQL Server

LPIM is not a universal requirement #

LPIM should not be enabled automatically on every SQL Server.

LPIM does not:

• Create additional physical memory.
• Correct an undersized server.
• Correct an inappropriate SQL Server memory limit.
• Eliminate every page fault.
• Guarantee better application performance.
• Protect other applications from memory pressure.

LPIM standard #

When LPIM is enabled:

• The SQL Server maximum-memory value must be explicitly configured.
• Windows and shared applications must retain adequate memory headroom.
• The SQL Server service account must be documented.
• The reason for LPIM must be documented.
• The change must follow change management.
• The required SQL Server service restart must be scheduled.
• The configuration must be reviewed after any VM or hardware memory change.
• Post-change monitoring must confirm that the complete server remains balanced.

LPIM is a memory-management control. It is not a replacement for capacity planning.

Windows memory displays may be confusing #

After LPIM is enabled, Task Manager or Resource Monitor may show a much smaller SQL Server working-set value than expected. This does not necessarily represent all SQL Server memory allocated through locked pages. Technical teams should validate SQL Server memory through SQL Server’s own dynamic management views and performance counters rather than relying only on the Task Manager Memory column.

9. Windows Pagefile Standards #

The Windows pagefile supports system commit capacity and operating-system recovery functions. It is not a substitute for adequate physical memory.

Required standard #

The pagefile must be:

• Intentionally configured
• Large enough for system commit requirements
• Appropriate for crash-dump requirements
• Located on suitable storage
• Monitored for capacity and use
• Documented

For many general-purpose Windows servers, a system-managed pagefile is a practical baseline.

A fixed or custom pagefile may be appropriate when intentionally calculated for:

• Crash-dump requirements
• Measured commit requirements
• Storage design
• Application-vendor guidance
• Organizational standards

Conditions requiring review #

• The pagefile is disabled without documented justification.
• The pagefile is configured at an extremely small fixed size.
• The selected volume lacks adequate free space.
• The pagefile resides on a highly constrained SQL Server data volume.
• Normal operations rely heavily on sustained pagefile activity.
• No one monitors total system commit.

A larger pagefile may improve resilience and reduce the risk of allocation failure. It does not make storage-backed memory perform like physical RAM.

10. Storage Architecture Standards #

SQL Server storage must support average demand, peak business demand, backup activity, maintenance operations, and expected growth.

Storage planning must consider:

• Input and output operations per second
• Throughput
• Latency
• Queue behavior
• Redundancy
• Available capacity
• Growth
• Backup activity
• Maintenance workloads
• Failure recovery

Common SQL Server storage workloads #

• Database data files
• Transaction-log files
• TempDB
• Native SQL Server backups
• Windows and application files
• Windows pagefile
• Application file repositories

Data files #

Database data files commonly experience mixed read and write activity.

Transaction logs #

Transaction logs depend on timely, durable writes and are especially sensitive to storage latency during transaction processing.

TempDB #

TempDB may experience intense temporary read, write, allocation, and deallocation activity.

Backups #

Backup jobs may create substantial sequential read and write workloads. They should not unexpectedly compete with production database activity or fill the production storage system.

Recommended logical organization #

A mature design may separate:

• Operating-system and application files
• SQL Server data files
• SQL Server transaction logs
• TempDB
• SQL Server backups

Separate drive letters do not automatically provide separate physical performance. In a virtual or shared-storage environment, several guest disks may still depend on the same storage pool, controller, RAID group, or network path.

Required storage documentation #

The organization must know:

• Where every SQL Server file resides
• Which underlying storage supports each volume
• Whether redundancy exists
• How much free capacity remains
• How growth is monitored
• Whether backup activity competes with production
• Whether the storage platform meets SQL Server durability requirements
• Which person or provider owns the underlying storage

11. Database File and Growth Standards #

Database files should be intentionally sized and monitored. Autogrowth is a safety mechanism. It should not be the primary capacity-planning method.

Data-file standard #

Data files should have:

• An appropriate initial size
• Sufficient internal free space for expected growth
• A reasonable fixed-megabyte autogrowth increment
• Monitoring for available storage capacity
• Documented maximum-size decisions
• Review before the storage volume becomes constrained

Transaction-log standard #

Transaction-log files should have:

• An appropriate initial size
• A reasonable fixed-megabyte growth increment
• Sufficient space for peak transactions and maintenance
• Log backups appropriate to the recovery model
• Monitoring for growth and truncation problems
• No routine shrink-and-regrow cycle

Why percentage-based growth can be problematic #

Percentage-based growth becomes larger as the file grows.

This can create:

• Unpredictable growth events
• Longer growth pauses
• Sudden capacity use
• Inconsistent sizing between files

Shrinking files #

Routine shrinking of database or transaction-log files is not a healthy maintenance practice.

Before shrinking a file, determine:

• Why the file grew
• Whether the growth was temporary
• Whether the space will be needed again
• Whether the file can safely remain at its current size
• Whether shrinking will create fragmentation
• Whether the file will immediately regrow

12. TempDB Standards #

TempDB supports temporary objects, sorting, hashing, row versioning, work tables, query spills, index operations, and other SQL Server activity. A poorly configured TempDB can create performance problems even when the production databases are otherwise healthy.

Required standard #

TempDB should have:

• Appropriate initial sizing
• Adequate storage capacity
• Fixed-megabyte growth increments
• Equally sized data files
• Matching growth settings for equal files
• A file count appropriate to the processor and workload
• Suitable storage performance
• Monitoring for growth, latency, and contention

Number of TempDB files #

For modern SQL Server versions, multiple equally sized TempDB data files are commonly used as an initial configuration when addressing allocation contention. One data file per logical processor, up to eight, may be an appropriate starting point in some environments.

More files are not automatically better.

Additional files should be based on:

• SQL Server version
• Logical processor count
• Workload
• TempDB activity
• Observed allocation contention
• Relevant SQL Server wait statistics
• Application testing

Routine shrinking #

TempDB should not be repeatedly shrunk as routine maintenance. Correct the sizing, workload, or storage-capacity issue instead.

13. Processor Parallelism Standards #

SQL Server can divide parts of a query among multiple worker threads. This may improve large queries, but unnecessary parallelism may consume excessive CPU and reduce capacity for other work.

Maximum Degree of Parallelism #

Maximum Degree of Parallelism, or MAXDOP, limits the number of processors used by an individual parallel query-plan operation.

Cost Threshold for Parallelism #

Cost Threshold for Parallelism determines when SQL Server begins considering a parallel plan based on the estimated cost of a query.

Required standard #

Parallelism settings must be:

• Reviewed during implementation
• Documented
• Aligned with processor and NUMA design
• Compatible with application-vendor requirements
• Validated against the actual workload
• Reviewed after major hardware, VM, or application changes

SQL Server defaults should not be assumed to be ideal for every production workload. At the same time, commonly discussed values such as MAXDOP 4 or Cost Threshold 50 are not universal standards. The correct configuration requires measurement, testing, and review through a representative business cycle.

14. Advanced SQL Server Settings #

Some SQL Server settings sound like easy performance improvements but may create risk when changed without evidence.

Examples include:

• Priority Boost
• Lightweight Pooling
• Processor affinity
• Network packet size
• Query Wait
• Lock limits
• Trace flags
• Forced parameterization
• Resource Governor
• Startup stored procedures

Priority Boost standard #

Priority Boost should normally remain disabled. It raises SQL Server’s Windows scheduling priority and may reduce the operating system’s ability to serve networking, security tools, administrative access, and other applications.

An enabled Priority Boost setting must have:

• A documented technical reason
• Authoritative vendor or Microsoft guidance
• Change approval
• Validation results
• A removal or review plan

Advanced-setting standard #

Advanced settings should remain at their supported defaults unless:

• A specific problem has been identified.
• Authoritative guidance supports the change.
• The application vendor approves it when required.
• The previous value is documented.
• Success criteria are defined.
• A rollback procedure exists.
• Post-change monitoring is planned.

“Someone recommended it online” is not sufficient documentation for a production SQL Server change.

15. Security and Access Standards #

SQL Server commonly stores sensitive business, customer, employee, financial, manufacturing, intellectual-property, or regulated information.

Security must be layered across:

• The physical or virtual server
• The operating system
• The network
• Authentication
• SQL Server permissions
• Database objects
• Applications and integrations
• Backups
• Administrative procedures

Authentication standard #

Prefer:

• Windows or Active Directory authentication where supported
• Managed service identities or group-managed service accounts where appropriate
• Group-based permission assignment
• Separate administrative accounts
• Multifactor authentication for server-level administrative access where available
• Documented application identities

Avoid:

• Shared administrator accounts
• Ordinary employee accounts running SQL Server services
• Broad use of SQL authentication without a documented requirement
• Passwords embedded in scripts
• Credentials stored in unsecured documentation
• Application accounts with unnecessary server-wide privileges

Least privilege #

Users, applications, vendors, and administrators should receive only the access required for their responsibilities.

Access should be:

• Approved
• Documented
• Reviewable
• Auditable
• Removable
• Reviewed when roles change

SQL Server sysadmin access #

Membership in the SQL Server sysadmin role must be tightly restricted.

It should not be granted simply because someone:

• Supports the business application
• Can log into the Windows server
• Is a domain administrator
• Manages backups
• Needs reporting access
• Needs to restart one application service

Service accounts #

SQL Server services must use documented, supported service identities.

Service-account documentation should include:

• Account name
• Services using the account
• Account owner
• Password-management method where applicable
• Required local and network permissions
• Service principal names where applicable
• Review and replacement procedure

Encryption #

Encryption decisions should be based on:

• Data sensitivity
• Regulatory and contractual requirements
• Application support
• Performance requirements
• Certificate and key-management capability
• Recovery requirements

Controls may include:

• Encrypted client connections
• Strict encryption for supported SQL Server versions
• Extended Protection
• Transparent Data Encryption
• Always Encrypted
• Encrypted backup storage

Encryption is only dependable when certificates, keys, renewals, backups, and recovery procedures are also documented.

16. Network and Connectivity Standards #

SQL Server must not be unnecessarily exposed.

Required standard #

• Limit SQL Server network access to required systems and users.
• Use host and network firewalls.
• Use network segmentation appropriate to the business risk.
• Disable unnecessary protocols and services.
• Use supported encrypted connections.
• Document SQL Server ports and dependencies.
• Do not expose SQL Server directly to the public internet.
• Restrict remote administrative access.
• Monitor failed authentication and unusual access patterns.
• Document DNS names, aliases, and application dependencies.

SQL Server Browser and dynamic ports #

SQL Server Browser should only be enabled when required. Static ports and documented connectivity are often easier to secure, monitor, and troubleshoot than undocumented dynamic-port configurations.

Application connection standards #

Application connection strings should use:

• Supported server names or aliases
• Encryption where supported
• Dedicated application identities
• Least-privilege database access
• Appropriate connection and command timeouts
• Secure credential storage

Connection strings must not expose passwords in tickets, scripts, screenshots, or unsecured documentation.

17. Backup and Recovery Standards #

A database backup is only valuable when it is complete, protected, monitored, retained appropriately, and restorable.

Required backup standard #

Every production database must have:

• A documented backup method
• An appropriate backup schedule
• Monitoring for success and failure
• Retention aligned with business and regulatory requirements
• Protection from ransomware and administrative deletion
• At least one copy outside the production server or primary failure domain
• Regular restore testing
• Documented recovery procedures
• An identified owner for backup failures

Recovery model #

The database recovery model must align with the business’s tolerance for data loss.

Simple recovery #

Simple recovery may be appropriate when recovery to the most recent full or differential backup is acceptable.

Full recovery #

Full recovery supports transaction-log backups and point-in-time recovery when properly configured and maintained. Full recovery without functioning transaction-log backups can create uncontrolled log growth without providing the intended recovery capability.

Recovery Point Objective #

The Recovery Point Objective, or RPO, answers: How much recent data could the organization tolerate losing?

Recovery Time Objective #

The Recovery Time Objective, or RTO, answers: How long can the application remain unavailable?

RPO and RTO are business decisions supported by IT. They should not be guessed after an outage begins.

Backup checksums #

Backup checksums can help detect certain damaged pages and backup-media errors. They improve validation but do not replace a complete restore test.

Restore testing #

Restore testing should confirm:

• The backup data is accessible.
• The required encryption keys and certificates are available.
• The database restores successfully.
• The restored database can be opened.
• The application can use the restored data where practical.
• The recovery procedure is documented.
• The measured recovery time aligns with business expectations.

18. High Availability Is Not Backup #

High availability is designed to reduce downtime from selected infrastructure or service failures.

Examples may include:

• Failover Cluster Instances
• Availability Groups
• Log shipping
• Replication
• Virtual-machine failover
• Application-level redundancy

These technologies do not replace backup. A logical deletion, malicious change, ransomware event, corruption, or administrative mistake may be copied to the secondary system.

High-availability standard #

High availability must be designed around:

• Business downtime tolerance
• Application-vendor support
• Failure domains
• Network design
• Storage architecture
• SQL Server licensing
• Operational skill
• Monitoring
• Failover testing
• Failback procedures
• Backup and recovery independence

An untested failover design should not be represented as dependable business continuity.

19. Monitoring and Alerting Standards #

Monitoring must go beyond confirming that the server responds to a network request.

Required monitoring #

At minimum, monitor:

• SQL Server Database Engine service status
• SQL Server Agent status when used
• Backup success and failure
• Database state and availability
• Storage free space
• Database and transaction-log growth
• TempDB capacity and growth
• Windows event logs
• SQL Server error logs
• CPU trends
• Available physical memory
• System commit pressure
• Storage latency
• Disk queue behavior
• SQL Server memory pressure
• SQL Server Agent job failures
• Certificate expiration where applicable
• High-availability health where applicable

Recommended monitoring #

• Query Store trends
• SQL Server wait statistics
• Blocking and deadlocks
• Long-running queries
• Database growth trends
• Backup duration
• Restore-test results
• Failed logins
• Permission changes
• SQL Server configuration changes
• Capacity forecasting
• Hypervisor CPU, memory, and storage contention
• Application response time

Alerts must be actionable #

An alert should identify:

• What happened
• Which server, instance, or database is affected
• The severity
• The likely business impact
• Who owns the response
• What action is expected
• When escalation is required

Alerting without ownership and response expectations creates noise rather than resilience.

20. Maintenance Standards #

SQL Server requires ongoing maintenance. The appropriate maintenance strategy depends on database size, workload, SQL Server version, application-vendor support, and recovery requirements.

Maintenance should include #

• Database consistency checks
• Backup monitoring
• Transaction-log backup monitoring
• Statistics maintenance
• Index maintenance when justified
• SQL Server patching
• Windows Server patching
• Storage-capacity review
• SQL Server error-log review
• SQL Server Agent job-history review
• Performance and capacity trend review
• Security-access review
• Restore testing
• Documentation review

Index maintenance #

Indexes should not be rebuilt on an arbitrary schedule solely because a generic maintenance script recommends it.

Index maintenance should consider:

• Fragmentation
• Index size
• Actual use
• Maintenance duration
• Transaction-log growth
• Backup impact
• Storage impact
• Blocking
• SQL Server version and available features

Excessive maintenance can create its own performance, storage, and recovery problems.

Database consistency checks #

Database consistency checks are important for identifying corruption.

They must be:

• Scheduled
• Monitored
• Appropriately sized for the environment
• Coordinated with backups and business workloads
• Escalated promptly when errors are detected

21. Production, Test, Development, and Reporting Separation #

Production data should not be copied casually.

Required standard #

Production, development, test, training, and reporting environments must be clearly identified.

Where practical, they should be separated by:

• Server
• SQL Server instance
• Database
• Security boundary
• Backup policy
• Maintenance policy
• Monitoring
• Access control

Production database copies #

A copied production database may contain:

• Customer information
• Employee information
• Financial records
• Credentials or authentication data
• Trade secrets
• Regulated information
• Confidential contracts or documents

Before copying production data:

• Obtain approval.
• Confirm the business purpose.
• Limit access.
• Mask or remove sensitive information where feasible.
• Define retention.
• Document the copy and its owner.
• Protect it with appropriate backups and security.
• Remove it when it is no longer required.
• Ensure maintenance and backup systems do not process it unintentionally.

Old development, test, and copied databases should not remain indefinitely because no one is certain whether they can be deleted.

22. Documentation Standards #

Every production SQL Server environment must have current documentation.

Required documentation #

• Server and SQL Server instance names
• SQL Server version, build, and edition
• Windows Server version
• Applications supported
• Business owner
• Application owner
• Technical owner
• Application-vendor information
• Database inventory
• Purpose of each production database
• Data, log, TempDB, and backup locations
• SQL Server maximum-memory setting
• LPIM status and reason
• Windows pagefile configuration
• MAXDOP and Cost Threshold settings
• SQL Server service accounts
• Administrative access
• Backup schedule and retention
• Recovery point and recovery time objectives
• Restore-test history
• Maintenance schedule
• Monitoring and alert ownership
• High-availability architecture
• Network ports and dependencies
• Encryption certificates, keys, and recovery procedures
• Lifecycle and upgrade plan
• Approved exceptions and risk acceptances

Non-default settings #

Every non-default setting should answer:

• What was changed?
• Why was it changed?
• Who approved it?
• What evidence supported it?
• When was it implemented?
• How was it validated?
• How is it monitored?
• How can it be rolled back?
• When should it be reviewed again?

Documentation is not optional paperwork. It is the operating memory of the organization.

23. Change Management Standards #

Production SQL Server changes must follow change management.

Changes requiring review and documentation include: #

• SQL Server patching
• Windows Server patching affecting SQL Server
• SQL Server configuration changes
• Memory changes
• Processor changes
• Storage changes
• Database-file moves
• TempDB changes
• Pagefile changes
• Service-account changes
• Encryption changes
• Permission changes
• Application upgrades
• Database migrations
• High-availability changes
• Maintenance-plan changes
• Database retirement or deletion

Change record #

The change record should document:

• Business reason
• Scope
• Business and technical owners
• Current state
• Proposed state
• Vendor guidance
• Risks
• Backup and recovery status
• Maintenance window
• Expected downtime
• Testing plan
• Rollback plan
• Communication plan
• Approval
• Validation result
• Follow-up monitoring

Emergency changes #

Emergency changes may use an accelerated process, but they still require documentation. The fact that a change was urgent does not remove the need to record what happened, why it was necessary, who approved it, and what follow-up work remains.

24. Exceptions and Risk Acceptance #

Not every organization can immediately meet every standard.

An exception may be necessary because of:

• Application limitations
• Vendor restrictions
• Legacy dependencies
• Budget
• Timing
• Merger or acquisition activity
• A planned replacement project

Exception standard #

An exception must document:

• Which standard is not met
• Why it cannot currently be met
• The business and technical risk
• Compensating controls
• The responsible business owner
• The target correction date
• The next review date
• Formal risk acceptance

An undocumented exception is not a managed exception. It is an unknown risk.

25. Support Boundaries #

Unsupported, undocumented, or non-standard SQL Server environments may require additional discovery before changes can be performed safely.

Examples include:

• Unsupported SQL Server version
• Unsupported Windows Server version
• No active application-vendor support
• Unknown database ownership
• No verified backup
• No restore testing
• Undocumented service accounts
• Unknown encryption keys or certificates
• Unidentified production database copies
• Unclear SQL Server licensing
• No approved maintenance window
• No authorized person available to approve changes

In these conditions, support may initially be limited to:

• Best-effort triage
• Read-only inspection
• Backup and recovery-risk identification
• Documentation
• Immediate stabilization recommendations
• Vendor coordination
• Remediation planning

IT support should not be expected to perform high-risk database changes without confirmed ownership, recoverability, authorization, and vendor alignment.

26. One-Glance Standards Checklist #

27. Questions Decision Makers Should Ask #

Business leaders do not need to understand every SQL Server setting or performance counter.

They should be able to ask and receive clear answers to these questions:

1. Which business applications depend on this SQL Server?
2. Who owns each application and database?
3. Is the platform supported by Microsoft and the application vendor?
4. How much downtime can the organization tolerate?
5. How much recent data could the organization afford to lose?
6. Are backups monitored every day?
7. When was the last successful restore test?
8. How long did the restore take?
9. Is the server sized for current use and expected growth?
10. Does SQL Server share resources with other applications?
11. What happens if the server, hypervisor host, or storage system fails?
12. Who has administrative access?
13. Are important changes documented and approved?
14. Are performance and capacity trends reviewed before users experience problems?
15. What is the upgrade or replacement plan?
16. Which risks has the organization knowingly accepted?

A mature IT environment can answer these questions without beginning an emergency investigation.

Frequently Asked Questions #

Does every business need a dedicated SQL Server? #

No. A shared application and SQL Server may be appropriate for a modest, predictable workload when the vendor supports it and resources are intentionally managed. A dedicated SQL Server becomes more valuable as business criticality, workload, security requirements, growth, and downtime impact increase.

Why does SQL Server use so much memory? #

SQL Server uses memory to cache frequently accessed data and execution plans. This can reduce storage reads and improve performance. High memory use is not automatically unhealthy. The concern is whether Windows and other applications retain adequate resources.

Should SQL Server be allowed to use all available memory? #

No. SQL Server should normally have a documented maximum-memory setting that leaves room for Windows, shared applications, drivers, backup software, monitoring, and temporary workload spikes.

Is Lock Pages in Memory always a best practice? #

No. LPIM may help when SQL Server is experiencing confirmed working-set trimming. It also reduces Windows’ ability to reclaim eligible SQL memory, so it must be paired with careful memory planning and monitoring.

Does a large pagefile fix a memory shortage? #

No. A properly configured pagefile improves commit capacity and resilience. It does not make storage-backed memory perform like physical RAM.

Should SQL Server data and log files always be on different drives? #

Not necessarily. Separate logical volumes can improve organization, capacity control, and sometimes performance. The actual benefit depends on whether the underlying storage resources are meaningfully separated.

Is an SQL Server backup on the same server sufficient? #

No. A backup stored only on the production server or the same storage system may be lost during server failure, storage failure, ransomware, or administrative error.

Does a successful backup job prove that recovery will work? #

No. A successful backup job is important, but only a restore test can confirm that the backup is accessible, complete, and usable within the expected recovery time.

Does high availability eliminate the need for backup? #

No. High availability may reduce downtime from selected failures. It can also replicate deletion, corruption, ransomware, or administrative mistakes. Independent backups remain necessary.

Why must business leaders define RPO and RTO? #

Technology teams can explain available recovery options and costs, but they cannot decide how much data loss or downtime the business can tolerate. Those are business-risk decisions.

Should production data be copied into a test environment? #

Only when there is an approved purpose and appropriate protection. Production copies may contain sensitive information. Access, masking, retention, backup, and deletion must be managed intentionally.

Can an unsupported SQL Server continue running? #

Yes, but continued operation does not make it low risk. Unsupported systems may lack security updates, vendor assistance, compatibility fixes, and predictable recovery options.

Who should have SQL Server sysadmin access? #

Only a limited number of authorized administrators with a documented need. Application support, reporting access, domain administration, or server login rights do not automatically justify SQL Server sysadmin privileges.

Why document non-default settings? #

Without documentation, future administrators cannot know whether a setting was intentionally selected, copied from generic advice, required by a vendor, or left behind from an old troubleshooting effort. Documentation makes future changes safer.

What happens when an environment does not meet this standard? #

The first step is to identify and prioritize the risks. Not every issue must be corrected at once. The organization should document the gap, business impact, compensating controls, responsible owner, and remediation timeline.

Final Perspective #

A healthy SQL Server environment is not defined by one memory setting, one fast storage test, or one successful backup.

It is defined by the complete operating system around the database:

• Clear ownership
• Supported technology
• Vendor alignment
• Appropriate architecture
• Intentional capacity
• Least privilege
• Reliable backups
• Tested recovery
• Monitoring
• Change management
• Documentation
• Lifecycle planning

The purpose of these standards is not to create unnecessary complexity. The purpose is to create a healthy and predictable technology experience.

When these foundations are in place:

• Performance problems are easier to diagnose.
• Changes are safer.
• Recovery is faster and more dependable.
• Security is stronger.
• Budgets and lifecycle decisions are more predictable.
• Business leaders understand the risks they are accepting.
• Technical teams can support the system without guessing.

Everyone deserves to operate from a clear, healthy, secure, and supportable technology standard, regardless of organization size.

Related Client University Resources #

This article establishes the hosting and configuration standard. Use the companion resources below for practical inspection and performance validation.

• SQL Server Performance Optimization: Simple Checks That Prevent Big Problems
  Learn how technical teams can inspect commonly missed memory, storage, TempDB, pagefile, database-file, and SQL Server settings.
• How to Validate SQL Server Performance After Optimization
  Learn how to interpret CPU, memory, hard faults, IOPS, throughput, latency, queue depth, disk activity, and network measurements after a change.
• Server Infrastructure Standards
  Review the broader expectations for physical servers, virtualization, warranties, redundancy, lifecycle planning, and business continuity.
• IT Health Standards
  Understand the broader operational baseline for supported, monitored, secure, and recoverable technology.
• Hardware Lifecycle Standards
  Learn why support status, warranty, age, capacity, and replacement planning matter.
• What Is a Change Management Request and Change Advisory Board?
  Learn how production technology changes should be reviewed, approved, documented, validated, and reversed when necessary.

Authoritative References #

• Microsoft Learn: SQL Server End of Support Overview
• Microsoft Learn: Download and Install the Latest Updates for SQL Server
• Microsoft Learn: Server Memory Configuration Options
• Microsoft Learn: SQL Server Memory Management Architecture Guide
• Microsoft Learn: Troubleshoot SQL Server Memory Issues
• Microsoft Learn: Enable Lock Pages in Memory
• Microsoft Learn: Configure Maximum Degree of Parallelism
• Microsoft Learn: Configure Cost Threshold for Parallelism
• Microsoft Learn: Configure Priority Boost
• Microsoft Learn: Recommendations to Reduce TempDB Allocation Contention
• Microsoft Learn: SQL Server I/O Subsystem Requirements
• Microsoft Learn: SQL Server Security Best Practices
• Microsoft Learn: Possible Media Errors During Backup and Restore
• Microsoft Learn: SQL Server Recovery Models
• Microsoft Learn: SQL Server Database Engine