Building Right or Build Twice- That is the Question…

This question seems to pop into my mind consistently over the years as a DBA.  I’m a “build it right or don’t build it at all” kind of DBA, but due to my gift for finding problems and fixing them, I find myself more and more often performing the second build on processes/procedures/designs, which I often would like to avoid.  I went through this repeatedly at a previous shop and it’s still fresh in my mind, even today…

Don’t get me wrong-  I think it’s a noble cause when you first come into a new shop and it’s either been neglected or didn’t have the DBA support it may have needed and the update in TLC is always appreciated, but if it’s something I’m revisiting because of a lack of requirements or a production change that wasn’t thoroughly benchmark tested, I find me getting a little cranky-  (usually with myself since I expect that I should be able to read minds at this point! :))

I believe to utilize your DBA and development team efficiently, not wasting money on resources, building “right” is essential.  Designing the initial database, as well as future development should be reviewed repeatedly to verify you can answer the following questions with a resounding “YES!”:

  • Is the main schema design scalable and easily adaptable to our business?
  • Does the daily/weekly/monthly processing require no manual intervention by support staff?
  • Have we performed capacity planning and does the hardware meet the foreseen demand and growth of the environment?
  • Has the schema owners been secured and application security been set to the DBA satisfaction?
  • Are all database files secured on the server from access to anyone outside the database group?
  • Are naming conventions documented and followed in the database environment?
  • Have code reviews been adopted, new code/processing plan meeting requirements been adopted?
  • Have different groups been assigned responsibility for the aspects they must be accountable for that will impact the database environment, (i.e. application support on call for apps, network admins for network issues, etc.)

The companies I’ve experienced DBA’s overstressed and over-worked in the past are commonly places who can not answer yes to the questions above.  Without these requirements in place, the databases become kin to a house with a foundation made of Popsicle sticks, (often built on a side of a mountain prepped for a future mudslide, BTW…:)) 

If a DBA is not there for the initial build of a database, as often is the case, and is just the “unlucky home buyer” of the database, then there is a unique opportunity in front of you to attempt to correct and change the culture that has built this rickety structure. Changing a culture is a difficult challenge-  I won’t try to pull the wool over any one’s eyes.   It is attainable and if you have management support, you will already have a huge head start! 

If you do have management’s support, what steps should you take to stabilize an environment that is expecting it’s DBA’s to be infallible instead of it’s databases?

  • Communication-  Demand daily meetings.  10-15 minutes to discuss what is happening and who is doing what.  Often folks live in their own little world and are unaware of how many folks are impacting the database, thinking there’s in the “one little process” requiring manual intervention.
  • Automation-  What can be automated?  Anything and everything that requires manual work or intervention by a DBA or developer in production should be stopped as soon as possible.  Make it a priority to automate it with procedures, scripts, crons, whatever it takes, make it hands off!
  • “White Noise”-  This is what I call all the informational emails or “fluff” in important emails that can mask or make someone miss what we really need to know about, (like failures!)  Remove emails that are just to inform people processes/jobs “have completed”.  If they are concerned about failures, create scripts on secondary servers to monitor for the main ones, but all email should be “just the facts” and pages should be narrowed down to production issues that require a DBA to address.
  • Worst Case Scenario Development-  AKA, developers and DBA’s should develop all processes with the lightest footprint on the database.  Develop the code with the idea it is going to always be running against the heaviest load on it, the most limiting resources and the least amount of time requirements.  This is the code that will last-  this is the code that will not demand to be revisited by the DBA or developer in the near future, (we’re hoping we can have code that won’t have to be revisited at all, remember? :))
  • Database=Two year old Toddler–  What does this mean?  For me, this means I am either the  DBA Mommy or often feel like a daycare provider.  Poorly developed databases are like toddlers and they will throw tantrums-  A LOT!  I need to be vigil, I need to have the resource time in my schedule to spend time with them, monitor their behavior and know when something is wrong with them.  Now, if you build them right, they will mature quite nicely and require less time, but please remember, the teenage years are often still ahead of us.
  • 500,000 Miles without an Oil Change-  This is how some companies run their databases.  Down the road, 80 mph, and then ask their DBA’s, “Can you change the oil while we continue down the road?  Oh, and while you’re under the hood, could you replace the transmission with a new one, too?”  Designate maintenance windows-  they should be a priority, not an after-thought.  Understand all that is required daily in automated maintenance to ensure the system continues to run, too-  statistics collection, backups and cleanup.  These are required for the health of the database, not just for the DBA’s sanity.
  • Make a List, Check it Twice-  The last task is the hardest.  Keep an up to date list of what makes up that “foundation of popsicle sticks”.  What are the design/process flaws in the environment that need to be corrected to build a sound, solid database and design small projects to correct them.  This will pay-forward in so many ways.  The goal should be to address objects/processes in the system that are not scalable with the database or require intervention due to design flaws.  Selling these projects to the business should include dollars saved in support/hardware or revenue that can be generated post the fix.

The subject of building/designing robust database environments is one I take very seriously and I may take this subject a bit too much to heart at times.   I was raised with a tough, dedicated work ethic- something I received from both my parents-  rural Canadians are a pretty tough bunch…   Taking on a challenge is right up my alley and I’m just not one to give up.  Can I accomplish it?  Eh yah, you betcha! 😛