sql server sizing planning - architecture help

[Ragu]

Hi

I joined a project where 100,000 rows were added everyday. Now due to
additional customers the expectation is 2 million reocrds/day ie 10 GB worth
of textfiles. We have to estimate the hard disk, memory, # of CPUs etc.We
will have one yearworth of data in the db. Rest will be in tapes etc.

We will be using WIN2000, SQL Server2000.- Any comparable server sizing will
be appreciated.

1. Tohandle every day load, I thought that we will have a table for each day
(pre created in the database )and have a view with union all selecting
fromall these 365 tables. (This is the only way to partition in MSSQL Server
right?).

2. The requirement is to populate datawarehouse tables with all the data.
However there will be only inserts mostly but there can be updates too which
happenned in the past 12 days.Hence we have to use the data from the last
12 days and massage it etc and populate into datawarehouse tables.

How can I do this so that I will have the datawarehouse tables with n-12
days of data and I will alwys add the last 12 days data to it.

Do you have any suggestions?

Ragu

[John Bell]

Re: sql server sizing planning - architecture help

Hi

Check out "Estimating the Size of a Database" in Books online:
mk:@MSITStore:C :\Program%20Fil es\Microsoft%20 SQL%20Server\80 \Tools\Books\cr e
atedb.chm::/cm_8_des_02_92k 3.htm

mk:@MSITStore:C :\Program%20Fil es\Microsoft%20 SQL%20Server\80 \Tools\Books\cr e
atedb.chm::/cm_8_des_02_33j s.htm

mk:@MSITStore:C :\Program%20Fil es\Microsoft%20 SQL%20Server\80 \Tools\Books\cr e
atedb.chm::/cm_8_des_02_224 8.htm


John

"Ragu" <ragudba@sbcglo bal.net> wrote in message
news:vgToc.1089 0$TC5.2751@news svr23.news.prod igy.com...[color=blue]
> Hi
>
> I joined a project where 100,000 rows were added everyday. Now due to
> additional customers the expectation is 2 million reocrds/day ie 10 GB[/color]
worth[color=blue]
> of textfiles. We have to estimate the hard disk, memory, # of CPUs etc.We
> will have one yearworth of data in the db. Rest will be in tapes etc.
>
> We will be using WIN2000, SQL Server2000.- Any comparable server sizing[/color]
will[color=blue]
> be appreciated.
>
> 1. Tohandle every day load, I thought that we will have a table for each[/color]
day[color=blue]
> (pre created in the database )and have a view with union all selecting
> fromall these 365 tables. (This is the only way to partition in MSSQL[/color]
Server[color=blue]
> right?).
>
> 2. The requirement is to populate datawarehouse tables with all the data.
> However there will be only inserts mostly but there can be updates too[/color]
which[color=blue]
> happenned in the past 12 days.Hence we have to use the data from the last
> 12 days and massage it etc and populate into datawarehouse tables.
>
> How can I do this so that I will have the datawarehouse tables with n-12
> days of data and I will alwys add the last 12 days data to it.
>
> Do you have any suggestions?
>
> Ragu
>
>
>[/color]

[John Bell]

Re: sql server sizing planning - architecture help

Hi

In addition....

On one of the CDs accompanying "Inside SQL Server 2000" by Kalen Delany ISBN
0-7356-0998-5, there is a stored procedure which will give you the size.

John

"John Bell" <jbellnewsposts @hotmail.com> wrote in message
news:7empc.92$M w2.5784667@news-text.cableinet. net...[color=blue]
> Hi
>
> Check out "Estimating the Size of a Database" in Books online:
>[/color]
mk:@MSITStore:C :\Program%20Fil es\Microsoft%20 SQL%20Server\80 \Tools\Books\cr e[color=blue]
> atedb.chm::/cm_8_des_02_92k 3.htm
>
>[/color]
mk:@MSITStore:C :\Program%20Fil es\Microsoft%20 SQL%20Server\80 \Tools\Books\cr e[color=blue]
> atedb.chm::/cm_8_des_02_33j s.htm
>
>[/color]
mk:@MSITStore:C :\Program%20Fil es\Microsoft%20 SQL%20Server\80 \Tools\Books\cr e[color=blue]
> atedb.chm::/cm_8_des_02_224 8.htm
>
>
> John
>
> "Ragu" <ragudba@sbcglo bal.net> wrote in message
> news:vgToc.1089 0$TC5.2751@news svr23.news.prod igy.com...[color=green]
> > Hi
> >
> > I joined a project where 100,000 rows were added everyday. Now due to
> > additional customers the expectation is 2 million reocrds/day ie 10 GB[/color]
> worth[color=green]
> > of textfiles. We have to estimate the hard disk, memory, # of CPUs[/color][/color]
etc.We[color=blue][color=green]
> > will have one yearworth of data in the db. Rest will be in tapes etc.
> >
> > We will be using WIN2000, SQL Server2000.- Any comparable server sizing[/color]
> will[color=green]
> > be appreciated.
> >
> > 1. Tohandle every day load, I thought that we will have a table for each[/color]
> day[color=green]
> > (pre created in the database )and have a view with union all selecting
> > fromall these 365 tables. (This is the only way to partition in MSSQL[/color]
> Server[color=green]
> > right?).
> >
> > 2. The requirement is to populate datawarehouse tables with all the[/color][/color]
data.[color=blue][color=green]
> > However there will be only inserts mostly but there can be updates too[/color]
> which[color=green]
> > happenned in the past 12 days.Hence we have to use the data from the[/color][/color]
last[color=blue][color=green]
> > 12 days and massage it etc and populate into datawarehouse tables.
> >
> > How can I do this so that I will have the datawarehouse tables with n-12
> > days of data and I will alwys add the last 12 days data to it.
> >
> > Do you have any suggestions?
> >
> > Ragu
> >
> >
> >[/color]
>
>[/color]

[Erland Sommarskog]

Re: sql server sizing planning - architecture help

Ragu (ragudba@sbcglo bal.net) writes:[color=blue]
> I joined a project where 100,000 rows were added everyday. Now due to
> additional customers the expectation is 2 million reocrds/day ie 10 GB
> worth of textfiles. We have to estimate the hard disk, memory, # of CPUs
> etc.We will have one yearworth of data in the db. Rest will be in tapes
> etc.
> ...
> 1. Tohandle every day load, I thought that we will have a table for each
> day (pre created in the database )and have a view with union all
> selecting fromall these 365 tables. (This is the only way to partition
> in MSSQL Server right?).[/color]

Yes, you set up a partitioned view over all this tables. Be careful
to apply your check constraints accordingly.

However, I'm in doubt that having one table per day is really a good
thing. Yes, loads will be fast, if the table is empty and clean
each day. But with a clustered index on date, you would append the
new rows at the end. One table per month seems more manageable....
[color=blue]
> 2. The requirement is to populate datawarehouse tables with all the
> data. However there will be only inserts mostly but there can be updates
> too which happenned in the past 12 days.Hence we have to use the data
> from the last 12 days and massage it etc and populate into datawarehouse
> tables.
>
> How can I do this so that I will have the datawarehouse tables with n-12
> days of data and I will alwys add the last 12 days data to it.[/color]

Too little information to say anything substantial, but a common technique
is to first load into a staging table, and then take it from there. Note
that for the previous days, you would need to do both and UPDATE and an
INSERT, presuming that new rows may appear too.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp