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: 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: Moving stored procedures
    ... stored procedures will move with the database. ... database in a table called syscomments.,,, so they go automatically. ... Wayne Snyder, MCDBA, SQL Server MVP ... "Michael Beck" wrote in message ...
    (microsoft.public.sqlserver.server)
  • Re: Infinite Loops and Explicit Exits
    ... the whole payroll system would be dependent on proper database ... I design large systems the same way. ... Stored procedures ARE application code. ... any language, including Cobol. ...
    (comp.lang.cobol)