How many databases SQL 7 can host

[jluost1]

I have a Web-based application with SQL Server 7 as the backend. The SQL7 Server machine is a powerful Win NT4 server, with more than 20gb hardware storage and 1.4 gb Ram. The Web is running on a separate IIS server. Each time a customer asks for this application, I create a database with it. So, potentially there will be hundreds of database the SQL7 server has to host. However, each database is less than 10MB. And there are not many activities happening each day for each database.

My questions is: Can SQL server 7 host hundreds or thousands of databases? What's the implication (especially in terms of performance) of doing this?

 

[harryrich]

I don't know what the limit is, but each database has a separate data and log file and is registered in the master.

With many database files, I would expect considerable fragmentation of the disk containing them.

You will also need to be prepared for growth in the master and back it up frequently.

Also, most of the SQL server maintenance is by database.

If your users actually have identities on the SQL server, you might consider using the SQL qualification of object names by user id's to put everything into a single database.

 

[tlbroadbent]

SQL Server has a limit of 32767 databases. I'm sure other limitations such as disk space and RAM will be more restrictive.

View all SQL Server Maximum Capacity Specifications at

http://msdn.microsoft.com/library/en-us/architec/8_ar_ts_8dbn.asp

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[RickCole]

Not sure why you need to create hundreds of databases but it would seem to me that it would be a maintenance nightmare going down that road.

From a personal point of view if I needed to do something along those lines then I would look at the option of creating hundreds of different tables within one database.

Just my thoughts.

Rick.

 

[jluost1]

Thank you for inputs from all of you. I am just thinking ahead based on our current architecture. Reasons for creating a database for each instance of an application are to isolate one customer's data from the other's, also to archive customer's data easily. The maintainence is OK with less than 200 databases for now (I have routines to replicate structural changes to all databases, as well as many other tasks such as backup, assign user roles,etc.). It certainly needs to be addressed when the number keeps going up. I am evaluating other alternative ways.

My major concern is, many database activities are performed in Master database and many objects are stored in Master database as well. Master database grows very fast and too much conggestion could slow down all sites.

If someone is doing the same as I do, especially in hosting environment, can you tell me what issues may float, especially in terms of performance?

Thank you.

 

[jjgraf]

On Performace

Move the Stored Procedures to the perspective Database that it will be run on.

Move objects to the perspective database

Look into clustering

move to SQL Server 2000 it has much improved clustering and Memory Management.

Defrag the Hard drive as offen as you can

If you Don't have hardware RAID get it. Stripping or Raid 10 is the best for performance.

W2K with Sql Server 2000 is faster than NT 4.0 with Sql 7.0
(i question this one because the benchmark was done by M$)