Basic SQL Server Configuration Help for Involuntary DBAs

After my presentation at the Techno Security and Digital Forensics conference, I had a information security professional stop by to ask a few questions. He’s in the position where he supports other clients since he works in a third-party security operations center (SOC). The reason most of these clients pay for a SOC instead of developing one of their own is cost. Since they don’t have the money to splurge on a lot of IT positions, another one that’s usually missing is the DBA.

Often times, as a SOC provider, when they interact with clients they can tell fairly quickly that the SQL Servers aren’t configured well. However, they don’t have the knowledge to go in and help their clients in a quick and easy way. He asked for advice. I pointed him to something that we have in our community: sp_Blitz. It’s part of the First Responder Toolkit from Brent Ozar.

Why did I recommend that particular tool? There are several reasons:

  1. It’s designed to provide a quick health check of your SQL Server.
  2. It’s a free tool (yes, you have to register), meaning budget isn’t an issue.
  3. The community has worked on and contributed to it.
  4. It provides explanations and recommendations on how to fix what’s wrong.

For someone such as an involuntary DBA or a consultant trying to assist a client when that’s not your primary skill set, it lets you make solid recommendations immediately that will improve the SQL Server setup. And it’s not hard to setup and run:

If you haven’t looked at this tool before, grab it, put into a non-prod environment, and see if it can help you.

Speaking at Charlotte BI Group Tomorrow

Tomorrow, Tuesday, April 7, 2015, I’ll be speaking at the Charlotte BI user group. The meeting starts at 5:30 PM.

Here’s the info:

RSVP Link

Topic: Securing the ETL Pipeline

We’re going to look at typical ETL (Extract, Transform, Load) pipelines and consider the weak points an attacker might go after. Our goal in this isn’t to cause FUD (Fear, Uncertainty, and Doubt), but to discuss risks at each point, options for protecting the vulnerability, and what we’ve seen typically done (if anything). While this talk primarily focuses on Microsoft SQL Server, especially the database engine and SSIS, many of the points covered will be applicable to any solution set.

Location: 8055 Microsoft Way, Charlotte NC 28273

Speaking on ETL Security

I will be giving a presentation on ETL (Extract, Transform, Load) security at two user groups in the coming weeks.

Securing the ETL Pipeline

We’re going to look at typical ETL (Extract, Transform, Load) pipelines and consider the weak points an attacker might go after. Our goal in this isn’t to cause FUD (Fear, Uncertainty, and Doubt), but to discuss risks at each point, options for protecting the vulnerability, and what we’ve seen typically done (if anything). While this talk primarily focuses on Microsoft SQL Server, especially the database engine and SSIS, many of the points covered will be applicable to any solution set.

If you’re in or near the Midlands or Upstate of SC, I’d love for you to come out so we can meet and discuss this topic, professional development, and SQL Server in general:

August 5, 2014 – SQL Server Innovators Guild – Greenville, SC

August 14, 2014 – Midlands PASS Chapter – Columbia, SC

Midlands PASS July Meeting – July 10

The Midlands PASS Chapter will hold its next meeting on July 10. We meet at MicroStaff IT in Cayce, SC. Here is the main presentation:

Statistics, Indexes, and their Impact

Speaker: Brian Kelley, SQL Server MVP

Statistics. Indexes. Clustered Indexes. Non-Clustered Indexes. Covering Indexes. Bookmark Lookups. Perhaps you’ve heard these terms. They determine how well or poorly your queries run. In this session, we’ll look at what these things are, how they impact your queries, what to do to maximize their use, and when you should consider making changes.

This is a 100-200 level presentation.

You can RSVP for the meeting (it helps us plan for food) at the EventBrite Event Page for this meeting.

 

Security #Datachat on Twitter Tonight

Tonight, at 9 PM Eastern, I’ll be participating in a #datachat on SQL Server security. It’s sponsored by Confio (now part of Solarwinds).

You can find more details about the #datachat here.

How can you participate? Simply open up a search for #datachat and participate in the community Q and A. The more, the merrier!

I hope to see you online.

Presenting on Security at Midlands PASS

On Thursday, February 13, 2014, I’ll be at Midlands PASS in Columbia, SC. We’ll be meeting from 5:30 PM to about 7:30 PM. I’ll once again be given an open ended SQL Server security talk. Here’s the description:

Midlands PASS Chapter’s annual SQL Server security refresher! This is an open-ended discussing hosted by SQL Server MVP and resident SQL Server security expert, Brian Kelley. Bring your scenarios and questions and we’ll work through the best ways to build security solutions for and using Microsoft SQL Server.

We meet at Microstaff IT in Cayce, SC. They are a great host and we enjoy the accommodations. If you’re in the area, please stop on by!

We do ask that you RSVP so we know how much food to bring.

Free Online SQL Server Training for the Week of November 17, 2013

If you’re a training provider and I’ve missed you, please drop me a line at brian {dot} kelley {at} sqlpass {dot} org.

All times are Eastern (New York). To convert to your local time, use the converter at timeanddate.com.

Tuesday, Nov 19:

Wednesday, Nov 20:

Thursday, Nov 21:

Training Providers I Regularly Review:

Free Online SQL Server Training for the Week of November 10, 2013

If you’re a training provider and I’ve missed you, please drop me a line at brian {dot} kelley {at} sqlpass {dot} org.

All times are Eastern (New York). To convert to your local time, use the converter at timeanddate.com.

Tuesday, Nov 12:

Wednesday, Nov 13:

Thursday, Nov 14:

Training Providers I Regularly Review:

New Performance Tips eBook Out from Red Gate

Not too long ago Red Gate asked for quick tips on SQL Server performance intended for developers. I sent a couple in. They’ve compiled those tips into a free eBook format. If you want to download it:

45 Database Performance Tips for Developers

 

Review: SQL Server Transaction Log Management

SQLServerTransactionLogManagementBook Details:

SQL Server Transaction Log Management
Davis, Tony and Shaw, Gail
Simple Talk Publishing, October 2012.

Free PDF download

Do I Recommend This Book?

Yes, I recommend this book for any DBA working with Microsoft SQL Server. Gail and Tony do an excellent job of covering how Microsoft SQL Server uses the transaction log for a database. There are plenty of code examples to reveal the behavior they describe. In addition, they provide plenty of references to other sources which reinforce or expand upon what they cover in the book.

What I Liked:

There’s a lot about this book I liked, so let me pick out the highlights.

Easy Reading Style:

Some technical books are hard to read. There’s a lot of jargon and a lot of assumptions are made as to the technical proficiency of the reader. Others have called this the Curse of Knowledge. Gail and Tony don’t have this issue. They make the book readable to a junior DBA level.

Extensive Code Examples:

Gail and Tony provide code examples for just about every behavior they describe. None of them were very long, but all were effective. Sometimes code samples are too long to reasonably type in. However, most of us learn best from learning, so typing in code is helpful. That means the code samples have to be reasonably small. They were in this book as I typed in nearly every example and then tinkered with them to see the behavior described. There was one example where there looks there was a printing/editing error, Listing 7.1), but the rest worked as long as I didn’t mistype. If you don’t feel like typing the code in, there is a provided download (Listing 7.1 is correct in the download).

Coverage of Bulk-Logged Recovery Mode:

This book had an extensive amount of coverage on the bulk-logged recovery mode. Gail and Tony did a great job explaining why this mode exists as well as the pros and cons of using it. If you don’t get this book for any other reason, get it to review what you know about bulk-logged recovery mode.

What I Didn’t Like:

There are only a couple of things I didn’t like.

Images Were Designed for an e-Book:

Some of the images used colors/shading that don’t show up well in a printed black & white book. Also, there were some references to shading in green and yellow. As a result, these particular images were hard to read, especially in chapter 2.

Oversight on Differential Backups:

Differential backups establish or re-establish a log chain with respect to future transaction log backups. However, most of the writing focused on full backups. The first time I noted this was in chapter 1, in the section titled Transaction Log Backup and Restore. Later chapters sometimes mentioned differential backups, but it was hit or miss.

 

Note: I was provided a free copy of this book for review.