By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
439,977 Members | 1,369 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 439,977 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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
Jul 20 '05 #2

P: n/a
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!
Jul 20 '05 #3

P: n/a
[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
Jul 20 '05 #4

P: n/a
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!
Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.