Database instance

In MS SQL you can create a database that is run in a seperate instance.

Why and when should a database run on a seperate instance ?
Benefits ?

What is prefered and why?
Two applications have it's own tables.
Appplication A have a set of tables.
Appplication B have a set of tables but also use tables from A
Should it run on different databases (DbA and DbB) and instance?
Or is it ok to let them use the same database (DbAB) ?

GTi  wrote:
> Why and when should a database run on a seperate instance ?
> Benefits ?

I think the main benefit is that it gives you the flexibility to
virtualise your servers so that you can maximise the return on your
server hardware investment. You can build database servers and
applications today that will be able to scale onto fewer physical
servers now or in the future without the constraint of one box = one db
server. Moore's law says that processor power doubles every 18 months
so if you run 20 server boxes today you may find you only need 10 or 15
to do the same work in two or three years time.

> What is prefered and why?
> Two applications have it's own tables.
> Appplication A have a set of tables.
> Appplication B have a set of tables but also use tables from A
> Should it run on different databases (DbA and DbB) and instance?
> Or is it ok to let them use the same database (DbAB) ?

It depends but here are some things to think about. The first practical
considerations are the needs of the data model (foreign keys can't
reference across databases for example) and the administrative
requirements. For example if you have distinct data sets that require
quite different backup or security regimes or different levels of
availability then those may be good reasons to put them in different
databases.

Beyond those considerations, separate databases by application or
business area or owner. Try to minimise dependencies between different
business units - the Sales department may not be happy if their
database has to go through a new release every time a new Marketing
application rolls out. On the other hand don't duplicate data
unnecessarily and do put some integration process in place to ensure
that common data can be shared where it does have to be duplicated.

These are all just suggestions. Your IT policies, organization and even
your company culture may determine what weight you give to any of these
factors.

Hope this helps.

--
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
--