Share via

Demo Transcript: Configuring SQL Server 2012 AlwaysOn Failover Clustering

  • Article

SQL11UPD01-TSCRIPT-08

This wiki is a transcript of a previously recorded video.

Related content assets:

Configuring AlwaysOn Failover Clustering

Hi. Welcome to introducing SQL Server 2012 AlwaysOn. This is the demo for configuring SQL Server 2012 AlwaysOn Failover Clustering. My name is Jonathan Kehayias. I’m a SQL Server MVP, a Microsoft Certified Master for SQL Server 2008, as well as a Principal Consultant for SQLskills.

So if we jump into our already-configured AlwaysOn 2012 SQL Server failover cluster – if we look, we’ve already got our failover cluster instance formed. It has two nodes associated with it and we have our SQL Server failover cluster instance installed inside of our failover cluster.

Now with SQL Server 2012 failover clusters we get the new failure condition level which gives us our predictable failure configuration for our failover cluster environment. The way that we access that is we go to our SQL Server resource inside of our failover cluster resource group and we open the properties for it.

And if we click on the properties tab, we have our failure condition level here which allows us to specify the value, either 0 through 5, that gives us our failure condition for the automated failover in our environment. And just to recap, a value of 0 specifies that there’s no automated restart or failover for the environment. A value of 1 will fail over only when the server is down. A value of 2 will fail over when SQL Server is up (or the service is showing in a running state) but it is not responding. A value of 3 is the default and that’s going to fail over for critical errors like a stack dump inside of the instance. A value of 4 will fail over whenever there’s a resource that’s unhealthy – for example, low memory conditions inside of the server that are raising a lot of ring buffer entries in the dm_os_ring_buffer. And a value of 5 will fail over for any qualifying condition inside of the environment.

And all we have to do here is simply change this value and when we click apply that will take effect inside of our environment.

The other thing we can change is the health check timeout, which is the value in milliseconds that defines how often the checks are going to occur. So we could adjust this from the default 60 right here… 60 seconds - make it shorter, make it longer depending on what the actual requirements for our environment is.

Keep in mind that this replaces the IsAlive and LooksAlive checks for our failover cluster instance and the way that it does that… if we go over to Management Studio, is through sp_server_diagnostics, which is a new stored procedure that gets executed in the environment. If you run/execute sp_server_diagnostics it will output information about the health of the environment and it will have the information about the 5 different areas that we’re looking at: our system, our resource, our query processing, the I/O subsystem, and events.

And we look; we can expand the XML data that’s being output. We can also see what the state is associated with those inside of the environment and we can take a look at what kind of information’s being used to evaluate what the health of our environment is. For example, the amount of available paging file or what the size of the working set for the SQL Server instance is, the percent of committed memory that’s in the working set right now.

These are all things that will drive the determination whether or not the instance is healthy based on our failure condition level and whether or not it should automatically restart or fail over inside of the environment.

Return to [[SQL Server 2012 Developer Training Kit BOM (en-US)]]