Operating System Support for SQL Server Versions

By: Glenn Berry
Posted on: January 12, 2017 3:13 pm

(New: we’ve published a range of SQL Server interview candidate screening assessments with our partner Kandio, so you can avoid hiring an ‘expert’ who ends up causing problems. Check them out here.)

There are currently six major versions of SQL Server that I commonly see being used in Production, along with five major versions of Windows Server. Only certain combinations of SQL Server and Windows Server are officially supported by Microsoft, but tracking down this information is a little tedious.

Table 1 shows these possible combinations and whether they are officially supported by Microsoft. One possibly surprising combination is the fact that SQL Server 2012 is not officially supported on Windows Server 2016. Perhaps this is less surprising if you keep in mind that SQL Server 2012 will fall out of mainstream support on July 11, 2017, which is not that far away.

Picture1

Table 1: OS Support for Recent Versions of SQL Server

The available links that document this are listed below:

Hardware and Software Requirements for Installing SQL Server 2008 R2

Hardware and Software Requirements for Installing SQL Server 2012

Hardware and Software Requirements for Installing SQL Server 2014

Hardware and Software Requirements for Installing SQL Server (for 2016 and later)

If you are getting ready to deploy a new instance of SQL Server 2014 or SQL Server 2016, then you should prefer Windows Server 2016, even though they are also supported on older operating systems. If you are getting ready to deploy a new instance of SQL Server 2008 through SQL Server 2012, then you should prefer Windows Server 2012 R2, even though they are supported on older operating systems.

Finally, if you are getting ready to deploy a new instance of SQL Server 2005, then I feel a little sorry for you! SQL Server 2005 is out of extended support, and it is missing so many useful features that were added in newer versions of SQL Server.

Actually, I recently helped a client deploy some new instances of SQL Server 2005 for some pretty valid business reasons. We ended up deploying to a VM (on new, very fast host hardware) that was running Windows Server 2008 R2, which worked perfectly fine.

Posted in: SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, Windows Server 2008 R2, Windows Server 2012, Windows Server 2012 R2, Windows Server 2016
24 Comments

AES-NI Encryption Support in SQL Server 2016

By: Glenn Berry
Posted on: June 10, 2016 12:51 pm

Microsoft’s Bob Dorr has recently consolidated a series of CSS posts about many low-level performance improvements in SQL Server 2016. One of these posts, SQL 2016 – It Just Runs Faster – AlwaysOn AES-NI Encryption describes how SQL Server 2016 improved and simplified Endpoint creation for AlwaysOn AGs to default to AES, and to better leverage hardware support for AES-NI (when you are also running on Windows Server 2012 R2 or newer).

Intel describes AES-NI this way:

Intel AES New Instructions (Intel AES-NI) are a set of instructions that enable fast and secure data encryption and decryption. AES-NI are valuable for a wide range of cryptographic applications, for example: applications that perform bulk encryption/decryption, authentication, random number generation, and authenticated encryption.

Intel provides more complete documentation of AES here:

Intel Advanced Encryption Standard (Intel AES) Instructions Set

Now that we have a better idea of what AES-NI is, we need to know which server processors have AES-NI support. AES-NI was introduced with the 32nm Westmere microarchitecture. In the server space, this means the Intel Xeon 5600 product family (Westmere-EP) for two-socket servers and the Intel Xeon E7-4800/8800 product family (Westmere-EX) for four and eight-socket servers. Any newer Intel product family will also have AES-NI support.

These product families were released in Q1 2010 and Q2 2011 respectively, so they are both pretty old by modern standards. If you are planning to upgrade to SQL Server 2016, I would hope that you would use a new server with newer processors (ideally the latest 14nm Broadwell-EP or Broadwell-EX).

You can also use CPU-Z to confirm whether a particular processor has AES-NI support. Just to confuse things, CPU-Z displays AES-NI support as “AES”, which is the name of the broader standard. Figure 1 shows AES as one of the supported instructions for an Intel Xeon X5660 processor.

 

image

Figure 1: CPU-Z CPU Details for Intel Xeon X5650

 

Posted in: Hardware, Processors, SQL Server 2016, Windows Server 2012 R2, Windows Server 2016
Be the first to Reply!

Hardware Extension Support in SQL Server 2016

By: Glenn Berry
Posted on: June 8, 2016 4:25 pm

Microsoft’s Bob Dorr has recently consolidated a series of CSS posts about many low-level performance improvements in SQL Server 2016. One of these, How It Works: SQL Server 2016 SSE/AVX Support talks about how SQL Server 2016 added the use of SSE and AVX instructions in various code paths to improve performance when your processor supports one of those instruction sets.

Unless you are a hardware enthusiast, you might be wondering what these instructions are and how you can determine whether your processor supports them or not. First, we have some basic definitions:

Advanced Vector Extensions (AVX) were introduced in the server space with the Sandy Bridge-EP architecture, which was released in Q1 2012. This was the Intel Xeon E5-2600 Product Family. This means that if you have a server with Intel Xeon E5-2600 (Sandy Bridge-EP), Intel Xeon E5-2600 v2 (Ivy Bridge-EP), Intel Xeon E5-2600 v3 (Haswell-EP), or Intel Xeon E5-2600 v4 (Broadwell-EP) processors, you will have AVX support which SQL Server 2016 will use for better performance.

You will also have AVX support with the Intel Xeon E7-4800/8800 v2 (Ivy Bridge-EX), Intel Xeon E7-4800/8800 v3 (Haswell_EX), and Intel Xeon E7-4800/8800 v4 (Broadwell-EX) product families. Anything older than these families will not have AVX support.

This is yet another reason to use a new server when you upgrade to SQL Server 2016 (which is a good idea anyway for lots of other reasons).

Advanced Vector Extensions 2 (AVX2), is a newer version of AVX that was introduced with the Haswell-EP/EX architecture. If you have a Haswell-EP/EX or Broadwell-EP/EX processor, then you will have AVX2 support.

Streaming SIMD Extensions (SSE) are a much older instruction set that were introduced back in 1999. Since then, there have been several newer versions such as SSE2, SSE3, SSE4.1, and SSE4.2. The latest SSE4.2 version was introduced in the Nehalem-EP/EX architecture, which came out in Q1 2009 and Q1 2010 respectively. This means that the Intel Xeon 5500 (Nehalem-EP) and Intel Xeon 7500 (Nehalem-EX) Product families are the oldest models that will have SSE4.2 support. From Bob’s post, it appears that the same code path in SQL Server 2016 will be used if you have any version of SSE (but you don’t have AVX or AVX2 support), so even much older Xeon processors will have some level of SSE support. Just to be clear, AVX and AVX2 are newer and better than any version of SSE.

The CPU-Z utility shows the various extension instructions supported by your processor. As you can see in Figure 1, the Intel Xeon E5-2690 supports the AVX instruction set.

image

Figure 1: Intel Xeon E5-2690 CPU Details

A current model Intel Core i7-6700K Skylake desktop processor supports the newer FMA, and Transactional Synchronization Extensions (TSX) instructions, as shown in Figure 2. These were both introduced with the Haswell microarchitecture, and they might be useful in a future version of SQL Server, if Microsoft decides to use them.

image

 Figure 2: Intel Core i7-6700K CPU Details

Posted in: SQL Server 2016, Windows Server 2012 R2
1 Comment