Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Accessing Multiple Databases through Single Datasource

Status
Not open for further replies.

webmastersmarte

Programmer
Aug 8, 2006
1
US
Hi,

We would like to know the pros and cons of accessing multiple databases through a single datasource, versus accessing each database through its own datasource. Our environment includes multiple web servers w/ the latest version of ColdFusion MX 7, clustered through a load balancer. Each web server has 800+ dsns pointing to different SQL databases. We have noticed that the ColdFusion administrator is taking a long time to display or verify all datasources and sometimes it even times out. Another problem is that sometimes the neo-query file gets corrupted (for unknown reasons) which results in the deletion of one, or more, or all datasources on the web server.

Because of the issues above we are researching the possibility of removing most of the datasources, and then accessing each database through a single bridge datasource. In that regard we plan to change our queries by inserting the sql db name and user in front of each table in the query such as:

<cfquery name="query" datasource="single_dsn_name">
select * from [#dbname#].dbo.tableName
</cfquery>

In the example above, obviously #dbname# would be a variable that will hold the name of the requested database. The code above would similarly apply to queries using, update, insert and join words.

Are there any limitations or negatives from scalability, performance, and reliability perspective in implementing the above scenario versus having one datasource for each database.
Also, if there is a better way of accomplishing this, we would love to hear about it.
 
I do this all the time. So long as the connection identity has rights to the specified database, I don't see a problem with it.

You may want to test the scalability of this against connection pooling limits.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK
-----------
I'll have the roast duck with the mango salsa.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top