Maximum databases per server

Dear All, We are planning an application where we need to maintain
separate databases for each customers. Now, the number of customers in
one year would be 50,000. I read that theoretically it is possible to
maintain 32000 databases in a single instance of SQL Server, but I can
guess that it would certainly have performance issues.
What could be the possible architecture? Can having views be a
solution, so even though the database would be one for all the
customers, it would be logically separated. Can this be a possibility?
I do not need to synchronize data between customer databases and I
reckon that the database would be having 30-40 tables.
Kind regards,
Santosh
Can you provide the Sql Server version and other server statistics?
Thanks,
Sree
"santoshmahale@.gmail.com" wrote:

> Dear All, We are planning an application where we need to maintain
> separate databases for each customers. Now, the number of customers in
> one year would be 50,000. I read that theoretically it is possible to
> maintain 32000 databases in a single instance of SQL Server, but I can
> guess that it would certainly have performance issues.
> What could be the possible architecture? Can having views be a
> solution, so even though the database would be one for all the
> customers, it would be logically separated. Can this be a possibility?
> I do not need to synchronize data between customer databases and I
> reckon that the database would be having 30-40 tables.
> Kind regards,
> Santosh
>
|||the version I am planning on is SQL Server 2000 EE.
Are you refering to hardware stats of the server? I am planning 4
processor enabled (2 installed initially), 8 GB RAM and a SAN.
What other stats information you had in mind?
|||Hi santhosh,
For sql Server 2000 The maximum capacity specification says '32767'. More
than that i dont think the single instance will take, i have tried other
maximum capacity specifications like "Maximum columns per select statement =>
4096". If we give 4097 the select statement throws an error. So your design
might fail if you try to make more than 32,767 and you were saying there can
be 50,000 customers a year. Will this count increase subsequent years? If so
you need to purchase servers as the sutomer strength increases, again on
performance side as you have a strong hardware support it wont affect much
but the concern is crossing 32,767 limit.
Why cant you maintain all customers in single DB? you asked for logical
separation between customers which i feel is not a concern. Again users
accessing it can be classified into separate logins no. Single DB is the
right choice, again seeing the application side how will you configure to
connect to each DB for each customer?
Thanks,
Sree
"santoshmahale@.gmail.com" wrote:

> the version I am planning on is SQL Server 2000 EE.
> Are you refering to hardware stats of the server? I am planning 4
> processor enabled (2 installed initially), 8 GB RAM and a SAN.
> What other stats information you had in mind?
>
|||Hi
a single db sounds the best way to go. I did something similar
recently. By adding some form of customer id column to each table, and
setting up a table to control which SQL user accesses which records.
Sreejith G wrote:[vbcol=seagreen]
> Hi santhosh,
> For sql Server 2000 The maximum capacity specification says '32767'. More
> than that i dont think the single instance will take, i have tried other
> maximum capacity specifications like "Maximum columns per select statement =>
> 4096". If we give 4097 the select statement throws an error. So your design
> might fail if you try to make more than 32,767 and you were saying there can
> be 50,000 customers a year. Will this count increase subsequent years? If so
> you need to purchase servers as the sutomer strength increases, again on
> performance side as you have a strong hardware support it wont affect much
> but the concern is crossing 32,767 limit.
> Why cant you maintain all customers in single DB? you asked for logical
> separation between customers which i feel is not a concern. Again users
> accessing it can be classified into separate logins no. Single DB is the
> right choice, again seeing the application side how will you configure to
> connect to each DB for each customer?
> Thanks,
> Sree
>
>
> "santoshmahale@.gmail.com" wrote:
|||santoshmahale@.gmail.com wrote:
> Dear All, We are planning an application where we need to maintain
> separate databases for each customers. Now, the number of customers in
> one year would be 50,000. I read that theoretically it is possible to
> maintain 32000 databases in a single instance of SQL Server, but I can
> guess that it would certainly have performance issues.
> What could be the possible architecture? Can having views be a
> solution, so even though the database would be one for all the
> customers, it would be logically separated. Can this be a possibility?
> I do not need to synchronize data between customer databases and I
> reckon that the database would be having 30-40 tables.
> Kind regards,
> Santosh
No question in my mind. Go for a single DB for all customers. 50,000
databases is madness.
Why do you say you "need" separate databases for each customer?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx