Five reasons to run SQL Server 2016 on Windows Server 2016 – No. 3: database uptime and reliability

This is the third post in a five-part blog series. Keep an eye out for upcoming posts and catch up on the first and second in the series.

In addition, join us for Microsoft Data Amp on April 19 at 8 a.m. PT. The online event will showcase how data is the nexus between application innovation and artificial intelligence. You’ll learn how data and analytics powered by the most trusted and intelligent cloud can help companies differentiate and out-innovate their competition. Microsoft Data Amp—where data gets to work.

When does 2 + 2 = 5? When two teams work hard to deliver great products individually, but also work together to make the combination more than the sum of their parts. Windows Server 2016 and SQL Server 2016 are prime examples. The development teams have collaborated closely to ensure that the very best experience for data professionals emerges when you take advantage of the synergies built into the Windows Server OS and the SQL Server data platform. In this post, we’ll share how the teams have worked together to deliver advanced functionality to improve database uptime and reliability, including effective disaster recovery across sites and domains.

Always On Availability Groups: Enhanced capabilities supporting new scenarios

Always On Availability Groups have been at the center of SQL Server availability since the 2012 release. Availability Groups establish a relationship between a set or group of databases and replicas of that group of databases on one or more replicas. This means all the databases in the group can move as a unit, eliminating the need for complex scripting solutions to do this task.

Up to now, with Windows Server Failover Cluster solutions, all nodes in the Availability Group had to reside in the same Active Directory domain. However, many organizations have multiple domains that can’t be merged, and they want to span an Availability Group across such domains. In other situations, organizations may have no Active Directory domains at all, yet still want to host disaster recovery replicas.

To give these organizations a solution, the SQL Server and Windows Server teams delivered Windows Server 2016 Failover Clusters (WSFC). Now, all nodes in a cluster no longer need to reside in the same domain—and indeed the nodes are no longer required to be in any domain at all. Instead, you can form a WSFC cluster with machines that are in workgroups.

SQL Server 2016 is able to deploy flexible Always On Availability Groups in environments with:

  • All nodes in a single domain
  • Nodes in multiple domains with full trust
  • Nodes in multiple domains with no trust
  • Nodes in no domain at all

With SQL Server 2016 and Windows Server 2016, Always On availability groups can include up to eight readable secondaries and can span multi-domain clusters. In addition, Active Directory authentication is no longer required. All this innovation opens up new scenarios and removes previous blocks that prevented migration from deprecated Database Mirroring technology to Always On Availability Groups. (For details, see “Enhanced Always On Availability Groups in SQL Server 2016.” Click here for a video demo.)

Hybrid Backup and Stretch Database provide online cold data availability in Azure

SQL Server 2016 and Windows Server 2016 are architected to work smoothly with the Microsoft Azure cloud in a hybrid environment. Microsoft hybrid cloud technology provides a consistent set of tools and processes between on-premises and cloud-based environments. This means that SQL Server 2016 is designed to work in a hybrid cloud environment in which data and services reside in various locations. You get faster hybrid backups and disaster recover that lets you back up and restore on-premises databases to Azure and place SQL Server Always On secondaries in Azure. The figures below show how Stretch Database works.

stretch-database stretch-database2

With this flexibility come new ways to save money and address business needs. For example, storing data is a critical business requirement that can be very expensive. To reduce this cost, SQL Server 2016 introduced Stretch Database. It allows production databases to offload older (cold) data to the Microsoft Azure cloud without losing access to the data. Many enterprises need reasonably quick access to their cold data for compliance reasons, and they can now push that data to the cloud to save money on storage costs while still having ready access for compliance audits. (Blog 5 in this series will discuss SQL Server running in a Windows Server infrastructure-as-a-service virtual machine on Azure.)

This means you no longer need to rely on extremely expensive dedicated solutions from storage vendors. In SQL Server 2016, Stretch Database lets you keep as much data as you need for as long as you need, without risking business service level agreements or the high cost of traditional storage. Database administrators need only to enable the database for stretch, and the endless storage and compute capacity of Azure ensures that your data is always online.

In addition, with SQL Server Backup to URL, you can easily back up directly to Microsoft Azure Blob Storage. You no longer need to manage hardware for backups, and you get the benefit of storing your backups in flexible, reliable, and virtually limitless cloud storage. (For details, see “SQL Server 2016 cloud backup and restore enhancements.”)

Storage Replica delivers inexpensive high availability and disaster recovery

Storage Replica is a new feature in Windows Server 2016 that offers new disaster recovery and preparedness capabilities. For the first time, Windows Server delivers the ability to synchronously protect data on different racks, floors, buildings, campuses, counties, and cities. If a disaster strikes, all data will be at a safe location. Before a disaster strikes, Storage Replica lets you switch workloads to safe locations if you have a few moments warning—again, with no data loss. (Read about how customer Danske Fragtmaend takes advantage of Storage Replica for its zero-data-loss SQL Server failover strategy.)

Storage Replica enables synchronous and asynchronous replication of volumes between servers or clusters. It helps you take more efficient advantage of multiple datacenters. When you stretch or replicate clusters, you can run workloads in multiple datacenters so that nearby users and applications can get quicker data access. In addition, you can better distribute load and compute resources. Most important, you can implement this built-in functionality on commodity hardware and use it with emerging technologies such as Flash and SSD (as Danske Fragtmaend did) to build cost-effective, high-performance storage solutions that can work with existing SAN/NAS implementations—or even replace dedicated SAN/NAS solutions at a fraction of the cost.

Visit the website for more details and demos on Storage Replica.

Rolling, in-place upgrades and less downtime

Customers often tell us they want to use the latest releases of SQL Server and Windows Server, but they need the upgrade process to be less time-consuming and complex. Now they can take advantage of rolling, in-place upgrades from previous versions to SQL Server 2016 and Windows Server 2016—while dramatically minimizing downtime.

Windows Server 2016 Cluster OS Rolling Upgrade lets you upgrade the operating system of the cluster nodes from Windows Server 2012 R2 to Windows Server 2016 without stopping the Hyper-V or the Scale-Out File Server workloads. Not only can you upgrade the OS in place, but Cluster OS Rolling Upgrade works for any cluster workload, including SQL Server 2016.

For SQL Server customers, this is important because you want to move the base OS without having to reinstall and reconfigure SQL Server. Now, in a rolling approach, you can move a cluster node, perform an in-place upgrade and do a clean install while other databases are being serviced by other nodes. The in-place upgrade preserves SQL Server backup and restore history, preserves permissions and group settings, and saves about 20‒30 minutes of upgrade time per node in the cluster. You can achieve this with minimal or no interruptions to the workload that’s running on the cluster, so you can upgrade the cluster in place. With a Hyper-V or Scale-Out File Server Workload, there’s zero downtime, which means you don’t need to buy new hardware. (For details, see Cluster operating system rolling upgrade. To see a video demonstration, watch Introducing Cluster OS Rolling Upgrades in Windows Server 2016.

Better together adds up to the best database reliability at a great price

For mission-critical workloads, you can’t settle for anything less than the best—and most cost-effective—data platform running on the OS that has built-in synergy to ensure database uptime and reliability with advanced disaster recover across domains and sites. Without spending vast amounts of your budget on third-party storage solutions, you can get the functionality you need built into SQL Server 2016 and Windows Server 2016.

Ready to give it a try?

For more info, check out this summary of five reasons to run SQL Server 2016 with Windows Server 2016.

Read more