Katie avatar image
Katie asked

Differences between a production and a development DBA

Hello everybody,

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
2) What are the steps or the process that takes place when ever there is a issue identified in the production
3) How does the team determine the intensity of the identified issue?
4) What would be the common production issues that a production DBA encounters?
5) What would be the checklist or to do list for a production DBA to take care of his/ her servers health?

Thank you very much in advance,

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mark Allison avatar image
Mark Allison answered

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.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
All excellent additions. We need to take all these, edit them together, and we'll have a decent little article.
0 Likes 0 ·
Håkan Winther avatar image
Håkan Winther answered

I agree with Grant on every point with one exception.

"They may or may not get involved with performance tuning and architecture."

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"

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mark Allison avatar image Mark Allison commented ·
The reality of the job though is that the production DBA often gets called to deal with performance issues, and is sometimes even expected to wave his/her DBA magic wand to improve a critical performance problem. I do agree that the best performance gains are often had by designing in from the beginning. As I said in my earlier post, most dev teams don't have DBA experience within the team to draw upon, and quite often have to ask the production DBA for help.
2 Likes 2 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
I think what Hakan (sorry mate I forgot how to do that a again) is saying is that a developer DBA should consider performance at the design phase, to try and minimise the amount that a production DBA has to wave that magic wand in the first place.
1 Like 1 ·
Håkan Winther avatar image Håkan Winther commented ·
You are right Mark, most of the developers think that they can build their own database design and stored procedures, and they expect to get the best performance. They even defend them selfs with arguments like "i have made some performance test", The performance test they are refering to is the thousand records they inserted to perform unit test of the app. And that is not a performance test. It is soo sad! It would have been cheaper to hire a development DBA in the end.
0 Likes 0 ·
Håkan Winther avatar image Håkan Winther commented ·
You are excused Matt. :) And thanks for clearing out my point, I think a production DBA have enough things to think of besides performance.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.