Stopping a Series of SQL Agent Jobs

Yesterday, I participated in my first DR test at this company. It was a long day, but we learned a lot. This was also our first SQL Server client that we tested. For our Oracle clients, the Oracle DBAs have this process down to a science.

We’re using Log Shipping for our DR environment, and before I opened up the databases, I needed to stop the LS Restore processes for all 22 of the databases, and I wasn’t about to right-click on 22 SQL Agent jobs. That’s when I wrote this little nugget:

set nocount on
go
select 'EXEC msdb.dbo.sp_update_job @job_id=N'''+ cast(job_id as varchar(60))
+''', @enabled=0
GO'
from msdb.dbo.sysjobs
where name like 'LSRestore_%'
and enabled = 1

What this does is generate a SQL script that I can copy, paste, and run. Sure, I could have encapsulated this into a cursor and EXECute it, but I like to have a little bit of control over these things.  Lately, I’ve become a big fan of writing scripts that generate other scripts.

After the DR test, I write something similar that re-enabled those jobs once I had my backups restored.

Audit Prep Toolkit

When I was the DBA at Acme Packet, we went through a Sarbanes Oxley audit at least twice a year. It’s the price you pay for being a publicly traded company. One of the things I learned in my tenure there was that the best way to survive an audit is to anticipate what the auditor is going to ask for. Over a few years, I developed a great rapport with my auditors and typically had a mountain of data for them to sift through before they even walked through the door. By putting together a handful of scripts and reports, our auditors were able to spend more time doing actual auditing instead of waiting for us to provide data.

I’m working on a toolkit that DBAs will be able to use to have this data ready for their auditors. It’s just a handful of scripts that generate the data needed to demonstrate some basic audit controls. By dumping that data into the BI engine of your choice, it will look like you know what you’re doing and are well prepared. Here are a few things that you can expect.

• Basic Server Configuration Info
• Database logins
• Database logins with the sysadmin role
• List of users per database
• List of users per database including role
• List of database roles and users included
• List of explicit grants for database users
• Backup history
• Failed backups
• Failed backups and proof of notification
• List of SQL Agent Jobs
• SQL Agent Jobs and Schedule
• SQL Agent Job History

SQL PASS Summit: This is Community

I’m writing this post on my flight from Seattle to Boston. (via Atlanta) The 2011 PASS Summit is a fond memory. And I made an observation this week. There are people who go to the summit to learn, and then there are people who go for the entire experience. I learned a ton sitting in some fantastic sessions this week. And I learned even more in conversations with experts than I did in any particular sessions.

This really hit me on Wednesday night when I was talking to Kimberly Tripp about virtual log files. Here I was, talking to one of the most knowledgable people on the subject, and she was answering my questions, seeming to enjoy the conversation. This is a community of people passionate about SQL Server, and we love to talk about it. I got so much out of some of these conversations, and I’m already questioning some of the “best practices” my company suggests with our product. It’s time to challenge the product people and rock the boat a little bit.

Before heading to the conference, Denny Cherry and Tom LaRock gave a webinar about the Summit, and one of the things they said was to take advantage of the networking opportunities. This is not a conference to just attend and then go back to your hotel room at night. Every night has some opportunity to network and connect with other SQL Server professionals. A lot of people don’t take advantage of those opportunities, and that’s sad.

I joked to a friend last night that I got to see my new boss at the Summit. I’m just not sure who that was.

There are people who attend the Summit and those who experience the summit. I’m so glad I was able to experience it.

PASS Summit 2011: It’s Not Over #sqlpass

Tomorrow is the last day of the PASS Summit, and it’s hard to believe that I’ve been running at this frenetic pace since Monday. I wanted to get some thoughts down before I went back to work and these thoughts got lost. This post isn’t going through my normal editing and waiting period process, so it may be a bit of a blather.

Being here is realizing a bit of a dream for me. I’ve been trying to get here for the past three years, and my former employer would never let me attend. Even when I agreed to pay for it out of pocket, they balked. Ultimately, it’s one of the reasons why they’re my former employer.

This is not a cheap event, and I have to say that it’s been worth every penny. Just by being here and listening to some of the experts, I’m already a better DBA for it. I’ve learned a ton, and a lot of it revolves around performance tuning. I like to think of myself as an operational DBA, dealing with security and the day-to-day getting of stuff done. Getting to listen to some of the speakers around this topic has really been remarkable.

Despite all of the great sessions, my top goal for attending the PASS Summit was networking, and that has been amazing. I’ve met some awesome people, but the highlight was last night when Paul Randal introduced himself to me. And he called me by name. It’s analogous to a teenager meeting his favorite sports icon. Paul is a cornerstone of this community, and the fact that he knew who I was was–well that was really flattering. And of course I got to meet his brilliant wife Kimberly. We had a great conversation about VLF management, which is something we’re struggling with in my current environment.

Speaking of cornerstones, I finally got to meet Kevin Kline from Quest Software. He’s been such a great resource for this community, and I’m glad to have finally met him.

Getting to meet Gail Shaw and listening to her speak about execution plans twice was another highlight. This is just the tip of the iceberg. I could blather on for days about the amazing people in this community that I’ve met this week.

And then there are the people I already know. Today, I went to Tom LaRock’s halfday session on performance tuning. Tom could have been presenting on tying your shoes, and I still would have gone. When he presents, he has such command of the room, and I envy his presentation skills. If I’m ever half that good, I’ll be thrilled. And then there is Buck Woody. We had a great conversation a few months ago about my career path, and we had a follow-up to that conversation today. The last two nights, I’ve gotten to hang out with Karen Lopez, and I can honestly say that she’s one of my favorite people on the planet. Denny Cherry was giving me some feedback on my ideas of writing my own log shipping process. And then there is Brent Ozar. He’s the guy that everyone asks advice from, and for very good reason.

Tomorrow is the last day of the summit, and there are still some amazing sessions to go. I think I shall be sad to be leaving Seattle. But next year, I will be back, hopefully as a presenter.