Top Ten Operational Mistakes at SNESSUG
Tonight, I’m presenting my Top Ten Operational Mistakes at the Southern New England SQL Server User Group meeting.
I gave this presentation at SQL Saturday in South Florida and it was a lot of fun. I’ve updated it slightly to include a warning on avoiding advice from random bloggers. My benchmark there is if someone says you should always or never do something, they’re not right.
Top 10 Operational Mistakes to Avoid 2012
Confirming Objects Modified
I work in a hosting environment, and frequently our clients will ask us to promote a stored procedure (or some other schema object) through the DEV, TEST, Production environments. We have one client that is really big on seeing some type of evidence that we did what we say we did.
I wrote this little nugget that generates enough confirmation for the client’s relationship manager to demonstrate that we did indeed move their code. And it seems to make the client happy.
set nocount on go DECLARE @dbname VARCHAR(30) DECLARE @num_objects INT DECLARE @object_type VARCHAR(3) SELECT @dbname = 'userdatabasename' -- Use the database where the objects were moved SELECT @num_objects = 1 -- Use the number of objects moved. SELECT @object_type = 'P' -- Use P for procedures, F for functions, U for tables, V for views, etc. DECLARE @SQL VARCHAR (1000) select @@servername SELECT @sql = 'select top ' + cast(@num_objects as varchar) + ' left(name,30) as object, object_id, modify_date from ' + @dbname + '.sys.objects where type = ''' + @object_type + '''
order by modify_date desc' EXEC (@sql)
Setting All Databases to SIMPLE Recovery Mode
I’m cleaning up some stuff in my dev environment today, and I have some pretty big transaction log files. These have gotten big even though I do regular full and transaction log backups. In order to do some maintenance work, I wrote this little nugget this morning. It’s anther script that generates a script.
Again, this is for my DEV environment. I’d never advise someone to run all databases in SIMPLE recovery mode in a production environment unless there was a very specific need to do that.
set nocount on go select 'ALTER DATABASE [' + name + '] SET RECOVERY SIMPLE' from sys.databases where database_id > 6 and recovery_model_desc = 'FULL'
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.
Log Shipping Without Maintenance Plans
I have this crazy idea spinning around in my head–setting up log shipping without using a maintenance plan. It’s either stupid, crazy, or brilliant. It’s probably a combination of the three.
I’m working with a company that hosts a database application for clients. In this environment, we’re growing quickly. I need a DR solution that will scale incredibly well. To support scalability, I’ve banished maintenance plans. They’re great, but they don’t exactly scale well.
So I’m thinking of writing the log shipping into our transaction log backup process. Clearly, I need to test it, but I’m thinking it has the potential to work if written properly.
The concept is that I know the filename based on the transaction log backup I’ve just done, and by passing that into an xcopy command line, I can copy the log file to the DR server. The DR server will have a similar process running that will look for files that have been copied, get the correct data from the source and restore the log file.
Will this work? Maybe. But at this point, I’ve thought about it enough to know that I’ve got to try.
The PASS Summit Approaches
According to Delta Air Lines, my outbound flights from Boston to Seattle (via Atlanta) for the SQL PASS Summit depart in 16 days. And I’m really excited about this.
That’s all I have to say on the matter.
Cancel Your Plans
I have a confession to make. I’m not a fan of maintenance plans on SQL Server. Sure, they’re easy to set up and they work well in small environments. But what happens when you migrate from one server to the next? What happens when your company is rapidly growing and you find yourself adding new database servers every week? Perhaps you’re a nut for consistency like me and want to change transaction log backup retention across all your servers without having to go change it on all of your servers.
Here is the dirty little secret: Maintenance plans don’t scale very well.
I’m currently working on a presentation that I’m hoping to give at SQL Saturday 92 in Oregon. My belief is that anything you can do with a maintenance plan, I can do with T-SQL. By leveraging a MSX-TSX relationship with SQL Server Agents, this can scale far and fast.
This is going to be fun to put together. Stay tuned.