SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

But We Have Always Done It That Way

I recently ran across the following scenario. A SQL Server instance had been upgraded from SQL Server 2005 to SQL Server 2008. At the same time, the older physical hardware had been replaced by newer hardware. The DBA who had set up and configured the older server was no longer at the organization, and a new DBA had performed the upgrade. After the upgrade was completed, I was asked to review its configuration. I started by reviewing the various server and database settings, along with the assorted database maintenance jobs, and then I began to ask questions about them.

I was expecting to get specific answers to each of my questions so I could better understand why settings and jobs were configured as they were, but instead, I got the same answer to all my questions: “The settings on the new server were used because they were the same ones used for the older server.” I followed up by asking the DBA if he knew why these particular settings and jobs were chosen in the first place for the older server. The DBA didn’t know, and said, “This is the way we have always done it.”

I just wanted to scream when I heard those words. What the DBA was telling me was that he was not thinking about the implications of how an upgraded version of SQL Server and new hardware could affect how SQL Server is configured. It also told me that the DBA had never bothered to take the time to review the server to see if the settings and jobs configured by the original DBA were even valid in the first place.

As DBAs, it is our job to ensure that all our servers are running as optimally as possible. We can’t just shirk off our responsibility because “this is the way it has already been done.” As DBAs, we need to be proactive. So if you start a new DBA job, don’t assume that the SQL Server instances are correctly configured. Take the time to evaluate each one, and don’t be afraid to challenge the status quo. If you upgrade a server to a new version, or to new hardware, don’t assume that the older settings are still appropriate. And even if you have optimized your SQL Server instances in the past, this doesn’t automatically mean that the original settings are still optimal. Over time, more data is added to databases, and often, different queries are run against the database, which could mean that older settings need to be updated. In other words, SQL Server instances need to be reviewed periodically to see if they are configured appropriately.

So the next time that someone asks you how your SQL Server instances are configured, be sure you can tell them exactly why the server is configured as it is. Anything less, then you aren’t doing your job.

What do you think?


Posted by Jason Brimhall on 29 January 2010

Point Poignantly made.  All too often, this is the case.

Posted by Glenn Berry on 29 January 2010

Since SQL Server 2005/2008 is so easy to install, and many of the default configuration settings are the the best choice for many workloads, it is easy to see why this happens. Still there is no real excuse for the DBA to not take the trouble to figure this out and fix it.

Posted by Steve Jones on 29 January 2010

The settings might not even be acceptable for data size changes as well, especially in code.

I agree with your sentiment, but you ought to also make sure that you understand what you are changing. There are times things are done for a reason, and an effort should be made to understand that.

Posted by Brad M. McGehee on 29 January 2010

One thing I didn't point out was that many of the settings were outright wrong for the server. The previous DBA made plenty of mistakes, and the new DBA did not ever bother to evaluate the configuration to see if it was appropriate. The new DBA just assumed that the old configuration was OK, and never questioned them. Of course, I am not suggesting that you always need to make configurations changes, but they should at least be evaluated.

Posted by Chris Houghton on 2 February 2010

Past experience has taught me that changes to existing settings should be made with care and their impact monitored. Fortunately, I work in an ITIL, change controlled, environment so changes to servers and the reasons for them are recorded. IMO the attitude "if it ain't broke, don't fix it" has its place.

Posted by Scott Simmons on 2 February 2010

That's true--but the way to find out if it's broke or not isn't to wait for end users to report a problem in production.

Posted by talltop on 2 February 2010

Very well stated Brad. I have found many DBA's in my career that tend to fit into the "Lazy DBA" category in that they tend to do just what is required and no more. This very common in the work place. I once asked a DBA when she was complaining about an installation that was not working after she had completed it "Ok, what were your post-installation tasks?" She looked very surprised and said "What are you talking about? I just installed SQL Server all the way through and clicked Finish!" What more do I need to do?" You would be surprised how many DBA;s out there think that this is all a SQL Server installation or upgrade process entails! I will take a pro-active DBA any day of the week over a DBA that just keeps up with the status-quo. This is such a common problem that I put together a Powerpoint Slide demonstration on this topic alone called "SQL Server 2005/2008 Post-Installation Tasks" which is a combination of my personal experience with installs and upgrades, tips from BOL, and of course tips from this forum and other seasoned DBA's. E-mail me at talltop@bellsouth.net for any of you that would like to see it and I will email it to you. You will come away from the presentation with one big discovery: You are not done when you click on Finish by a long shot!!!! Great article Brad! and spot on too....

Posted by bwillsie-842793 on 2 February 2010

It sounds to me like the current DBA was "trained" but not "educated".

It's the difference between pushing a button and knowing when and why to push a particular button.

Education (and experience) also helps in determining when something has gone from "ain't broke" to "ain't right".

Posted by Willy Esteban on 2 February 2010

I'm not a DBA, but I do lots of DB work with DBAs developing business applications. I frequently run into the always-done-it-that-way thinking; and its not exclusive to DBAs. I've seen it in CIOs, managers, project leads, and application developers. I guess some folks fear stepping out of a certain comfort zone, even if it is the right thing to do. I don't subscribe to that concept. Everything I do has a well-known purpose and it is the best it can be at the time it is developed - and that's the way I've always done it! :)

Posted by jparker2 on 2 February 2010

I really do not blame the DBA for this attitude.

I blame Sigma Six and SOX for this attitude.

Both of these policies in orginazations make it very very difficult to change existing settings until either

a. They fail an audit.

b. Something is broken.

The statements "If it's not broke do not fix it." and "We have always done it that way." are logic supreme in these worlds.  

IT in publicly traded companies has lost invoation and inspiration to political technology stagnation.

I watched one person in a local company get layed off even though he had been with the orginazaiont longer. The reason was becuase he wanted to update the servers that where working fine to the most recent service packs and install an anti-virus application.  There was no money or will to do this, because the servers had run just fine for over 2 years without these things.

Be carefull when the Emporers New clothes seem very pretty to everyone but you.

Posted by David Walker on 2 February 2010

"many of the default configuration settings are the the best choice for many workloads".  BUT, but, but, the default location for the database files is in the C:\Program Files\SQL Server\ directory.  

Blecch!  Even if the database files for the "real" databases are eventually placed somewhere else, the two files for the Temp database will be on the C drive unless they are moved later.  This is bad for several reasons.  (Along with Model, Master, and MSDB, etc.  I can possibly see leaving those three on the C Drive in a lightly-loaded server, but not the Temp database.)

This default is NOT the best choice.

Posted by Tim Mitchell on 2 February 2010

Brad, this is a good example of how just "going with the flow" can be a negative.  One doesn't have to go through and change everything to what he/she thinks it should be (especially in a heavily audited environment as mentioned in a previous comment), but the DBA should at least understand what the settings represent and why they're set a certain way.

Posted by Randy Lent on 2 February 2010

I worked for a small organization where the DBA was also the Windows network administrator, and also performed several other functions.  He had no formal training in SQL Server, and most of what he knew about the servers came from the vendor.  As I began learning SQL Server, I realized that this guy didn't know what he was doing.  I worked for another organization where the DBA was very sharp, but once again, he had many duties other than administering our SQL Server databases.  I suspect that one or both of these scenarios is not uncommon, particularly in small organizations, and especially in times of tight budgets.

Posted by James Goodwin on 2 February 2010

I'm going to come in on the side of the DBA in the article.  If I was tasked with upgrading the Hardware and the Software at the same time then I would endeavor to make the new environment as much like the old environment as possible.  I might not know why those decisions were made since I wasn't there.  But what I do know is that you don't introduce additional potential problems until you have the system working.

While "that's the way we've always done it" is frustrating to hear, the implied ending is "and it works".

They brought you in to review the configuration so the DBA was NOT saying "that he was not thinking about the implications of how an upgraded version of SQL Server and new hardware could affect how SQL Server is configured."  The DBA was saying:  "I don't know, that's why I'm asking YOU"



Posted by djnewman on 2 February 2010

I too have to agree with the DBA. As a project lead and developer, I really get unhappy when some change is made to a production server. Invariably, hours and hours of developement effort is required to get an application working again and the comment from the DBA team is that "there were no changes" and/or "we can't put it back the way it was".

Changes to production systems require huge effort and buy in from all parties.

We are currently investigating upgrade from various SQL 2000 and 2005 servers to fewer SQL 2008 servers. As a major part of that effort, we are identifying ALL of the applications that use SQL server in any form so that we can determine the development effort required to move each of them. Further, we will be doing a proof of concept upgrade for one complex application in each of Data Warehouse, Web and client server.

In your case, it was great that the inside DBA asked for your assistance, but that does not mean that he should follow it, just that he should understand the implications of the changes you suggest. Implementing those changes is a project on its own.

Posted by talltop on 3 February 2010

You cannot blame Sarbanes-Oxley for a SQL Server that is either poorly configured or has just outgrown its original settings due to many factors, of which are too many to go into in this one post. That is just the silliest thing I have ever heard. Change is part of our business and changing SQL Server settings is an integral part of that as well. Old cliques like "If it ain't broke don't fix it" or "That is the way we have always done it." are outdated IMHO and many times used as easy answers by people who don't want to make the change in the first place, or be responsible for its consequences. I call this the "Lazy DBA" syndrome. If things weren't really meant to change then why would we have Test encironments in the first place? Change isn't always about just changing code, it sometimes involves changing existing configuration settings as well. That's just the way it is in the real world. Particularly, in this wildly changing IT environment we are all in. :)

Posted by robcline on 4 February 2010

Looking at this situation from my prospective as a former Division IS Manager. The answer, "This is the way we have always done it." would have been unacceptable. A DBA is one of the highest paid individuals in the IT/IS departments because they are expected to have a high degree of competency in the maintaining and optimizing the database systems of the company.

While the answer is the safest path to avoid creating an error that might get you fired, it was completely unacceptable in the context of an audit asking why each and every setting was kept the same even though the  new version had new and, presumably better, options. This is the best opportunity you will ever get to correct prior suboptimal existing configuration choices and enhance the new system via reviewing each and every option, it's effect on all existing Sprocs, Functions, and misc database objects.

That's why you're paid the big bucks.

Leave a Comment

Please register or log in to leave a comment.