Best Practices for Large Database

not sure I am enough of a DBA to give you great answers - check out
http://www.sql-server-performance.com/

and http://vyaskn.tripod.com/sqlserverres.htm for more links
--
HTH,
David Satz
Principal Web Engineer
Hyperion Solutions

"MiGuy" <hendry@blg.co.id> wrote in message
news:3dca0462$1@tnews.web.dev-archive.com...
>
> Hi All,
>
> I'm currently in a design phase of my enterprise-level application. The
database
> for this application will be loaded with a bunch of data and there's also
> a heavy load for the database.
>
> To be honest, I have no experience for dealing with Large Databases. I'm
> really concern about performance issue. For best perf, should I split my
> database into several smaller database? I've no idea about this one but it
> seems will make my queries become more complex. Also, should I split the
> data file (.mdf) and the log file (.ldf) into several files?
>
> Please give me some advice for dealing with Large Database, Any ideas will
> be greatly appreciated.
>
> Thanks in advance
>
> Hendry
>
>

MiGuy,

What do you define as "large"? 10GB, 100GB, 1TB, 10TB? What hardware are you
running on? What "sort" of db is it? OLTP, MI?

> I'm currently in a design phase of my enterprise-level application. The database
> for this application will be loaded with a bunch of data and there's also
> a heavy load for the database.
>
> To be honest, I have no experience for dealing with Large Databases. I'm
> really concern about performance issue. For best perf, should I split my
> database into several smaller database? I've no idea about this one but it
> seems will make my queries become more complex. Also, should I split the
> data file (.mdf) and the log file (.ldf) into several files?
>
> Please give me some advice for dealing with Large Database, Any ideas will
> be greatly appreciated.
>
> Thanks in advance
>
> Hendry
>

Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
(faqxxx.zip in lib 7)
or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq

Hi guys,

Sorry for taking so long to reply.

The Large DB I'm about to design is a DB that will have a heavy workload
(many concurrencies), but I'm not pretty sure about its exact size, I think
its size will grow up to 10 GB for a 1 year period as it will hold all the
past data (importing from clipper) and also the new data as well.

I'm not sure how to define this DB (OLTP or not) since it always deal with
daily transaction and also some application will produce many reports over
the past data. I have no experience in OLAP & its programming and am about
to learn it now. I always have a question in my mind, should I separate some
tables which deal with daily trans from other tables which just hold past
data (for reporting purpose).

I'd like to create two identical DBs, one for dealing with daily trans and
the other will hold past data. After some period of time (perhaps 3 months),
the data from heavy trans DB will be moved off to the other DB. But with
this design, I'm worry it will make my query become more complex, because
it's possible that at any point, my app would need to get the data from both
of the DB. (any ideas ?)

Please give me some advice on this.

Anyway, Thanks Mr. Buttles, I'm curently doing some research on RAID and
I'll try your RAID advice.

Thanks for any help

hendry

"C. E. Buttles" <enterprise.@127.0.0.1> wrote:
>
>Mr. Pike is right, more information is needed. The actual size of the DB,
>the hardware (and its configuration), file locations, memory allocation,
>indexing, type of dataload (and size) are just a part of what needs to be
>known UPFRONT.
>
>Spreading the database files over several drives can be expensive, but large
>corporations may go for it if the business case is convincing. Remember
>that you will need to put them on unrelated drives (different RAID5 drive
>arrays would be best for data files). Log files can be placed on mirrored
>(RAID1) drives as these usually have higer I/O needs.
>
>Check the Microsoft SQL Server site and look in the white papers. There
>is one there that covers all of this. It fills a 3" ring binder nicely.
>
>You are right to ask about this. Too many application developers don't
even
>think about it and it is difficult to impossible to fix after-the-fact.
>
>
>Neil Pike <neilpike@compuserve.com> wrote:
>> MiGuy,
>>
>> What do you define as "large"? 10GB, 100GB, 1TB, 10TB? What hardware
>are you
>>running on? What "sort" of db is it? OLTP, MI?
>>
>>> I'm currently in a design phase of my enterprise-level application. The
>database
>>> for this application will be loaded with a bunch of data and there's
also
>>> a heavy load for the database.
>>>
>>> To be honest, I have no experience for dealing with Large Databases.
I'm
>>> really concern about performance issue. For best perf, should I split
>my
>>> database into several smaller database? I've no idea about this one but
>it
>>> seems will make my queries become more complex. Also, should I split
the
>>> data file (.mdf) and the log file (.ldf) into several files?
>>>
>>> Please give me some advice for dealing with Large Database, Any ideas
>will
>>> be greatly appreciated.
>>>
>>> Thanks in advance
>>>
>>> Hendry
>>>
>>
>> Neil Pike MVP/MCSE. Protech Computing Ltd
>> Reply here - no email
>> SQL FAQ (484 entries) see
>> http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
>> (faqxxx.zip in lib 7)
>> or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
>> or www.sqlserverfaq.com
>> or www.mssqlserver.com/faq
>>
>

Neil, Thanks very much for your valuable input. As I'm not really sure how
big my database is, I'll always keep it in mind.

Hendry

Neil Pike <neilpike@compuserve.com> wrote:
> MIGuy,
>
>> The Large DB I'm about to design is a DB that will have a heavy workload
>> (many concurrencies), but I'm not pretty sure about its exact size, I
think
>> its size will grow up to 10 GB for a 1 year period as it will hold all
the
>> past data (importing from clipper) and also the new data as well.
>
> It's all relative, but 10GB is not big IMHO, not even close.
>
>> I'm not sure how to define this DB (OLTP or not) since it always deal
with
>> daily transaction and also some application will produce many reports
over
>> the past data. I have no experience in OLAP & its programming and am about
>> to learn it now. I always have a question in my mind, should I separate
some
>> tables which deal with daily trans from other tables which just hold past
>> data (for reporting purpose).
>>
>> I'd like to create two identical DBs, one for dealing with daily trans
and
>> the other will hold past data. After some period of time (perhaps 3 months),
>> the data from heavy trans DB will be moved off to the other DB. But with
>> this design, I'm worry it will make my query become more complex, because
>> it's possible that at any point, my app would need to get the data from
both
>> of the DB. (any ideas ?)
>
> If the database were 500GB or above, then this solution would be reasonable,

>and it's one I've used before. Typically I would give the users a different

>"read-only" interface to the old data.
>
> But unless the data is likely to grow well above 10GB I wouldn't worry
about
>doing this.
>
> Neil Pike MVP/MCSE. Protech Computing Ltd
> Reply here - no email
> SQL FAQ (484 entries) see
> http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
> (faqxxx.zip in lib 7)
> or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> or www.sqlserverfaq.com
> or www.mssqlserver.com/faq
>