Schema vs separate database  
Author Message
hannee





PostPosted: Thu Mar 23 04:14:29 CST 2006 Top

SQL Server >> Schema vs separate database

Hi,
This is an architecture related question. The context is the development of
a project with a SOA - Service Oriented Architecture. There are several
service providers in the project each being the owner of a disjoint database
(i.e. group of database objects). There is a single redundant database
server machine in this project.

I would like to share opinions regarding the merits of using either separate
schemas in 1 database or using separate databases altogether for each of the
service provider. There is also the need to share some common tables across
all of the providers.

The evaluation criteria that I considered so far are the following:

a.. Simplicity: single database with 1 schema per service provider.
b.. Security: appears to be equal complexity with the need to control
access to individual schemas in one case and to individual databases in the
other.
c.. Performance: any comment here is welcome.
d.. Resources needed: any comment here is welcome.
e.. Availability: this would suggest the separate database approach where
a potential corruption / problem affecting 1 database file would only bring
down 1 service provider.
f.. Scalability: this would also suggest the separate database approach
where they could later easily be distributed on separate database server
machines if needs be.
Thank you in advance for your comments.

SQL Server233  
 
 
stcheng





PostPosted: Thu Mar 23 04:14:29 CST 2006 Top

SQL Server >> Schema vs separate database Hi Patrick,

Welcome to MSDN newsgroup.

Regarding on the database deployment question, IMO, if those different
database are not tool tight coupled, and your actual hardware environment
is powerful enough, of course I'd prefer deploying them to multiple servers
to a single server(with multiple schemas). That can help improve the whole
application/service's performance(data read/write) and the availability or
reliability...


Regards,

Steven Cheng
Microsoft Online Community Support


==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may

learn and benefit from your issue.

==================================================


This posting is provided "AS IS" with no warranties, and confers no rights.

 
 
SQLpro





PostPosted: Thu Mar 23 07:04:08 CST 2006 Top

SQL Server >> Schema vs separate database Patrick Cournoyer a écrit :
> Hi,
> This is an architecture related question. The context is the development of
> a project with a SOA - Service Oriented Architecture. There are several
> service providers in the project each being the owner of a disjoint database
> (i.e. group of database objects). There is a single redundant database
> server machine in this project.
>
> I would like to share opinions regarding the merits of using either separate
> schemas in 1 database or using separate databases altogether for each of the
> service provider. There is also the need to share some common tables across
> all of the providers.
>
> The evaluation criteria that I considered so far are the following:
>
> a.. Simplicity: single database with 1 schema per service provider.
> b.. Security: appears to be equal complexity with the need to control
> access to individual schemas in one case and to individual databases in the
> other.
> c.. Performance: any comment here is welcome.
> d.. Resources needed: any comment here is welcome.
> e.. Availability: this would suggest the separate database approach where
> a potential corruption / problem affecting 1 database file would only bring
> down 1 service provider.
> f.. Scalability: this would also suggest the separate database approach
> where they could later easily be distributed on separate database server
> machines if needs be.
> Thank you in advance for your comments.
>
>
schema is a logical concept and can have RI. A schema cannot be migrate
with data to another place.
catalog (wich in fact is the DB) is a physical concept (files) and
cannot have RI. A DB cans be easyly moved to another place.

RI : referential Integrity.

Do not use schema for DB !

A +


--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************