MarkatTekTips2004
Programmer
I'm setting up an ASP.NET application using SQL Server 2000 which will be used by hundreds of clients. Among the tables in the database, each client should only have access to his own records.
Here's where I'm stuck...
Should I
1. Create a separate database for each client and duplicate the table set. In this case, the connection string would be slightly different based on the client logging in (i.e. the DB name will change, but that should be it). While all tables would contain a fairly small amount of records (e.g. 15,000 - 250,000), this would require several hundred databases, one for each client, on the server. Note, as clients come and go, DBs would have to be deleted / created on the fly programmatically.
or...
2. Create a single database and in each table have a field which uniquely identifies the client (e.g. clientId)? This would require that all queries include a "clientId" specification so as to retrieve/update/delete the proper rows.
I know that some tables will contain several million records in total across the hundreds of client. Others will contain perhaps only 100,000 in total.
I am unaware of the performance issues in SQL Server 2000 and wanted to get advice on this issue.
I do figure SQL Server 2000 will tackle large datasets, but is it better to keep all data within a single database with large numbers of records, or separate them as indicated above?
Thanks for any advice.
Mark
Here's where I'm stuck...
Should I
1. Create a separate database for each client and duplicate the table set. In this case, the connection string would be slightly different based on the client logging in (i.e. the DB name will change, but that should be it). While all tables would contain a fairly small amount of records (e.g. 15,000 - 250,000), this would require several hundred databases, one for each client, on the server. Note, as clients come and go, DBs would have to be deleted / created on the fly programmatically.
or...
2. Create a single database and in each table have a field which uniquely identifies the client (e.g. clientId)? This would require that all queries include a "clientId" specification so as to retrieve/update/delete the proper rows.
I know that some tables will contain several million records in total across the hundreds of client. Others will contain perhaps only 100,000 in total.
I am unaware of the performance issues in SQL Server 2000 and wanted to get advice on this issue.
I do figure SQL Server 2000 will tackle large datasets, but is it better to keep all data within a single database with large numbers of records, or separate them as indicated above?
Thanks for any advice.
Mark
