Voice of the DBA

Author: way0utwest

Monitoring for Non Existent Events

I was catching up on work recently, reading the third installment of The 5 Worst Days in a DBA’s life, starring The DBA Team. Someone had asked me if I enjoyed having Paul Randal (b | t) of SQLskills join them team. The piece had been edited and published while I was gone, and I hadn’t had a chance to immerse myself in the adventure. I was anxious too read how Paul helped save the day.

It was a fun read, but one quote in the piece struck me. “A job that runs long or doesn’t run at all can sting just as bad as one that fails.” That’s a quote from my character showcasing a situation that few people actually think about. However jobs that don’t run or don’t finish are situations that DBAs should be monitoring for.

So many of us adopt a set-it-and-forget-it mentality with our jobs. We assume that things will work, or fail, as we set them up. However it’s easy to forget that there are other states we might find ourselves or our systems in that can cause issues.

Monitoring is critical to any well run system, but monitoring needs to be set up well. If we require that certain jobs run, we need to not only check for success or failure, but if the job has actually run and completed. It’s easy to accidentally disable the wrong job and not notice. It’s also entirely possible that a job gets stuck and doesn’t complete.

If you’re not watching for those other states, you might find yourself in a situation where you don’t have backups and your job is on the line. However you probably won’t have The DBA Team to call on.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.2MB) podcast or subscribe to the feed at iTunes and LibSyn.

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

July 31, 2014
Creating a Table–Sabbatical continued

My sabbatical officially ended on Jul 14, but I still had a few weeks of class left. And I need to get my flagpole mounted, but that’s another tale.

I missed a week of classes with travel to SQL Bits and the Red Gate office, and last week I ended up cutting my wood in to pieces for the final project. Not a lot getting done there, and some simple planing of edges and sides, but I walked into class on Tuesday with this:

The left boards were planed at home to size (perhaps a touch small), but were square and ready to become legs. The middle stack is the aprons, which were planed (by hand) and squared, and then machine planed to thickness. The right stack is the top, which I managed to get fairly smooth and needed to glue.

My first step was to mark up the legs for mortises. That’s one of those tasks I couldn’t do at home, so I wanted be sure I did that.

I was in class early, with no instructor, so I had to remember from the last month how to mark these up. I did two, and then the instructor showed up to note I only needed one as the machine would do the rest.

This is my favorite machine.

It’s a specialty machine, but it does such a consistent job of making mortises, way better than I could. Without it, I’d dread cutting more than 1 or two and certainly wouldn’t be sure I’d do them well. With it, I’d use this joint much more often.

It took 10 minutes or so to set up, and then another 20 to cut 12 mortises. By hand I’d likely have 2 done, tops. They looked good, too.

With those cut, and the instructor around, I turned my attention to the top .He looked at my boards, and with a slight gap in the centers (more by accident than design, though I did want this), he clamped them dry and proclaimed them good. We added glue, clamped and set them to dry.

I wanted to watch him do this, even though I’d clamped a few boards at home. I was looking for hints and got a few. Hopefully I’ll do a better job next time. While those were drying, I went back to legs.

First I had to smooth them. While I can use a little sandpaper at the end, I needed to plane them first.

Once that was done, I dropped them in the jig and cut tapers on the bandsaw. My first tapering, and while it was easy, it was also nice to have someone else build the jig and show me how to use it to build confidence.

Once the top was dry, I had to flatten and square the edges. That went quicker than I thought and I managed to get it done with a few minutes to spare. The sides were easy, the top ,a little more challenging, but in the end, it was flat enough to grip the bed of a planer when I dropped it on.

With time left, I needed to bevel the bottom. One student was doing this by hand with a planer, but the instructor took pity on my lost week in the UK. He let me use the table saw. I lined it up and then he showed me how to safely run it through. I’d never run a board through vertically, so it was good to see how someone else does it

I ended up with a nice pile of parts, including a beveled top.

The next major step is to cut tenons and then assemble the thing.

And hope it fits.

July 30, 2014
Elevation of Privileges

At SQL Bits this year I attended a security presentation from Andreas Wolter. The session examined some attack methodologies, showing the flow that an attacker might go through to gain information about your database instance with SQL Injection. It’s a scary and eye-opening talk, and one that I might recommend to all DBAs and developers so that they can understand the dangers involved with poorly coded applications.

One of the most scary attacks was the elevation of privileges from a web user to a sysadmin on an instance, mainly because of the Trustworthy setting being enabled. I had never imagined this as an attack vector, but it was disconcerting to say the least. However it got me wondering about instances I’ve managed.

Would I detect if a new sysadmin were added? Or an existing user added to the role? I’m not sure I would, though that’s certainly something I plan on setting up with some sort of monitoring to detect. I would guess that most DBAs, whether professional or accidental, might not catch this either, at least until some audit was performed. At that time it might be too late to protect your data, and certainly too late to protect your reputation.

Security is a tough topic, and it’s an ongoing process to protect your systems. I hope to see more presentations like this at future events, and I’d encourage you to request them for any events you plan on attending. You can certainly do this for all SQL Saturday events (there’s a suggest a session on the schedule page).

Security requires vigilance and vigilance requires monitoring. Both of those also need knowledge, so be sure that you don’t neglect the security of your SQL Servers and continue to educate yourself over time as well as implementing technical solutions.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.5MB) podcast or subscribe to the feed at iTunes and LibSyn.

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

July 30, 2014
Continuous Database Delivery from the Command Line

I’ve been working with Red Gate to learn more and the different ways in which we can help you ease and smooth the database deployment process. I know that I’ve dreaded deployments to production in most of my jobs, and only enjoyed them in one. The mission of one of the teams at Red Gate is to make this easier and easier and I’m learning about how they do that.

They’ve also been working with many others in the world of software, providing them with tools, getting feedback, and learning from their experiences. One of the people that has done some work with our tools is Matt Whetton, and he wrote a piece on how he’d gotten SQL Compare to work with his version control system (VCS) to get a Continuous Integration (CI) process in place. He’s using TFS, as many of you are, though he has the SQL Source Control plug in to SSMS to make managing his database code easier.

I won’t duplicate his process here, though it’s one that should work well. It has lots of pieces and parts, and might seem confusing, but it’s not. He describes in detail how he runs tools from the command line to build a more automated process. If you want us to do more of the work, we have an automation pack to smooth the entire process, and we’re improving it all the time.

I would encourage you to think about building your own automated process for database changes. It might seem like adding work for no good reason, but it can pay off as you expand the process through later environments to deploy to QA, Staging, UAT, Production and any other environments you might need to. If you move to the cloud, having automated processes become even more important.

You can also take a look at the Red Gate “Learn more about databass delivery and continuous integration” page. We’ve got some resources up there and are looking to add more all the time.

July 30, 2014