Re: Identical database w/ identical stored procedures?

HeadScratcher (mayur@xxxxxxxxxxxxx) 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@xxxxxxxxxxxxx

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

.

Relevant Pages

  • Re: Unable to view system stored procedures from .NET IDE and unable to debug SQL
    ... But what is the reason for the debug problem I mentioned below? ... try to step into a stored procedure in the Northwind database, ... Run SQL server setup or contact database ... I can't see any system stored procedures in master. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Does nesting stored procedures make sense to increase performance?
    ... Reducing client or middle-tier round trips to the database is a good idea in that it will reduce network traffic. ... In SQL Server 2000, because recompiles of query plans are done on a per-procedure basis, it's helpful some execute one SQL statement per procedure as well. ... If you're going to combine many calls into a single call, you'll need to add robust error handling to the "driver" procedure so as not to call procedures 2,3, and 4 if procedure 1 fails, etc. You'd need to replicate your existing client/middle-tier code's concept of success/failure results quit the batch of procedures on a failure and the return the results/return code that the client/middle-tier sees in the original. ... I've recently became aware of nesting stored procedures in SQL Server. ...
    (microsoft.public.sqlserver.programming)
  • Re: Debugging in VS.NET
    ... > "Cannot debug stored procedures because the SQL Server database is not ...
    (microsoft.public.sqlserver.msde)
  • Re: Query Analyzer Connect Option
    ... the database, but the users only have SELECT access. ... through stored procedures. ... As Mary pointed out, giving users full access to the database, and ... Books Online for SQL Server SP3 at ...
    (microsoft.public.sqlserver.tools)
  • Re: Database Model - Class, objects and interaction
    ... I was just proving stored procedures can call views. ... stuff like security and logging. ... So given 100,000 users, you would create database accounts for each? ... Part of our system's integrity was its reliability, and reliability is often assisted by simplicity. ...
    (comp.object)