Central vs separated databases

Hi,
I'm making a study about pro en contra for central and separated
databases in one sql server.
I googled and found some interesting opinions but most of the are
about management.
But what about performance, what if a database has 100-200 tables and
50 views and keeps growing.
Can Sql server handle this?
What about restore?
What about (I'm missing)...
Any help or a good link is welcome.Performance is not generally a function of the number of tables or
views. With a good database design the limiting factors are more the
number of concurrent users and transactions, as well as table size and
database size.
100-200 tables with 50 views, those numbers are not a challenge at
all. How any gigabytes of data do you anticipate? How many users? Is
it transaction processing or reporting?
Roy Harvey
Beacon Falls, CT
On Wed, 31 Oct 2007 03:20:33 -0700, HDI <hdinf@.hotmail.com> wrote:
>Hi,
>I'm making a study about pro en contra for central and separated
>databases in one sql server.
>I googled and found some interesting opinions but most of the are
>about management.
>But what about performance, what if a database has 100-200 tables and
>50 views and keeps growing.
>Can Sql server handle this?
>What about restore?
>What about (I'm missing)...
>Any help or a good link is welcome.|||On 31 okt, 14:03, "Roy Harvey (SQL Server MVP)" <roy_har...@.snet.net>
wrote:
> Performance is not generally a function of the number of tables or
> views. With a good database design the limiting factors are more the
> number of concurrent users and transactions, as well as table size and
> database size.
> 100-200 tables with 50 views, those numbers are not a challenge at
> all. How any gigabytes of data do you anticipate? How many users? Is
> it transaction processing or reporting?
> Roy Harvey
> Beacon Falls, CT
>
> On Wed, 31 Oct 2007 03:20:33 -0700, HDI <hd...@.hotmail.com> wrote:
> >Hi,
> >I'm making a study about pro en contra for central and separated
> >databases in one sql server.
> >I googled and found some interesting opinions but most of the are
> >about management.
> >But what about performance, what if a database has 100-200 tables and
> >50 views and keeps growing.
> >Can Sql server handle this?
> >What about restore?
> >What about (I'm missing)...
> >Any help or a good link is welcome.- Tekst uit oorspronkelijk bericht niet weergeven -
> - Tekst uit oorspronkelijk bericht weergeven -
It's should be processing and reporting.
The users are now 50 but keeps growing like all the rest of course.
100-200 tables now are 200-300 tables next year ...|||On Mon, 05 Nov 2007 04:12:50 -0800, HDI <hdinf@.hotmail.com> wrote:
>On 31 okt, 14:03, "Roy Harvey (SQL Server MVP)" <roy_har...@.snet.net>
>wrote:
>> 100-200 tables with 50 views, those numbers are not a challenge at
>> all. How any gigabytes of data do you anticipate? How many users? Is
>> it transaction processing or reporting?
>It's should be processing and reporting.
>The users are now 50 but keeps growing like all the rest of course.
>100-200 tables now are 200-300 tables next year ...
You still did not provide the size estimate (gigabytes).
From what you have mentioned so far there is no reason related to size
or performance that would suggest needing more than a single database.
The one really good reason for considering multiple databases that
always applies regardless of how small the set of data or the number
of users is that the data is unrelated. The company's internal
phonebook probably should not be in the same database as the accounts
payable.
Roy Harvey
Beacon Falls, CT|||On 5 nov, 17:42, "Roy Harvey (SQL Server MVP)" <roy_har...@.snet.net>
wrote:
> On Mon, 05 Nov 2007 04:12:50 -0800, HDI <hd...@.hotmail.com> wrote:
> >On 31 okt, 14:03, "Roy Harvey (SQL Server MVP)" <roy_har...@.snet.net>
> >wrote:
> >> 100-200 tables with 50 views, those numbers are not a challenge at
> >> all. How any gigabytes of data do you anticipate? How many users? Is
> >> it transaction processing or reporting?
> >It's should be processing and reporting.
> >The users are now 50 but keeps growing like all the rest of course.
> >100-200 tables now are 200-300 tables next year ...
> You still did not provide the size estimate (gigabytes).
> From what you have mentioned so far there is no reason related to size
> or performance that would suggest needing more than a single database.
> The one really good reason for considering multiple databases that
> always applies regardless of how small the set of data or the number
> of users is that the data is unrelated. The company's internal
> phonebook probably should not be in the same database as the accounts
> payable.
> Roy Harvey
> Beacon Falls, CT
Now +- 1GB.
>>The company's internal phonebook probably should not be in the same database as the accounts payable.
And what if you want a base table for common thing like name,
address,nationality... or a postal code table (wich will be used in
more than one database).
Maybe yours isn't a good exapmle but what about following situation:
Employee database, intranet database, room reservation (for employees
and others),...
People can be in all of the databases.
So shouldn't it be better in this situation to create a general
database for the table people (and use a trigger to check te
integrity).|||On Tue, 06 Nov 2007 06:30:43 -0800, HDI <hdinf@.hotmail.com> wrote:
>Now +- 1GB.
>>The company's internal phonebook probably should not be in the same database as the accounts payable.
>And what if you want a base table for common thing like name,
>address,nationality... or a postal code table (wich will be used in
>more than one database).
>Maybe yours isn't a good exapmle but what about following situation:
>Employee database, intranet database, room reservation (for employees
>and others),...
>People can be in all of the databases.
>So shouldn't it be better in this situation to create a general
>database for the table people (and use a trigger to check te
>integrity).
1 GB is small. These days 50GB is not large. You need not consider
multiple databases because of size.
If you are going to have the same entities for multiple applications
then integrity should be enforced through foreign key constrains, and
that requires that they be in the same database. So yes, if
applications are going to be integrated to that degree that justifies
keeping them in a single database.
Roy Harvey
Beacon Falls, CT|||On Tue, 06 Nov 2007 06:30:43 -0800, HDI wrote:
(snip)
>Maybe yours isn't a good exapmle but what about following situation:
>Employee database, intranet database, room reservation (for employees
>and others),...
>People can be in all of the databases.
>So shouldn't it be better in this situation to create a general
>database for the table people (and use a trigger to check te
>integrity).
Hi HDI,
Employee data, intranet, and room reservations are all part of managing
the internals of a company. I would never spread these tables out over
multiple databases.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||What you have described is a very small database these days.
But, if you are going to grow it by over 200 tables, in 1 year,
it sounds like you have a design problem. Nothing you
have described needs 200 tablesto accomplish properly.
Robert
On Nov 6, 8:30 am, HDI <hd...@.hotmail.com> wrote:
> Now +- 1GB.
> >>The company's internal phonebook probably should not be in the same database as the accounts payable.
> And what if you want a base table for common thing like name,
> address,nationality... or a postal code table (wich will be used in
> more than one database).
> Maybe yours isn't a good exapmle but what about following situation:
> Employee database, intranet database, room reservation (for employees
> and others),...
> People can be in all of the databases.
> So shouldn't it be better in this situation to create a general
> database for the table people (and use a trigger to check te
> integrity).- Hide quoted text -
> - Show quoted text -|||On 8 nov, 20:03, raibeart <raibe...@.gmail.com> wrote:
> What you have described is a very small database these days.
> But, if you are going to grow it by over 200 tables, in 1 year,
> it sounds like you have a design problem. Nothing you
> have described needs 200 tablesto accomplish properly.
> Robert
> On Nov 6, 8:30 am, HDI <hd...@.hotmail.com> wrote:
>
>
> > Now +- 1GB.
> > >>The company's internal phonebook probably should not be in the same database as the accounts payable.
> > And what if you want a base table for common thing like name,
> > address,nationality... or a postal code table (wich will be used in
> > more than one database).
> > Maybe yours isn't a good exapmle but what about following situation:
> > Employee database, intranet database, room reservation (for employees
> > and others),...
> > People can be in all of the databases.
> > So shouldn't it be better in this situation to create a general
> > database for the table people (and use a trigger to check te
> > integrity).- Hide quoted text -
> > - Show quoted text -- Tekst uit oorspronkelijk bericht niet weergeven -
> - Tekst uit oorspronkelijk bericht weergeven -
Because this are only a few of the project for the comming 5 years.