August 29, 2003 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/storedproceduredevelopmentcycle.asp
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
September 3, 2003 at 1:06 am
Hi Robert
Good article. This and other "change management" process/procedures are critical for ongoing successful change, when I say successful, that mean change with rollout/rollback/hotfix which all cost resources (aka money) that is vert difficult to plan for (let alone truely cost for a business).
I follow a similar approach:
a) develop on the DEV servers, assign base security roles
b) script, rollout to TEST (access restricted purely to the role)
c) formal user testing on TEST
d) run TEST scripts against PREPROD
e) micro test on preprod
f) backup prod db
g) rollout to prod
h) minor functional testing
Simple enough, but few sites follow it.
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
September 3, 2003 at 1:30 am
Yep, agree with all this but I would add documentation to the list.
The situation I am in is where the stored procedures are effectively an interface to the database layer. My developers access the database via that interface layer.
The result of this is that the stored procedures have to be fully documented so that we don't end up with hundreds of stored procedures, all doing something very similar but not quite the same.
As we work with a number of web and cms (content management systems) these applications get hit by a stress testing tool. OK I've never had problems with the database server side of things partly because we tend to over-specify from day one and partly because IIS is the main bottle-neck!
I haven't been able to find any instructions for the Visual Studio Application Performance Explorer but I believe that this would help stress test the database.
We do use SourceSafe but isn't it about time MS updated it? It looks very Windows 3.0.
September 3, 2003 at 7:46 am
Just to mention, it is possible to set up fairly automated integration between SQL Server and Visual Source Safe using Visual Interdev. (And VSS 6 may be old, but in my experience it's one the most reliable applications Microsoft ever churned out, and when it comes to source code I'll take reliability over appearance any day.)
Setting it up can range from very hard to a royal nightmare [MS made us our own bug fix once], but once properly configured continuous operation is painless and seemless.
It requires doing your development in Visual Interdev, or at least checking code in and out with it. You theoretically can run and debug your code in VI, but given the pain required to set it up in the first place I've never tried to configure that. I tend to write the code in VI (it has a slightly better text editor than Query Analyzer), and then cut and paste and perform testing within QA.
Documentation and duplication of effort is one of our biggest problems, as we have 10+ procedure developers spread across two disparate geographical locations. I would love to see how David.Poole manages this!
Philip
September 3, 2003 at 9:09 am
I'm glad to know I am not the only one who does development the way I explained in my article.
David.Poole, you have a good point. I failed to mention documentation. However, I will see if I can create a good article about documentation in stored procedures.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
September 3, 2003 at 12:51 pm
Robert,
I, too, found this to be a great "Best Practices" article on SP development. Allow me to add my voice to those who would be interested in reading your take on the documentation side of things.
John
September 3, 2003 at 1:15 pm
.Net has the application test center that is pretty worthwhile for testing. Just record a web session, play it back, get lots of charts and graphs. Supports multiple users per test. Honestly takes 5 minutes to get up and running for a simple scenario.
Andy
September 3, 2003 at 1:22 pm
My concern over documentation is not so much about "internal" documentation as "external". There must be better terms for this, but I can't think of them offhand, so:
"Internal" documentation are the /*comments*/ you put in the code to make it easier for others (including yourself, months later) to figure out what the code is doing. This code (obviously) sits in the stored procedure itself, and so is only accessible to those who have access to the procedure, and only if they open and look at the file.
"External" documentation, as I'm thinking of it, is more a description of what the procedure does and how to use it (inputs and outputs), and not how it actually works. This should be a single object (document? database? file?), readily accessible and understandable by everyone who needs this information, and this could range from the old hands who know the system inside-out to the summer interns or new hires. This is the document that tells you "hey, someone already wrote something that does what you need".
I have no real idea how to create, maintain, and distribute such a document (and again, it wouldn't necessarily be a piece of paper or even a simple text file). But if we had had something like that around hear a few years back, we'd maybe have several hundred procedures today, and not a 2000+ load of redundancy and chaff.
Philip
September 4, 2003 at 8:56 am
This may be a little nit-picky but I recommend building the query in Query Analyzer outside the boundaries of a stored procedure and just using variables for the parameters. It's a lot easier to debug and do performance analysis and, since I am using parameters, the performance shouldn't vary when I create the procedure.
Otherwise, I think your process is excellent and I wish management-types could see the benefits of a little up-front legwork.
Bryant E. Byrd, MCDBA
Sr. SQL Server DBA/Systems Analyst
Intellithought, Inc.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog
September 4, 2003 at 9:09 am
I often created my queries and tested them before turning them into stored procedures. I still do this on occassion, however if I know this will be a stored procedure then I simply create it from the start as a stored procedure and work with it from there.
This saves me the trouble of saving the query in Query Analyzer. The stored procedure is stored in the database and the database is backed up every night.
Also, if you happen to need to use the datatypes image, ntext, or text you must create a stored procedure since you can't declare them as local variables. If someone knows a way around this I would love to know it but when I try to use the keyword DECLARE for a variable with datatype image, ntext, or text I get the following error:
Server: Msg 2739, Level 16, State 1, Line 1
The text, ntext, and image data types are invalid for local variables.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
September 8, 2003 at 1:51 am
Philip,
What you need is a good document managements system plus the internal procedures in place to use it.
Ideally the documents need version control so I would say have an official location on your network for this sort of documentation and check it all into source safe.
There are more elegant and vastly more costly systems for doing this but no-one gets rewarded for implementing more admin!
The idea of the document management system is
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply