473,394 Members | 1,048 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

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

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
4 2013
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
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
[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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

14
by: mjkahn | last post by:
I've read (and read!) that you shouldn't store objects in Session variables. I've read these reasons: - The object takes up memory that may not be freed until the session times out. Better to...
5
by: Raquel | last post by:
This is a very simple DB2 SQLJ stored procedure. The problem is that it seems to run fine but returns NOTHING. I mean..as if nothing has happened..not resultset is returned. I am passing value...
2
by: Kent Lewandowski | last post by:
hi all, Recently I wrote some stored procedures using java jdbc code (admittedly my first stab) and then tried to implement the same within java packages (for code reuse). I encountered...
1
by: Lyle Fairfield | last post by:
I created a new MS-SQL Database, "TestODBC". I made Table1 and StoredProcedure1. I made an ODBC DSN for that MS-SQL Database. I created a new AccessXP mdb, "TestODBC". I linked to the...
2
by: Dino L. | last post by:
How can I run stored procedure (MSSQL) ?
17
by: | last post by:
I have an app that retrieves data from an Access database. At the moment I have the SQL string as a Const in my app. I understand this is not best practice. I don't want the user to have access to...
2
by: jed | last post by:
I have created this example in sqlexpress ALTER PROCEDURE . @annualtax FLOAT AS BEGIN SELECT begin1,end1,deductedamount,pecentageextra FROM tax
5
by: Pedro Vera | last post by:
I am helping somebody setup one of the asp.net starter kits. I converted it from sql express to sql server with no real issues, and I got it running local perfectly. On my first attempt to run...
11
by: peter | last post by:
I am trying to get a SQL stored procedure to use user maintained MQT implicitly which raises questions on when they are used or not used. In theory you would expect the stored procedure to pick up...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.