Storing all stored procedures in one database to minimize different connection strings

[Minh Tran]

In order to minimize the number of connection strings I have to use to
access different databases on the same Sql Server, I was considering
storing all stored procedures in just one database. I want to do this
because connection pooling in my application - ASP.NET is based on
this connection string. So if I need to access 6 different databases
on one sql server & set 6 different connection strings, I end up
creating 6 different connection pools.

Other than it might create more management work for the DBA, are there
any performance implications with implementing this scheme? Do stored
procedures run any slower if they access tables that are stored in
different databases within the same server?

Any comments/suggestions are appreciated.

TIA,
Minh Tran

[Erland Sommarskog]

Minh Tran (mi*********@yahoo.com) writes:

In order to minimize the number of connection strings I have to use to
access different databases on the same Sql Server, I was considering
storing all stored procedures in just one database. I want to do this
because connection pooling in my application - ASP.NET is based on
this connection string. So if I need to access 6 different databases
on one sql server & set 6 different connection strings, I end up
creating 6 different connection pools.

Other than it might create more management work for the DBA, are there
any performance implications with implementing this scheme? Do stored
procedures run any slower if they access tables that are stored in
different databases within the same server?

The first question that strikes me is why you would have six
databases for the same application.

But given that this is not something that you cannot change, I
recommend against having all stored procedures in one database. This
is not because of performance, but one of manangement. Say that someone
says "let's set up a test environment on the the same server". If you
have hardcoded database names in your stored procedures, you lose.

The connection-string problem could easily be overcome by
always using the same database when you connect, and then specify
the procedure as db.owner.sp.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[Minh Tran]

Thank you Erland,

I think every connection string sample I've seen always showed the
database in it. It didn't even occur to me that you can make a
connection w/o specifying a database at all.

Now my connect string looks like this:
"Trusted_Connection=yes;server=(local)"

And I just specify the database & owner as you suggested. This makes
alot more sense, & I'm sure my DBA will be much happier. :)

And yes, the many databases on this server are completely out of my
control.

Regards,
Minh Tran

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

[posted and mailed]

Minh Tran (mi*********@yahoo.com) writes:

I think every connection string sample I've seen always showed the
database in it. It didn't even occur to me that you can make a
connection w/o specifying a database at all.

It is probably good practice to include the database in the connection
string, because else SQL Server will pick the user's default database,
and this may be a completely irrelevant database that he no longer
has access to, or that even no longer exists. In this case the connection
would fail.

My suggestion was that you should always include the *same* database in
the connection string. Typically one that the user must have access to, to
be able to use the application. If in your case, users may only have
access to some of your six databases, you could use tempdb instead.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[Minh Tran]

Thanks again Erland. I appreciate you taking the time to explain the
best practice for achieving a connnection to Sql Server.

Regards,
Minh Tran

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!