The weekend before last, I attended my 4th #SQLSaturday event; my 2nd in San Diego CA (the others were Orange County CA, which is equally fantastic, and a little closer to work, though about the same distance from home). If you haven’t heard of or been to one, check out the home page and find one coming to a city near you! They’re fabulous FREE training events for the MS data platform, including but certainly not limited to SQL Server. For example, you’ll almost always find Kevin Feasel talking about aRrr or Kafka or Hadoop.
Did I mention free?
So I thought I’d share a few things that I learned this time!
The LinkedIn app’s Killer Feature
Did you know? The LinkedIn app has a “find nearby” feature that uses
magic your phone’s various radios to instantly connect you with a fellow user who has the app open near you. It’s awesome! Now you don’t even have to look up from your convention coffee and security-blanket (phone) to network — just fire up the app, go to the People tab, hit “Find Nearby”, and commence trolling. =P
No, that’s horrible; be a normal human and talk to people. The tech is just there to help make the post-conversation connection.
Storage Myths Busted
SANs have become a bit like the printer industry — You don’t pay a lot for the enclosure, the device itself, i.e. the SAN box & software; but you pay through the nose for ‘refills’, i.e. the drives that your SAN vendor gods deem worthy of their enclosure.
It’s frighteningly accurate. Ask your storage admin what it costs to add a single drive (or pair of drives, if you’re using something with built-in redundancy) to your SAN. Then compare that cost with the same exact drive off the retail market. It’s highway robbery. And we’re letting them get away with it because we can’t evolve fast enough to take advantage of storage virtualization tech (S2D, SOFS, RDMA) that effectively makes servers with locally attached SSDs a superior architecture. (As long as they’re not using a horribly outdated interface like SAS!)
Data Protection and Privacy is Hard
But completely necessary. We all need to become security practitioners to some extent. Even if it just means raising and documenting our concerns to our bosses. The great state of California has even jumped on the bandwagon with its very own privacy act. Still in the early stages, yet on the heels of GDPR, it can only mean that there will be more to come.
A few concrete action items from this, which should be “fairly simple” (with a big ol’ asterisk that says “depending on your organization and your change-management process”).
- At least encrypt your database backups. (And make a plan to implement some kind of “full” encryption across the data estate, be it TDE or AE or app-driven encryption via your developer teams.)
- Stop using
sa! Reset the password, and disable it. Yes, your Agent Jobs will still run just fine.
- Disable Named Pipes & Shared Memory protocols; just use TCP/IP. Disable the SQL Browser service.
- Cut off your SQL servers from the public Internet (yes, you should still patch them; just download the patches to a fileshare and install them using PowerShell!). And stop letting people run SSMS on the server; that’s what client machines are for!
Columnstore All The Things!
Here are just a few of the tips I gleaned from the session on this:
- They were designed for data warehouses, but…
- They’re also great for “operational analytics” — where you want to do aggregate reporting on your ‘live’ data, but that performance usually kinda sucks (and you don’t want to lock up those tables anyway).
- Best with SQL 2016 or higher; 2012’s “v1” implementation was horrible, and 2014’s “v2” was semi-usable but still had some major drawbacks
- Best candidate tables are “very large” (millions of rows or more), and best candidate columns have “low cardinality”, meaning they’re not full of unique values — they should be “compressible”. A simple example would be a Customer’s “State of residence” — you probably have millions of customers, but only 50-ish “State”s, and your typical report is asking “Hey, how many Customers per State ‘do X'” — that’s a great candidate for a
Users Don’t Like Date-Pickers
I mean they’re still better than text-entry boxes, but we can do better. Talking about SSRS here — reporting services, i.e. “the poor-man’s Tableau”.
Picture a typical business user, middle-manager type, going to your SSRS report that you just built for him/her. The first thing it asks them to do is “pick a start-date and an end-date” to define the “reporting period”. But you should know by now that they almost always want to see the current Fiscal Quarter. So you default them to those dates that define the current quarter. Great!
Now they want to quickly compare to the previous quarter, or the same quarter of the previous Fiscal Year. Ruh-roh. Nobody wants to go messing with those lame date-pickers with the pop-up calendar thing.
Give them a clickable label instead, which says “Compare to last Quarter” or “Previous Fiscal Year”.
The click action should be to “drill through” to the same report, while changing the (now internal/hidden) date parameters to the appropriate dates. Presto! The user only had to click once to get exactly what they wanted. Much nicer experience.
I’ll try to have a future post going into detail on this. I’ve got tons of ideas swimming around in my head after FishHeadTed‘s excellent SSRS classes, and not nearly enough time in the day to flesh them out.
Stay tuned, and go find a SQLSaturday near you!