| Author |
Topic |
|
CanadaDBA
583 Posts |
 Posted - 2005-10-31 : 12:48:00
|
Sometimes we need to run some data update scripts and I am notified that the developers ran a script on the production.  The question is that should I ristrict them to pass the script to me? I have also noticed that almost all of the developers have a connection to production on their EM. What is your sugesstion? Any Advise or experience? Canada DBA |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2005-10-31 : 12:53:36
|
| I always advise the developers to try out their scripts on a test box, before going to production. If they don't, and something bad happens, well, they can go to the business owner, and explain why a day's worth of data has just evaporated. Developers running data only scripts is fine by me. They just can not modify tables/views/procedures. If your developers are smart, they will notice that the users ask for a certain type of update more often than others, and that update statement should make it into some web page, or screen on the application, so the users will quit bugging them. |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-10-31 : 12:58:14
|
| If you are responsible for the data, then you need to protect it. All changes to production should go through the DBA. This means you need to do a little work though.I would suggest that (at minimum) you have a DEV, a TEST (or QA) and PROD instances in your landscape to perform adequate testing. Where I work, we let the developers run wild in the DEV environment. We have them create update scrips, or we help them create update scripts that we then run against a TEST intance. The TEST instance is a refresh of production from the previous nights backup. We run the scripts in TEST until we have zero problems. Then I (the DBA) run the scrips in production. Also, we have an internal change request system that we announce our changes and the change is documented. Nothing too fancy, and internal wiki or blog environment could suffice for something like this. This also allows us to search for things, which is especially useful if there are issues down the road and someone asks "what changed on sept 17th?"-ec |
|
|
|
sachinsamuel
Constraint Violating Yak Guru
383 Posts |
Posted - 2005-10-31 : 13:17:19
|
| Added to the above nice suggestion, you can also give them a read only access to the Dev, so that they can check the value in the database.Rest everything should be taken care by the DBA. If the ball is handled by multiple people, then chances are there that it going to be droped.RegardsSachin |
|
|
|
CanadaDBA
583 Posts |
Posted - 2005-10-31 : 13:40:17
|
| I have a Dev and a Test server and I have forced the Dev people to run their scripts and tests against these servers. The Dev server has fresh data from last night backup. The data update scripts and approvals are documented. But the developers know the user IDs with SysAdmin role because it is hard coded in the application. I am doing some ristriction to prevent using these user IDs. I am a little bit worry about the connections to production in their EM.Canada DBA |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-10-31 : 14:13:42
|
| Document the connections and escalate. You need to take care of this. You also need to get rid of using SQL Server logins in your apps. If you can't get rid of them, at least get rid of accounts with SysAdmin rights at the app level. You're just asking for all of your data to disappear into a dust cloud as some teenager glories in your companies demise from his hacking abilities.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-31 : 14:23:36
|
| I think that DBAs naturally worry about the recoverabilty of scripts being run "in an emergency" on Production.You either start the statements with BEGIN TRANS and then manually type & run a COMMIT or ROLLBACK at the end, or you do aSELECT * INTO TempTable FROM LiveTableso you can unpick the mess - if it goes T.U.But developers (IME) don't think like that so they do "UPDATE MyTable SET ColA=0" .. and then"Doh! Should have been a WHERE clause on that ..."Not all DBAs fit into CategoryA, and not all Developers into CategoryB ... so YMMV! but I wouldn't in general allow Developers to run scripts on Production. In general it doesn't happen here, but on the occasions it does our developers to to ask me to look over their script to check for an Snafus before they run it - which is good enough, because I then extol the virtues of BEGIN TRANS or SELECT * INTO to them ...Kristen |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2005-10-31 : 15:42:32
|
| We're going through some dev/prod battles right now within our environments. My opinion as a developer is if it's inserting just 1 or 2 rows, it's harmless. You don't need to know or care that we're doing it. If it's a large script that is updating,removing data in our larger and more important tables, then absolutely the developer should test it on one server and hand it off to you to actually implement. Keep in mind though, when you do this, you are responsible for the code working. Don't be one of those DBA's who say "well development handed it to me it's their fault".Mike"oh, that monkey is going to pay" |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-10-31 : 16:02:27
|
| I was specifically talking about DDL with regards to DEV/TEST/PROD landscapes. DML is another issue altogether. Following Kristen's advice with using transactions when making changes like this is certainly a best practice.Having a proper TEST environment helps avoid these problems. If setup properly, things that run correctly in TEST will always run correctly in PROD. It really makes life easier.-ec |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-10-31 : 16:53:29
|
quote:
My opinion as a developer is if it's inserting just 1 or 2 rows, it's harmless.
That's all it takes in they financial industry to make your shares drop by 80%.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-01 : 01:35:43
|
"My opinion as a developer is if it's inserting just 1 or 2 rows, it's harmless"We'd only ever use that approach if we were inserting some rows from another database - e.g. test - where they had already been tested.Why wouldn't you have a Maintenance Screen for that table that allowed "ad hoc" edits? That way the data you entered would go through whatever data validation / sanitization process.For example, we have plenty of stuff that is reliant on UPDATE DATEs on the rows.We used to allow UPDATE direct to the table for "ad hoc stuff", and then people always forgot to set the UPDATE DATE column to GetDate() - we removed the triggers from those tables because they slowed the process, BUT the SProcs that are supposed to do the UPDATE definitely have the GetDate() stuff in there  Buck the system and you'd better know what you are doing for sure!Somebody said in another thread along the lines of "Developers who make updates on live systems get to explain what they did to management" ... I like that!Kristen |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-11-01 : 11:07:35
|
It's kind of like playing Lemmings really.  MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
DBA role