In Loving Memory

Do not waste moments. Put forth light and love into this world. Emanate joy and kindness. And cherish each day, for our time here is never guaranteed.

may your wings be strong
-her favorite quote, from the movie Maleficent.

Dear readers. It is with a heavy heart that I write this. 2018 has seen some significant losses of meaningful lives, whether it be in #SQLFamily or elsewhere. Sadly, that toll increases again.

In October this year, my wife of 10 years passed away unexpectedly, at home. No chronically debilitating illnesses being fought off, no sudden acute onset of horrible afflictions, just here one day, gone the next. Words could not express the deep sorrow and profound shock we experienced that night, and in the days and weeks that followed.

I count myself incredibly blessed to have been loved by her. She loved so deeply and so fiercely, pouring out her generous heart to those who would take the time to truly know her. The day we said “I do!” was the happiest day of my life. She had a contagious laugh and a smile that could light up the room, even when she was hurting, sometimes more than anybody knew. It gives me some small comfort that she no longer feels any pain, but is surrounded instead by joy, light, and love.

She was a sensitive soul, leaving a little bit of herself in every life she touched. She was an empath, which means she would often know how you were feeling before you did. She felt the emotions of others, magnified in herself, and was an amazing listener, who could always be counted on for comfort and support.

The holidays were her favorite time of year, specifically Thanksgiving and Christmas. So today, on the heels of our day of thanks and the toes of the season of giving, I encourage you with two things.

First, be thankful for your loved ones, your family and friends, and most of all your children, if you are so blessed to have them. Life is so precious. Spend it with those you love, and do not waste any moments.

Second, give of yourself unto others, be it time, money, helping hands, a listening ear, or a friendly voice. Put forth light and love into this world, not darkness or hate. Emanate joy and kindness, not malice or bitterness.

And above all, cherish each day, for our time here is never guaranteed.

May you ever walk the beaches of paradise,
May you dance along the sunsets,
And sing until the end of eternity.
You are forever loved, and always in my heart.

SQL Server for the IT Helpdesk, part 2

Part 2, as promised.  We were talking about the components of SQL Server.  So far we hit “the big ones”: the database engine sqlservr.exe, the data files (mdf, ldf), and the agent sqlagent.exe.

Thirdly, less of a “component” and more of a “tool-set”, the venerated SSMS – SQL Server Management Studio. (Formerly known as SQL Server Enterprise Manager, if you’ve been around for a long time.) SSMS is the “stock” application interface for SQL Server, the DBA’s basic swiss army knife – all of the essential functionality, a couple odd-ball modules here & there, and no “fluff”. It’s not the prettiest, nor the sexiest, but it gets the job done. It even has some basic monitoring capability; as an infrastructure person, you would most likely use it to check up on DB statuses, Agent Job runs, and perhaps the occasional “everything is slow!” panic moment by peeking at the Activity Monitor (right-click on a server instance).

Just the essentials!

SSMS is powerful, and with great power comes great responsibility. Your DBA should be practicing good security principles and granting limited access to all but the most trusted, experienced team members, because that top tier access, sysadmin (or sa for short), is literally capable of destroying the server (maybe not physically, but logically!). SSMS is a client-side application, primarily – meaning that yes, while you do “get it for free” when you install SQL Server on your server (at least, until SQL Server 2016, when they finally broke it out to a standalone installer, praise the gods), you should really install it on your own workstation and use it from there.

Key takeaway: ask your DBA to install SSMS on your workstation, IF it’s appropriate (and trust me, they will tell you if it’s not); often, there are better monitoring tools and infrastructure-focused apps that can “talk to” the SQL servers without needing you to dive down deep into SSMS-land. But, in a pinch, it helps to know a few basic things about what it can do.

The original; don’t you forget it!

Finally, and less commonly, we have a collection of supplemental services that work within and around those two main components to provide additional functionality and support for things like bulk data interchange, reporting, data warehousing, and scale-out options. Again, the curious reader can easily find more detailed info at his/her favorite reference sites, so I’ll define these briefly and move on. They all start with “SS”, which of course stands for SQL Server. So, here we go. SSIS – Integration Services – bulk data import/export, ETL (extract/transform/load), and similar tasks. SSRS – Reporting Services – lets us build & manage reports, and provides a basic no-frills web interface from which any business user can view said reports to which they’re granted access. SSAS – Analysis Services – data warehousing, cubes, “analytics” – basically, we’re filling up disk-space with pre-defined roll-ups and drill-downs of our data for faster reporting queries & data analysis. So as you can imagine, often those last two go hand-in-hand, in a mature environment that’s cognizant of its “business intelligence” (BI) needs. But that’s another topic way outside the scope of this post!

Key takeaway: if any of those SS*S components are a part of your environment, monitor them at the service/process level, and, just like with the Agent, have a procedure in place to address incidents.

So what have we learned? A SQL server machine runs one (or more, but hopefully/usually just one!) instances of SQL Server. We should primarily be aware of the engine & agent service processes, the locations of the mdf & ldf files, and the agent job schedules; and secondarily, if applicable, the other components (SS-whatever-S).

Oh, but wait! What about performance concerns?!? Glad you asked.

SQL Server EATS disk for breakfast, memory for lunch, and CPU for dinner. In that order.

I may have made a slight miscalculation..

So your dedicated SQL server machines better have FAST disks, LOTS of RAM, and a decently fast, multi-socket multi-core CPU. The disk speed helps keep transaction log throughput & data file read/write activity up-to-snuff, so faster == better. Memory is for holding the “hottest” data for fastest access so it doesn’t always have to go to disk for everything, so more == better. And CPU cores help it serve 100’s (or even 1000’s) of concurrent requests in a sane, efficient manner.

Because moar… and Zoidberg.

In reality, SQL Server performance is a huge topic; there are entire consulting companies dedicated to it, and tons of excellent blogs about it. You want a good DBA on your team who knows how to troubleshoot and address performance issues with your SQL servers. But when you’re tasked with helping spec-out a new physical box, or sizing a new VM, it doesn’t hurt to know what kind of demands you’ll need to meet.

Which brings us to our last key point…

Key takeaway: SQL Server is primarily a scale-UP technology, not scale-out. If you’re not OK with that, you might want to look into other DBMS’s.

I have to give full credit to this guy because his slide really illustrates my point well.

That’s not to say you can’t scale-out with it; obviously, large corporations with mature ecosystems often have 100’s of instances in some resource-groups, but as I discussed earlier in What’s in a Name?, the applications which depend on those SQL servers are also architected with that scaling in mind, and it’s not like an “easy switch” you can make to an existing monolithic app.

Anyway, I hope this helps IT Helpdesk and SysAdmins/Engineers get a little more visibility into the SQL DBA’s world, and bit more understanding of how things work behind-the-scenes.

Thanks for reading, see you next time!