Datasource management with SQL Server

[KempAS]

We're looking at migrating our applications from Interbase to SQL Server 2000.

Basically, we have a number of databases which, currently, are set up as
individual datasources (with their own user/password) in CF administrator which
requires management.

We are looking at the option of simply using a single CF datasource connection
to the database server and then specifying which database object is required
from within the queries themselves. This would mean simpler datasource
management but introduce the fact that each connection to the server is via the
same user/password since using a single datasource would mean a single
user/password combo.

Any advice as to the best datasource management? I.e. to those that are using
SQL server behind CF and with more than one application/site requiring indidual
databases, do you use multiple datasources, i.e. one or two for each, or do you
access each from the top level access of the server or any other scheme?

Advice/comments welcome!

[Dan Bracuk]

We use separate datasources.

Any efficiencies gained by only setting up one datasource in the administrator will be offset by the extra typing in the queries.

[KempAS]

Yep, that was my first thought!

Think we will go as currently with multiple datasources and also do a top
level server datasource for the cases where we need inter-database joins.

Thanks for the reply

[Dan Bracuk]

I'm not 100% sure, but you might be able to set up the inter-database permissions in SQL, so you won't have to worry about it in Cold Fusion.