Post reply

SQL Server 2000 Setup ; Best Practice

  • June 21, 2006 at 3:07 am

    #171394

    Good morning folks,

     I have finally been allowed to set up our production database as I want to (YYYYYYYYYYYEEEEEEEEEEESSSSSSSSSS!!!!!!!!!) and after searching this forum have an idea of how I would like to set it up (thanks to everyone whose ideas I have passed off as my own ). I thought I'd put down my ideas here and let people nudge me in the right direction if I have got it wrong (always a possibility ) or missed something out

    The situation is this :

    SQL Server 2000 Enterprise, SP4 etc

    Windows 2000 Server box with 4 Gb memory. Box will be used by other applications (mainly java)

    Data Files : About 200 GB in ten or so databases, Some tables have about 100 million records in them. We import about 4 million records a day

    Log Files : About 60 GB (but are shrunk daily as we load data in batch)

    SAN Storage : Luns, Meta Luns and a ATA drive configured for write

    I was thinking :

    Seperate drives for the logs

    Seperate drives for the mdfs

    Seperate drives for the backups (the ATA drive)

    Seperate drives for the msdb

    Seperate drives for the tempdb

    Performance counters on.

    What have I missed / got wrong in order to get this database setup as correctly as possible

    Thanks

    SubPop

     

     

     

     

  • June 21, 2006 at 11:05 am

    #645101

    Seperate drives for the logs :ok

    Seperate drives for the mdfs: ok

    Seperate drives for the backups (the ATA drive) (could be on a removable drive as well so you don't have to use tapedrives)

    Seperate drives for the msdb : overkill, these rarely change

    Seperate drives for the tempdb: depends on your load

    *check if the server has the same collation as the other sql servers (if any)

    *Box will be used by other applications (mainly java)->On the same machine or run by other machines?

     

     

     

  • June 21, 2006 at 1:11 pm

    #645128

    thanks for the reply. The java apps will mainly be running on the same box but the box will also be accessed externally by java apps.

    I was under the impression that msdb was utilised by backups and dts packages... that's why i put it on a seperate drive? I'll check up on this, I could be wrong... very wrong!

    My main concern with setting up this machine is that i provide overnight support for the loads of data and I wanna get it running as fast as possible so that the million plus loads get done ASAP. Configuration of SQL server is a possibility but MS suggest that SQL Server does most of it's configuration given time. Can anyone offer an opinon on this?

     

    cheers

     

     

  • June 21, 2006 at 2:07 pm

    #645140

    *msdb is largely a database for sql server agent, to store dts-definitions, scheduled jobs, backup history...

    Sql server is quite selfconfiguring (buffers, updating statistics,recompiling,...).Chanches are that you have to tweak memory allocation a bit to avoid a memory competition between sql server & your java apps.

     

     

     

     

  • June 22, 2006 at 3:00 pm

    #645390

    consider multiple segments for data (mdfs).

    then you could partition tables or seperate indexes from table data

    Cheers,

    Todd

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply