No you haven’t.  I know you haven’t, because I was a DBA, and I hardly ever did it either.  Now that I work for the makers of LiteSpeed, I hear the horror stories from our support folks.  DBAs call up screaming in pain, and we can hear the boss loading the shotgun in the background.  The DBAs wrote backups to tape and the tapes got hosed, or maybe the backups never actually made it to tape because the DBAs wrote the backups to disk but the network staff never backed up that disk share.

Do yourself a favor - pick your three most critical databases, the ones that’d really give you heartburn if they disappeared, and go try to restore them onto a testbed server that has decent performance.  Time how long it takes, and mention it to your boss.

Mention it to your manager in casual conversation.  “Hey, Pointy-Headed Boss, I tested the restores today just like I’ve been doing every week (cough) and they worked great.  I never mentioned it to you before, but in case you’re curious, it’d probably take us about X minutes in order to recover in case the production server barfed.”

Your boss will probably say, “Why are you telling me this?”

You say, “I just thought you might want to know.  If that server dies, I want you to kinda have a feeling for how long it’ll take me to restore, because in the heat of the moment, you know, people kinda stand around the desk waiting for it to happen.  If the box explodes, I want you to be able to answer when your manager and your manager’s manager ask us how long it’s going to take.”

At that point, the fidgeting will start, and maybe they’ll ask you to make it happen faster.

Why am I telling you this?  Because budgeting time is coming up, and that’s a good way to segue into a conversation about getting more storage performance for your servers or getting a faster, dedicated backup share.

Good luck.  Let me know how it goes.  (And start testing your backups regularly.  Please.)

Update 10/2 - Funny Ha Ha - Jason Massie aka StatisticsIO.com posted a funny comic about testing your database backups.

It’s hard for me to believe that I started working with the first board of directors for PASS nearly a decade ago, but it’s true.  PASS has grown over the years due to the support of the user community, the SQL Server expert community, and Microsoft’s SQL Server team.  This year’s conference is just around the corner in mid-November.  If you haven’t thought about registering, you should!  It’s the best and most concentrated SQL Server learning you can get anywhere.  This year there are over 130 sessions, dozens of full day pre-conference seminars, and huge contingents from Microsoft’s SQL Server support, consulting, and development teams.  Take a look at the event schedule at http://www.sqlpass.org.

I’ve had some neat and funny experiences over the years.  In 1999, I was awed to meet the late, great Jim Gray for the first time.  When I saw him again later that year, he remembered me in detail and, every other time after that, always expressed interest and excitement in what I was up to.  What an honor.  Back in 2001, I fell off the stage while delivering a session on translatinge between Oracle PL/SQL code and SQL Server Transact-SQL code.  That tought me to never anger the gods of Transact-SQL by speaking about other database platforms at their conference.  In 2005, I experienced and blogged about the shower that tried to kill me in the conference hotel.  And at the conclusion of the 2007 conference, I’d been working from 6:00 am until midnight or later all week.  This resulted in Rick Heiges sending me a picture of me unconscious, literally stone cold asleep, on the lunch table on the conference on Friday. (Did you blog that Rick?)  In 2004, I had one attendee stop me on Monday to tell me it was her worst conference ever and that she hated it.  I worried about her all year, then in 2005, I saw her sitting at one of the lunch tables.  “I worried about you all year!” I told her.  “The conference was awesome after the first couple sessions.  I really love PASS now.  I was just mad because I kept getting lost,” was her reply.  Whew!

One of the things I’ve most enjoyed about the conference is the awesome people I get to meet.  I remember meeting Allen White a couple years ago and was impressed with how eager and intelligent he was (and a good speaker too). Low and behold within a year or two, he had joined the ranks of the Microsoft SQL Server MVPs.  I’ve seen that pattern repeated many times now.  It’s always a pleasure meeting the true geniuses of the business like Kalen Delaney, Itzik Ben-Gan, Kimberly Tripp, and the late Ken Henderson.  If you’ve ever wanted to talk to the best known authors in the business, you should consider attending the event.  They’re all gracious people and are happy to speak with everyone.

So I thought it’d be fun to hear some other stories from other PASS events.  I’ve tagged several friends below (in addition to Allen and Rick above).  Now it’s their turn to tell some good PASS stories and to tag a handful of other bloggers in turn! 

• Joe Webb at http://weblogs.sqlteam.com/joew/
• Andy Kelly at http://sqlblog.com/blogs/andrew_kelly/default.aspx
• Roman Rehak at http://sqlblog.com/blogs/roman_rehak/default.aspx
• Paul Randal at http://www.sqlskills.com/blogs/paul/
• Aaron Bertrand at http://sqlblog.com/blogs/aaron_bertrand/default.aspx

Check their blogs to read their stories. Enjoy!

-Kev

I got an email from a user who shall remain nameless to protect the innocent:

“Tomorrow is my first day at work as a DBA . Can you please give me some suggestions. I will really appreciate it.”

Well, I don’t change jobs too often, but the last two times I did, here’s what my first week consisted of:

1. Take an inventory of every SQL Server that I’m responsible for.  It should be a simple inventory of server names at first - later, we’ll flesh that out with what applications live on what servers.
2. Get my manager to sign off on that list - anything off the list, I’m not responsible for (yet).
3. Check (NOT FIX, just check) the backups on all of those servers.  You need daily full backups at the bare minimum, and preferably transaction logs until you know for sure what the recovery needs are for that application.
4. Check the error logs on all of those servers.  Look for critical errors like drive failures, SQL Server application errors, security problems, etc.

After that initial round was done, I sat down with my manager and said, “Here’s the ‘before’ picture.  It’s going to take me X days to get automated backups set up across all of these servers.  Until I make sure the data is backed up for everything, is it okay if I don’t do anything else?”

Of course they’ll agree, because the blood will be draining out of their face when they realize their data isn’t completely backed up.  And yes, no matter how good of a shop you walk into, if you’re building your own to-do checklist, that means the other DBAs haven’t got their act together enough to give you a to-do list and the databases probably aren’t all backed up.  This goes for large companies too - I went to work for one of the (few remaining) major financial institutions, and out of my 200+ instances, roughly 10% of them were not being backed up.

After you are absolutely sure every server is being backed up, and you’ve tested some restores (if not all), then my next steps were:

1. Build a list of applications (not just database names) on every server.
2. Build a list of contact names for each application.  Sometimes you can reverse-engineer this by looking at the security information on the database, like who the sysadmins are.
3. Build a list of sysadmins on each server.
4. Set up a brief meeting or conference call with every application owner and get them up to speed.

The meeting should cover:

• Ask them how critical the database is - meaning, how much data can they afford to lose.  Be prepared with a few backup schedules so they can see how much it will cost (in terms of maintenance windows and backup tapes) to hit their backup goal.
• Ask them who needs to be called if the server fails.  (You’re going to implement monitoring sooner or later.)
• Ask them if they plan to upgrade versions of SQL Server in the future, or if their application will support newer versions.  You’re not planning on taking action yet, just trying to get a picture of the environment.
• Ask them if they have any pain points related to the database.
• Show them the list of sysadmins on their server, and explain that these users can stop the server, truncate tables, delete backups, even delete whole databases without any warning.  Ask them if that’s okay, or if they would like to shrink that list down.

After these meetings, build a server/database/application inventory for your manager.  Show them your progress so far, and you’ll have a list of everything that everybody wants in terms of database management.

That will take you quite a while, but you want to get a picture of the environment before you go making any wild plans about how you want to change the world.

Stephanie wrote in with two questions:

1. Is there a gain in getting 2 LDF (log) files for one database?  My opinion is no, because log file access is sequential, so no performance gain can be obtained and this will complicate maintenance.
2. Is there a gain in separating the data & indexes onto 2 specific filegroups?  My opinion is no, because the way SQL Server handles indexes (as opposed to Oracle) does not help if you split them from the data.

First things first, multiple log files - I agree that you don’t want to do two LDF files for a single database unless there’s special circumstances.  At one shop, we had a pre-grown full-size log file for each database (with autogrow turned off), but we kept an “emergency” log file for each database on an empty drive.  The “emergency” log files were 1mb, and set to autogrow.  If a database completely ran out of log space in its normal pre-grown log files, then it would start using that “emergency” log file and growing it.  That was better than stopping the database completely, and gave us enough time to kill transactions and roll things back.

That certainly wasn’t a best practice, and it didn’t buy us any performance, but we had to do it based on the unpredictable nature of those applications and the way they would sometimes load huge amounts of data in a single transaction.  In that case, multiple LDF files served a purpose.  (When our Microsoft contact found out what we were doing, they hit the roof and told us to just go buy enough disk to be done with it, and the business guys finally decided to shell out the dough.)

Adding a second file adds complexity, like you said, and the same holds true whether it’s a log file or a data file, which segues into your second question.  Adding a second data file for indexes adds more design overhead - suddenly the DBA has to pay attention with where they’re placing objects.

In the days of my youth (ahh, for the sweet days of 2004), I liked this idea for my 1tb data warehouse because I could rebuild indexes faster with less impact on the end users.  Now that I’m old (okay, maybe just older) and SQL Server 2005 Enterprise Edition lets us rebuild most indexes online, I don’t bother with that anymore.

Plus, when I started managing storage area networks, I had a different reason.  Storage is getting faster and faster every year, but DBAs aren’t getting any more hours in their day.  Keeping data and indexes on different filegroups requires careful attention during development.  All it takes is one knucklehead adding an index without paying attention, and bam, you’ve got indexes on the data filegroup.  That means you have to introduce a new task into your daily/weekly/monthly routine: running a query to identify which objects are on which filegroups.

What a pain.

I’ve struggled with finding and hiring good DBAs, and if I have a choice between making my database 5% faster or making my DBAs 5% faster, I choose that latter option.  I can get 5% more storage performance by adding a few more drive spindles, but it’s harder - and more expensive - to get 5% more good DBA time.

On the other hand, if I was in a shop where my DBAs had plenty of time (or worked for peanuts, whate, then I would take a sandbox system, set it up the same way as production, and do performance benchmarking to see how much of a difference a separate index filegroup made on my exact hardware, storage and application.

You’re asking me, though, so I bet you don’t have the time to do that research either.  In that case, keep it simple!

After being on both sides of the recruiting & recruited fence, it seems like we need a simple, standardized checklist that DBAs and recruiters can use to quickly convey their skills and a job’s needs. I see this as a yes/no checklist that the DBA and the recruiter would fill out and score to see how close of a match the candidate is. Maybe we could even add a “years of experience” column.

Environment Size:

• 1-10 servers
• 10-100 servers
• >100 servers
• Databases under 100gb
• Databases 100-500gb
• Databases over 500gb
• Tables under 10 million rows
• Tables 10-100 million rows
• Tables over 100 million rows
• Single DBA working alone
• Worked in a team of 2-3 DBAs
• Worked in a team of more than 3 DBAs

High availability:

• Clustering
• Database Mirroring
• Replication
• Backups & restores
• Server monitoring (MOM, Tivoli, etc)

Performance tuning:

• Performance monitoring (Perfmon, Spotlight, etc)
• Performance tuning existing code (rewriting stored procs/views/etc)
• Index tuning
• Partitioning
• Triggers
• Storage tuning (RAID, SAN, filegroups)

Other skills:

• Data modeling
• SOX & HIPAA compliance
• Security specialists
• Application architecture

Other related products:

• SQL Server Integration Services (SSIS)
• SQL Server Reporting Services (SSRS)
• SQL Server Analysis Services (SSAS)
• DTS
• CLR programming with VB.NET, C#

That last group (Other related products) is tricky. My personal belief is that DBAs who work with the below tools are really specialized database administrators. They tend to create content rather than manage databases, and they’re a different breed. When someone says they’re a DBA, but they only do SSIS or SSRS work, then they’re more of a developer than a DBA.

I started this checklist earlier this year because I had some bad experiences with recruiters, and it wasn’t really their fault. In one case, a position was a perfect fit for me - except they wanted replication. I don’t have replication skills, and I shouldn’t go into a shop as their only DBA if replication is mission-critical to them. I can learn a lot quickly, but I don’t want to learn that particular skill the hard way. In another case, I was sold a “DBA” position interview only to find out I’d really be writing SSIS/DTS packages. Ugh.

What would you add to the checklist that would determine whether or not a DBA was right for a job?

The following question was posed to me during a call today and its one that I’ve heard quite a bit so I figured it warranted a blog post.

The answer is, absolutely!  SELECT statements acquire a shared lock on the tables being accessed.  This shared lock will not affect other SELECT statements hitting the same table, but if someone tries to modify data in the table (via an UPDATE statement for example), the UPDATE statement will be blocked.  When analyzing locks using sp_lock the (S) symbol indicates a shared lock is on an object.

This default behavior can be modified by using one of many query hints.  Using the NOLOCK hint on a SELECT statement will force SQL Server to read data from the table without creating a shared lock on it.  When using this hint, you run the risk of reading uncommited data from the database but in cases where reading data with 100% accuracy is not required, NOLOCK can dramatically reduce blocking and improve the performance of your SELECT queries.  Another option is to use the READ UNCOMMITED isolation level when running your transactions, which conceptually does the same thing.

One additional caveat about both methods.  If your SELECT statement is running, and it expects to read a page that has been deleted by a transaction that is currently executing, SQL Server may deadlock this transaction.  There is an interresting post on this topic that can be found here.

I ran into an interresting situation today while working with a customer and thought it worthwhile to blog on the subject since my internet searching proved lengthy on the subject.  While trying to run a RECONFIGURE statement after making an sp_configure change I received the following message:

Msg 5808, Level 16, State 1, Line 2
Ad hoc update to system catalogs is not supported.

I’m running on SQL Server 2005 SP2+ so my first thought was… obviously, but I’m not trying to update system catalogs, I’m trying to make an sp_configure change.  After doing some digging I found that the culript was the sp_configure ‘allow updates’ parameter.  This configuration in SQL Server 2000 allowed or dissalowed direct system table updates.  In SQL Server 2005, this configuration item still exists but it is obsolete since direct access to system tables in always prohibited.  While the configuration item is obsolete, having it set to 1 in SQL Server 2005 requires you to run the RECONFIGURE statement using WITH OVERRIDE, otherwise you will get the message above.

If you ever see the message above when trying to run RECONFIGURE you will either need to run RECONFIGURE WITH OVERRIDE, or first run:

EXEC sp_configure ‘allow updates’, 0
RECONFIGURE

I find it confusing that an “obsolete” configuration parameter can have an effect on instance behavior, but oh well.  I’d love to hear comments if anyone has any insight into this or similar issues.

Thanks to Jasper Smith, who posted here and pointed me in the right direction.

I had a great time speaking with the large communities of SQL Server users as I traveled about Europe last week and much of this week.  I’m always impressed by the skill, intelligence, and creativity of these professionals.

And sometimes they raise questions that I think are worth sharing with everyone because they too might’ve heard the question before or asked it themselves.  Here are a few examples.  What are your thoughts on these questions?

1. To what extent does compatibility mode effect the behavior of SQL Server?  For example, direct catalog updates aren’t supported in SQL Server 2005. If we’re running in SQL Server 2000 compatibility-mode, can we still access the SYS schema?

2. There are strong architectural reasons why SQL Server doesn’t support multiple log readers, right?  Let’s discuss.  (I have my own ideas about why you wouldn’t want to enable it.)  However, the potential for improving the performance of replication by the use of multiple Log Reader agents is very strong because you could have more data flowing from one server to another.  Imagine for example, if you could have one log reader pumping data for the “customer” table and another pumping data for the “orders” table.  How can we build a solution that offers us those benefits?

3. Error messages?  Yes, error messages.  Is there any way we can find out what error messages are most commonly encountered across the SQL Server universe?  Maybe someone on the PSS team can help us out here?

4. How do I give a good presentation?  Well, there’s a hilarious YouTube video that covers all the bases - http://www.youtube.com/watch?v=cagxPlVqrtM&feature=related.  But if you’re looking for some real resources, here are some others that are useful:

Good web sites from Garr Reynolds and his associated  book:
http://www.presentationzen.com/
http://www.garrreynolds.com/Presentation/index.html
http://www.amazon.com/gp/product/0321525655/103-6148611-3957463?ie=UTF8&tag=garrreynoldsc-20&linkCode=xm2&camp=1789&creativeASIN=0321525655

Guy Kawasaki web site:
http://blog.guykawasaki.com/
http://blog.guykawasaki.com/2008/09/winners-of-worl.html
http://blog.guykawasaki.com/2005/12/the_102030_rule.html

How to Give a Steve Jobs Caliber Keynote Presentation:
http://www.maclife.com/article/how_to_give_a_steve_jobs_caliber_keynote_presentation?page=0%2C0

So what do you think about these questions?

Best regards,

-Kevin

The last several times I’ve spoken at user groups, I’ve asked for a show of hands to find out when people plan to put their first SQL Server 2008 instance in production. I ask because it helps me write better presentations - there’s no sense in me talking about something you don’t plan to use, or to give you a getting-started-101 presentation on something you’ve been using for months.

The results have been surprisingly low - usually less than 10-20% plan to do it in the next 30-60 days. I’m curious to see if my readership has the same plans, so with no further ado, my first poll is below.  (This may not work for readers with RSS readers.)

 Loading ...