November 25, 2010 at 3:47 pm
Hi,
in our environment Developers are responsible for supplying script or scripts to be applied to Test/Prod environments. We have situation where Developers code scripts that don't meet DBA standards.
I'd like to automate some of the standard DBA checks
e.g.
1. schema's under userid's not dbo or other approved.
2. Granting permissions to user accounts
3. Anything Else
Has anybody got some scripts or tools for automating this type of parsing.
It could be after the scripts have been deployed but would nice to do it before hand.
Thanks.
November 25, 2010 at 7:57 pm
Joe Fuller (11/25/2010)
Hi,in our environment Developers are responsible for supplying script or scripts to be applied to Test/Prod environments. We have situation where Developers code scripts that don't meet DBA standards.
I'd like to automate some of the standard DBA checks
e.g.
1. schema's under userid's not dbo or other approved.
2. Granting permissions to user accounts
3. Anything Else
Has anybody got some scripts or tools for automating this type of parsing.
It could be after the scripts have been deployed but would nice to do it before hand.
Thanks.
I don't have such a thing but open the scripts in MS Word and run some macros you build. I used to do a similar with a similar product called "TextPad"... I had it so it would bookmark everything I need to do a visual on.
Still... don't trust homegrown automation or even shrink-wrapped automation 100%. Nothing replaces the trained eye and skilled mind of a good DBA especially when it comes to things like checking for SARGable WHERE and ON clauses or making sure that all functions are "inline Table Valued Functions" (iTVF's).
--Jeff Moden
Change is inevitable... Change for the better is not.
November 25, 2010 at 8:06 pm
Good Idea.
Besides those Jeff has mentioned>
What do you look for?
November 25, 2010 at 10:13 pm
Joe Fuller (11/25/2010)
Good Idea.Besides those Jeff has mentioned>
What do you look for?
Table names (and most other SQL objects. that don't use only the 2 part naming convention. The 2 part naming convention should, with very few exceptions, always use the 2 part naming convention. The use of synonyms (or pass through views in 2k) for 3 and 4 part named objects should normally be used instead of 3 and 4 part naming directly in the code.
Proper embedded documentation. One could write a book on such a thing and it has nothing to do with ANSI or ISO. 😉
RBAR. I always check for While Loops, RBAR style recursion, triangular joins, etc. I also check for the use or creation of scalar functions and mlTVF's (multi-line table valued functions). Again, there's always the exception but these are usually another form of hidden RBAR that code can do without.
This will sound harsh, but I also check for, ummmm.... "stupid" code. For example, to get the right most delimited value from a string only takes one REVERSE (expensive code-wise) and not 3. Code that is built to call procs that work on a single row are generally forbidden. Sprocs that call more than 2 levels deep are generally forbidden. Views that call views are generally forbidden. Functions that call functions are forbidden. More than 2 levels of CASE statements normally means something is really wrong with the code. More than 8 joins normally means that some form of "Divide'n'Conquer" method should be implemented.
I also check line length (119 characters max for landscape printing and ease of view on-screen), header information, a whole gambit of other things including improper joins on DELETEs and UPDATEs that will almost guarantee the "Halloweening" will occur. I also check datatypes on joins to make sure no implicit conversions will occur and I check new indexes for cardinality (simple count based on the columns of the index). I also check for hard-code functionality that should be table driven.
Like I said, there are a lot of things that you just can't easily write a macro for. A good DBA always needs to look the code over from haircut to pedicure.
Then, comes the hardpart. You can't just simply reject the code. You have to give reasons. That means you need standards to backup the reasons. AND, the smart DBA will call a Developer in and explain each "failure" so the Developer learns enough over a relatively brief period of time so the code reviews become easier and easier because the Developers keeps getting smarter and smarter.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 26, 2010 at 3:06 am
Apex have a tool called SQL Enforce which you can completely customise using a .net language.
I've used it in the past but couldnt get the backing of the development manager to enforce the results. :angry:
November 26, 2010 at 8:27 am
MysteryJimbo (11/26/2010)
Apex have a tool called SQL Enforce which you can completely customise using a .net language.I've used it in the past but couldnt get the backing of the development manager to enforce the results. :angry:
That's always the difficult part. I've not found many managers that understand that bad code is bad code even in the face of difficult schedules. I've found that the real problem is that most managers don't understand the concepts of 1) reasonable schedules where a job CAN be done correctly without doing all-night-death-marches and 2) understanding the idea that you have to submit final code to the "QA" process (which includes such things as code reviews) early enough so that if there is a standards violation, there's still enough time to fix it and still meet the schedule.
One of the snippets of wisdom that I used to carry in my signature is something that I believe all people who plan projects should remember...
Poor planning on your part [font="Arial Black"]DOES [/font]constitute an emergency on my part... so learn how to [font="Arial Black"]PLAN BETTER![/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
November 26, 2010 at 8:57 am
I guess now is as good a time to mention it as any...
Code reviews (and unit testing... but that's a different subject) are super important to success. When I first started at a company I used to work for (loved it but they moved to a different State), "change controls" took 6 hours just to implement a dozen new stored procedures because the sanity checks made after implementation always failed. Even simple data changes took a long time because they were so poorly written. One simple data change of a 1000 rows took 40 people a week to fix because SQL Server had a problem where if conditions were just right, the WHERE IS NULL in the code was simply ignored and it updated all 800,000 customers with the wrong information. (I should also mention that backups had been failing for the previous 3 weeks and no one knew... important reason why the DBA's should be involved and not just the folks in OPs).
You need to know expected rowcounts and they should be hardcoded into the change code so the DBA can instantly determine if the code worked correctly or not. That also means that the data change code should have a BEGIN TRANSACTION and the DBA should be doing manual COMMIT's or ROLLBACK's. I'm not sure that a solution like APEX can check for such a thing.
I'll also tell you something that NO solution like APEX can tell you... quality of the embedded comments. Why are those important? It's simple... rework and/or modifications. Even if the person doing the rework/mod is the same person the wrote the code, it could be months or even years between changes. No one can remember everything. The "standards" I forced the company to follow was to explain the "WHY" of every INSERT, SELECT, UPDATE, and DELETE including subqueries! The effect was amazing. First, the amount of rework actually needed dropped substantially because people had to write comments to explain what they were doing and it helped them figure that out. They simply did a better job right up front. Second, the amount of research it took someone assigned to do some rework or modification plummeted because they didn't actually have to analyze ever blasted query to figure out where they needed to make a change. All they had to do was read some relatively simple comments. Rework research times dropped from an average of 16 hours to less than an hour for most things at the company.
Oh... and those 6 hour change controls that normally had failed code in them? They dropped to about 15 minutes for as many as 50 stored procs and a dozen data changes (some were quite large, to boot) with no errors and no failed sanity checks.
Further, required post production rework dropped from an insane average of 16 hours per proc (most were fairly complicated procs) to almost zero.
Make no doubt about it... it was a difficult task to get people to understand that the idea of "Standards Compliance" and proper embedded documentation were both the keys to succes. As you can see from my narrative above, it was definitely worth training the managers to understand that no code goes to production until I say it does. It works for both "waterfall" and "agile" shops, as well. The final hidden benefit was that the amount of required overtime for salaried Developers and their Managers dropped from more than 12 hours to nearly zero. Although everyone bitched like they had a nipple caught in a bear trap when we started, everyone made the realization that the changes improved not only drastically improved the quality of the code, but it improved the quality of their lives, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 27, 2010 at 2:08 pm
"Functions that call functions are forbidden"... i'll bite. let's assume your data has telephone numbers stored in some format, and you want them to always be returned in a standardized format, so you write a function (dbo.FormatPhone) that formats a phone number. that way, there's only one place to change the phone number format when your company becomes multi-national.
as part of formatting the phone number, you need to have the input parameter converted into a known state. since that ability might be useful in other places, you write a function to UNformat the input parameter (principle of modular programming).
logically, the formatPhone function should call the UNformatPhone function.
would this formatPhone function now violate your "no function can call another function" rule?
November 27, 2010 at 3:42 pm
To be honest, I never store formatted phone numbers and I don't format phone numbers in the database. That would be the job of the GUI if there is one. None of the billing companies I've worked with and none of the service providers I've worked with have ever passed or accepted formatted phone numbers either.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 27, 2010 at 10:22 pm
you are indeed lucky. NONE of the providers i've had to deal with can figure out how to pass UNformatted phone numbers! not to mention they often can't be consistent within their own data.
for me, there is no gui, so the formatting must be done in the database somewhere.
November 28, 2010 at 2:52 am
Has anyone used SQL 2008 Policy based Management to enforce these type of Policies.
Can it be done?
Can it be used with 2005?
Cheers.
And Thanks Jeff for a number of ideas.
November 28, 2010 at 9:15 am
ikjeft01 (11/27/2010)
you are indeed lucky. NONE of the providers i've had to deal with can figure out how to pass UNformatted phone numbers! not to mention they often can't be consistent within their own data.for me, there is no gui, so the formatting must be done in the database somewhere.
Heh... no, not lucky... imposing, maybe, but not lucky. When it came time for the company I worked for to do business with a potential new provider, I ran them through the Gin Mill first to make sure they could provide consistent and accurate data. If they can't do that simple thing, then what else are they doing wrong? I just can't recommend that the company do business with someone that can't provide good data. 🙂 The important part is that managment agrees with me and I'll admit that not everyone has that luxury. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply