T-SQL window functions talk summary and sample scripts

Yesterday, I spoke to the Steel City SQL User group on T-SQL window functions. Below is a short summary and links to the sample scripts and database.

The T-SQL window functions came about in SQL Server 2005, but you really want SQL Server 2012, because many functions were added and also because support for PARTITION BY was introduced only in 2012.

The basic script below does a good job demonstrating each function with very small data sets in temporary tables. This makes it easy to examine the output. For larger examples, I used two real-world data sets: domestic flight data from October and November 2014 (about 1 million rows) and course section data from Troy University. The flight data is available for download (caution: large file), but I can’t make the course section data available.

The functions that I focused on during my presentation were

  • ROW_NUMBER
  • RANK, PERCENT_RANK and DENSE_RANK
  • LAG and LEAD

I briefly showed the other window functions also, like CUME_DIST, PERCENTILE_CONT, etc. Some of the sample scripts also include turning SUM and COUNT into window functions instead of aggregate functions (i.e no GROUP BY required).

Here are the downloads (hosted on OneDrive):

SQL Saturday #342 in Mobile is tomorrow

Here is a summary of my session, at 3 PM in “Room 2″ (that just sounds too easy…)

Of course, you really want to sign up for the other speakers, but just maybe at 3 PM my session will appeal to you.

P.S.: Bring your family to Mobile! Tomorrow they can get free admission to the USS Alabama and Battleship Memorial Park.

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 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.

Conclusion

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.

Follow

Get every new post delivered to your Inbox.

Join 136 other followers