Partition layout for Microsoft SQL 2005

[casperdacat]

Hi all,

I'm to configure a small SQL Server 2005 system soon and I know I need to be careful when sizing/creating partitions but I'm not sure what the best practices are. I'm talking a small SQL system with multiple databases, but minimal user load (we'll have only 60 users at most).
I have a server with hardware raid controller and 4 10K 146gb SAS disks. I don't have enough disks to put database and logfiles on separate physical disks but I've read that for smaller systems like this I shouldn't bother. I then thought of creating one raid 10 set and setting up multiple logical partitions but again I found out that for systems with few users raid 5 is advisable because: less complex and more effective free disk space.

So I'm thinking of this layout:
4x 146GB RAID 5 : approx. 430 GB free disk space

partition 1 : 30 GB : OS
partition 2 : 160 GB : DB
partition 3 : 160 GB : TempDB
partition 4 : 80 GB : logfiles

Does this make sense? Do I have to create a partition for the TempDB as big as the actual DB partition?

I realize this is a newbie question so thanks for your patience.

Fred

 

[Mich]

Putting the files on different logical partitions on the same physical array won't have an affect on performance. I suggest you keep it as simple as possible and, unless you have security concerns, install to the defaults.

For most setups I recommend you place your Windows and SQL files on a two drive RAID1 setup and put your data files on a RAID5 setup. However, you would need one more drive for that.

Whenever you get into a RAID environment be sure to have a spare drive on your shelf. It's a bad feeling to have a system RAIDed only to not have a spare drive to rebuild it when it fails...trust me.

-If it ain't broke, break it and make it better.

 

[Mich]

I need to amend that post.

When working with single volume systems I like to put my Windows and SQL files on the root partition. Afterward, I will create another partition and place my data files there. With the space that you have I'd put 50GB on the root and the rest on another partition (assuming this is all this box will be used for).

Creating the two partitions is a basic security step. Don't short your root partition or you'll run into headaches when you patch/update your system down the road.

-If it ain't broke, break it and make it better.

 

[baddos]

Thats not entirely true, as having a different partition for each filegroup / log will save on disk defragmentation some. It wouldn't be a major performance increase, but hey. ;-)

 

[mrdenny]

Having different partitions also gives windows separate queues for each local disk, instead of a single queue. But with a system this small that probably won't matter. This faq962-5747 might help you out as well.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[casperdacat]

Oké, thanks a lot for your advice, I guess I'll stick to the setup mentioned in my post. Maybe one more thing: how much space should I allocate for the tempdb? I've read it's used to store temporary tables (heh ;-)) so I suppose it shouldn't be as big as the database partition? Right?

Thanks,
Fred

 

[mrdenny]

It depends on how much temp table space you need. If you will be using temp tables a lot, then the tempdb will need to be larger.

If you are going to using the SORT_IN_TEMPDB switch when creating, or altering your index then you will need additional space in the tempdb database.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog