webmastersmarte
Programmer
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.
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.
