Database sizing question

Hi,

I have 1.3 GB ASII file for each month to be tranferred to a table. I want to get 18 months( 18 files) so 18*1.3 GB will be my total file size. Now I am transferring one file at a time to the table using INSERT. I have an index on one column only ( NON CLUSTERED NON UNIQUE). My question is what should be my data file size and transaction log file size for this operation. I will only be doing SELECT and INSERT in this table. Any other SQL server parameters that I need to take care of then please let me know.

ThanksHowdy

Without knowing the table datatypes used in the table columns, its difficult to know. It can be calculated but its time consuming and probably more hassle than you want.

Try this ( its easier and faster ) :

(1) Create the table you are importing data to.

(2) Switch the database into Bulk Insert or Simple recovery mode during the data load ( this will stop the transaction log becoming huge and is good practice anyway during a large data load into a database ).

(3) Once the data is loaded, re-create the index. This will ensure the database is "clean" and the tran logs is as small as possible. During normal database operation ( inserts & updates ), the tran log will grow of course, if the recovery mode is FULL or BULK LOGGED.

(4) Change the database back into FULL recovery mode ( assuming you want to be able to recover the database to apoint in time ) and start using it.

Cheers,

SG.

Database Sizing Question

My company is currently setting up an application for a client that requires a Database server on the backend. We have selected SQL Server 2000 Enterprise as our DB but we are not sure how to size the hardware of the server. I have searched around and it appears that DB sizing is more experience/trial & error based than formula-based. I hope someone here can provide me with some advice if I provide the performance requirements of the server.

5 GB of data currently (will double in size every year) Approx. 10 million records (will double in size as well) 85,000 Transactions / hour 200 Concurrent users Client requires no greater than 1/2 second response time.

Questions:

How many CPU's should be needed? Why? How much RAM should be needed? Why?
RAID 10 was the recommended fault tolerance. Agree? Disagree?

Thanks in advance

JBAs you guessed there is no magical answer but here are some things to think about.

The actual size of the db is not too critical as long as you plan for growth and take into account how the drive arrays should be set up to achieve good performance. 85K per hour is less than 30 a second and although I wouldn't try that on a single processor box it is not too bad. (I routinely do over 800 a second with 4 Processors).

But since you need to handle Peak amounts and want less than 1 second response time you should be particularly aware of minimums. Ram will depend on how much of the data you actually use on a regular basis. You want to aim for enough ram to have all the data that you access on a regular basis in cache at one time leaving room for the procedure cache etc. Just because you have 5GB of data doesn't mean you will need 5GB of ram. You can always add ram if needed later just make sure you plan for growth ahead of time and save some ram slots so you don't have to throw away ram later to add more. It's always better to have more ram than not enough (don't forget to leave some for the OS too).

As for the hardwareI would shoot for the following:
RAID 1 (or 10) for the Log file(s).
RAID 5 or 10 for the data files (smaller and more disks vs less larger ones).
Raid 1 for the OS and SQL System files.

Depending on how much you will use tempdb (sorting etc) you may or may not want a separate Raid for tempdb.

If you do disk backups you may want to think about another RAID or 5 for the backups. In all cases make sure the arrays are expandable for future growth.

As for processors you will probably want to start with a 4 or 8 processor box with less than all the procs to begin with. The number of procs will depend on so many things but if you don't have enough you will probably see less than 1/2 second response time in peak loads while the rest of the time it will be fine. Poor code or schema is usually the reasons for needing more processors.

Andrewsql

Database Sizing

I need to convert a hierarchical database and a Siebel
Relational database to SQL a Relational database. I
currently have two databases list below:
29,151,892 MB = hierarchical database
8,222,178 MB = Siebel Relational database
About what size of a database would this be in SQL?Hi,
Storage depends up on the way you are going to redesign when you move it to
SQL Server. If it is going to be Siebel to SQL Server direct
loading then the storage will be almost identical, but whn you create
indexes the storawill go high. So I recommend you to have atleast double the
size of
current database projecting the data growth as well.
For the Hierarchical database you might require a new relationa redesin.
This will re-use the storage and may not require more space. So probably you
can have 1.5 times of current size to hold the indexes and new data.
All these are just assumptions. To get it almost correct value you have to
do some small calculations based on the books online topic "Estimating the
Size of a Table".
Thanks
Hari
MCDBA
"Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:2a07601c46542$92b74df0$a301280a@.phx
.gbl...
> I need to convert a hierarchical database and a Siebel
> Relational database to SQL a Relational database. I
> currently have two databases list below:
> 29,151,892 MB = hierarchical database
> 8,222,178 MB = Siebel Relational database
> About what size of a database would this be in SQL?
>

Database Sizing

I need to convert a hierarchical database and a Siebel
Relational database to SQL a Relational database. I
currently have two databases list below:
29,151,892 MB = hierarchical database
8,222,178 MB = Siebel Relational database
About what size of a database would this be in SQL?Hi,
Storage depends up on the way you are going to redesign when you move it to
SQL Server. If it is going to be Siebel to SQL Server direct
loading then the storage will be almost identical, but whn you create
indexes the storawill go high. So I recommend you to have atleast double the
size of
current database projecting the data growth as well.
For the Hierarchical database you might require a new relationa redesin.
This will re-use the storage and may not require more space. So probably you
can have 1.5 times of current size to hold the indexes and new data.
All these are just assumptions. To get it almost correct value you have to
do some small calculations based on the books online topic "Estimating the
Size of a Table".
--
Thanks
Hari
MCDBA
"Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:2a07601c46542$92b74df0$a301280a@.phx.gbl...
> I need to convert a hierarchical database and a Siebel
> Relational database to SQL a Relational database. I
> currently have two databases list below:
> 29,151,892 MB = hierarchical database
> 8,222,178 MB = Siebel Relational database
> About what size of a database would this be in SQL?
>

Database Sizing

I need to convert a hierarchical database and a Siebel
Relational database to SQL a Relational database. I
currently have two databases list below:
29,151,892 MB = hierarchical database
8,222,178 MB = Siebel Relational database
About what size of a database would this be in SQL?
Hi,
Storage depends up on the way you are going to redesign when you move it to
SQL Server. If it is going to be Siebel to SQL Server direct
loading then the storage will be almost identical, but whn you create
indexes the storawill go high. So I recommend you to have atleast double the
size of
current database projecting the data growth as well.
For the Hierarchical database you might require a new relationa redesin.
This will re-use the storage and may not require more space. So probably you
can have 1.5 times of current size to hold the indexes and new data.
All these are just assumptions. To get it almost correct value you have to
do some small calculations based on the books online topic "Estimating the
Size of a Table".
Thanks
Hari
MCDBA
"Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:2a07601c46542$92b74df0$a301280a@.phx.gbl...
> I need to convert a hierarchical database and a Siebel
> Relational database to SQL a Relational database. I
> currently have two databases list below:
> 29,151,892 MB = hierarchical database
> 8,222,178 MB = Siebel Relational database
> About what size of a database would this be in SQL?
>