Part 5 DBAs distribute data to the 4 corners...

Another role a DBA plays is distributor of data.  Keeping the data safe, well fed and maintained, accessible and highly available are all well and good BUT what if a giant meteor were to fall from the sky and destroy the datacenter where your server lives(ed?)?

Well many companies would simply go out of business, sure they would scramble trying to scrape together hardware that can keep things moving, the poor DBA will hunt down the latest possible backups and desperately try to remember all of the settings and gotchas in building up the environment.  But in the end, it will take days, if not weeks to get back up, and by that time your customers have moved on.  Your company may survive, but many people will be out of work while the company rebuilds.

A huge part of a DBAs life is to make sure that this doesn’t happen.  No, I don’t mean building some sort of laser or rocket defense against falling objects from space.  But, the DBA needs to drive and work with other departments to create a plan, document the plan, and implement it.

Sometimes budgetary constraints mean that a duplicate datacenter is simply not feasible.  That is no excuse for not having a plan.  This plan should be documented in soft and hard copies, and kept someplace other than the office and the data center.   It needs to be updated at least bi-annually if not quarterly.  

The DBA needs to have it clearly spelled out what the minimum requirements are for being up and afloat.  DR doesn’t have to fully support all functionality, nor does it have to be as fast as production, but it needs to support all mission critical functions, and do so with only a slight degradation in performance.  

Your DBA SHOULD know the business well enough to make many decisions on how to accomplish this, however, you as a business person, can help them by answering any and all questions about what is mission critical AND be honest.  You can’t have everything.  Pretend like you can only have one thing, what would that one thing be.  Then work down the list.  DR is all about minimizing exposure and minimizing risk.  But reality dictates that most organizations simply won’t be able to spend enough to duplicate the total environment.  

Now, you may ask, why should a DBA spend so much time on something that you don’t want to use?  Why do you have homeowners insurance?  Why do you have live insurance?  Why do you have major medical?  Of course you do.  Perhaps not enough, but you have some coverage.  Having a DBA spend the proper time on this task is crucial to the survival of your business in the event of catastrophe.  

Again, this is why your DBA has a negative outlook frequently.  DBA’s constantly have to look at all of the possibilities of what can go wrong, how it can go wrong and what the fallout is when it goes wrong.  In a world that wants to see the positive in everything I hope you can appreciate your DBA and his negative outlook.  Yes, he can rain on any parade, but you need somebody who sees what is broken and can come up with inventive and constructive ways to fix it.

It is important to realize that DBAs have to have a long term view, and sometimes they cannot (should not) allow little fires here and there distract them from the big picture, or a literal little fire under your server cabinet could really ruin the day of many people working for you.

It is important that business people allow the DBA to work on this aspect of their job, and even have them schedule time to focus on it.  Ask your DBA about their plans, ask them to show you what they are thinking of, ask them to help the company live on and people keep their jobs in the face of disaster.

DBA Part 4 Architect and Builder

Another metaphor, perhaps even a labored one!  

DBA’s are Designers and builders.  Even if you have a system in place it needs renovation or perhaps is close to end of life.  A DBA always is looking to improve things.  Tweaks to make things faster, more safe, more reliable, more available.

There are two key ways DBAs look at the database world.  One is very application centric and deals with tables, columns rows and relationships.  The other is very infrastructure (Servers, disks, networking) centered revolving around GHz, gpbs, GB and so forth and so on.

Again the DBA must know the business and the application.  Working with a Business Analyst (or being one himself as I am a BA too) the DBA needs to learn as much about how business works in order to know how to best store the data in tables.   Now, in many shops programmers do this task, and some even do a good job.  But in most shops that have a DBA, the DBA is brought in to bless the data design in ER diagram form before being tasked to actually build it.

In an earlier installment of this series I mentioned the term Normalization.  This is the time in the development life cycle (SDLC) that the DBA will employ normalization the most.  Looking at the data that the company wishes to store element by element, seeing how it all relates and then weeding out redundancy and dependency until all that is left is a lean, mean, data storage machine.  Or database.   This takes time, thought, printouts, many hours of staring at printouts,  a few key moments of head hitting wall, and then more thought.

Once the design is done (assuming no emergencies are currently under way, no new software is being deployed, all maint tasks have been done properly and there are no end users with pending query requests lurking about) , the DBA needs to meet with the development staff and explain the design.

I once had the misfortune of designing a very clever schema that was very powerful, flexible and fit the need just right, but didn’t have the time to explain it.  My developer (who happened to be, in this case, my boss’ boss) claimed to understand the design.  I assumed he was right and moved on to other DBA like tasks (napping perhaps or donut eating) and left said developer to their own devices.  Fast Forward to 3 days before go live.  There was no code review, no testing, no nothing.  The developer was in my office hot under the collar because he couldn’t get something to work right.  I pointed out that it was clear he misunderstood the design, and had put in place many hacks and workarounds to my elegant design, and that was causing unneeded complexity and was the cause of his bug.

This revelation went over like a ton of bricks to which he told me

1) that he “Hated me in the marital way” and

2) would NOT ever fix it the right way because he had invested too much time in the wrong way.

Lesson?  Always take the time to talk to your developers, even if they claim to understand.  To you developers in the audience, please make sure you request a walk through, and understand it fully.  This database went into production, caused so many issues that I re-redesigned it, handed it off to proper developers EXPLAINED it fully, and now there is a much better application sitting on top of this awesome database.  This bug was my fault because I did not make sure that the user of my design understood it clearly before setting off.  When your DBA explains things to you and tries many ways to make it clear, please realize he is just trying to avoid any missteps.  

DBAs also must make sure that everything that the database is sitting on meets the needs of the applications that rely on the database.

This means that the DBA has to walk down to the guys who deal with nuts and bolts.  In this unsavory world people do actual physical labor including, but not limited to, lifting heavy metal boxes, and plugging cables into them.

Most end users aren’t brave enough to even talk to these people, and for good reason, they are scary!  The DBA has to make sure to not only talk to them, but *gasp* develop a great working relationship with them.

While wearing this hat, the DBA must use his knowledge of the applications, the company and how databases work, coupled with his knowledge of server hardware, operating system, networking and storage to help to design a solution that meets today’s needs AND will cover future growth.  

Yup, DBAs also must look into the crystal ball and predict the future.  I did that with a few key metrics on data growth, transactions per time period and looking at the sales pipeline and talking to the execs in Sales as well as the CEO to make sure I understood where the company was going.  If your DBA wants to talk business, indulge them, educate them, help them to understand what is important to your business.  They are just trying to be as educated as possible so that they can make good decisions.

Armed with this, a plan (or three) are developed.  First there is the “Money is no object” plan.  This one is fun because you can look at all sorts of exotic technologies and put together the killer system.  There is the middle of the road that gives you nice features but costs less, and then the bare bones, what can you get away with and make due with.

Once the plans are in place, the DBA will then have to defend decisions and assumptions, and therefore must be armed with data (ironic?) .  Being armed with data is one thing.  Another is being able to make cogent arguments that hit at the core.  Always hit them where it counts, in the money belt.  Yes you are asking to spend a lot of money, but what happens if the data isn’t available, what happens if it isn’t retrieved as quickly as possible, what happens if the company grows faster than expected and then they have to spend EVEN MORE money on a solution that they could have had in place to start with.

Business people speak in terms of money. DBAs don’t.  DBAs need to, but they simply don’t think that way.  Remember when speaking to a DBA about this wiz-bang infrastructure that he has planned that he does indeed want the company to succeed, and wants to do so without spending too much money and without working too hard.  We are people too, and I do realize every penny I spend on a new SAN means one less penny for my pay check or bonus!

This is, in my opinion, the most fun a DBA can have.  The work is almost all theoretical, and has no current impact.  Nothing breaks when pencil hits paper, there is no down time, no risk.  Just research into what is, and what should be.  Both in the technology realm, and in the business realm.  If the DBA seems carefree and smiles, chances are there are no looming emergencies or maintenance windows AND he is busy designing the next generation for the company.  That is a good time for a DBA.  At least for a brief moment when a possible issue that they thought of today is not currently handled, and that will cause another sleepless night!

DBA Part 3 Pit Crew of the company

I am a die-hard Formula 1 and LeMans style endurance racing fan.  I watch every F1 race and qualifying session broadcast in the US every year, and have for over 10 years.  I watch ALMS and Le Mans style racing too.

They are vastly different forms of racing that require a different approach and have different rules, yet one thing they have in common is that the less time spent in the pits with the car being serviced the better chance they have at victory.

In many ways a DBA is like this as well.  More often than not these days, a company’s data needs to be accessible 24 hours a day, 7 days a week, 365.25 days a year.  

Not only must it be accessible, but retrieval must be quick, and the data must be right.

What this means for your friendly local DBA is that every move must be planned out with precision, and there are tasks that must happen to keep everything smooth.  Granted, any good (read lazy) DBA will automate as many of these as possible, and then keep tabs on how the automation is running with logs and reports.  A big portion of the DBA’s time is spent on making sure that THEY get the data they need on how things are working.

In this (labored) metaphor, the database server (or data) is like the race car, the driver would be the end user and the DBA is the pit crew.  DBA’s do not drive the car, we are not the car, we build, maintain and fix the car as need arises.

When the server (car) enters the race (goes into production) it is costly to bring the server in for repair.  Be that the tasks mentioned before about index building or maintenance, or checking for corruption.

There are a few tools your DBA has in his toolbag for this.  First and foremost is knowledge of your business cycle, the daily, weekly, monthly and quarterly cycles of your business is crucial.  You may hear your DBA ask all sorts of questions about your business.  He isn’t nosey, he is trying to do what is best for the company.  There are times in Endurance Racing where the team will drive to a pace, and times the team will go flat out.  In many ways the daily and weekly maintenance windows are these times.

In most business’ based in the US nothing can happen until after 9PM.  Us folks on the east coast have to take into account the pesky west-coasters and keep things running in ‘production mode’ for them until their business day is over.  Some business’ cater to end users who may not use their services until after the workday is over, which case, it is Midnight or later when the window opens.

The DBA will work hard at getting the backups, defrags, reorgs and any nightly roll ups for reporting done in this time.  The data must still be available but it is okay to be a bit.. slow.

The DBA also makes sure the databases are in tip-top shape and check for all sorts of issues using automated tasks and reports based upon data collection.  There will be charts about locks, blocks, CPU utilization, Disk Utilization, you may hear him utter things about split pages, buffer cache hit ratio or any number of other things in a language you may not understand.  This is normal!

This is the normal routine.  What happens when the car breaks, or even worse, the driver crashes it?

Professional pit crews plan for just about every possible scenario and practice each and every move many times until it is second nature.  Pit crews also know their car inside and out, and can diagnose issues fairly quickly, and for instances where something is truly broken beyond repair will have spares to use to get the car back into action as quickly as possible.

The same is true for a DBA.   We must know our data, our infrastructure, our applications and business’ so well that when something goes wrong (it always does) we can respond properly and quickly.

This brings me to one key point of a DBA’s design and development work.  High Availability.   High Availability or HA is simply a way to make sure that if one set of hardware were to break there is a second nearby to take over without much fuss.  I have built this many times over in my travels, and it works well for all sorts of issues both planned (patching) and unplanned (memory going bad in a server).  

Having the HA plan built and well documented is essential to the smooth operation of an IT Shop.  The DBA’s first responsibility here is to make it as seamless as possible so that the least number of people need to be involved in any failure.   What good is HA if the dev staff has to be dragged out of bed kicking and screaming to fix an issue.  Budget is also a huge part of this.  The DBA wants to do the best possible job, and may have the inclination to spend copious amounts of cash on this issue.  Trust me, he has the best interest of the company in mind.  Left to their own devices most good DBA’s don’t want to do work that isn’t needed.

After the HA plan is documented, approved and built, the DBA must test it and schedule a routine test of the plan to make sure it works.  This is nerve racking, because if it doesn’t work the DBA is on the spot to fix the issue.  This is the company’s data, and when the test fails, the company is most likely down.

The DBA must also practice for all sorts of other issues.  Practice restoring data, practice debugging or performance tuning.  Learn about all of the bits and pieces the database depends on (The Database Engine of course, also the OS, Hardware, networking and storage).  The DBA cannot possibly know all of this, but must keep a working knowledge of all of these subjects and have a close and good relationship with the people that do know. There has to be a trust there on both sides, a mutual respect, and if that is there then some healthy joking.  I mean hardware people are only good for plugging stuff in after all ;)  

A good DBA will have all of this going on in the back of his mind at all times.  Always keeping an eye on how things are going, always thinking about how things can go wrong and planning and practicing for every possibility.

Many have key scripts written, and instructions for most errors.  Many use Google to find things ( I know I do) that they don’t know.  DBAs like the boring life, and strive and work hard to have one.  When they are surprised the stress level builds.

When an emergency does happen please remember this.  The DBA will have a Director, VP and possibly a C-Level person in their office until things are fixed.  This is very stressful.  DBAs are often asked questions that they do not have the answer.  I mean, if I KNEW why it was broken right away, it would be fixed, or better yet would never have gotten to this state.  

DBA’s are stressed and are paid to be negative.  We are paid to think about every single way data can be made unavailable, corrupted or destroyed and then to formulate a plan to keep that from happening and fix it when (not if) it does happen.  I know I’m slipping into DR here, and I promise I will touch on that more deeply later, but remember this always.  The DBA has lots of important tasks to do, and if they do not jump on your query or answer your question straight away it isn’t because they don’t want to, it is because they are trying to keep the race car running, keep the valuable data from breaking down, crashing and fixing it when it does.

Stick with me, next time I’ll talk about how a DBA architect the data inside of applications for you people who have home grown apps, and how the DBA works with the infrastructure manager to build a high performance system using bits of technology that many don’t see.  

Until then, bring your DBA a donut and thank them for all the hard work they do.  Most DBA’s respond well to donuts and praise, and who knows maybe your query request will be done more quickly.  Not that I’m advocating bribery or flattery......

DBA Part 2, Shepherd of the data

In my intro I mentioned that the DBA is the Shepherd of the Company’s data.  First we must define what a shepherd is and what one even does.  In ancient times, a shepherd took care of a flock or flocks of domesticated animals.  These animals were pretty much helpless, easy prey and needed constant care and attention.  They needed good fresh pastures to eat, clean water to drink, and the protection from the odd wolf, bear or lion.

In biblical accounts, a shepherd took great pains to care for his flock, counting each one, knowing them by name and what each animal would do.

This is not a stretch for a DBA.  The DBA cares for and protects the Company’s data.  Ever diligent to threats to the data and the servers it is stored on.  A DBA will know his environment, he will know the servers.  He will know what alerts mean what, he will know when to spring into action and when to let things run their course.

The first duty of data protection is security. The DBA is responsible to design and maintain a security methodology that not only keeps the data safe from prying eyes looking to steal or destroy it, but also must understand the business and the applications that need access to the data and design the method to be easy to maintain, easy to administer and create fewer problems than it solves.  

Security is one of the big items many users simply do not understand.  Many users think that data access should be wide open, and free range.  Users tend to be offended when access is restricted and good security policies are in place.  

While I understand, as most issues are, this one is far more complex and gets more complex the deeper in you get.

Databases are not like Excel spreadsheets, they aren’t laid out in an easy to understand way to people who have not studied relational databases.  This isn’t done to have job security, but rather to meet other goals of a good DBA.  

The process that complicates data retrieval is called Normalization.  There are many forms of normalization from first to fifth (perhaps beyond?)  and most good databases are in third normal form.  Normalization is a complex subject in and of itself.  What happens is that data is split out into various tables with different keys to reduce redundancy and dependency.  For a quick look into normalization (I won’t go deep here)  you can read this https://en.wikipedia.org/wiki/Database_normalization

DBAs want to protect the normalized base data from end users and keep them from having SQL query access because getting accurate results from normalized data takes practice and a good grasp on the SQL (Structured Query Language) code needed to retrieve the data.

I’ve been in environments where power users were granted this access, and consequently sent incorrect data to customers causing all sorts of issues and reducing the customer’s confidence in the data provided to them.  

Keeping the database locked down for nobody to see is also not an option.  There are legitimate uses for data (otherwise why store it?)  but perhaps to only a subset of the data.  The DBA plans on this, and creates a framework to build security based upon roles and groups so that when people are added or removed this maintenance is simple and quick. To do this the DBA must understand the company’s data, the org chart of the company, and the applications.

Like a shepherd the DBA protects the data against predators too.  You may think that there is no way somebody can get into your network, and get to your data, but I beg to differ.  There are many methods that hackers and others can use to either see data they aren’t supposed to see or destroy data that they shouldn’t have access to.  All using seemingly legitimate means.  I refer you to a XKCD comic that is a staple among all DBA’s

This may seem far fetched, but it is common, and dangerous.  A DBA protects against these threats as well as other.

I will tell a story about a great Data Analyst named JB.  JB is perhaps one of the most gifted analysts I have ever run across.  Careful, intelligent, and extremely adept at writing SQL code.  

One day I got a call from JB who informs me that an entire table had the first and last names changed to “Joe” and “Bob” respectively.   JB had legitimate read access to this data, however perhaps he should not have had Update.  That was a debate that we could have had.  But at the time I had half a million people who needed their proper names returned.

DBA’s must protect against threats internal and external, and must think of things like little Bobby Tables, and well meaning and gifted internal folks digging around in the database.

Shepherds feed their flock and provide water.  Without these two basic needs all of their animals will die.  Likewise a DBA must maintain the company’s databases daily, making sure that many tasks are accomplished every day, week and month.  If the company is healthy and growing, so are its databases.  Most companies I’ve worked for do not like to archive things off, opting instead to have huge databases.  When I started in this line of work, having a 1 GB database was huge.  Now I have databases with tables that are over 300GB, and multiple terabytes of data on my servers.  Keeping access to this data fast and reliable is a huge chore.  

Yes much of it can be automated, but a good DBA keeps tabs on the health and wellbeing of the databases.  There are constructs such as Indexes that are designed to make data retrieval fast. A DBA must not only monitor the existing Indexes and keep them from being fragmented, but must keep tabs on how they are utilized, look for indexes that need to be removed, and look for indexes that need to be added.  Keeping in mind that most databases have new data added, old data updated and all data retrieved a DBA must understand how the database is used to properly design the indexing strategy for the application at hand.  

The DBA must also check for corruption in a database and repair any issues there along with keeping tabs on how the server is used, Memory, processor, networking, storage (disks) .  A good DBA will note that the trends and proactively get the ball rolling on upgrades and other tweaks so that any outages in service are planned and work towards a better solution rather than a surprise when the disk runs out of space or a query doesn’t complete because it times out due to the processor being too busy.

This post is turning into a long read.  I will wrap it up here.  Without a good and careful DBA shepherding the databases a company runs the risk of many dangers, and snares.  From issues that arise from greater database usage and growth, to external threats.  Having a DBA who wants to understand your business, who wants to know all about the underlying technology of what the database lives on, not just the database engine, but the operating system, hardware, storage and networking is essential to all going concerns who have a database.

This is stressful work, a misstep can cause some big problems, so when the DBA isn’t responsive please remember they are taking care of the data like a shepherd takes care of his sheep!

In the next installment I will show how the DBA is part of a pit crew for a race car, and while there is some overlap it will be another angle into the mind of a DBA.

Just what is a DBA?

You see him walking briskly towards the IT Director’s office early in the morning, the look of concern deep on his face, he walks by without a glance, without saying hello, without even noticing you.  This is the DBA.  He does this every so often, and since you don’t work directly with him you just think he is rude or self centered.

At times he looks worried, can be snippy or terse and will provide you with a curt answer if interrupted while staring at his screen.  Surely what he is looking at isn’t as important as your problem, you think, all you needed was help getting some numbers for your customer.

Chances are, you see this DBA creature lurking around, looking stressed or carefree.  Rarely in between.  He hangs around with other systems administrators, networking folks and rarely programmers talking about strange and magical things.

Do you know your DBA?  Do you ever wonder what makes them tick, why they are so high strung, and how to better get along with them?  You see, DBAs are told constantly they need to be more open with people, be warm and nice even, and while I agree with this, I think this role is highly misunderstood.  I mean, it is just data, it just kinda sits there doesn’t it?

Chances are, you only have one or two DBA’s in your company, and chances are they are on call 24/7/365.  Even on holidays and vacations, they are tied to their phones, their trusty laptop by their side ready to swoop in at a moments notice.

This series of entries will hopefully shed a little light on this odd creature, the DBA, or Database Administrator.

The DBA is a curious creature, some are nocturnal while others  wake before the sun rises and work mainly during daylight hours.  They seemingly subsist on junk food and caffeine, and by in large prefer the soft glow of monitors and a 60 watt incandescent bulb to the harsh bright florescent tubes in most offices.

They don’t move much, but when they do it is with great purpose and is fairly swiftly.  

DBA’s are accused of being lazy, and the best ones are.  No real DBA wants to do more work than is necessary.

We will start with what a typical DBA is responsible for, and use that to explain the laziness and the seemingly bad manors.  I will list out the areas a common DBA is concerned with, and in a series will break down what it means to you, the ‘user’ and why you really want his focus there.  This list is hardly exhaustive, some DBA’s do more, others do less.  None of these tasks is trivial and all are important

DBA Responsibilities:

1) Shepherd of the Company’s data

• Granting access to people who need it
• Denying access to people who don’t need it
• Protect the data from predators
• Feed and care for the data

2) Pit Crew of the Company’s data

• Keep the data fast
• Keep the data reliable
• Keep the data free from corruption
• Move quickly to keep the data always running.
• Respond to emergencies quickly, professionally and without error

3) Architect and Builder of the Company’s data

• Design how the data is stored and retrieved
• Design (along with other infrastructure people) how the data is stored and retrieved
• Architect systems for both Transaction Processing and Reporting needs

4) Distributor of the data

• Making sure the data is safely located in more than one place
• Keeping the data avaliable as much as possible

5) Support structure for users of data

• Support Developers and their many needs
• Support all levels of end users of data
• Support company’s customers in their use of data
• Provide a shoulder to cry on when data is lost

I know I’ve left out some, leave a comment and tell me what I’ve left out.  I will be growing this over time as I seek to show people just how valuable a resource their DBA is.

I hope to make this a more than 5 part series, but time will tell.