What Happens to Pages in the Buffer Pool when your Availablity Group Fails Over?

Recently at SQL Saturday Philadelphia, we started discussing failover  as it relates to mirroring and Always On Availability Groups. Specifically, we were wondering what would happen if you had a relatively busy readable secondary replica (which would have a lot of pages in the buffer pool on the secondary instance) and if those pages would be flushed from cache or anything like that. So I reached out to the product group and Kevin Farlee from Microsoft was extremely helpful:

Pages in the buffer pool are still valid, as they are updated by the redo thread.  By the time the secondary has transitioned to primary, all pending updates will have been applied, so pages in the bufferpool will all have correct contents. Note that the set of pages in bufferpool may be different between primary and secondary so you may have some cache warm up to do.”

So in a nutshell, if you have a busy readable secondary your cache won’t take much time to warmup, since the hot pages are already there.

PASS Summit 2016 I’m Speaking

pass_2016_websiteI have been honored to be selected to speak at this October’s PASS Global Summit in Seattle this October. I will be speaking on a topic near and dear to my heart, Security in Azure SQL Database. I worked last year to write a white paper with Stacia Varga and Microsoft discussing the best practices and security for Azure SQL Database.

In this session we’ll talk about all the encryption features, the security certifications that Azure has, and how audit is better in Azure SQL Database than it is in SQL Server. Additionally, you’ll learn about some of the other enhancements Microsoft has made to protect your data in Azure.

 

 

SQL Server 2016—The Licensing Info

SQL Server 2016 launched last week to great reviews and with a ton of great new features. I have been working with this version for well over a year now and extremely happy to see it hit RTM and be broadly adopted. So as DBAs it always sucks when you get excited about new features, only to find out the price changed, or vendor “O” made that feature a cost option. So what’s new with SQL Server 2016 licensing? (you won’t this as a session title at any upcoming SQL Server events).  Well first the good news—SQL Server 2016 is the same price and 2012 and 2014 (roughly $6800 core for Enterprise Edition). That’s definitely good news—Microsoft gave us a bunch of new functionality and didn’t raise the price. Additionally, if you see my below post on what is in Standard Edition, they added a lot of functionality there, too.

But we know finance and marketing employees have jobs to do as well, and there is no way they were letting a major version release happen without some changes. So let’s take a look at the one’s Denny Cherry (b|t) and I could glean out of the licensing guide. Please download and read for yourself.

Licensing Changes

Additional licenses are required when:
 A single hardware thread is supporting multiple virtual cores. (A core license is required for each v-core.)
 Multiple hardware threads are supporting a single virtual core simultaneously. (A core license allows a
single v-core to be supported by a single hardware thread.)

What does this mean? It means you can’t over provision CPUs on your VMs (which you shouldn’t be doing for DB servers anyway). There may be something that applies to hyperthreading here, but if you are licensing individual VMs, you probably shouldn’t be using hyperthreads.

“Beginning with SQL Server 2016, deploying and running SQL Server PDW is done through SQL Enterprise
Edition Per Core licensing with SA coverage. The number of SQL Server Enterprise Edition core licenses for an
APS appliance will depend on the total number of physical cores in the SQL Server PDW compute servers
configured within the appliance.”

What does this mean? It means you can actually know what an APS costs, at least from a licensing perspective. Hardware costs will need to be gathered from a reseller. This is a good change as it makes SQL Server’s pricing consistent across platforms (pro-tip: use SQLDW)

“For each server licensed with SQL Server 2016 and covered by active SA, customers can run up to the same
number of passive failover instances in a separate, OSE to support failover events. A passive SQL Server
instance is one that is not serving SQL Server data to clients or running active SQL Server workloads. The
passive failover instances can run on a separate server. These may only be used to synchronize with the primary
server and otherwise maintain the passive database instance in a warm standby state in order to minimize
downtime due to hardware or software failure.

 The secondary server used for failover support does not need to be separately licensed for SQL Server as
long as it is truly passive, and the primary SQL Server is covered with active SA. If it is serving data, such as
reports to clients running active SQL Server workloads, or performing any “work”, such as additional
backups being made from secondary servers, then it must be licensed for SQL Server.”

What does this mean? I had to consult Microsoft on this one, as it was a change in my understanding of the “free” secondary licensing benefit. Basically, if you are going to dedicated hardware (that you own or lease) your secondary license (if you have SA) is still included in your primary license. However, if you go to Azure from on-prem for your HA model, you will need to license the secondary. This does get murky because if both of those workloads are in Azure, you only license the primary.

“All SQL Server licenses with active SA can be reassigned to another server within the server farm as often as
needed; however, they can only be reassigned to another server in another server farm, or to a non-private
cloud, once every 90 days.
A server farm may consist of up to two data centers located in time zones that are within four hours of
one another and/or with the European Union (EU) and/or European Free Trade Association (EFTA).
A given data center may only be part of one server farm.”

What Does this Mean? Basically you can’t cross an ocean for HA or DR and not pay for it.

SQL Server Developer Edition
SQL Server 2016 Developer Edition is a fully featured version of SQL Server software—including all of the
features and capabilities of Enterprise Edition—licensed for development, test and demonstration purposes
only. SQL Server Developer Edition may not be used in a production environment or with product data. Any
test data that was used for design, development or test purposes must be removed prior to deploying the
software for production use.
Customers may install and run the SQL Server Developer Edition software on any number of devices. This is
significant, because it allows customers to run the software on multiple devices (for testing purposes, for
example) without having to license each non-production server system.”

What does this mean? It basically means only your production environments need to be licensed as long as you are following Microsoft’s rules for not using production data. This is a huge benefit, note that you can’t just restore prod to dev, you need to create some testing data, which is best practice anyway.

“Version Upgrade Rights are offered as a Software Assurance (SA) benefit for qualified licenses and allow
customers access to upgrade their deployments at no additional cost. Existing SQL Server 2012 software
licenses covered by SA are automatically upgraded to licenses for the corresponding SQL Server 2016
edition.”

What does this mean? You need to have 2012 licenses (at least) to upgrade to SQL 2016. Basically Microsoft wants to make sure you went through the core conversion in 2012.

Version Upgrade Rights are offered as a Software Assurance (SA) benefit for qualified licenses and allow
customers access to upgrade their deployments at no additional cost. Existing SQL Server 2012 software
licenses covered by SA are automatically upgraded to licenses for the corresponding SQL Server 2016
edition.”

What does this mean? If you had BI edition, your licenses will get converted to Enterprise Edition. If you read further into page 29, you will see that BI edition customers will be treated quite favorably. Any time there is a change like this, it’s usually a good time to negotiate with your Microsoft sales professional about getting a better deal.

Summary

There are no earth shattering changes in SQL Server 2016 licensing. The developer edition changes are probably the most significant, and can reduce your overall costs a great deal.

%d bloggers like this: