Skip to main content
Enterprise applications

Microsoft SQL Server on ONTAP

Contributors jfsinmsp manoharvk
PDFs

ONTAP delivers an enterprise-class security and performance solution for your Microsoft SQL Server databases while also providing world-class tools to manage your environment.

Note This documentation replaces the previously published technical report TR-4590: Best practice guide for Microsoft SQL Server with ONTAP

NetApp assumes that the reader has working knowledge of the following:

  • ONTAP

  • Microsoft SQL Server architecture and administration

  • SnapCenter backup software, including the following components:

  • NetApp SnapCenter as backup software, which includes:

    • SnapCenter Plug-in for Microsoft Windows

    • SnapCenter Plug-in for SQL Server

The scope of this best practices section is limited to technical design based on the principles and preferred standards that NetApp recommends for storage infrastructure. The end-to-end implementation is out of scope.

For compatibility information across the NetApp products, see the NetApp Interoperability Matrix Tool (IMT).

Microsoft SQL Server workloads

Before deploying SQL Server, you must understand the workload requirements of the applications that your SQL Server database instances support. Each application has different requirements for capacity, performance, and availability, and therefore each database should be designed to optimally support those requirements. Many organizations classify databases into multiple management tiers, using application requirements to define SLAs. SQL Server workloads are often categorized as described below:

  • OLTP, which are often the most critical databases in an organization. These databases usually back customer-facing applications and are considered essential to the company's core operations. Mission-critical OLTP databases and the applications they support often have SLAs that require high performance, are sensitive to performance degradation and require maximum availability. They might also be candidates for Always On Failover Clusters or Always On Availability Groups. The I/O mix on these types of databases is usually characterized by 75% to 90% random read and 25% to 10% write.

  • Decision support system (DSS) databases, sometimes referred to as data warehouses. These databases are mission critical for many organizations that rely on analytics for their business. These databases are sensitive to CPU utilization and read operations from disk when queries are being run. In many organizations, DSS databases are the most critical during the month, quarter, and year end. This workload typically has a nearly 100% read I/O mix and IO throughput is often more important than IOPS.