|
Home > Archive > microsoft.public.sqlserver.server > August 2002 > Multiple small databases vs one large database
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Multiple small databases vs one large database
|
|
| Greg Milne 2002-08-13, 4:23 am |
| Hi,
Apologies if this question sounds naive but I am very new to MS-SQL.
We are embarking on a large project which will ultimately consist of a large
number of tables (I would guess in the order of a couple of hundred). The
project will consist of several self contained but related modules.
My question is: Is there any benefit in partitioning the database into
several smaller databases as opposed to one large database? Would this cause
any problems when we need to query two tables from different databases?
We are using ADO via Delphi to access the database just in case this has any
bearing on the answer.
thanks in advance,
Greg Milne,
Zonal Retail Data Systems.
| |
| Zenduce 2002-08-13, 4:23 am |
| The aim is to make your SQL database administration mininal as possible.
Depends if you are going to break the databases into departments / companies
etc...
If you are going to design a system for one department /company, create one
user database. Make sure you have a good spec server with RAID and you have
good back/ maintenaince plan in place.
a couple hundred of tables in one database is not big in SQL terms, but
would be advisable to index the tables for fast read/write operations.
"Greg Milne" <greg@zonal.co.uk> wrote in message
news:uySLm1qQCHA.2024@tkmsftngp10...
> Hi,
>
> Apologies if this question sounds naive but I am very new to MS-SQL.
>
> We are embarking on a large project which will ultimately consist of a
large
> number of tables (I would guess in the order of a couple of hundred). The
> project will consist of several self contained but related modules.
>
> My question is: Is there any benefit in partitioning the database into
> several smaller databases as opposed to one large database? Would this
cause
> any problems when we need to query two tables from different databases?
>
> We are using ADO via Delphi to access the database just in case this has
any
> bearing on the answer.
>
> thanks in advance,
> Greg Milne,
> Zonal Retail Data Systems.
>
>
>
| |
| lindawie 2002-08-13, 8:23 am |
| Greg,
> We are embarking on a large project which will ultimately consist of
> a large number of tables (I would guess in the order of a couple of
> hundred). The project will consist of several self contained but
> related modules.
>
> My question is: Is there any benefit in partitioning the database into
> several smaller databases as opposed to one large database? Would
> this cause any problems when we need to query two tables from
> different databases?
If the tables are related, put them all together in one database.
Foreign key constraints cannot span databases.
The Index Tuning Wizard cannot look across databases.
A single backup is easier to manage than trying to keep several
backups in sync.
Linda
| |
| Geoff N. Hiten 2002-08-13, 8:23 am |
| Just to put my two cents worth in.
Look at the data that has to remain internally consistant. That is tables
that will have DRI relationships or will have transactions involving
multiple tables. These need to be in the same database for transactional
consistancy in case of a failure that requires restoring data from backup.
Under versions prior to SQL 2000, multiple databases gave some I/O
performance benefit, but no longer.
If your database is likely to get over 40GB or so, you might look at
partitioning it into multiple filegroups for quicker backup and restore.
40Gb is my personal choice, other DBAs might have different recommended file
sizes.
If some of your data is logging type data, look at breaking it up into
Year-Month (YOMO) tables and use a view to unify the tables or let the
application do it. This gets workwhile if your rows/month climbs over 500k
or so. It is essential if your rows/month exceeds 4M. Having each YOMO
table set in its own filegroup also helps a lot.
--
Geoff N. Hiten
Senior Database Administrator
Careerbuilder.com
"Zenduce" <james@zenduce.net> wrote in message
news:erZMd9qQCHA.3664@tkmsftngp11...
> The aim is to make your SQL database administration mininal as possible.
> Depends if you are going to break the databases into departments /
companies
> etc...
>
> If you are going to design a system for one department /company, create
one
> user database. Make sure you have a good spec server with RAID and you
have
> good back/ maintenaince plan in place.
>
> a couple hundred of tables in one database is not big in SQL terms, but
> would be advisable to index the tables for fast read/write operations.
>
>
>
> "Greg Milne" <greg@zonal.co.uk> wrote in message
> news:uySLm1qQCHA.2024@tkmsftngp10...
> > Hi,
> >
> > Apologies if this question sounds naive but I am very new to MS-SQL.
> >
> > We are embarking on a large project which will ultimately consist of a
> large
> > number of tables (I would guess in the order of a couple of hundred).
The
> > project will consist of several self contained but related modules.
> >
> > My question is: Is there any benefit in partitioning the database into
> > several smaller databases as opposed to one large database? Would this
> cause
> > any problems when we need to query two tables from different databases?
> >
> > We are using ADO via Delphi to access the database just in case this has
> any
> > bearing on the answer.
> >
> > thanks in advance,
> > Greg Milne,
> > Zonal Retail Data Systems.
> >
> >
> >
>
>
| |
| Greg Milne 2002-08-14, 8:23 am |
| Thanks to all who replied!
Your comments helped us to decide that one single database most suits our
needs.
Greg Milne,
Zonal Retail Data Systems
|
|
|
|
|