asked
What is a DBA, and should I still work here?
Apologies for the non-technical question, but this goes to career and ethics. I am looking for best practices and how the rest of the world does this. I am a DBA by necessity. I am not certified (yet), but I have gotten pretty good at what I do. We currently have grown to about 28 db's company-wide and I maintain them all. Backups, capacity planning, performance tuning, indexes, the works. [SSC has been invaluable, BTW] We are running a large ERP on SQL (4rd in world popularity I would guess) and having come from the server/network side of the house, it was a natural fit for me to become DBA, especially since no one else stepped up to the plate, and no new hires were going to happen. My dilemma comes from management and our DEVs. Everyone thinks that they should have full access to everything. They claim it is 'annoying' to be hampered by restrictions. We have 4 DEVs that customize our ERP system, and we are also upgrading to the latest version of this particular ERP, moving all our old customizations into the new version, so our DEVs are pretty busy. My problem is that mgmt does not really know much about databases and their stability, security or what goes on in a day in the life of a DB. As such, he wants all of the DEVs to have unrestricted access to all DBs, even our production DB. I have warned him about this countless times and even caught mistakes that the DEVs were making, thereby saving us from disaster. I have one DEV that has unlimited access to a non-production conversion DB for our upgrade. While I was on vacation recently, he had that DEV give unlimited access to the other DEVs on some of the DBs under his control. I am being circumvented. And mgmt seems to have no regard for reason or survivability. My fear is that something bad is going to happen, and that I am not going to be able to save us this time, and I will get the blame. Does anyone have a set of best practice guidelines on this topic? Any advice for how I should handle this? TIA for your replies and advice.
Should we meet, remind me to tell you stories about the business that gave everyone, including the receptionist, sa privs because "Databases are easy." I lasted 9 months in that job.
Good article to provide to your manager:
http://www.bloomberg.com/news/2011-06-27/human-errors-fuel-hacking-as-test-shows-nothing-prevents-idiocy.html
I just have to add my 2 cents. We all fight this battle on a daily basis as you can tell from the responses thus far. I have certain systems where application support and developers have access. I have yelled, pleaded, and begged for this to change and to let me reduce the level of access yet it hasn't been done. Since I can't stop them from dorking up my production database I can at least monitor what they do. Since I work in the financial sector we need to keep up with certain things and show audits for releasing changes into production (even though developers and admins don't follow proper change control sometimes). With this requirement I was able to purchase a fairly inexpensive piece of software called [Source Control by Red Gate](
http://www.redgate.com). You have probably seen their adds on this site. What this has enabled me to do is check in the database into Source Control and I get to notice when certain changes are made to the database that I didn't make. This has been fun! Its like a big brother watching over them. It has served me well. One of the best ways is when the person does something bad and I can simply revert back to the state before the change (in most cases). What is even better is then I get to ask where the change control ticket is for the change they made to production. Does this process make me lots of friends, not so much, does it cover my butt, absolutely. Management is slowly taking note of this as well. As for becoming an accidental DBA, WELCOME to our world. Take a loot at [PASS](
http://www.sqlpass.org) and see if there is a local user group near you or join one of the many virtual ones. Lots of free training to be gotten there. Also check out [SQL Saturday](
http://www.sqlsaturday.com) and see if there are any events happening near you. These are free for attendees (may be a small lunch fee) and you get to pick and chose which sessions you want to attend. Very cool events. You mentioned best practices. One of the best resources for best practices has been put together by [Brad McGehee](
http://www.bradmcgehee.com) His blog is pack full of books and resources.
@Andomar, I am all for developers having full access on the development servers. However playing trial and error with code on my production server isn't something I am a fan of. I would much rather my developer modify a SPROC in development so that I can see it generates a new execution plan and requires a new covering index so that the index can be created prior to the modification of the SPROC. That way my external customers don't take a performance hit. Quite honestly, I shouldn't be creating indexes or anything else like that in a production environment without testing in development as well. It has taken us years to get to this point and we still have a long way to go. Our most mission critical systems have been locked down first, middle tier is next. This has made our systems much more stable. Before any new change can be put in a committee has to review and approve the change. This has prevented a lot of oops from happening both for developers, dba's, and application support folks.
Welcome to the wonderful world of being a DBA. It sounds like you got a pretty good idea of what you should be doing, and it sounds like you are doing a good job of putting it into practice. Don't worry about not being certified, I know plenty of DBAs who aren't who I would go to in a flash, I also know plenty certified people I wouldn't trust...... As for the age old battle between devs and DBAs over permissions, I think you are doing the best you can, especially given the circumstances where management either don't care or don't want to listen. The best thing you can keep doing is informing upwards of the issues and worst-practices that you have. It will be very difficult to pin the blame on you if you have evidence that you told the manager 6 months ago that continuing to go down a particular path would result in pain. Be ready for that day, and meticulously keep your backup and restore strategy alive - you'll be the hero who saves the day, and the devs won't be allowed to touch the systems again (at least that's how it 's supposed to pan out). Keep documenting those issues, and keep fixing them. It may seem painful to do it this way, but if no-one will listen, then you'll have to show them *after* the event. It's not about being able to say 'I told you so', more about keeping professional and having a plan, and being able to demonstrate that you identified the risks earlier, and that you were *prevented* from doing anything about it. If nothing else it is giving you a heap of good experience, good experience you can demonstrate on your resume, and move on to better prospects when you are ready. Is it not possible to keep the devs limited to a dev environment?
DBA = Default Blame Acceptor But I agree. Documentation is your best friend. First document the best practice, distribute it, then document the horror that occurred because of the violation.
@Andomar I'm sure it does in stricter environments like financial or defence, probably not so much in others
We feel your pain to be sure. Many of the respondents here have been though the same "career advancement" and have fought the battles. You want to do your best but between developers and management that is hard to do. @Kev Riley has given good advice. About all I can offer is to start cranking up the auditing a bit and log stuff for CYA purposes. Also get management to state what your compliance requirements are for regulatory bodies. This may spark them to see the wisdom of your intended actions. Make sure you get sign-off for any elevated access that has to be granted in PROD. It's a pain, but since the lawyers took over...
Thanks for these responses, they are great. It feels good to know that I am not all alone in this quandary. To answer a few of your questions, the DEVs have their own dev environment(s) but they still feel the need to have unfettered rights on -all- servers... and mgmt haphazardly agrees. Auditing is now turned on - this is my attempt to pin a cataclysmic event back to a process or person, crime scene reconstruction, if you will. We are under no compliance mandates, there are no regulatory restrictions on us. Its the wild wild west in here. Sign-off, that's a breeze. they are all for it. What I was hoping to hear in response was some form of guidelines or hierarchy that top (professional) companies use as best practice in this scenario. Can anyone comment to that? Thanks again. This is an outstanding community of experts and I am proud to be in your midst.
Quick answers are: - ITIL processes (changes are documented, tested and approved) - the principle of assigning the least privileges necessary (unless a user has a business need to the data then they don't need the data).
It does seem as though you are walking a path familiar to many of us (One day I will do a study on whether DBAs comprise some of the slowest runners in the world - the number of careers that have been started because you didnt get away from the SQL Server fast enough is amazing!). Having read a couple of the answers here and seen your responses then I think my tactic would be to identify what certifications your competitors have or what they DONT have and then try to highlight to management how it would be useful for your Co. to get them first or as well. I am guessing you are in the US - here in the UK we can get a Co. certified to [ISO 27001][1] which means that IT best practise is followed to a given level throughout the business. There are increasingly cases where you have to be certified in order to bid for some contracts. Obviously this might not apply to you but if there is something like this that intrinsically has a value to your Co then management will be on your side because of its commercial benefits. There is also the advantage that the ISO assessors (or indeed there are contractors who help companies attain the certificate) can be the people who 'enforce' the rules. Taking the heat of the relationship between you and the Devs. In a good cop / bad cop sort of scenario the certificate enforces the rules and you do your best to help the Devs with the requests they have and solving their problems. Instead of being the bad guy you are their friend - enabling them to do their work and taking the chores away from them on the prod server(s). You may have to up your work rate in that respect in order to bank some 'Brownie points' in the early days but once they see the logic behind it then they will be the odd ones out if they still rebel against it. Good luck and keep coming back here for tech or non-tech questions. We are SQL/DBA centred but not every question has to be about obscure trace flags and such :) [1]:
http://en.wikipedia.org/wiki/ISO/IEC_27001
I think that was me. I somehow gave you two or three up votes & tried to fix it. Sorry, sorry.
Anyone any ideas why this got a down vote? I didn't think it was that bad a suggestion... :(
@Grant Fritchey !!! No worries, just wondered what had I said that offended someone! Happy to have a down vote so long as it gets an explanation :) I have had similar problems when voting via my phone - just out of interest, what browser were you using?
I would add go to Google News and search "data breach". Take that search link and send it to your manager. Ask them if they have heard of a hacking group called Anonymous or LulzSec. Alot of the hacks recently done by Anonymous were done strictly with user accounts that had escalated privileges, privileges they should not have had to begin with. As far as your question about staying with the company, I would suggest staying. You will run into this issue at most companies in some capacity, as you can see from everyones post. If you eventually do get tighter security implemented in your environment that will be one good war story to tell the next company you interview with.
You're going through what all of us go through on a regular basis. I think everyone has given you pretty good answers already. I'll add a couple of notes. First, if the status quo is not causing problems, leave it be. You need to pick your fights. But if anyone causes problems, that should be all the business needs to let you start lcking stuff down. As my previous boss put it, pick the hill you want to die on. As for documentation and best practices on development of this type, there are very few resources. One is the [Team-based Development][1] book from Red Gate. I wrote three chapters for the book. It's pretty good, but I don't think it'll answer all your issues, but it'll answer some of them. [1]:
http://www.simple-talk.com/books/sql-books/the-red-gate-guide-to-sql-server-team-based-development/
In my experience, at least a third of all people in management, got there because... well they like to manipulate people. They like to be in control and they don't respond well to underlings who show that they are brighter than them. When this happens you will find yourself in a situation where management decisions are made based on personality, not facts. So you can be a good DBA and get nowhere if you happen to rub the manager up the wrong way. Nobody likes to be made to look like an idiot - especially idiots. If that idiot happens to be your boss and your boss gets on handsomely with the Dev who is messing with your prod system, you aint gonna win by ranting. Document. Educate. But above all be polite. And sometimes you have to accept that your integrity will lead you to new employers. As I say, in my experience one in three of your managers are probably there for the wrong reasons, but that means two in three will be at least bearable. Best of luck.
Welcome to the world of DBA. I would suggest a couple of books to get you started. First up is the "SQL Rockstar" Thomas LaRock's book "DBA Survivor". Speed-read this immediately, and then go through it a bit slower. Next up is a ransacking of the shelves of Red Gate's bookstore (
http://www.red-gate.com/our-company/about/book-store/) - bookmark that link, it's a pain to find. They have loads of eBooks. Check out Kuznetsov's "Defensive Database Programming", and @Phil Factor, @Grant Fritchey and co's Team-based Development, as Grant mentions above. Get involved in your local user groups, be thay PASS, SQL Saturdays, or the UK-based groups, or... And keep an eye out for training events and conferences. SQLBits, for example, does a three day conference, the final day of which is free to attend and contains high-quality material presented by some of the big names in the business. Check out the blogs too - there are lots of SQL Server-related blogs out there that cover pretty much every aspect of the trade. Find a couple, and generally, each one will have a list of blogs they read regularly. (That reminds me - must update mine...) And now, a tip for managing your boss: Don't go to him saying "this is wrong". Go saying "this is wrong, this is why it's wrong, this is what it'll cost you if it goes wrong, this is what we do to fix it, this is how long it'll take / how much it'll cost." They're much more likely to take notice if there are cold, hard numbers. Like "your business could lose a million dollars". What else? Where are you? Find out what regulatory bodies apply, if you're subject to SOX, for example. If so, that can be used to help you achieve the system security you need, through a lovely little thing called "Segregation of Duties". That's all I can think of for the moment. But please do stick around here - you've made a good impression on us already (look at the number of times your question has been up-voted!), and do join in the discussions.
Development is very much an iterative effort. You try something, and if it doesn't work, you try something else. The key to quality is to iterate quickly. Not only developers work that way: DBA's work like that too. To take a simple example, no DBA understands the impact of adding an index. Even for the experts, tuning a database requires multiple iterations. Now if you, as a DBA, keep the privilege of iteration to yourself, the developers literally can't develop. This means they'll put pressure on management for more access. Management will try to strike a balance, and developers will only get some of the rights they want. Personal relations are stressful, progress is slow, quality is low. This is the place most businesses are in, so it's no wonder many companies are considering outsourcing! My advise would be to assist iterative development in any way you can: - Give developers any rights they ask for - Make frequent backups, provide a server where backups can be restored - See if you can introduce source control for database changes - Turn on auditing (people are way more careful when they're being logged) - Keep developers sharp by adding monitoring for recurring problems
@Andomar, the points you make are very valid and are the exact reason that there are different versions of key systems - Dev, Test, QA and production. A developer, on their own server can do what ever they want, try, test, break, restore, try again ... They are SA. In Test they have lesser privileges, they can still make changes but its more serious as their change could affect the work of others if its a breaking change. When you get to QA then they should have very limited security access, changes should have been tested once to make sure they dont kill anything and its user testing that is being done in an 'almost live' scenario. Once something passes UA testing then it gets scripted into production by the person responsible - the DBA - and changes going in to production should be known to be safe, tested and accepted. A DBA would make changes to a Test or Dev database and then test to make sure what effect that will have - load testing etc so that there is lessened risk of a system going offline because of a change. Dont confuse a DBA stopping open access to Prod with a DBA trying to stop fast develoment. They are very different.
If a shop is truly embracing segregation of duties where developers develop on non-production equipment and QA truly QA's the environment then they should be building those environments appropriately. My development environment may not be an exact replica of production, I am not building a 24 core A/A cluster with 256 GB of ram, but they are on a 16 core with 128 GB of ram. I don't even like making changes to indexes on production without testing first. In my world of working with Financial data, I can not allow testing on my production server. The idea of being able to restore the database back quickly in the event a developer did something awful to my database is cause for closing the doors to the bank. Can you imagine a developer changing the datatype of one of my columns and the length of the account number field being truncated? Oh just restore from backup a 300 GB backup. Meanwhile the bank websites are down. There is this thing we try to adhere to called the [SDLC - Software Development Life Cycle](
http://en.wikipedia.org/wiki/Software_development_process) The steps are 1) Planning, 2) Implementation, testing, and documenting, 3) Deployment and Maintenance.
@Fatherjack: Well I beg to disagree; the effect of stopping open access to production is to stop fast development. Most test environments are little more than restored databases on old hardware. You can test internal issues, but the difficult problems are in the interaction with other systems. The key to quality is to iterate quickly in production.
I don't really think outsourcing is on the rise, at least not in the US. The number of job postings I have seen lately tend to point toward the opposite. Companies are getting tired of having to deal with outsourcing. They may get consulting help every so often. For the most part they are either tagging someone internally to start supporting the database side of their business or hiring a full-time person.
These Devs have no need to have SSMS access on Production - they can browse the db thru the application and see all the tables and views. The main thing my lockdowns prevent is them creating SPs with 5-6 level nested selects with an execution plan that looks like the rap sheet of a heroin dealer. They can even manipulate the db thru the app (which is the mfg's preferred method anyway). I have no control over how SQL unfriendly their app code is, tho. They just pi$$ and moan b/c they have to come thru me as QC, and I reject their crappy SQL much of the time. Quality = less problems later. If it runs slow when they write it, it will be like death with 200 users on it. If its worth doing, its worth doing right.
1 Person is following this question.
