October 24, 2008 at 2:43 pm
Would I quit my job over backups? Probably not, but I'd want someone to put it in writing that I was absolved of issues because of this. And I might start looking for another job.
LINQ? That's not a danger to the data, performance maybe, but not the data. I'll work on applications being built stupidly if someone really wants to pay me to do it.
October 24, 2008 at 5:58 pm
Here's to stupid applications; they keep us employed! 😛
Though it can be annoying to work on them. My co-worker was reviewing another view that we're converting to a stored procedure today, and discovered it took it from processing 22 million records down to 2,250 records. Progress is being achieved.
I feel like we need a support group - DBA's Determined to Bring Outsized Processes to a Stop. (D BAD BOPS). :w00t:
Steph Brown
October 25, 2008 at 9:17 am
I guess I'd have to argue that developers are sometimes even more arrogant than the DBA. What makes a GUI developer think (s)he knows something about how to store, manipulate, or retrieve data than someone who has studied and committed to doing so?
There are basic three basic rules I follow...
1. Protect the data at all costs. That obviously covers a lot including telling developers and managers "Oh hell no... not in my database you won't."
2. Protect the server at all costs if it doesn't interfere with Rule #1. That means performance and scalability, for the most part.
3. Protect the developers and managers at all costs if it doesn't interfere with Rules #1 and #2. Developers and managers are schedule driven and will take the damndest shortcuts to get something done even if it's wrong. Being wrong on mission critical systems sometimes means unnecessary long hours to fix something or, if it's bad enough, it may mean getting fired.
I just went through some serious crap last night. We, unfortunately, have a legacy system that was born from the slime of folks that spend a lot of time at the shallow end of the gene pool worried mostly about schedule and not future maintainability, performance, or scalability. As a result, supposedly intelligent developers have SA privs on all the production servers... obviously, that's something I intend to remedy.
Anyway, a developer that I actually do think of as being somewhat intelligent caused a runaway situtation on TempDB on the reporting server about 4 days ago. I killed the job, found out who did it, told him not to run the code until he could fix it and I could review it, and then sent out emails and gave a small lecture to the entire group on how to avoid such problems in the future.
Last night, under pressure from managment, he decided to run the code again, no fix in place and without reviewing the code with me... this time on the server that serves the users on WebSites. TempDB blew out to 100GB, hit the new "limit" I put on it to keep it from taking out the whole bloody hard disk, and there I am trying to recover a mission critical server from the absolute stupidity of a developer and his manager over the phone because they don't want me (just a contractor at this moment) to have external access to the servers.
So, to answer your original question of which single rule would I like to put in place and enforce with the ferocity of a Lioness protecting it's cub? NO access, not even read-only access, to production servers by any developer, manager, or basically, any human except DBA's and designated SysOps. Yes, you need to build a sandbox server that looks like production so folks can actually develop and test code. But the no access rule is the one that all production servers should have and I don't give a rat's patooti if that does seem arrogant... it's absolutely the right thing to do because unless someone is actually charged with protecting the data, the server, and the people, they simply will not and they do really stupid things that put the data, the server, and themselves at very high risk.
And, I'd say that it was the developer who was bloody well arrogant in this case.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 25, 2008 at 8:49 pm
So many rules, so little time...
One word: vetting
Vet, transitive verb - to evaluate for possible approval or acceptance
Changes to the database - ANY change - needs to go through the guy (or gal) who will be spending sleepless nights and weekends troubleshooting/fixing/baby-sitting/living with the consequences of those changes. Not that everything has to be approved by the DBA, but the DBA should be given the opportunity to review and provide feedback on said changes.
The corollary to this is: Only the DBA is 'sa'. (someone else did say this already).
How many times has the DBA heard this one: "If you quickly make me 'sa' I can get in and solve that issue."
No problem, champ. Can I get you a coffee while I'm at it?
James Stover, McDBA
October 26, 2008 at 12:01 am
IceDread (10/24/2008)
I do not really have any absolutions. I judge from situation to situation. But there are a few things I try to reach... Never ever use cursors, because if you do it right there is always a better way. Do not allow null values, if you need null you have a design flaw, however, not allowing null in some cases would bring in a way to high complexity in several situations.One more important thing, always debate the old grumpy one's that think they know best but haven't read the latest articles and who has not tested another approach then the one's they always use.
We have a couple of places where we have to use cursors to generate interface files that are sent to legacy mainframe apps. Not an ideal situation, but sometimes you don't have a choice.
Using NULL is a design flaw? What else am I supposed to put in a date field for an event that will occur some time in the future? I suppose I could have a new table JobCloseDate, but why make things more complicatedf than they have to be?
I find that talking to the grumpy old guys is a great way to learn. They aren't stupid, and many times there's a legitimate reason for their reluctance to embrace the latest fad.
October 26, 2008 at 3:59 am
Ross McMicken (10/26/2008)
Using NULL is a design flaw?
It's a relational theory issue and there are two sides to it. One says nulls are fine to indicate missing or unknown values, the other says that any null indicates a flawed design.
It comes from the rule for the 1st normalised form that states that "Every row-and-column intersection contains exactly one value from the applicable domain" (src: Wikipedia). Some will say that simply means you can't have multiple values in the field. Some will say that it also means there has to be a value in the column.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 26, 2008 at 8:35 am
It's when theory hits reality that things get interesting. So given the "must contain a value" theory, if we translate that to reality then table A would need an associated table for any field that won't contain a value when the intial record is created in table A. And table B may need an associated table C, if records are created in table B that still don't have data for other fields that will eventually be needed by the record in table A (for later reporting, of course).
Is it the attempt to keep the application managable that keeps us from following this rule? (I know, there's at least two camps on the meaning of the theory, but doesn't interpretation get done based on our own reality?) Where does the balance point come it between project schedule and best practices? Has anyone actually built a working production database following the theories of database design, and up to which normal form, and does it work well or have problems of its own (and did it add to project time spent, or decrease it)?
Steph Brown
October 26, 2008 at 10:04 am
Stephanie J Brown (10/26/2008)
So given the "must contain a value" theory, if we translate that to reality then table A would need an associated table for any field that won't contain a value when the intial record is created in table A. And table B may need an associated table C, if records are created in table B that still don't have data for other fields that will eventually be needed by the record in table A (for later reporting, of course).
I'm not a proponent of that interpretation (I'll just leave a value NULL if it's unknown/not applicable), but from what I've heard from people who do do it this way, each lookup table would have a N/A entry (and/or unknown, etc) and the foreign key column would be set to that. If non-lookup columns don't have a value, then something is explicitly put in there to indicate that (like N/A, unknown, etc)
Is it the attempt to keep the application managable that keeps us from following this rule?
Which interpretation of it? Excluding the 'no nulls' interpretation, I've never designed a DB that didn't adhere to the rules of 1st normal form
Has anyone actually built a working production database following the theories of database design, and up to which normal form, and does it work well or have problems of its own (and did it add to project time spent, or decrease it)?
Sure. I try, as far as possible, to make my designs 4th or even 5th normal form (3rd is a minimum). I've seen more cases where a lack of normalisation caused problems than the other way around.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 26, 2008 at 5:00 pm
Wow Jeff ! you said a mouthfull !! And yes I'd agree on the NO ACCESS. Why ? because it is the calls at 1.00am (Local time) from a manager overseas saying that the "Your requested database is either unknown or you don't have sufficient privledges to access" type of message has appeared on his screen.
Reason
Developer A gained access (from a previous role) to server A and without the proper Change Protocol being adhered to descided to reindex all yes ALL the production databases on the Server A and because it was taking 'a long time' (4 Tb databases tend to !!) he went to bed because it was 1.00 am and he was understandable tired. Developer A now has to wear braces to hold said developer pants up ! Yes he is now bumless.
Protection at all costs
CodeOn
😛
October 26, 2008 at 6:19 pm
My Absolutes
1. Backups, Backups and just in case it was missed Backups.
2. No 'sa' access (My sa password is 128 random chars) Security groups and Integrated Security where possible (I know that isn't aslways absolute, but Theory Vs Real World sometimes dictate otherwise).
My Aims
1. No Cursors - this has been done to death but ... if the solution is a cursor have another go !
2. PK and FK Key Constraints - This ensures Business rule (for which the DB was developed) adherance, future proofing and scalability
3. No Individual Logins. Application has application logins. User group have Roles.
4. NO - Repeat NO Development/Sandboxes dbz on Production Servers. DEV>> PRE Prod >> Prod database life cycle.
There are other burning issues like :
SSRS reports running against a heavily transactional DB. or
Naming standards - each Vendor has their own.
I have found in my dealings with others that confidence is sometimes mistaken for arrogance and therfore I offer my credo as a starting point in being able to change what and where I can.
"Be consistant in your expectations, be concise in your requirements and constructive in your Criticisms"
Hey this is a wide, wide and diverse world and if you don't flex with it, it will break you !
CodeOn
😛
October 26, 2008 at 7:17 pm
Ross McMicken (10/26/2008)
We have a couple of places where we have to use cursors to generate interface files that are sent to legacy mainframe apps. Not an ideal situation, but sometimes you don't have a choice.
No you don't. That's just an excuse.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 27, 2008 at 7:37 am
But not necessarily a bad excuse. I'd agree with Jeff you want to avoid cursors and almost always can. However they are a tool that can work in places.
October 27, 2008 at 8:40 am
I have to agree with Isaac's Three Rules, . . ., er Jeff's. Especially his "No Access" sub-rule . . . and I am developer, not a DBA! Our lead developer, who was originally a one-man IT department here, is always messing with production and has caused untold crashes, which he continues to do, despite having been warned numerous times that he can't do that. But still our DBA and Network admin people are hesitant to take away his (and my) SA and server administrator privileges on the production servers no matter how many times I tell them they should.
Database wise, I have always been an adherent to old Wiorkowski Kull's DB2 Design & Development Guide quotation: "The rules leading to and including the third normal form can be summed up in a single statement: Each attribute must be about the key , the whole key, and nothing but the key." -- But only as a "near" absolute!
In fact, I still have my Database Programming and Design poster of the "5 Rules of Data Normalization" with the Daisy Hill Puppy Farm examples, tacked up over my desk.
"Any fool can write code that a computer can understand. Good programmers write code that humans can understand." -- Martin Fowler
October 27, 2008 at 12:01 pm
GilaMonster (10/26/2008)
Stephanie J Brown (10/26/2008)
Has anyone actually built a working production database following the theories of database design, and up to which normal form, and does it work well or have problems of its own (and did it add to project time spent, or decrease it)?Sure. I try, as far as possible, to make my designs 4th or even 5th normal form (3rd is a minimum). I've seen more cases where a lack of normalisation caused problems than the other way around.
I overwhelmingly agree here. Far more problems seen not having at least 3NF (and higher) than ever without. In fact, show me a case without, and I will show you a problem waiting to happen, or an inflexible design, that will eventually require (a) fix(es) or work-around(s).
Data does not belong to the GUI; the GUI is just ONE consumer of it, and preferable an indirect one at that. Data belongs to the business in question as a whole.
October 27, 2008 at 12:04 pm
Shaun McGuile (10/24/2008)
'...I consider the database just to be a big bucket to put stuff in...'
We (and I include myself in the victim population) are in so much trouble as the clouds form. The Cloud® really is just a big "bucket" (the developers' nirvana) to store stuff in.
In helping a group who shall remain nameless with their replication problem (which is supporting the launch of a really popular video game sequel Real Soon Now®), I came across a sentiment that hurt my feelings: "We don't want to worry about databases. We don't like SQL. We don't want to provision hundreds of servers. We don't want to hire DBAs. We don't care how or where it gets stored. We just want a place to put stuff and get it back in a hurry."
:hehe:
The fact that the architecture was designed with a plan to replicate 25MB/s or more of binary data to support peak load and keep the queues drained was... well, that's how The Developers® and I came to be having a conversation. Heh.
Viewing 15 posts - 61 through 75 (of 92 total)
You must be logged in to reply to this topic. Login to reply