Confirmation please - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Confirmation please

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-17 : 05:42:39

I have a task to perform a new SQL Server 2005 installation at a client.
They have a system with 10 external SCSI drives, each of 72GB. They only have one database of 80GB in use at this system.

1) Userdata: I think that I will put 5 disks at SCSI channel 1, with RAID 3 or 6, with formatted space of 140GB.
2) Log: I think that I will put 3 disks at SCSI channel 2, with RAID 5, with formatted space of 70GB.
3) TempDB: I think that I will put 2 disks at SCSI channel 3, with RAID 0, with formatted space of 140GB.

System already has two built-in drives (RAID 1) for operating system, where I think I will put system databases.

I am open for suggestions and improvements!

Peter Larsson
Helsingborg, Sweden

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-04-17 : 06:10:12

Backup area, do you need that?

rockmoose

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-17 : 06:25:49

Good point.

Alread taken care of with an Backup SQL Agent.
They have a 1GB backbone for servers.

Peter Larsson
Helsingborg, Sweden

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-04-17 : 06:52:51

I would consider:

For speed: (Stripe indexes and data on the 2 RAID10 volumes. Put tempdb files on both volumes as well)
Data1: 4 RAID10 = 140GB
Data2: 4 RAID10 = 140GB
Log: 2 RAID1 = 70GB

For failsafe:
Userdata: 4 RAID10 = 140GB
Log: 3 RAID5 = 140GB (+ archive data if applicable)
Tempdb: 2 RAID1 = 70GB
Spare: 1 disk

rockmoose

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-17 : 07:02:30

They are using hot swap technology, but a spare is good!
I emailed the client and got an answer a few minutes back that the database is SAP and is highly mission critial.

I think I will go for option #2.

Peter Larsson
Helsingborg, Sweden

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-04-17 : 07:21:15

I am also interested in what other people might have to say.

I am not very well versed on RAID3 or 6, but it seemed that the parity disk would become a bottleneck, since all IO needs to access that one.
But I am not sure if that is a big deal, it's just that I don't have any experience with that.
I'm a sucker for RAID10

About tempdb on RAID0, what happens if one of the drives fail?
The server goes down, but just bringing it back up should be no problem, running tempdb on just one of the disks?

I also like the idea of a spare, now just thinking out loud... running some partition on RAID1(or other redundant) (e.g. logs) could that not count as an "implicit spare"? If a more important disks fails somewhere else.

Normally I favor redundancy and fail safety, and usually am willing to offer some performance or disk space just to avoid any hardware mishaps.

rockmoose

Kristen
Test

22859 Posts

Posted - 2007-04-17 : 07:36:42

"2) Log: I think that I will put 3 disks at SCSI channel 2, with RAID 5, with formatted space of 70GB."

I hate RAID5. As you may well have ready in other threads over the last year or so, every disk failure we have had on RAID5 systems has resulted in a Torn Page and thus a trashed database

May be the Dell disk controllers, and I'm not sure how you could simulate it during a write cycle! - Pull a drive perhaps ??

We now use RAID10 on all logs and data.

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-17 : 07:38:37

In this case I favor failsafe too.

You won't believe me, but today all 10 drives are connected to same Channel 1 on the SCSI card in a single set as 700GB!
Then, in Microsoft Server 2003, they have partitioned the space in four logical drives (D: E: F: and G:), where D: is userdata, E: is log, F: is tempdb and G: is system databases...

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-04-17 : 07:55:18

That's great isn't it. You see four Drive Letters and assume they are separate devices and then come Server Crash Day you discover that all bets are off!

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-17 : 08:02:03

That's why I get paid to do an inventory of the server

The company who asked me to do the inventory didn't know this themself, since they bought the server from one of the largest consultancy companies in Sweden (who are specialized in databases)...

Peter Larsson
Helsingborg, Sweden

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-04-17 : 08:08:06

> "...one of the largest consultancy companies in Sweden (who are specialized in databases)..."

Oh dear, now we have to feel ashamed for being swedish

rockmoose

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-17 : 08:11:43

Don't. I am pretty sure this happens in the USA too.

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-04-17 : 12:03:39

"Oh dear, now we have to feel ashamed for being swedish"

It will be a computer built in the USA, configured in the UK, and sold in Sweden!

Kristen

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-17 : 12:08:26

quote:
Originally posted by Kristen

"Oh dear, now we have to feel ashamed for being swedish"

It will be a computer built in the USA, configured in the UK, and sold in Sweden!

Kristen

Correction..

It would have been built in China/Taiwan.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-04-17 : 12:29:53

quote:
Originally posted by Peso

In this case I favor failsafe too.

You won't believe me, but today all 10 drives are connected to same Channel 1 on the SCSI card in a single set as 700GB!
Then, in Microsoft Server 2003, they have partitioned the space in four logical drives (D: E: F: and G:), where D: is userdata, E: is log, F: is tempdb and G: is system databases...

Peter Larsson
Helsingborg, Sweden

Just checking my understanding here - clearly being able to configure different RAID levels, and using separate SCSI channels for the data, logs, and tempdb are major reasons to separate the 10 disks as you have proposed.
What I am wondering is whether there are any other benefits to doing it that way instead of using one large set?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-17 : 12:43:48

What other things are you thinking of?

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-04-17 : 12:48:14

"whether there are any other benefits to doing it that way instead of using one large set?"

Where we only have a Data and Log channel (well, probably an O/S channel too) we put the TLog backups on the LDF channel. So when the MDF channel fails we have a fighting chance at a Restore without data loss.

Copying the BAKs as soon as they are made to the other channel would help.

(Copying them OFF that machine would be better, but we find this is often not [considered[ viable [by erstwhile clients!!] for small systems hosted as ISPs etc)

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-17 : 13:15:42

If it's "highly mission critical", then will this be on a cluster?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-17 : 13:18:34

Unfortenately not.
They rely on dual power supplies, dual network cards and other dual things.

Peter Larsson
Helsingborg, Sweden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-04-17 : 14:08:28