Re: RAID configuration for SQL Server 2005

Tech-Archive recommends: Speed Up your PC by fixing your registry

It seems pretty light to me - are there some charts out there, some kind of
guiding priciples on how to interpret this data?

Not really as a transaction is not a constant. It can depend heavily on what the transaction is and the amount and type of data being modified. And most of that is probably coming from Tempdb but you can't tell just from the total counter, you need to break it out to see for sure. But I would still recommend the configuration I posted. Normally without more specifics I would say add the two disks from the Raid 1 for Tempdb to the Raid 10 and just put the tempdb data on it as well. But since you need a place for the backups anyway and tempdb is rebuilt from scratch each time it starts up that is probably a good place to put it.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"devprog@xxxxxxxxxxx" <devprogmsbcompcom@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:3E07DB14-0410-419B-A9F7-9766C30B7956@xxxxxxxxxxxxxxxx
I looked at the Transaction/sec counter for the _Total instance and it ranges
between 50-200 - never saw it higher than that during normal operations. How
can I tell based on this what kind of load we have on the server?

It seems pretty light to me - are there some charts out there, some kind of
guiding priciples on how to interpret this data?





"Andrew J. Kelly" wrote:

How many transactions per second are you talking about? Are you concerned
with performance and scalability? If so then you should keep the log files
on a Raid 1 by themselves or on the Raid 1 with the OS since it is not used
that much. Backups should never be placed on the same array as the user data
or log files since if that array goes south you loose the backups and the
data or log. You might consider this:

Raid 1 OS & Log files (including tempdb log file)
Raid 1 Tempdb data and Backups
Raid 10 User data files

But it can really depend on how you use the database.


--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"devprog@xxxxxxxxxxx" <devprogmsbcompcom@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:2B507DF1-FE80-4D9F-B3F2-7EAA661B8297@xxxxxxxxxxxxxxxx
> We're upgrading SQL Server from 2000 to 2005 and installing 2005 on a
> brand
> new server...I have a question about the RAID configuration.
>
> We're getting a Dell PowerEdge 2970 with 8 drives, all SAS 2.5" 10K and
> 300
> GB in size.
>
> I know 4 of those drives will be setup in RAID 10 and will hold the > main
> data files. My question is about the other 4 drives.
>
> The options are:
>
> A. Configure 2 drives in RAID 1 for OS, tempdb, backup and another 2
> drives
> in RAID 1 for transaction logs & backup
>
> B. Configure all 4 drives with RAID 10 in one array and put OS, tempdb,
> transaction logs and backup all together
>
> I'd rather go with option B because that gives me flexibility in how
> storage
> is used - 600 GB total - (as opposed to having to work with 2 separate
> arrays
> at 300 GB each) but I'm not sure what the negative impacts will be (if
> any)
> from having all those things together.
>
> This is for an OLTP database (ecommerce site) and there will be just > one
> RAID controller installed in the server.
>
> Is it a bad idea to combine OS, tempdb, transaction logs into a single
> RAID
> 10 array? Or do I need to use two arrays in RAID 1 (option A) and lose > the
> space flexibility?
>
> Thanks.



.

Relevant Pages

  • Re: RAID configuration for SQL Server 2005
    ... If so then you should keep the log files on a Raid 1 by themselves or on the Raid 1 with the OS since it is not used that much. ... Backups should never be placed on the same array as the user data or log files since if that array goes south you loose the backups and the data or log. ... transaction logs and backup all together ...
    (microsoft.public.sqlserver.setup)
  • Re: RAID configuration for SQL Server 2005
    ... If so then you should keep the log files ... on a Raid 1 by themselves or on the Raid 1 with the OS since it is not used ... Backups should never be placed on the same array as the user data ... transaction logs and backup all together ...
    (microsoft.public.sqlserver.setup)
  • Re: Next best option for TempDB location?
    ... Since tempdb is also a random access database, I would keep tempdb data on ... RAID 10 - DATA FILES ... (5-disk set, 15k 146GB SAS drives) ... RAID1 - LOG FILES and TEMPDB ...
    (microsoft.public.sqlserver.setup)
  • Re: Building New Server - What RAID should be used and how to divi
    ... are setup on the same RAID which is RAID1 which will provide you performance ... System files would not be used frequently (tempdb is an exception and it is ... You can keep the log files and data files of your user databases at ... My manager wants to know how many drives are needed depending ...
    (microsoft.public.sqlserver.setup)
  • Re: raid question
    ... > what is the ideal RAID level for tempdb? ... then put its data and log files with the other data ... All the databases on the same RAID array, ...
    (microsoft.public.sqlserver.server)