Identical database w/ identical stored procedures?

[HeadScratcher]

We have written an application which splits up our customers data into
their individual databases. The structure of the databases is the
same. Is it better to create the same stored procedures in each
database or have them in one central location and use the sp_executesql
and execute the generated the SQL statement.

Thank you.

Mayur Patel

[Stu]

Better in terms of what?

I mean, there are risks with using sp_executesql, and you've already
made the decision to split your data (thus minimizing contamination
and/or infiltration) so it seems a bit odd to allow users to jump into
a common database (but there are ways to do this without
sp_executeSQL). ..

What's your criteria for defining "better"?

Stu

[HeadScratcher]

Better in terms of performance of SQL Server. B/C if there is one
common stored procedure, then the stored proc will have to create the
sql statement to include which database to perform the action to.
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?

Thank you


Stu wrote:

Better in terms of what?

I mean, there are risks with using sp_executesql, and you've already
made the decision to split your data (thus minimizing contamination
and/or infiltration) so it seems a bit odd to allow users to jump into
a common database (but there are ways to do this without
sp_executeSQL). ..

What's your criteria for defining "better"?

Stu

[SQL]

If you have 1 proc I think you will have to use dynamic SQL or a lot of
if's
What's the problem (since all table names are the same anyway)
scripting the proc out and running it on all your DB's how may db's are
you talking about anyway?
Running straigh sp's over dynamic sql is alway better
------------------------------------------------------------------------------------------
"I sense many useless updates in you... Useless updates lead to
defragmentation ... Defragmentation leads to downtime...Down time leads
to suffering..Defr agmentation is the path to the darkside.. DBCC
INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with
you" -- http://sqlservercode.blogspot.com/

[Erland Sommarskog]

HeadScratcher (ma***@servicem g.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, es****@sommarsk og.se

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