Author Archive for Jeremy Lowell



20
Aug
10

Chapter 28 – Practical Auditing in SQL Server 2008

In chapter 28, Jasper Smith (Blog) introduces us to the built-in auditing features of SQL Server 2008.  This.is.awesome.  For those of you who have had to “build your own” dml and ddl auditing in the past, it was not intuitive nor was it an easy “out of the box” solution. 

A few years back (o.k., 9 years), I was tasked with creating a dml audit for all select, update, insert and delete statements against tables that contained personal information.  This effort required a continual trace for DDL and another for DDL.  Once I had trace files being created, I then wrote additional t-sql and bcp jobs to insert the data into a set of tables so that the security team could review who was looking at what data, where they were logged in from, etc…  While it was a great excercise for me from a professional development standpoint, it wasn’t as easy as the point-n-click implementation that Jasper walks through in this chapter. 

I would highly, highly recommend that every DBA out there set up DDL auditing in their environments.  It can help with so many issues that come up.  I have several clients who run a lot of Commercial Off The Shelf (COTS) packages and when upgrades happen in these environments and the database slows down or rapidly speeds up, many DBA’s are left out in the cold wondering what happened.  This most often means that a call has to be put into the vendor and the DBA spends a significant amount of time understanding what happened and why it had the effect that it did to their systems.  If DDL auditing had been set up, it would have been a quick check and may not even involve having to call into the vendor… score!

Another common scenario is a new release of your custom software will go out and during the deployment phase a piece of DML code updates too much data or not enough data and the release has issues.  Having the ability to know what ran, when, by whom etc… is truly invaluable in these scenarios.

Thanks for a great chapter Jasper!

05
Aug
10

Chapter 27 – Automating SQL Server Management using SMO

Timing… so much in life and career comes down to timing doesn’t it? 

Allen White (Blog | Twitter) could not have timed this chapter any better for me.  I’ve been implementing a policy management for a client across many servers and even mulitple domains and I’ve been thinking to myself, there has got to be a better way to deploy and manage all of these policies.  Viola.  SMO. 

In this chapter, Allen walks through the pre-reqs, introduces and then dives right into real life practical examples of leveraging the SMO libraries via PowerShell.  Again this book does not disappoint as the prior chapter was on Powershell and this one is using powershell to illustrate the power, agility and scalibility of SMO.

There are many pieces of information that I didn’t know about SMO in the pages of this chapter but one of them I thought was very interesting…  Via SMO, when restoring a database, the location of the default data and log file paths is available.  There’s no other way to get that in a restore process… pretty cool.

If you find yourself using Powershell and/or managing a large number of SQL Servers, I would dive into this chapter, become familiar with SMO and, in time, find yourself with your nights and weekends available to put your feet up, work on that personal project or get to the honey-do list.

Great chapter Allen, thank-you.

22
Jul
10

Chapter 25 – Working with maintenance plans

Maintenance plans generally get a pretty bad rap with a lot of the more seasoned (read: OLDer) DBA’s out there.  I think that this is due to a myriad of reasons, few of which warrant the negative viewpoint in SQL Server 2005 (post SP2) and SQL Server 2008.

In this chapter, Tibor Karaszi (Blog | Twitter), goes through the available options of maintenance plans and offers descriptions, use cases and his own opinion of which tasks he prefers and which tasks he really, really dislikes. 

Prior to SQL Server 2008, I did not have a positive opinion on maintenance plans.  Over the last couple of years, that opinion has changed drastically.  One of the reasons for this change is, as Tibor said in the chapter;

 

I hesitate to leave home-grown maintenance jobs with customers who do not have the competency to maintain this code over time.

Compentency is one reason that I’ve run into but more frequently it’s around resource allocation.  In today’s database environments, I’m increasingly seeing shops with a lot of database work that needs to be done and there simply isn’t time to perfectly fine-tune or write your own re-indexing scripts.  With clients who actually ship their code base / database as part of a product, this is nearly always the case as environmental and/or customization considerations make it incredibly challenging to build in your own maintenance routines. 

With that said, there are clients and databases where maintenance plans just don’t cut it.  As with anything, there are limitations to what maintenance plans can do really well, however that list has grown in recent releases.  So, if in the past you have thrown out the idea of maintence plans due to a poor experience in SQL Server 2000 or another build pre-2005 SP2, I’d highly recommend that you take them for a spin.

15
Jul
10

Chapter 24 – What does it mean to be a good DBA?

The third part of the book SQL Server MVP Deep Dives focuses on Database Administration.  The first chapter in this part is written by Brad McGehee (Blog | Twitter) and is preceded by an introduction to this section by the editors Paul Randal (Blog | Twitter) and Kimberly Tripp (Blog | Twitter).  Let me just say that if I were asked to write a chapter in a book and was told that the editors would be Paul and Kimberly, I would …. huh, not sure what I would do, but the point is that it would be an incredible honor and at the same time an incredibly brutal endeavor.  Of course, the brutality of it all would be self-inflicted but even so, it’s still brutality, no?  At any rate, for the authors that submitted chapters on this section, kudos.  Big kudos.

What does it mean to be a good DBA?  Based on the chapter, I was ready to begin scribbling notes to myself on how to become a better “DBA”.  This chapter surprised me, in a good way, by answering that question inside of typical DBA tasks.  Brad defines 36 tasks that DBA’s of varying title find themselves responsible for. 

Years ago when I was a full-time employee, I often struggled with how to communicate what it is that I did to others in my group, my boss and the always important yearly review process.  I believe the information laid out in these pages can help you articulate and define your role and responsibilities in your organization.  Heck, it’s written so well that I’m likely going to photo copy it, highlight the relevant sections, hand it to my mom and say, here, this is what I do.

Over the years I’ve had the opportunity to interview many candidates for varying levels of DBA positions and one of the more frustrating parts of the interview process is when candidates are narrow-minded about what it means to be a “DBA”.  This chapter makes it very clear that a DBA role consists of many different skills and responsibilities that many of us may not have considered a “DBA” task before.  This is an important consideration when you look at your role as the DBA in your company.  It’s an important consideration if you are a developer, manager or someone who wants to become a DBA.  Only by truly understanding what the role *can* consist of can you, or anyone define what the role will consist of in your position.

Another portion of the chapter speaks about “DBA Specialties”.  This part of the chapter is useful if you are looking to dive deeper into a specific area of SQL Server.  It also helps define what you might want your next job title to be.

All in all, another great chapter.




Chapters

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 13 other followers


Follow

Get every new post delivered to your Inbox.