October 1, 2013 at 7:51 am
Jeff Moden (10/1/2013)
Kurt W. Zimmerman (10/1/2013)
kevaburg (10/1/2013)
Kurt W. Zimmerman (10/1/2013)
I think the resolution is more a function of control. As a DBA restrict access to those developers who don't at by the rules. Make them come to the DBA for any DBA work such as backups/restores. The justification is simply a matter of controlling a situation that is out of control and has the possibity of greater problems. It is a matter of taking ownership of the environment.Once I started my new position I've taken over ALL DBA roles. I'll get handed off a machine from Operations and once I have itit is my responsibility. Operations and developers come to me if something needs to be done once I'm in control of the box.
Just saying
You have got it exactly right! And that unfortunately is also the reason why some (not all) developers think of DBAs as roadblocks to production!
Small price to pay by being a good DBA... 😉
+1000 to that.
I'll also add that good DBAs aren't actually roadblocks. They're enablers with "best practices" in mind to not only protect the data, but to protect the developers, as well. Good DBAs usually know how to get the {database} job done much faster, more accurately, more repeatably, and in a much safer fashion.
For example, blowing up a C: drive on a machine is more of a roadblock than any DBA would ever be. That would be a very bad day for the happless developer that did it. If there's something like a backup that the developers needed to do at the drop of a hat, I'd build a proc or job or combination of the two that would allow the developers to do the backup but in a tested, controlled, and properly configured fashion. In other words, I'd enable them to do it easily and correctly instead of wasting my time trying to figure out how to prevent making backups to the C drive. Then, I'd teach them how to use it.
Developers and DBAs need to understand that they're on the same team. The DBA needs to help the devs to their jobs and protect them in the process. The only way that works is if everyone knows that and works together to achieve it. And management buy in is essential.
Amen!
October 1, 2013 at 7:59 am
Jeff Moden (10/1/2013)
Kurt W. Zimmerman (10/1/2013)
kevaburg (10/1/2013)
Kurt W. Zimmerman (10/1/2013)
I think the resolution is more a function of control. As a DBA restrict access to those developers who don't at by the rules. Make them come to the DBA for any DBA work such as backups/restores. The justification is simply a matter of controlling a situation that is out of control and has the possibity of greater problems. It is a matter of taking ownership of the environment.Once I started my new position I've taken over ALL DBA roles. I'll get handed off a machine from Operations and once I have itit is my responsibility. Operations and developers come to me if something needs to be done once I'm in control of the box.
Just saying
You have got it exactly right! And that unfortunately is also the reason why some (not all) developers think of DBAs as roadblocks to production!
Small price to pay by being a good DBA... 😉
+1000 to that.
I'll also add that good DBAs aren't actually roadblocks. They're enablers with "best practices" in mind to not only protect the data, but to protect the developers, as well. Good DBAs usually know how to get the {database} job done much faster, more accurately, more repeatably, and in a much safer fashion.
For example, blowing up a C: drive on a machine is more of a roadblock than any DBA would ever be. That would be a very bad day for the happless developer that did it. If there's something like a backup that the developers needed to do at the drop of a hat, I'd build a proc or job or combination of the two that would allow the developers to do the backup but in a tested, controlled, and properly configured fashion. In other words, I'd enable them to do it easily and correctly instead of wasting my time trying to figure out how to prevent making backups to the C drive. Then, I'd teach them how to use it.
Developers and DBAs need to understand that they're on the same team. The DBA needs to help the devs to their jobs and protect them in the process. The only way that works is if everyone knows that and works together to achieve it. And management buy in is essential.
I agree Jeff! I think it is a function of the amount experience the developer has vs. their acceptance of the role of a DBA. I've seen this time and time again. One case in mind, a so called hotshot developer who had a few years under his belt was probably my biggest headache. I've never had issues/problems with junior developers as well as the well-rounded seasoned developers. The junior developers just didn't know. The senior developers really understood their place as well as the place of a DBA.
My approach is to give just enough rope for those "sophomoric" developers to hang themselves. For the most part they will learn a lesson. I've found I'll get a much faster response from that developer than trying to preach to them.
I am fortunate that I have senior developers as a part of the IT group at my job. It is an environment that has far less levels of stress only because everyone knows their limits of responsibility.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
October 1, 2013 at 3:24 pm
crmitchell (10/1/2013)
assuming your c: drive is only used for the OS could you use disk quotas in windows to limit the amount of disk space visible such that attempting to take a backup will fail.
That is actually a very interesting suggestion, nice piece of lateral thinking.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
October 1, 2013 at 3:27 pm
Jeff Moden (9/29/2013)
andrew gothard (9/27/2013)
I'm a DBA.I'm not paid to solve problems. I'm paid to prevent them.
I absolutely love that. Well said.
Cheers mate. I think I'm going to make that my sig. Sure it's not going to get the traction of RBAR, was just a throwaway, but on reflection I quite like it too - cheers 🙂
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
October 1, 2013 at 3:44 pm
andrew gothard (10/1/2013)
crmitchell (10/1/2013)
assuming your c: drive is only used for the OS could you use disk quotas in windows to limit the amount of disk space visible such that attempting to take a backup will fail.That is actually a very interesting suggestion, nice piece of lateral thinking.
I like that idea too for the C:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 1, 2013 at 3:44 pm
kevaburg (9/29/2013)
Jeffrey Williams 3188 (9/29/2013)
In addition to which drive backups are performed - I really need to be able to block someone from taking native backups on some of my systems.I have and use Litespeed on all of my larger systems, and because I use Litespeed my backups are compressed allowing me to use less space for my backup drive.
I have had multiple instances where some vendor decided to do a native backup to the data drive, the log drive - even to the tempdb drive in a couple cases (because it is a temp drive...right).
Best case is they couldn't even run the backup because there wasn't enough space available. Worse case - there was just enough space available and they filled it with a non-compressed, native backup. I only get notified after the fact when the drive is full then have to figure out whether or not I can delete that backup safely.
I cannot prevent this because the vendors always have a sysadmin account. Tried to restrict that one time and found out that they also setup their application to use this sysadmin account also - even though they stated it was only for support to use when needed.
As long as they have sysadmin access - nothing I have been able to find to prevent them from doing whatever they want.
We won't even consider vendors that say they "need" sysadmin. They have programmed in a lazy fashion, don't know exactly what right and permissions their users need to perform its function and have increased the attack surface area on the server. SYSADMIN is for these vendors a get-out clause....
I used to work in a place like that. The IT Manager used to work in a Nuclear Powerplant - he kind of had a zero tolerance approach to berks turning up onsite saying "Our app needs SQL Server sysadmin and network manager rights/Server admin rights" - I kid you not. "Oh, sorry about your wasted journey. I'll tell X the demo's been cancelled and my - DBA/Network Admin/Guy at the back laughing his head off - will see you out".
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
October 1, 2013 at 3:48 pm
I wonder how well that trigger in the msdb database would work.
Some have really hit hard on a crucial piece to this problem - controls and audit.
I agree that if somebody is not abiding by the rules, then enforcing a policy for them to have the DBA do it is an appropriate step. They need to learn not to be a roadblock to themselves.
On the audit side, you could set up an audit that would alert and email you should a backup start to some other location than the specified location (the location you have approved). Then talk to the person that kicked it off. If they do it again, you have a paper trail and start running that up the ladder of management until it is fixed.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 1, 2013 at 4:26 pm
Disk quotas can work! I like this idea.
I also read about File Screening, that can limit the file types that can be saved on disk. For example we can specify .BAK files to be restricted. There are a few articles on that on Windows 2003 and Windows 2008 server, but I cannot find this MMC snap-in on the server, probably need to install
http://technet.microsoft.com/en-us/library/cc732431.aspx
File Server Resource Manager
It contains File Screening Management node. I wonder if somebody used that.
I understand that limiting the file type will not prevent all issues, but can limit them.
Yelena
Regards,Yelena Varsha
October 1, 2013 at 4:30 pm
Yelena Varshal (9/30/2013)
Anyone tried to create a trigger on a system table?Could we do Instead of (or other type of) trigger on [msdb].[dbo].[backupmediafamily] ? The backup location is inserted there into the field [physical_device_name] If the path in this file name contains C:\, then check the backup size, check available drive space on the correct backup drive and move the backup file to there. And send appropriate emails.
Microsoft does not recommend creating triggers on system tables with this cryptic note in Create Trigger statement article, but they don't say we cannot do that.
http://technet.microsoft.com/en-us/library/ms189799.aspx
"Because SQL Server does not support user-defined triggers on system tables, we recommend that you do not create user-defined triggers on system tables."
Have not tried that myself.
Oh - a very nice suggestion, but, rather than a trigger (Disclaimer: I detest triggers pretty much in all situations - certainly as you point out on system tables, that would certainly break most 3rd party support agreements as well as Microsoft's I'd imagine). That rather nice idea should work as a 10 minute monitor in Powershell though. Look for that on the appropriate servers (some only have a C:\ drive - so another option would be required there) and if someone's backing up to C:\, kill the SPID and e-mail me. Not my original train of thought, but a nice line of inquiry. Thanks Yelena - good food for thought there!
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
October 1, 2013 at 4:43 pm
Kurt W. Zimmerman (10/1/2013)
I think the resolution is more a function of control. As a DBA restrict access to those developers who don't play by the rules. Make them come to the DBA for any DBA work such as backups/restores. The justification is simply a matter of controlling a situation that is out of control and has the possibility of greater problems. It is a matter of taking ownership of the environment.Once I started my new position I've taken over ALL DBA roles. I'll get handed off a machine from Operations and once I have it's is my responsibility. Operations and developers come to me if something needs to be done once I'm in control of the box.
Just saying
To be honest, the DEV's are all on board, and don't have rights to do that in live now anyway - they ask me. Same with Networks now (they have admin access in case I die). Third party garbage or clueless "Our Database Expert" stuff is the problem - as an example - from one clueless individual from a vendor <Quote> Our system isn't programmed to work with full backup, only simple </Quote> my manager had to put the phone on mute when he saw the look on my face after that. He was a "SQL Server expert".
I'm assimilating stuff bit by bit. Resistance is futile, and all that. I'd not have taken the job if I didn't have the buy-in to exert appropriate control. But a SQL Server estate going back to v7 with no DBA until me, just recently, needs a bit of BLC. (Brutal Loving Care) until it's rationalised
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
October 1, 2013 at 4:51 pm
kevaburg (10/1/2013)
Kurt W. Zimmerman (10/1/2013)
I think the resolution is more a function of control. As a DBA restrict access to those developers who don't at by the rules. Make them come to the DBA for any DBA work such as backups/restores. The justification is simply a matter of controlling a situation that is out of control and has the possibity of greater problems. It is a matter of taking ownership of the environment.Once I started my new position I've taken over ALL DBA roles. I'll get handed off a machine from Operations and once I have itit is my responsibility. Operations and developers come to me if something needs to be done once I'm in control of the box.
Just saying
You have got it exactly right! And that unfortunately is also the reason why some (not all) developers think of DBAs as roadblocks to production!
I come from a development background myself. In all honesty, I probably 55-45 prefer the Dev to Prod DBA role - but the critical thing ATM is Server prodding to get the estate into reasonable shape. If I do my job well - from scores of randomly configured, barely monitored servers and licencing confusion, I reckon within 3-5 years the Making Progress, rather than just babysitting side should be less than full time for someone with my background
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
Viewing 11 posts - 31 through 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply