I was a bit surprised by this question as it never occurred to me it that someone would even ask it, but heck, that was the reason for the Ask a Jedi series so maybe others are wondering as well. Audra asks:
Is it possible to create a ColdFusion application with 2 DSNs? Both database sources would have to be updated from the application. It's not the ideal situation, but we have two very large access databases, in which we have considered combining similar fields/tables. However, there are so many dependent functions and variables that the transition with have to contain many phases. And it would be best, in this particular situation, to work with two datasources.
The answer is yes, of course. There is no correlation between ColdFusion applications and how many datasources can be used. Obviously you don't want to use too many. I'd say 90% of my applications used only one databases, and the rest used two or three. Typically these were old databases that we were not allowed to upgrade. Typically they were also very segregated databases that had content specific to only one small section of the site. But the short answer is yes, you can use as many DSNs as you want in an application.
Now - you may be restricted by your ISP. You probably want to check for then when shopping for a ColdFusion host. A decent one should give you at least five I'd say.
Archived Comments
Another question on the same lines...
If you have a very very busy site, and the DB is taking lots of hits, could you see any advantage to having 2 (or more) DSN pointing to the same DB and switching between them at different calls to spread the load on the JDBC?
One important thing to note in this situation: You cannot do a cross DSN join of two tables except with Query of Query. So in this situation your data is rather seperated.
michael - not true with SQL Server - if the db's are linked you can join acrossed tables in different dsn's.
Todd,
Yes that is the case, but the users question was specific to an access database. Inside of a database system you can query agains tdifferent databases like in MSSQL with linked tables you can query even against an oracle database and join across these tables.
@David
To answer your question, NO. You wouldn't see any performance improvement because your still hitting the same CF server and hitting the same SQL server.
The best thing to do would be cache as much of the database as possible into memory so you don't have to hit your database as much. Another good technique is to have seperate DB and CF servers. This way if you need to load balance the CF server or cluster the DB servers, you can.
One way is to move as many of your joined queries into views, make the database do more of the db/logic/crunching work, and coldfusion less.
I would also point out to the user that it's a good habit to use variables instead of static DSN names. That way you could easily change the variable's value in one place and have it immediately change for the whole application. This makes the app more portable as well.
For instance, in Application.cfm:
<REQUEST.dsn = 'AccessDB1'>
In every other file:
<CFQUERY DATASOURCE="#REQUEST.dsn#">
@david
Yes, there is an advantage (but it's been a long time). I know that I had a very very very busy Access database that was giving me all sorts of problems (locking issues, single threads, etc). When I access the database with two DNS settings (one for one sort of qeuery, the other for the ones that seemed to lock the system), mauc of the performance issues went away. I gues I could have run a script as well and 50/50 the system to pick one or the other db for any query.
There's some (this was back in 4 by the way) going on at serveral levels : CF, the server, the Access database, but this did help at that time.
To the original poster:
One reason you may want to do this is in a corporate environment. I'm working on a set of apps where we receive very basic employee information from a view in an Oracle database. The Oracle view was created a few years ago for another application and we access 1 table only of 50k+ employees (email, their ID, name, etc.) We use this for basic authentication and to give us our HR based employee hierarchy (subordinates, managers, directors, etc.).
From there, we use MS SQL 2000 for our actual application. We have 3 databases, dev & test (on the same box) and one for production. When working on the app, most pages use 90% SQL with the occasional call in a query to the HR database.
Note, per other commenters, we too use #Request.DSN# or #Request.Datasource# in our query statements. In our application.cfm (still on CF 6) we have some logic that checks the cgi.server variables to determine which server you are on and then dynamically sets the datasource based on which server (CF) you are on. This is great since it allows us to develop CF on my desktop using the dev db. I can wipe it out and jack it up at will (and then copy the clean data from production when needed). When my boss is reasonably happy with the code or he needs a clean copy for demoing to someone internally he uses the test server which maps to the test db, then to staging and the real db, then the real CF and real db. All with no manual rewriting of query statements, the application.cfm handles the name of the datasources dynamically.