Posted by Erland Sommarskog on 10/26/05 01:09

HeadScratcher (mayur@servicemg.com) writes:
> But, is the performance hit to do this really that high compared to
> creating the maintence nightmare of updating the same stored procedure
> in every database?

That is no maintenance nightmare with proper configuration-management
procedures in place.

First of all, put your source code, tables, stored procedures and all
under version control. Then learn about labelling, using baselines
and so on. To deploy a change, you need to develop an update script.
Yes, you will need to keep track of whether you have run a certain
update script. This can be achieved with a help table in the database.

Having centralised stored procedures, on the other hand, does lead to
maintenance problems, since the code so much more complex.

Also, having procedures in each database gives you more flexibility. Maybe
you have a bigwiz customer that want extra features. Or absolutely flat
out to accept an upgrade for the next two months. And one day you find
you need to scale out, and move some databases to another server.

As for possible tools, there are a couple on the market, whereof some
vendors certain will post a blurb in response to this. (If memory servers
DBGhost people are very fond of this.) I can even offer a solution myself,
which have the distinct advantage of being freeware. That's simply what
we use in our shop, you find it http://www.abaris.se/abaperls/.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]