That is MOST helpful, Bill.

Thanks

JimN

>Jim,
>
>I have been working on an integration of three applications each of which uses a different SQL Database on the same or different SQL Servers. Near as I can tell, the answers to your questions are as follows: (see below)
>
>>Given a VFP environment I might elect to have each companies data in a separate DATABASE, (opening) switching to the active database as required. There could be a few hundred companies serviced and multiple copies of the app. could be used.
>>
>>But I have no idea if that can be done in SQL Server.
>
>Yes, you will establish a connection to each SQL Database using a different ODBC Driver. If you check some of the Active X controls available, I believe that you may be able to switch between databases that are on the same server thru the same ODBC connection, however, don't quote me on this, you may have to have a separate 'handle' for each connection to each database regardless of whether they are on one or more servers.
>
>>1) What are the implications on performance/response?
>
>Performance is affected only by the architecture of the LAN/WAN. If all databases are on the same LAN, no affect on performance (provided all servers are of equal horsepower)
>
>>2) What are the implications moving the databases off of the network server if this (separate databases) is done?
>
>You can have unlimited number (32,767 I think) of SQL databases on the same server. You can split databases between servers. I would think that you will have some table somewhere that keeps track of which servers the databases are on but the master table on a SQL server maintains a table of all databases and tables within databases that are on a particular SQL Server.
>
>>3) What are the implications on database maintenance?
>
>Each database will have its own maintenance scripts for backups and cleaning up but once one set is done, you can pretty easily clone the others.
>
>>4) What are the implications on LOGGING (can it even do it, can it do them all in 1 or would there be 1 for each)?
>
>Each database has a separate transaction log which is maintained independently of the others.
>
>>5) What else might make this a bad idea?
>
>I think you have to look at whether maintaining innumberable numbers of databases is easier than finding a way of differentiating different entities through some sort of entity hierarchy managed by the security (as in company or division numbers with security permitting certain people to see one and not the other. Will there ever be a need to present some consolidated numbers, writing a connection routine for the reports to be run against the miriad of different databases vs. a filter on an entity number to print one set of reports against one database. Stuff like that.
>
>BTW, all of my answers are in the context of MS SQL7.0 although SQL 2000 makes some of this stuff a little easier. MS SQL Enterprise Edition deals with fail over switching among multiple servers.
>
>HTH,
>
>Bill
>
>>
>>Any comments would be helpful.
>>
>>Thanks,
>>
>>Jim Nelson