Advice about database deployment

Hello All,
I am writing a database application that will serve multiple companies,
each company contains users that will store/retrieve data from the database.
I am in the process of deciding whether I should have one big database
that stores data for all users of all companies or have a separate
database for each company.
What would be the advantage/disadvantage of selecting one over the other
with the following considerations?
Considerations:
1. failover support
2. performance
3. load balancing via adding multiple database servers
4. clustering
5. backup (full and incremental)
6. database upgrade (eg. adding more tables and relationships, modifying
tables and relationships)
7. special equipment considerations
Any advised would be greatly appreciated.
Thanks in advanced
Hi
Both methods have their advantages and disadvantages so you will have to
weigh up the pros and cons to make a discission. Having a single database may
be ruled out because of security reasons (in fact having databases on the
same instance may be ruled out!) , but it will ease the management and
upgrades. Having single databases will allow you to tailor the databases to
specific requirements and (say) adjust intensive tasks so they don't overload
the system e.g. spread out the backups and other maintenance tasks. Having
separate databases would potentially limit the scope of any mistake (e.g. if
someone managed to take an exclusive lock on a table the impact would be
within a single company not other possibly unrelated companies who could sue
you!).
Hardware clustering should not be affected by either configuration, although
if you are using SQL 2005 and database mirroring you could fail over one
different databases separately and mix the primary instances in similar way
to having multiple instance on a clustered server.
HTH
John
"buzlite@.sympatico.ca" wrote:

> Hello All,
> I am writing a database application that will serve multiple companies,
> each company contains users that will store/retrieve data from the database.
> I am in the process of deciding whether I should have one big database
> that stores data for all users of all companies or have a separate
> database for each company.
> What would be the advantage/disadvantage of selecting one over the other
> with the following considerations?
> Considerations:
> --
> 1. failover support
> 2. performance
> 3. load balancing via adding multiple database servers
> 4. clustering
> 5. backup (full and incremental)
> 6. database upgrade (eg. adding more tables and relationships, modifying
> tables and relationships)
> 7. special equipment considerations
> Any advised would be greatly appreciated.
> Thanks in advanced
>
|||Hi
In addition to John's reply ,I'd ask you ,how big will be databases? Have
you considered to install SQL Server 2005 Express Edition on the company's
site?
It depends on your business requirement , thus there is no common scenario
<buzlite@.sympatico.ca> wrote in message
news:%23Atf%236kCHHA.3916@.TK2MSFTNGP06.phx.gbl...
> Hello All,
> I am writing a database application that will serve multiple companies,
> each company contains users that will store/retrieve data from the
> database.
> I am in the process of deciding whether I should have one big database
> that stores data for all users of all companies or have a separate
> database for each company.
> What would be the advantage/disadvantage of selecting one over the other
> with the following considerations?
> Considerations:
> --
> 1. failover support
> 2. performance
> 3. load balancing via adding multiple database servers
> 4. clustering
> 5. backup (full and incremental)
> 6. database upgrade (eg. adding more tables and relationships, modifying
> tables and relationships)
> 7. special equipment considerations
> Any advised would be greatly appreciated.
> Thanks in advanced
|||On Fri, 17 Nov 2006 08:46:13 -0500, "buzlite@.sympatico.ca"
<buzlite@.sympatico.ca> wrote:

>Hello All,
>I am writing a database application that will serve multiple companies,
>each company contains users that will store/retrieve data from the database.
>I am in the process of deciding whether I should have one big database
>that stores data for all users of all companies or have a separate
>database for each company.
>What would be the advantage/disadvantage of selecting one over the other
>with the following considerations?
>Considerations:
>--
>1. failover support
>2. performance
>3. load balancing via adding multiple database servers
>4. clustering
>5. backup (full and incremental)
>6. database upgrade (eg. adding more tables and relationships, modifying
>tables and relationships)
>7. special equipment considerations
>Any advised would be greatly appreciated.
>Thanks in advanced
* Impact of company-X on the performance of company-Y's operations.
Given a choice like this, perhaps the answer is: both.
Write it for one database for better sharing, it's probably easier to
un-share later, than to add the sharing. Easier to do any
consolidations, when they're on one database, of course.
Then you can deploy it all on one system/instance or on N
systems/instances, if one of the big companies decides they want their
own servers after all.
J.
|||I would say that the data and amount of data from one company could be
pretty large. Given that images can be uploaded to the database in
addition to text.
Given a previous reply to this posting and the fact that large amounts
of data can be produce by any or all given companies, the performance
degradation related factor is indeed a point that needs to be considered.
Thanks
Uri Dimant wrote:
> Hi
> In addition to John's reply ,I'd ask you ,how big will be databases? Have
> you considered to install SQL Server 2005 Express Edition on the company's
> site?
> It depends on your business requirement , thus there is no common scenario
>
>
> <buzlite@.sympatico.ca> wrote in message
> news:%23Atf%236kCHHA.3916@.TK2MSFTNGP06.phx.gbl...
>
|||To Add to the original post, what would the impact of using one database
for all companies and one database instance for each company as it
relates to the following:
Full backups
Transaction log backups
Rebuilding indexes
Integrity Check
buzlite@.sympatico.ca wrote:
> Hello All,
> I am writing a database application that will serve multiple companies,
> each company contains users that will store/retrieve data from the
> database.
> I am in the process of deciding whether I should have one big database
> that stores data for all users of all companies or have a separate
> database for each company.
> What would be the advantage/disadvantage of selecting one over the other
> with the following considerations?
> Considerations:
> --
> 1. failover support
> 2. performance
> 3. load balancing via adding multiple database servers
> 4. clustering
> 5. backup (full and incremental)
> 6. database upgrade (eg. adding more tables and relationships, modifying
> tables and relationships)
> 7. special equipment considerations
> Any advised would be greatly appreciated.
> Thanks in advanced