SQL Saturday 328 Birmingham 2014 is next week!

SQL Saturday 328 Logo

In my usual fashion, I am both late in blogging about and in preparing for the next SQL Saturday.

Anyway, after a one-year hiatus, SQL Saturday is back in Birmingham on Saturday, August 23, 2014. I’ve had very enjoyable experiences on every visit to Birmingham’s SQL community (Steel City SQL) events and based on the schedule for this year, I think any DB professional should attend.

I am presenting two sessions, the information for which I’ve included below. I hope to see you in Birmingham next week!

Windows Server 2012 R2 High Availability for SQL Server

After presenting this session a few times, I now know to make sure attendees a clear from the beginning that this is a Windows-focused session! With the increasing adoption rate of virtualization, creating SQL Server clusters is now within reach of many more DBAs. What do you need to know about Windows Server 2012 (R2) virtualization and high availability to set up your first cluster? Come learn from a Windows Server admin and (accidental) DBA who’s been building SQL Server clusters since 2000 (the year and the version!). We’ll discuss initial planning, the process and the maintenance best practices. There will be demos too, so if you want to see a multi-node SQL cluster run on a Lenovo X230 tablet, come see (it’s pretty cool!).

SQL Server Optimization for SharePoint

This is a new session for me. DBAs know that SharePoint just doesn’t play nice with SQL Server. SharePoint administrators know that DBAs just don’t understand SharePoint. This session will show hands-on how to get a SQL Server instance set up more effectively for SharePoint 2013. It’s not black magic and it doesn’t involve undocumented trace flags. Maybe DBAs and SharePoint admins can’t get along, but at least you’ll be able to cooperate after this session.

TechDays San Francisco Wrap Up

My first visit with the Pacific IT Pros group was a fun experience. The Pacific IT Pros group put on a great event with their TechDays San Francisco. Kudos to the organizers and thank you to the sponsors!

For the attendees who attended one or both of my sessions about the Microsoft Deployment Toolkit, thank you! You were a great group to present to. As promised during the presentation, here are the links to the slide decks as well as my demo scripts:

Getting Started with MDT 2013 to Deploy Windows 8.1

Application Deployment Tips and Tricks

Presenting at TechDays San Francisco only two weeks away

On June 5-6, the Pacific IT Pros user group is hosting their TechDays San Francisco event. There is still time to register, and for 2 days of training from recognized speakers (and me too) the price is a bargain. Head over to http://techdays.org and register today.

Below is a summary of the sessions I will be presenting on Deploying Windows 8.1 with MDT.

Getting Started with MDT 2013 to Deploy Windows 8.1

Designed for IT professionals who are new to MDT and/or Windows 8.1, this session demonstrates how to set up your first deployment shares and get you started quickly. Many Windows 8.1 specific deployment tips will be included, such as customizing the start screen and Windows Modern app deployment. Other demonstrations will include application silent installers and setting up PXE. We’ll look at the most basic aspects of troubleshooting in MDT. The session overall will focus on applying some tried and true practices of OS deployment with MDT.

Application Deployment Tips and Tricks for MDT

MDT provides out-of-the-box support for deploying Microsoft Office applications. Beyond those, you need to find out the silent installation options for other applications. This session demonstrates some practices to make application installations consistent, regardless of their origin. You will learn general approaches to finding, testing and implementing silent installations for many common and some not-so-common applications. You should walk away from this session confident that there will be far fewer applications in your inventory that will require manual installations as part of PC deployments.

Case of the Unexplained (Aelterman Edition): fingerprint readers, Windows Biometric Service and a few false turns towards VPN and dead SSD

So I am borrowing this title from Mark Russinovich because this one took a while to figure out. I am writing this down mostly for my own records, but perhaps someone may find value in this encounter.

Rewind to SQL Saturday 285 Atlanta 2014. At the end of the session, I get distracted and close the lid on my Lenovo X230 Tablet without turning the system off. Put the system in the backpack. For most, that’s not a problem, but I set Power Options to Do Nothing when closing the lid (this has to do with fingerprint reader access while docked under a monitor stand). The next day, I get the laptop out and turn it on. No power. Makes sense, I didn’t turn it off so it drained the battery and eventually shut down. Connect to power and the battery charge light blinks orange (it either means “really empty” or “bad battery” – the former in my case).

Then, I swipe my finger to turn on the system, perform pre-boot authentication, boot into Windows and automatically log on. No response from the fingerprint reader. It was not a bad swipe because then the light would blink orange. Try again, no avail. Still no lights. Turn the system on manually. Asks for the power-on password – but still no finger swipe accepted. I type in the power-on password. It continues to boot. In Windows, no option to log on with fingerprint reader. Log on with password and get to work.

By this time, I am suspecting that the fingerprint reader died. I have several conferences and personal travel trips coming up, so I figure I’ll deal with it later. I can live without it for a few weeks. I happily use the tablet for a whole week. Then I get to Houston for TechEd North America 2014. I receive an e-mail from our backup system that the tape drive needs cleaning. No problem, I’ll log on to the VPN, move a cleaning tape from the library to the drive and back and move on. I fire up the VPN client, my password manager and attempt to connect. All of a sudden, after entering the password, my system freezes. No LBSOD (light-blue screen of death, in Windows 8), no response to power, etc.

About two weeks earlier, our IT group had asked me to test the VPN on Windows 8.1 because other users had reported stability issues. It worked fine for me, but my thoughts went back to that. I thought perhaps connecting to the VPN caused the freeze. Pull power cable, pull battery, turn it back on.

Then I get the dreaded HDD0 not found BIOS message. Uh oh. Now I am in real trouble. Or maybe not. Maybe my backpack got bumped too hard and something inside came loose. Like any good boy scout (even though I only made it two years) I come prepared. I open up the laptop, remove the SSD, inspect the connectors and put it back. Same result…

So, now I am faced with a broken SSD. But it’s a nearly-new 1 TB Samsung 840 EVO, supposedly the most reliable SSD ever built? My thoughts are racing to the work I might have lost building some VMs for an upcoming talk, etc. I decided to hook up the SSD to my Surface Pro tablet (thank you Microsoft for the full size USB 3 port!). It is recognized immediately and works fine. I am still considering the possibility that perhaps the drive is about to fail, so I don’t leave it connected for too long. My Surface Pro doesn’t have the kind of storage needed to backup what I need to safeguard. And while I have a 1.5 TB external drive with me, I don’t have a USB 3 hub that would allow me to transfer that kind of data in any reasonable amount of time.

I even try to boot the system from a USB connection thinking if the fingerprint reader went bad, why couldn’t the SATA connector have gone bad?

I ask one of my IT helpdesk techs to overnight a Windows 8.1 USB installation disk and a new SSD drive. That will allow me to see if the laptop is dead, and if not, install a new OS and get my critical data that wasn’t backed up yet. In between, I take a look at the BIOS of the machine. Everything seems in order. I am thinking about the possibility that this dead fingerprint reader has something to do with it – but why now?

Turns out, I am having a lot of trouble accessing the BIOS. Sometimes I can get in, sometimes not. It seems to work more reliably to access the BIOS with the 1 TB SSD removed. I am hunting in the BIOS for a way to turn off the fingerprint reader, but can’t find it (it exists, I just couldn’t find it). While in the BIOS, I realize that I never enabled UEFI Secure Boot and make a note to make sure to do that with the next OS install.

When the spare drive comes in, I mount it, install the OS. It goes off without a hitch. So now I am pretty convinced that the SSD is bad. After installing the OS, I hook up the “bad” SSD and my 1.5 TB HDD and start copying data. I might as well get everything off as long as I am copying data, so I end up copying about 150 GB. The SSD performs great. How is that possible?

I decide to find a tool that can read S.M.A.R.T. data from the USB bus. I found a tool and it turns out that no issues were reported at all.

Now, I decide the take things a step further. Back to the BIOS and reset all security data, including TPM keys, secure boot keys (even though disabled, the keys exist) and I finally manage to find the setting to disable the fingerprint reader. (Note the new OS on the new SSD didn’t mind the faulty fingerprint reader.) One more attempt at mounting the “bad” SSD. IT WORKS!

Boot into Windows, no problems. So what happened? I call upon an old friend, the Reliability report. My reliability index tanked after SQL Saturday…because the Windows Biometric Service kept crashing. Turns out (what I realize now) that the bad hardware or connection to the fingerprint reader kept “finding” the fingerprint reader, then not, then again. This caused the Windows service to presumably go nuts and crash – many times that week. I just never noticed it.

What caused the system to finally freeze may or may not have been related to the VPN connection. I also don’t quite understand why it wouldn’t let me reboot anymore until I cleared TPM keys and/or disabled the fingerprint reader. Clearly, some of this did the trick.

For my upcoming conference trips, I’ll be sure to take a spare laptop just in case I misdiagnosed the issue still, or in case additional hardware fails.


My best guess at this time is that after SQL Saturday, my laptop got really hot in my bag. That caused an issue (fortunately only) with the fingerprint reader and subsequently caused a system freeze.

Categories: Troubleshooting

Alaska SQL User Group Presentation

I was pleased to have been able to do a virtual presentation for the Alaska SQL User Group today. The Alaska SQL User Group is a relatively new group and I certainly feel honored to have been part of their speaker line-up already.

I presented my talk on Windows Server 2012 R2 High Availability for SQL Server. It’s mostly a Windows failover clustering talk, but designed for DBAs because I embed as many SQL Server-relevant considerations as I can muster.

The group records their speakers and within a few days you will find my presentation alongside those of others on their YouTube channel. My slide deck is available from SlideShare.

iSCSI Target in Windows Server 2012 R2 *does* support dynamically expanding VHDX

As an update to a previous post (http://svenaelterman.wordpress.com/2013/01/07/iscsi-target-in-windows-server-2012-does-not-support-dynamically-expanding-vhds/), Windows Server 2012 R2 now supports mapping iSCSI targets to dynamically expanding VHDX.

It comes with a major catch though: you cannot create, for example, a 2 TB iSCSI target on a 60 GB disk. You can however create two 60 GB targets on a single 60 GB disk. In other words, each target cannot have a maximum size larger than the size of the disk. If you attempt to create a larger iSCSI target, you’ll get this friendly error message:

Server 2012 R2 iSCSI Wizard

Error message when trying to create a 2 TB iSCSI target on a volume with only 0.05 TB free space.

For production environments, this feature is probably of little use anyway. However, for test or staging environments where space may be at a premium but you want to test automation or a script, this could have been very handy. And of course, it would be very handy for speakers who want to build demo environments with realistic numbers without needing an actual multi-terabyte storage array in your trunk.

There is a bigger picture consideration. For me, this was the first time I’d created an iSCSI target on Windows Server 2012 R2. It’s not been available that long and I have one Hyper-V host running in production but there it is not an iSCSI target host. With more frequent releases of Windows Server, Microsoft will be adding value to the operating system in small increments. The increased release cadence may lead to more Windows versions being skipped – especially on the server OS. As IT professionals, we should however keep up with each release and its new and enhanced features because otherwise the leap after 3-4 years may be much larger than we anticipate.

Have you deployed “12R2″ in your lab? Have you experimented with the new features such as Work Folders or Hyper-V Replica to a third server? What do you do to keep your lab and skills current?

Windows 8.1 Update Cannot Connect to SSL-Enabled WSUS 3 SP 2

If you’re running Windows Server Update Services (WSUS) 3.0 Service Pack 2, have enabled SSL (the default I think) and now have Windows 8.1 Update clients, you’ll find that those clients are unable to connect to the WSUS server to check for updates.

I am writing this blog post to collect all symptoms and all workarounds and solutions in one place.


The symptoms are as follows:

  • Windows Update fails with Code 80072F8F
    Lots of material has been written about this Windows Update error. While it all relates to SSL/TLS failures, there is likely nothing wrong with your computer’s time or root certificates if it’s only happening to Windows 8.1 Update (KB 2919355) machines.
  • System Event Log Event ID 36888 from Schannel source
    Schannel is the Windows component responsible for establishing “secure channels,” like SSL and TLS.
    If you arrived at this page looking for info about this error but not related to WSUS, try eventid.net.

If you try to get updates online from Microsoft Update, you will find that it works because Microsoft’s servers are configured to accept TLS 1.2 connections.


The cause of the problem is that Windows 8.1 Update will check for updates using the TLS 1.2 protocol, which is not enabled by default on Server 2008 R2 and not at all available on earlier versions.

Solution and Workarounds

For WSUS running on Windows Server 2008 R2, my recommended action is to enable TLS 1.2. It requires a registry edit for which I’ve provided the contents of a .reg file below. You can also use the workarounds for older versions below, but those are less attractive options, IMHO.

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2]

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server]

If you would rather not edit the registry yourself or want to enable numerous protocols and ciphers all at the same time, you may be interested in a tool by Nartac Software that can do it for you: https://www.nartac.com/Products/IISCrypto/Default.aspx
[Please note: I have not tested this tool and can't vouch for its effectiveness or reliability.]

You will need to reboot the server after making the change.

For WSUS running on Windows Server 2008 and earlier, you will need to either

  • Disable SSL until the fix is available
  • Manually have those clients check Microsoft Update

Additional references

Enabling TLS 1.2: KB 245030

WSUS Product Team Blog: Windows 8.1 Update (KB 2919355) prevents interaction with WSUS 3.2 over SSL


Get every new post delivered to your Inbox.

Join 131 other followers