There are other concerns for using stored proc's other than just
performance and security.
We decided to enforce a "stored procedure only" policy because we were
experiencing issues with our cf servers related to database operations.
In addition we noticed a very number of database calls per page (using
cfstat server statistics collected over time.)
Because of this we decided we needed more eyes taking a harder look at
database operations, and the best way *that we found* to do that was to
make sure all SQL was in the database, and easily accessible by the
DBA's.
In our environment, developers don't have access to production SQL
servers at all. SQL changes must go through the DBA's. They also don't
have direct access to the production CF servers, but can publish to the
production servers at will. However accept for a code review to get on
to the production CF servers, there is no oversight of CF after a code
review, (unless an application starts misbehaving.) Additionally to
this, we had been recommending and urging developers for years to use
only stored procedures, but we didn't really make a dent.
So because of all of these factors, when we rolled out a new environment
for CF 7, we made "stored proc's only" part of the rules of using the
new systems. We don't have the "having to ask the DBA's to make every
change" problem because we have a development SQL server with databases
to which developers have owner access. They still have to ask for
changes in production, but that's a constraint of our environment, and
the DBA's are fairly responsive to changes during the working day.
Has this solved our problems? Well to some degree, yes. We don't have
random, hard-to-diagnose problems due to database operations anymore.
>From time to time, when we do have issues with our database connections,
I have found them to be much easier to troubleshoot on the MS SQL side,
than on the CF side. The developers are less happy about it, and I
understand that, but it was something we needed to do. The important
thing we did though was make sure that many of the disadvantages of
doing this we mitigated. Like all things it's a trade off, and these
decisions should be made in response to the actual conditions in play,
as opposed to some philosophical or religious reasons.
Terrence Ryan
Senior Systems Programmer
Wharton Computing and Information Technology
E-mail: [EMAIL PROTECTED]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251204
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
