I am a novice to the field of SQL Server Database Administration, and I am very interested to know how is the life of a production DBA different from a development DBA.
These are the series of questions that pop up in my mind whenever I think of production environment of an organization. I have read quite a number of books, but didnt find much clearer emphasis on this classification of a production and a development DBA. I would really appreaciate if these questions are answered, as I feel this is the right place to get an correct input and clearer understanding of the concept from this forum, as so many learned and experienced professionals are part of this forum.
1) What are a production DBA responsibilites
Thank you very much in advance,
There is no hard and fast definition that a development DBA does one thing and the Production DBA does another. What I lay out here may not match what goes on in another person's shop.
1) In general terms, the production DBA is responsible for maintenance and up-time of the production systems. They spend more time worrying about backups, index defrags, security, monitoring and metrics. They may or may not get involved with performance tuning, design or architecture.
Development DBA's on the other hand are usually responsible for building the databases, design, deployment, writing stored procedures. They may or may not get involved with performance tuning and architecture.
2) We have multiple monitoring packages running against the servers. Some are home grown, some are purchased. These tools gather metrics and raise alerts. The metrics are used for long term management, the alerts are for identifying when there are immediate issues.
3) Usually anything that means that the business can't do it's job right now, or shortly will be unable to do their job is an emergency. Anything else gets prioritized.
4) Too numerous to list, but a few that happen frequently, slow performance, backup failure, drives are full, connection errors, scheduled jobs failed...
5) Buy my book
To read a bit more about the classification between a production dba and a development dba, I'd suggest Craig Mullins book, Database Administration. It's a great resource.
answered Dec 09 '09 at 05:48 PM
Grant Fritchey ♦♦
You may want to consider breaking this up into multiple posts since you have so many questions, but to address a couple of them in broad terms.
1: Grant covered the general difference between the two pretty well, but I will reemphasize the fact that this is an organization by organization question and so you are never likely to get a clear cut answer. To rephrase what he said in a way that might be helpful, generally a production DBA handles administration and maintenance while a development DBA handles creating new things. Also, as a general rule a production DBA will work closely with system administrators and even end users while a development DBA will generally work closely with programmers and designers, generally.
2: This depends on what the issue is, but one general thing to have handy is a recent backup, and if you need to make any substantial changes to fix an issue I would generally double check those backups and perhaps make a new one, depending on the situation, before making any significant changes.
5: The checklist should be tailored to the situation and the environment, but I think there are a couple that are close to universal:
Ensure that backups are taken sufficiently often, that those backups are validated on a regular basis, and that the backup files themselves are backed up somewhere else, preferably off-site.
Keep an eye out for long running queries that are blocking other processes. The definition of long running depends on the environment though.
Ensure that indexes are not overly fragmented.
Review security regularly to ensure that it is appropriate for the situation and that there has not been incidents of "privilege creep." or people left in groups they should have been removed from.
answered Dec 09 '09 at 07:15 PM
Grant and Timothy covered it pretty well, but I thought I'd chip in because I have a lot of experience in both roles over the last 14 years. I like to alternate my consultant jobs with production DBA work and development DBA work just because its more interesting and I like to understand both sides of the fence.
1) Covered well by Grant but I'll add some more. Production DBAs may also get involved with defining standards across an organisation which could include the development and naming standards across databases. This is never an easy thing to do esp. if you are responsible for third-party databases, but can make development and release management easier. Production DBAs will also do some development work generally scripting which will include writing VBScript, Powershell, Perl or whatever to do things like Standard SQL Server builds, monitoring, inventory gathering. They may also build SSRS reports to report to management on the state of the environment. They will also get involved in buying decisions for third-party packages from companies like Red-Gate, Quest, Microsoft, Idera, etc. They should get involved with dev teams and guide them on design and architecture of new systems at the database level, as most dev teams do not have the luxury of a development DBA and some are weak on set-based programming and database design skills.
2) This is usually controlled by other departments such as Change Management, Release Management, Incident Management, IT Helpdesk, Application Support, IT Operations. The production DBAs will take on an incident usually from a queue and try and fix issues as they arise. The incident has usually already been looked at by another party first if the issue was identified outside of the DBA team and the DBAs are called upon for their expertise. But as Grant and Timothy have said, it depends on the problem.
3) What Grant said. Sometimes an incident or problem manager will get involved to prioritise. Depends on the size of the organisation.
4) To add to Grant: bad code releases from developers, DBAs called in to fix or troubleshoot performance, disaster recovery in the event of a site outage, or network issue.
5) A production DBAs primary responsibility is to guarantee the availability, security and consistency of data for the company's applications. So whatever you can do to keep this high should be your goal. Things like building High Availability solutions such as clustering, database mirroring or log shipping, etc. Good monitoring is critical to know when something unusual has happened. Reporting and auditing are also critical to understand whether there has been unauthorised access to data which could compromise the availability or security of the data. Ensuring backups work on a regular basis is very important. Disaster recovery tests are also very important to do but are usually initiated by a Business Continuity team, however DBAs will always be heavily involved in this.
answered Dec 10 '09 at 06:36 AM
I agree with Grant on every point with one exception.
From my point of view, performance tuning is a developer DBA responsibility. If you don't design and build your database with performance in mind, you will end up with a database that doesn't perform as expected and no production DBA in the world can tune the db without major changes in the datamodel and all the procedure code.
One of the production dba responsibility is to monitor the performance to find the cause of the performance loss. (hopefully he will have a performance baseline to compare with).
And yes i think the production DBA also should be involved in performance tuning, like index tuning, statistic updates, hardware configurations etc.
"Not planning for performance is planning for disaster"
answered Dec 10 '09 at 10:01 AM