One big database vs. many small  
Author Message
JenEx





PostPosted: Thu Sep 18 00:41:01 CDT 2003 Top

SQL Server Developer >> One big database vs. many small

Hi all

My company is going to implement a central database (over Web services) for
about 400 shops. We use SQL server 2000 as DBMS.
We need to choose between 2 confugurations:

1. One big database for all clients, with one table-set, where each table
has a column "ShopId" to identyfy the owner of the row.

2. On the central server, create a separate database for each shop.

Details:
-------
Each shop has 10 users in average.
Average data for one shop is about 500MB.

Can anyone give me pros and cons for each configuration
(security,performance, maintainability, resources, flexibility etc.) ?

TIA
Boaz Ben-Porat
DataPharm a/s
Denmark

SQL Server88  
 
 
Uri





PostPosted: Thu Sep 18 00:41:01 CDT 2003 Top

SQL Server Developer >> One big database vs. many small Hi, Boaz (shalom)
The biggest reason you would want one database instead of multiple databases
is to take advantage of SQL Server's ability to cache data, such as the data
used in lookup tables. If you use multiple databases instead of a single
database, then each database would have to cache the same lookup tables,
over and over for each database. The same applies to common stored
procedures used in each database. This would present an inefficient use of
SQL Server's memory and could potentially reduce performance.





> Hi all
>
> My company is going to implement a central database (over Web services)
for
> about 400 shops. We use SQL server 2000 as DBMS.
> We need to choose between 2 confugurations:
>
> 1. One big database for all clients, with one table-set, where each table
> has a column "ShopId" to identyfy the owner of the row.
>
> 2. On the central server, create a separate database for each shop.
>
> Details:
> -------
> Each shop has 10 users in average.
> Average data for one shop is about 500MB.
>
> Can anyone give me pros and cons for each configuration
> (security,performance, maintainability, resources, flexibility etc.) ?
>
> TIA
> Boaz Ben-Porat
> DataPharm a/s
> Denmark
>
>


 
 
Boaz





PostPosted: Thu Sep 18 02:17:15 CDT 2003 Top

SQL Server Developer >> One big database vs. many small Hi Uri (shalom to you too)

Can you tell me what the result (which database cache is used) in the
following situation:

1. Each client has it`s own database with client-specific data, called myDb.
1. One common database holds all common data (lookup tables etc.) and stored
procedures, called commonDb.

database calls from each client are in the form:

Example1 - join tables from 2 databases:

SELECT myDb.Table1.Field1, commonDb.LookupTable1.Field1
WHERE <some condition>

Example2 - call stored proc in another database:

EXEC commonDb.StoredProc1

The advantages of using many small DBs are (as I see):
1. We get rid of the extra "ShopId" column in all tables.
2. Easier to Identity columns.
3. Easier to maintain/rescue/upgrade a client`s database without shutting
down all other clients.

So, if we can solve the problem with cache other resources utilization, we
can make our life better.

TIA for any answer.

Boaz Ben-Porat
DataPharm a/s
Denmark







> Hi, Boaz (shalom)
> The biggest reason you would want one database instead of multiple
databases
> is to take advantage of SQL Server's ability to cache data, such as the
data
> used in lookup tables. If you use multiple databases instead of a single
> database, then each database would have to cache the same lookup tables,
> over and over for each database. The same applies to common stored
> procedures used in each database. This would present an inefficient use of
> SQL Server's memory and could potentially reduce performance.
>
>
>


> > Hi all
> >
> > My company is going to implement a central database (over Web services)
> for
> > about 400 shops. We use SQL server 2000 as DBMS.
> > We need to choose between 2 confugurations:
> >
> > 1. One big database for all clients, with one table-set, where each
table
> > has a column "ShopId" to identyfy the owner of the row.
> >
> > 2. On the central server, create a separate database for each shop.
> >
> > Details:
> > -------
> > Each shop has 10 users in average.
> > Average data for one shop is about 500MB.
> >
> > Can anyone give me pros and cons for each configuration
> > (security,performance, maintainability, resources, flexibility etc.) ?
> >
> > TIA
> > Boaz Ben-Porat
> > DataPharm a/s
> > Denmark
> >
> >
>
>


 
 
Uri





PostPosted: Thu Sep 18 07:30:50 CDT 2003 Top

SQL Server Developer >> One big database vs. many small Hi,Boaz
> The advantages of using many small DBs are (as I see):
> 1. We get rid of the extra "ShopId" column in all tables.
> 2. Easier to Identity columns.
> 3. Easier to maintain/rescue/upgrade a client`s database without shutting
> down all other clients.

1)First ,If you have one big database don't you get the extra "ShopID"
column ?
You will be need to store shopID (unique) within one table .

2) Did you mean to put Identity property on the column? Or just Identify
unique values?
3) Well, you need to write a job that backups all your databases ,you will
need to store it on the disk ( and it is matter of time ,database growh up
every time)
Look ,I think you are going to make your server very busy and it will reduce
a performance issue.
Just thinking about for the system to keep all info about a lot of databases
or for only one?






> Hi Uri (shalom to you too)
>
> Can you tell me what the result (which database cache is used) in the
> following situation:
>
> 1. Each client has it`s own database with client-specific data, called
myDb.
> 1. One common database holds all common data (lookup tables etc.) and
stored
> procedures, called commonDb.
>
> database calls from each client are in the form:
>
> Example1 - join tables from 2 databases:
>
> SELECT myDb.Table1.Field1, commonDb.LookupTable1.Field1
> WHERE <some condition>
>
> Example2 - call stored proc in another database:
>
> EXEC commonDb.StoredProc1
>
> The advantages of using many small DBs are (as I see):
> 1. We get rid of the extra "ShopId" column in all tables.
> 2. Easier to Identity columns.
> 3. Easier to maintain/rescue/upgrade a client`s database without shutting
> down all other clients.
>
> So, if we can solve the problem with cache other resources utilization, we
> can make our life better.
>
> TIA for any answer.
>
> Boaz Ben-Porat
> DataPharm a/s
> Denmark
>
>
>
>
>


> > Hi, Boaz (shalom)
> > The biggest reason you would want one database instead of multiple
> databases
> > is to take advantage of SQL Server's ability to cache data, such as the
> data
> > used in lookup tables. If you use multiple databases instead of a single
> > database, then each database would have to cache the same lookup tables,
> > over and over for each database. The same applies to common stored
> > procedures used in each database. This would present an inefficient use
of
> > SQL Server's memory and could potentially reduce performance.
> >
> >
> >


> > > Hi all
> > >
> > > My company is going to implement a central database (over Web
services)
> > for
> > > about 400 shops. We use SQL server 2000 as DBMS.
> > > We need to choose between 2 confugurations:
> > >
> > > 1. One big database for all clients, with one table-set, where each
> table
> > > has a column "ShopId" to identyfy the owner of the row.
> > >
> > > 2. On the central server, create a separate database for each shop.
> > >
> > > Details:
> > > -------
> > > Each shop has 10 users in average.
> > > Average data for one shop is about 500MB.
> > >
> > > Can anyone give me pros and cons for each configuration
> > > (security,performance, maintainability, resources, flexibility etc.) ?
> > >
> > > TIA
> > > Boaz Ben-Porat
> > > DataPharm a/s
> > > Denmark
> > >
> > >
> >
> >
>
>


 
 
Boaz





PostPosted: Thu Sep 18 07:35:12 CDT 2003 Top

SQL Server Developer >> One big database vs. many small Hi, Uri

1. - 2. "ShopId" and IDENTITY columns:
In a database for a single shop, table "Orders" looks like:

OrderID int IDENTITY (1, 1) NOT NULL ,
CustomerID nchar (5),
... < more columns>

If the the database holds data for all shops, there is only one "Orders"
table, looks like:

OrderID int IDENTITY (1, 1) NOT NULL ,
ShopID int NOT NULL, // ******** must identify the owner of this
order.
CustomerID nchar (5),
... < more columns>

3. Maintainace
Pseudocode for backup procedure

- One (big) database:
Backup Databes MyBigDatabase to C:\DB_BACKUP\MyBigDatabase.bck

- Separate database for each shop:
foreach ($Shop in <list-of-shops-in-config-file>)
{
Backup Databes "$Shop + "Db" to C:\DB_BACKUP\"$Shop + "Db.bck"
}

- Disk usage:
The amount of data is (almost) the same, just spread over many small
databases

- Error/Crash situation:
Crash/repair of one database doesn`t stop all others shops.

- Programming:
I admit: it`s mpre complex to remember which tables reside in shop`s DB and
which on common DB.

***** PERFORMANCE *****
How would each configuration (one big DB vs. many small) affect cach and
other resources utilization on a large SQL Server installation like ours ?

TIA
Boaz Ben-Porat





> Hi,Boaz
> > The advantages of using many small DBs are (as I see):
> > 1. We get rid of the extra "ShopId" column in all tables.
> > 2. Easier to Identity columns.
> > 3. Easier to maintain/rescue/upgrade a client`s database without
shutting
> > down all other clients.
>
> 1)First ,If you have one big database don't you get the extra "ShopID"
> column ?
> You will be need to store shopID (unique) within one table .
>
> 2) Did you mean to put Identity property on the column? Or just Identify
> unique values?
> 3) Well, you need to write a job that backups all your databases ,you
will
> need to store it on the disk ( and it is matter of time ,database growh up
> every time)
> Look ,I think you are going to make your server very busy and it will
reduce
> a performance issue.
> Just thinking about for the system to keep all info about a lot of
databases
> or for only one?
>
>
>
>


> > Hi Uri (shalom to you too)
> >
> > Can you tell me what the result (which database cache is used) in the
> > following situation:
> >
> > 1. Each client has it`s own database with client-specific data, called
> myDb.
> > 1. One common database holds all common data (lookup tables etc.) and
> stored
> > procedures, called commonDb.
> >
> > database calls from each client are in the form:
> >
> > Example1 - join tables from 2 databases:
> >
> > SELECT myDb.Table1.Field1, commonDb.LookupTable1.Field1
> > WHERE <some condition>
> >
> > Example2 - call stored proc in another database:
> >
> > EXEC commonDb.StoredProc1
> >
> > The advantages of using many small DBs are (as I see):
> > 1. We get rid of the extra "ShopId" column in all tables.
> > 2. Easier to Identity columns.
> > 3. Easier to maintain/rescue/upgrade a client`s database without
shutting
> > down all other clients.
> >
> > So, if we can solve the problem with cache other resources utilization,
we
> > can make our life better.
> >
> > TIA for any answer.
> >
> > Boaz Ben-Porat
> > DataPharm a/s
> > Denmark
> >
> >
> >
> >
> >


> > > Hi, Boaz (shalom)
> > > The biggest reason you would want one database instead of multiple
> > databases
> > > is to take advantage of SQL Server's ability to cache data, such as
the
> > data
> > > used in lookup tables. If you use multiple databases instead of a
single
> > > database, then each database would have to cache the same lookup
tables,
> > > over and over for each database. The same applies to common stored
> > > procedures used in each database. This would present an inefficient
use
> of
> > > SQL Server's memory and could potentially reduce performance.
> > >
> > >
> > >


> > > > Hi all
> > > >
> > > > My company is going to implement a central database (over Web
> services)
> > > for
> > > > about 400 shops. We use SQL server 2000 as DBMS.
> > > > We need to choose between 2 confugurations:
> > > >
> > > > 1. One big database for all clients, with one table-set, where each
> > table
> > > > has a column "ShopId" to identyfy the owner of the row.
> > > >
> > > > 2. On the central server, create a separate database for each shop.
> > > >
> > > > Details:
> > > > -------
> > > > Each shop has 10 users in average.
> > > > Average data for one shop is about 500MB.
> > > >
> > > > Can anyone give me pros and cons for each configuration
> > > > (security,performance, maintainability, resources, flexibility etc.)
?
> > > >
> > > > TIA
> > > > Boaz Ben-Porat
> > > > DataPharm a/s
> > > > Denmark
> > > >
> > > >
> > >
> > >
> >
> >
>
>


 
 
Joe





PostPosted: Thu Sep 18 10:13:04 CDT 2003 Top

SQL Server Developer >> One big database vs. many small Think about the data model. If all the shops are totally independent of
each other, sharing no data, no common accounting system, no common
ordering system, etc. then you use one database per shop.

Even if these DBs start off the same, they will all be slightly
different in about a year, so you will spend huge amounts of time if you
have to integrate their data. Ask anyone who used to work with file
systems about "data drift" and why we went to centralized databases.

If the shops are part of the same company, with common accounting,
ordering, etc. then they are modeled as one enterprise, in one database.

Get the data model right, then worry about the implementation.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!