New and Improved

I’ve been using Windows Server 2012 for a while now, not just because it’s the newest and shiniest Microsoft operating system, but also because some of its new features are useful to me as both an architect and implementer of SQL Server platforms.

The most significant of these are in the failover clustering feature where some of SQL Server 2012’s requirements for Availability Groups can now be done in the GUI rather than the deprecated cluster.exe command line.  For example, modifying quorum voting, dynamic quorum models, and handling asymmetric storage.

Subtle Issues

Previously, I’d implemented clustered instances of SQL Server 2012 on Windows Server 2012 without any problems using the native RTM releases of both, in a production and development environments and all went well.  However, this week I began a new clustered platform installation and came across some real-world issues that I hadn’t seen before and thought I’d share.

Installing the .net framework 3.5

SQL Server 2012 requires that the .net framework 3.5 is installed prior to installation, however in Windows Server 2012 the install files for it are not shipped as part of the operating system.  Instead when you go to add the Windows feature, it will ask for the path to the source files on the Windows installation DVD to perform what it calls a side-by-side installation.  Sometimes, through luck, you’ll put the exact path details in correctly first time and it’ll work; the rest of the time you’ll find what is reported to be a bug where it’ll never accept anything you enter.

To get around this, you can use one line of PowerShell instead:

Install-WindowsFeature NET-Framework-Core –Source X:\Sources\sxs

Problem solved!

.net framework hotfix KB2750149

This hotfix is one of many that get installed automatically through the Windows Update service, however it has a side effect of breaking the Windows Failover Cluster manager, specifically when you click on the Roles branch (used to be known as Services).  You’ll receive a big error message screen starting with the text:

“A weak event was created and it lives on the wrong object,
there is a very high chance this will fail, please review 
and make changes on your code to prevent this issue”.

This is a known issue that Microsoft has acknowledged and is investigating.  In the meantime, the only workaround is to un-installed the hotfix KB2750149.

DNS Registration Failure

Another random error that I received was the DNS record for the cluster failing to get registered correctly during the creation of the cluster.  Windows Server 2012’s management tools now integrate with the other nodes in a cluster a lot more closely than they used to so even trying to make disk storage changes caused random server connection errors.  The main error I got when trying to bring the cluster itself online was the following:

Cluster network name resource ‘Cluster Name’ failed registration 
of one or more associated DNS name(s) for the following reason:
The handle is invalid.

I’m still not sure of the cause, apparently it could have been related to Active Directory permissions, but the solution that worked for me was to Repair the cluster’s virtual network name with the following two steps:

Firstly, taking the virtual network name offline.

Secondly, repairing the virtual network name.

Finally, bring the virtual network name back online.

I’ve no idea if this will fix similar issues you may get, but knowing the option to repair the cluster’s DNS record is useful to know.

Summary – A Powerful Combination of Platforms

Implementing a clustered instance of SQL Server 2012 on Windows Server 2012 is still a very capable and impressive combination, and something I’d still recommend be the default option these days.

This blog post covers what’s true about all failover cluster installations and that’s that they touch every part of the server’s infrastructure: storage, networking, operating system, active directory, as well as SQL Server.  Consequently, minor tweaks to any of these areas can leave the SQL Server professional with a new area of the infrastructure to master.

In December 2012, I presented about “Accelerating the Data Warehouse Using xVelocity Columnstore Indexes” at a SQLSanta event in London.

My presentation’s purpose, and demos, were to show how this type of database engine index, that’s new in SQL Server 2012, significantly improves the performance of data warehouse style queries. 

Columnstore indexes, and their complimenting Batch-Mode Query Processing feature, are used transparently by SQL Server once they’re created so the benefit for SQL Server professional is that that there’s no need to change existing data warehouse applications or queries to benefit from these performance improvements. 

Slides

The presentation’s slides are available here.

Session Questions

Below are some of the answers to questions that I remember giving during the session:

• Even though the levels of internal compression of data within a Columnstore index are affected by the cardinality of the index’s data, the performance benefits of Columnstore indexes are so great compared to a non-clustered index that your data’s cardinality is not likely to influence whether you use Columnstore indexes or not;
  
• You can use Columnstore indexes with AlwaysOn Availability Groups;
  
• The SQL Server query optimiser will automatically consider using a Columnstore index and Batch-Mode Query Processing itself, you don’t have to re-write queries to use start using them;
  
• Batch-Mode Query Processing only works with Columnstore indexes;
  
• The column order, and number of Columnstore index keys, do not affect the performance of a query that using the Columnstore index.

In the last couple of months my community activities list has had things leave it and new things join it, so I thought share my future plans, and promote some of the events I’m supporting.

Microsoft Certified Architect : SQL Server – Giving back

Preparing for my MCA Board was the hardest, yet in hindsight the most rewarding and interesting, thing I’ve ever done.  The subjects it covers still interest me to the extent that I’m now contributing to the MCA programme itself, allowing the next people through the certification’s process to benefit from someone else’s experiences and inputs.

For those of you with the pre-requisite SQL Server MCM certification and who are interested in taking the next level of certification, there are more details here:
http://www.microsoft.com/learning/en/us/mca-certification.aspx

Additionally, free feel to email me if you have any questions about my experience of the programme gavin@coeo.com

Accelerating Data Warehouses with Columnstore Indexes – SQL Santa – 14th December

The fact that Columnstore indexes work in a totally different way to how we’re used to SQL Server working makes me curious about them.  I’m sharing the learnings from my curiousness, and outcome from a customer proof of concept test, in a hour’s presentation at a London event in mid-December.  SQLSanta is a day long even at Microsoft’s London venue, and has a range of Microsoft Product Marketing, and industry professionals, presenting about contemporary SQL Server industry topics.

Although the event is now fully booked, you can find more details about the sessions here http://www.sqlserverfaq.com/events/452/SQL-Santa-2012.aspx as I suspect some will be presented at future community events, I’ve certainly submitted mine for some already.

Mission Critical Data Platforms on Windows Server 2012 Pre-con – SQL Saturday 194 – Exeter – 8th March

Christian and I are hosting this one day workshop with the agenda of un-leashing the new capabilities of Windows Server 2012 to make SQL Server deployments even more highly available in even more scenarios.  Rather than reviewing the introductory level aspects of Failover Clustering or Availability Groups, the day looks at how previous platform limitations can now be overcome using native features in the latest Windows operating system.  These include subjects such as the merging of high-availability and disaster recovery design patterns, the use of new storage formats such as SMB file shares, and using Hyper-V virtual machines in HA and DR configurations.

You can read more about our pre-con, and book, here http://sqlsouthwest.co.uk/SQLSaturday_precon.htm