SQL Server 2012 eLearning Resources

In both of the sessions that I covered in SQL Server Pro’s eLearning event, Practical SQL Server Improvements for Businesses, I made mention of additional context, guidance, and links that could be used to better get a handle on SQL Server 2012 AlwaysOn functionality as well as Contained Databases and Indirect Checkpoints.

SQL Server 2012 AlwaysOn

One of the key things I tried to iterate in both of my sessions is that while it’s a bit silly for Microsoft to have called improvements/successors to both Clustering (AlwaysOn Failover Cluster Instances) and Mirroring (AlwaysOn Availability Groups) by the same AlwaysOn ‘moniker’, the reality IS that BOTH successors do, indeed, provide some amazing benefits in the sense that they BOTH offer not only HA benefits, but DR benefits as well. Moreover, what makes AlwaysOn Availability Groups so powerful is the fact that DBAs can now use this single technology (and/or this single technology in conjunction with AlwaysOn Failover Cluster Instances) to achieve solutions that enable High Availability and which provide Disaster Recovery coverage as well. All from the same set of tooling.

But, as cool as both AlwaysOn Failover Cluster Instances (FCI) and Availability Groups are, they represent a number of changes and new potential pitfalls and gotchas that you really need to be comfortable with before just ‘willy nilly’ rolling these solutions out into your environment. Accordingly, the goal of both of my sessions was to call out the BENEFITs of these new technologies and provide a quick overview of some of the LOGICAL shortcomings that you’ll encounter when using them. (And to me, the BIGGEST problem with AlwaysOn Availability Groups isn’t a technical issue (though there are plenty of those). Instead, it’s the fact that AlwaysOn Availability Groups are ONLY supported in SQL Server 2012 Enterprise Edition.)

Still, having the ability to create synchronous-commit availability replicas that can be used for automatic failover is cool. So too is the prospect of being able to set up asynchronous-commit replicas in different subnets or locations that can be used as a failover solution in cases where you might lose an entire data center or site (such as where you had your high-availability replicas running in redundant fashion.). Moreover, being able to put Availability Group nodes on actual Failover Cluster Instance nodes means that you can come up with some very powerful HA solutions (complimented by DR ‘backups’ if you will) that are just far above and beyond what we were capable of achieving previously with SQL Server in the past. Especially since SQL Server 2012 Failover Cluster Instances can now ‘stretch’ across multiple subnets (out of the box).

Additional Resources

At any rate, the roughly two hours that I spent trying to explain so many of these ‘ins and outs’ of AlwaysOn and some of the OTHER great new features that SQL Server 2012 provides in terms of benefit for both HA and DR just wasn’t enough to provide an full overview of everything you’ll need to know before putting these solutions out into production in your own environments. As such, here’s that list of additional links and resources that I mentioned.

Additional AlwaysOn Resource

Jonathan Kehayias (blog | twitter) has done a great job of whipping through gobs of the nitty-gritty details of Failover Cluster Instances and Availability Groups in a whole suite of  presentations that you can find here – as part of a greater set of training materials available as part of a Microsoft TechNet Training Kit that covers a number of different SQL Server 2012 features and improvements.

Mark Broadbent (blog | twitter) also provides a great overview of some management aspects of AlwaysOn that I just didn’t have time to cover – failover preferences and ‘weights’ in his Weight doesn’t ALWAYS have to be AlwaysOn post on his blog – something that I highly recommend that you review.

There’s also a great four-part series of articles on SQLServerPerformance.com that provides a nice introduction into what’s required to set up and manage AlwaysOn Availability Groups. But, sadly, these articles are linked together very well – so I’m providing direct links to all four parts here: Part I, Part II, Part III, Part IV.

Another great and obvious source of additional background information and details on AlwaysOn is, of course, Books Online – where there’s a whole set of documentation for AlwaysOn Failover Cluster Instances and AlwaysOn Availability Groups. Additionally, I also found the Requirements for Setup and Getting Started sections obviously very helpful. So too was the section on Troubleshooting. There’s also this important section that covers interactions between AlwaysOn Availability Groups and AlwaysOn Failover Cluster Instances.

More documentation that you’ll want to pay attention to as well is the guidance that Microsoft provides for interacting with Availability Group Listeners – or managing connections through SQL Server clients that will be interacting with Availability Group databases.

Automatic Page Repair
I mentioned the fact that AlwaysOn Availability Groups continue to provide automatic page repair support – something that Mirroring also provides, but which is fantastic for recovering from certain types of corruption without down time.

Licensing and Feature Comparisons
Another thing we focused on in my presentations was licensing options and feature availability across editions of SQL Server 2012 – where we focused on how AlwaysOn Availability Groups are ONLY supported in Enterprise Edition, and where Microsoft states that Standard and BI Editions get so-called ‘basic high availability’.

Database Recovery Advisor
I also very briefly mentioned the new UI enhancements in SQL Server Management Studio 2012 that Microsoft is collectively referring to as the Database Recovery Advisor – and Ashish Kumar Mehta has done a great job of providing a more in-depth overview of these new features over at MSSQLTips.com.

(Partially) Contained Databases
While I mentioned in my presentations that Contained Databases are a FANTASTIC new addition to SQL Server 2012 in terms of making it so much easier to set up both HA and DR environments (AND keep them synchronized), the reality is that Contained Databases come with a host of additional benefits above and beyond just those that apply to HA/DR concerns. And, as I mentioned there’s also a great DMV, sys.dm_db_uncontained_entities which you can use to determine how ‘portable’ your currently contained databases are.

Indirect Checkpoints
Finally, Indirect Checkpoints (mentioned as a feature of AlwayOn Failover Cluster Instances – though there’s no requirement to use them ONLY in conjunction with FCIs) are a great new feature that SQL Server 2012 brings to the table – both in the sense that they now allow DBAs to specify target recovery intervals on a database by database basis (instead of just at the server level only), and because they also provide the option to provide a bit of additional control or specificity over recovery priorities as well – something that’s important on servers with LOTS of databases where you might want certain DBs to come online before others. Again, though, this is a POWERFUL new feature – so make sure you use this new option with all of the requisite caution and testing it really requires.