Wednesday, June 05, 2013

High Availability vs Disaster Recovery. What are they, and do you really need them?

I’ll admit, Disaster Recovery and High Availability are almost an obsession with me.  Having been a production DBA for a number of years and being in shops big and small with mission critical 24/7 applications to support can you blame me?


And while this topic has been written on and pontificated upon many times over, I’ve yet to see many companies do both Disaster Recovery and High Availability well.  Not that they haven’t tried, but sometimes it is seen as good enough to have one or the other, forgetting that they are two sides to the same coin, and important in their own way.  I assume it is because many people lump the two into the same category, and if you have one you have both, right?  Well I think that is a dangerous way to think.


Lets start out with my working definition of the two, first up High Availability:
High Availability or HA is simply having enough redundancy built into a given datacenter to sustain massive hardware failure within that datacenter.   Be it a Server’s Power Supply, a network Switch or Router, a disk drive or even an external internet connection.


Sounds like an oversimplification, but when distilled down that is all it is. Having a ‘fault tolerant’ infrastructure that can absorb the hit of having equipment break.  Be it an entire server or a sub-system of a server. This varies by application and by company.  


For example, if you have some important applications that can tolerate some down time, you may not want to put in the expense of a MSCS cluster, or a VMWare ESX cluster, rather you could employ redundant power, RAID, and having enough spares and scripts to move the functionality to another piece of equipment.


What if there is little to no tolerance for downtime?  My approach is simple, purchase the most industrial grade hardware you can, redundant power, networking, storage and controllers, then duplicate it.  VMWare ESX clustering is pretty cool if you make sure to be N+1 with your nodes.  Microsoft Clustering Services is also pretty interesting.  And contrary to VMWare’s insistence I have used both on the same servers.


If you think about it, even the clustering has different usages.  With VMWare, sure you can move the virtual server in the case of hardware failure, but what of OS failure? What about patching?  Sure you can take a snapshot, and yes VMWare reboots are fast.  But, if the patching doesn’t work well and you have to revert back a snapshot and reboot and what not, the server is down that entire time.  With MSCS clustering you can simply make sure all services are moved off of one node of the cluster, patch it, and then test it.  If everything went horribly wrong, you still have a fully functioning node and were only down for about 2 minutes while the cluster decided to not allow the service move.


So would I take Microsoft Clustering over ESX Clustering?  In a word?  No.  ESX clustering gives you so many advantages, allowing seamless upgrades to  hardware (Memory, CPU etc) by simply vMotioning the VM off of one host to another, and using VMWares tools to up the amount of RAM or number of CPUs.  


Would I rely solely upon VM Clustering and ditch MSCS (or other OS level clustering)?  No.  My most robust SQL installs used both.   MSCS clustering allowed me to patch an entire OS while the cluster was still up, then do a test by moving the service over to the patched node and see if it worked.  I would typically let it run a week to decide if I liked it, then I would patch the other node and we’d be 100% up to date.


So this is all great stuff, how is this different from Disaster Recovery?  Isn’t it a disaster to have an entire server blow up?  Yes and no.  The scope of Disaster is much broader.  A Disaster in this context is where the entire datacenter goes away.  Be it a Russian space station or a huge rock falling from the sky.  Or, more typically some sort of storm, hurricane, tornado or an earthquake.    In the case that a Hurricane comes through and floods your datacenter, or a tornado blows it off the face of the map all the high availability technology won’t do you any good.


Disaster Recovery, in essence, is having a complete datacenter in an area that is far away.  Now, I’ve read studies that state that you can have the datacenters as close as 10 miles away, but lets be safe and make it 100 or 200 miles.  You don’t need one in California and the other in Chicago.  Where I live, in North Carolina, it is okay to have a data center in Charlotte and one in Raleigh. Look at some of the most wide spread damage, Hurricanes.  Hugo hit in 1989, Raleigh was fine, Charlotte was in the line of fire.  In 1996 it was Fran and Raleigh was in the cross heirs, while Charlotte was a-okay.


DR doesn’t have to be an exact copy of production.  That would be very expensive, essentially doubling your hardware costs.  Yes, you do need to have the same amount of Storage, for example, but the speed of the storage doesn’t need to be as good as production.  Your servers also don’t need to be as powerful.  The idea is that you have something that will work while you rebuild your production environment.


Here is where they both are similar.  Neither HA or DR solutions are any good unless they are tested, and tested regularly.  I have a saying when it comes to backups.  Your backups are only as good as your last restore.  What that means is that sure you may have a backup from last night, but you don’t really know it will work.  It might, it probably will, but are you 100% sure?  No!  The only backup you KNOW works is the last one that you successfully restored.


The same is true for both High Availability and Disaster Recovery.   Unless you go and pull plugs you don’t know if your teamed NICS or redundant power supplies are any good.  Unless you fail-over to the other node in your cluster you won’t know if it will run the services.  I’ve seen this before, where one node went unused for a long period of time, and then an event occurred and the clustered service attempted to fail over, only to have a dependance not come online.  Oops.  iSCSI decided to drop the disk and bam, your HA cluster is now useless both nodes dead, and you are getting a frantic call in the middle of the night.


Same goes for DR.  Fire it up, test it out.  Make sure it is current with logins/passwords, patches and code for your applications and the most current data is there.   What good is a DR site if the data is months old or nobody can login?


When building HA and DR plans there are a few go-to questions I use.


1) how important is your data?
2) how much does an hour of total downtime cost?
3) What is the cost per hour by sub-system breakdown look like?
(many companies have many smaller applications, some may be mission critical
while some are nice to have, but the company can live without them a day or to
without losing any money)
4) Will we go out of business if we are 100% down for 14 days?  7 days?  2 days?
5) What sort of realistic budget do we have?


I tend to ask the value of data and time first because when you approach a business with the prospect of lost money, they get serious and they realize that you want them to succeed.  


Once you have the basic questions down, you can figure out what the realistic options are.  I once had a manager who wanted me to scope out the pie in the sky, soup to nuts.  So I did.  We duplicated 100% what we had in production, and then added all of the bits to failover and failback as well as the bandwidth needed to keep the DR site up in very near real time.


The cost was staggering.  I think it made the business people stop and think that they would rather risk being down, because there wasn’t enough money for that project.


And while it is really nice to have all the bells and whistles, it is MORE important to have DR.  Without it you are really one bad storm away from not having a company to work for... and worse yet, all of the people who work for the company will also be out of a job, and any customers that relied on your goods or services would also be harmed.


There are plenty of sites that talk about the details of both, and I would be happy to talk to you about them as well.  I am a DBA, so I can tell you from a data-centric point of view what all is needed, but the networking, storage and virtualization specifics are really black magic to me!

Labels: , , , , , ,

Tuesday, April 30, 2013

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.


Labels: , , ,

Monday, April 29, 2013

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!

Labels: , ,

Friday, April 26, 2013

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......

Labels: , , ,

Thursday, April 25, 2013

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.

Labels: , , ,

Wednesday, April 24, 2013

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.

Tuesday, October 16, 2012

Another error not as it seems

The Error: Msg 1934, Level 16, State 1, Line 1 ALTER TABLE failed because the following SET options have incorrect settings: 'ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

 I was attempting to do a simple change
 Alter Table TABLENAME  Alter Column COLUMNNAME BIGINT NOT NULL 

When I got the error above. I was floored, I set ANSI_Warnings every which way but loose. Then I read the error more closely. I dropped the non-clustered index on the computed columns and tried again. No dice. So I looked closer. and decided, well I don't need the computed columns. I dropped the computed columns and then I was able to alter the table. I'm guessing there is a bug in SQL Server 2008 R2 RTM and SP2 (I build a special environment with just this one table). And, yes, I do need a bigint here. This table ran out of ints in less than a year.

1

Labels: , , , , , ,