Leveraging Windows Server 2016 with SQL Server 2016

Windows Server 2016 is expected to be officially released at the September Ignite 2016 conference in Atlanta with appearance on the price list on October 1st. SQL Server has always had tight integration with Windows Server, and that is definitely the case with Windows Server 2016 and SQL Server 2016.

First and foremost, the maximum memory supported by Windows Server 2016 has jumped to 12 TB--a three-fold increase over the memory supported by Windows Server 2012 R2. As with earlier releases, SQL Server 2016 can deliver much better performance when it has adequate memory. This is particularly important in scenarios in which SQL Server 2016 is running in a VM that supports dynamic memory--where the VM’s RAM can be dynamically expanded in response to increasing workloads. Likewise, SQL Server 2016’s memory-resident technologies, like In-Memory OLTP and Columnstore Index, require substantially more memory than traditional disk-based relational database technologies.

Of course, it’s one thing for the OS to support this much memory. The underlying hardware platform also needs to be able to support it, and most server systems--even many enterprise-class servers--don’t support Windows Server 2016’s level of memory. To get the most out of Windows Server 2016, therefore, organizations will need server hardware designed for maximum enterprise scalability. The HPE Superdome X, for example, can support up to 24 TB of RAM, which can be divided into two 12 TB partitions--all on a single server.

At the same time, Windows Server 2016 can support all of the processors in the system: While most database workloads do not require these level of scalability, the HPE Superdome X can be configured with a maximum of 288 cores, enabling enterprise-level performance and scalability for resource-intensive workloads. SQL Server 2016 can also utilize the NUMA support in the Superdome X and Windows Server 2016 for high-performance symmetrical multiprocessing.

Windows Server 2016 can also provide SQL Server 2016 with domain-independent high availability. In previous releases, Windows Server Failover Clustering and the nodes for SQL Server AlwaysOn Availability Groups all needed to be members of the same Active Directory domain. Windows Server 2016 enables Failover Clusters without the need for membership to the same domain. SQL Server 2016 leverages this ability by providing domain-independent AlwaysOn Availability Groups.

SMB 3.0 Improvements Carry over to Win Server 2016

Beginning with Windows Server 2012 and SQL Server 2014, SQL Server could be configured to run on SMB file shares. Prior to Windows Server 2012 and SMB 3.0, SMB file shares were too slow to support I/O-intensive SQL Server workloads. Plus, the SMB protocol was a file sharing protocol meant for many fast opens and closes—as opposed to applications like SQL Server that keep files open for long periods of time. With the SMB 3.0 protocol, Microsoft implemented a number of significant improvements that are carried over to Windows Server 2016. For example, it can be used with individual SQL Server instances, clustered FCI instances and clustered AlwaysOn AG instances. Storing SQL Server databases on SMB shares can also be useful for database migrations. If you have a database on an SMB file share, you can move it to another instance by simply detaching the database and then attaching it to the new instance.

Another closely related technology in Windows Server 2016 that provides tight integration with SQL Server 2016 is Storage Spaces Direct. Storage Spaces Directs is essentially the next evolution of the Storage Spaces technology, also first introduced with Windows Server 2012. Storage Spaces Direct enable admins to use multiple, clustered file server nodes to build highly available, scalable storage systems with local storage. This provides an additional high-performance deployment choice for SQL Server storage. As with SMB shares, Storage Spaces Direct can be used with stand-alone SQL Server instances, clustered FCI instances and clustered AlwaysOn Availability Groups.

HPE and Microsoft are the underwriters of this article.