Installation of MS SQL Server involves a lot of work before an IT specialist can actually start the installation process. Before we address the software and hardware requirements, we need to consider one thing: what edition of SQL Server we will actually need, depending on the features of SQL Server we intend to use. Hardware and software requirements, including the operating system, storage, CPU, and memory, depend on this decision.

Planning the Installation of MS SQL Server

This process of considering what we will use and what we will not is called installation planning. Installing SQL Server is not a task that can be casually dismissed by simply clicking ‘OK’ in the installation wizard. During the planning phase, we need to think about what features we will need in our enterprise environment now and in the future (estimate).

• What computational power do we need?
• Will support for 8 processor cores be sufficient, or do we need more (the expensive Enterprise edition)?
• How many users will access SQL Server services during the day and at night?
• How will we handle disaster plans?
• To what extent do we want to scale?

All of these factors influence the choice of the SQL Server edition and, consequently, the hardware and software requirements.

SQL Server Editions

• Express – the basic edition with the most limitations. It is used for getting acquainted with SQL Server and for building smaller projects. However, even this edition can handle a lot and may often be sufficient for client needs.
• Developer – This edition includes all the features of the Enterprise edition. However, its use is strictly limited to development and cannot be deployed on production servers.
• Web – A cost-effective option for web servers.
• Standard – A full-fledged solution, including the database engine, full support for data integration (SSIS), analytics services (SSAS), and data management.
• Enterprise – The biggest difference compared to the Standard edition in terms of features is in the advanced data management capabilities – extended disaster recovery options, availability, security, parallel query processing, and more. In terms of hardware resource utilization (scalability), the Enterprise edition is the top choice, allowing you to utilize 100% of the resources, while other editions have their limits.

Resource Usage Limits by Edition (Selected)

The Standard edition provides sufficient resource utilization in almost all cases. Even the Express edition often provides enough computational power in environments with few users or processes. Effective computational power is probably the most important limitation of SQL Express (although it is quite small).

Its limitation is the database size, which is only 10GB, along with other restrictions listed below. One way to extend the usability of the Express edition with regard to its computational limitations is to use SQL table compression (available from SQL Server 2016 and higher in all editions – previously only in Enterprise), table partitioning, and more (see below).

Programming Tools

Microsoft already offers quite a bit in Express. Notable mentions include native support for XML and JSON – you can query directly from the database, an XML data type, integrated functions for XML and JSON. Full-text search capabilities are not found in Express, and the ability to create SQL endpoints is also missing. The Enterprise edition is geared toward corporate environments using data science and big data (Hadoop) – with R language and Polybase.

Special Features for Data Warehouses in SQL Server

SQL Server is often used for data warehouses that handle large amounts of data from many different sources. Large data warehouses require a fair amount of functionality to meet the demands of internal customers – reporting (SSRS, SSAS), data integration (SSIS) from multiple sources, analysis, fast access – tips for optimizing queries, compression, security (multiuser environments), and more.

When analyzing hardware and software requirements for data warehouses, careful consideration is needed. Most customers won’t need most of what’s listed below (although features like change data capture are nice), but you’ll need many standard features from other categories. Express edition is generally uncomfortable for data warehouses, and I would recommend at least the Standard edition.

Performance and Scalability (Selected)

There are no significant differences here. Enterprise edition provides access to Resource Governor, which allows allocating maximum computational power to various defined groups (e.g., good users vs. notorious script runners).

Columnstore indexes and data compression are certainly helpful even in the Express edition – they allow mitigating the impact of computational limitations by optimizing queries with appropriate indexes or, if storage is a priority, compressing tables (row-level compression vs. page-level compression).

Security in SQL Server

There are no significant differences between Enterprise and other editions in terms of security; most environments do not need the features offered by Enterprise. Transparent database encryption is a more advanced encryption method compared to Always Encrypted (available in all editions). You can learn more about the differences between these encryption types on Microsoft’s blog – Transparent data encryption or always encrypted?.

SQL Server Tools – Management Tools

At this point, Express editions might be lacking. You can forget about SQL Server Agent (automation, notifications, alerts), SQL Profiler (allows detailed analysis of performance issues), Tuning Advisor (a tool for query optimization), and Distributed Replay Controller, which is a set of services capable of intentionally stressing servers.

Installation of MS SQL Server – Hardware/Software Requirements Depending on Edition

Requirements Common to All Editions

• .NET framework – for SQL Server 2016, version 4.6 is required, which you can install manually or it will be automatically installed during the installation process.
• Hard disk – at least 6 GB of free space for installation.
• Internet access
• Network services – supported protocols include shared memory, named pipes, TCP/IP, VIA.

Processor and Memory Requirements

• Processor – At least x64 processor 1.4 GHz; recommended 2GHz and higher. Processor type: x64 Processor: AMD Opteron, AMD Athlon 64, Intel Xeon with Intel EM64T support, Intel Pentium IV.
• Memory – Minimum requirements vary depending on the edition.
  • Express – at least 512 MB, recommended 1 GB.
  • Others – at least 4 GB, recommended not specified – depending on the situation.

Operating System Requirements

You can no longer install any edition of SQL Server 2016 and 2017 on Windows 7. At least Windows 8 is required. For servers, you will need at least Windows Server 2012. SQL Server fully supports only 64-bit operating systems.

Disk Space Requirements

Features selected during installation of MS SQL Server and their storage requirements are shown below. There are two valid approaches to installation, and both are valid. You can either install everything even if you may not need it or choose what you need during installation. The second method requires more knowledge (to avoid forgetting something). For example, you might want to use Integration Services for data pumps but forget to install Client Components (ODBC drivers, etc.).

Tip: During the SQL Server installation planning process, you can use a useful utility that allows you to test whether the I/O configuration is correctly set up. The utility is called SQLIO. It’s worth it – wait states related to I/O operations are quite common.