Partitions size

Last Post 20 Jan 2012 02:16 PM by darko_73. 7 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
darko_73
New Member
New Member

--
17 Jan 2012 08:31 AM
Hi,

We are moving our SQL Server 2008 DBs to new servers. Long story short, we are given 850 GBfor 5 databases. It is RAID6 (never heard anyone uses this RAID for DBs). They ask me
to provide partitions size. Initialy I asked for the following:

RAID1 C:\- WIndows ans System Tables (master, msdb..)min 70 GB
RAID1 D:\- tempDBmin 80 GB
RAID1 E:\- tempDBmin 80 GB
RAID1 F:\- transaction logmin 120 GB
RAID5 G:\- Database filesmin 150 GB
RAID5 G:\- Database filesmin 150 GB
RAID5 H:\- Backupsmin 200 GB


Databases size:
DBMDFlog
115GB4GB
2154
383
473
541

Soon we will have 3 new databases around 10GB each. 

Now, since it is all RAID6 does it matter if they give us one drive (C 850GB)
ot partitioning will make a difference (using suggested sizes from the above ignoring RAIDs)?

Thank you for the ideas.
rm
New Member
New Member

--
17 Jan 2012 01:22 PM
Better to separate them.
Duane Bentley
New Member
New Member

--
17 Jan 2012 07:23 PM
these are small for now, but consider what is your expected growth size. This will be your main factor in how you set up your drives. From what I read of your description it is like they set you up on a single RAID 6 system. this is not a good choice for a sql server. Our main db is 1 TB sitting on a RAID 10 (striped/mirrored) our log files are on RAID 10 our backups are on RAID 5 and our reporting DB (replicated main tables) are on RAID 5.

RAID 6 is great because of the double parity allows for simultaneous loss of 2 drives. Great for data loss but you will be wriiting 2 parity bits for every data bit. this will be very costly on your main DB where you are constantly reading and writing. so it is best for main DB files and your transaction logs plus temp DB to be on a RAID 10 the more spindles the better.

So things to consider with your IT staff how many users are you planning to hit this DB (users or application connections etc...) What size do you forsee it growing to by month and year to year. How many transactions by day and at peak hours.

If you forsee it growing very large and have thousands of hits and or thousands of concurrent connections then RAID 10 is a must with either 10-15 k rpm spindles 4 drives minimum 8 to 16 drive are better.

your read only DB's RAID 5 is best lower overhead due to less writes outside of replication and more high speed spindles gives extremely fast reads.

hope this helps
Duane Bentley
New Member
New Member

--
17 Jan 2012 07:30 PM
oh yeah forgot your 850GB RAID 6 is not very big and partitioning your RAID 6 out like that is no very helpful. you are still sharing your io on the same spindles. you can partition how you want but those drive heads are the same ones for each partition. Is this a production system or a test / staging system? If it is production with low amount of users you may be ok. But if you hit a high in read/write trafic, and replication etc... you will bog your system down quickly till it is unusable. then you will be forced to migrate your db's to seperate RAID array.

i dont know your spindle count or if it is direct attached storage or san.

if you had direct attached storage with the right amount of disks you can do some very fancy setups.

give us some more details and we can guide you better.
russellb
New Member
New Member

--
19 Jan 2012 12:46 PM
RAID 6 is likely a SAN, and is actually a very good choice for database files.
Duane Bentley
New Member
New Member

--
19 Jan 2012 04:17 PM
Yes it can be under certain circumstances. I know when we moved our DB to new site we were put on a RAID 5 on SAN. With all the read/write activity and replication our DB went to a complete crawl to the point of unusability. As soon as we put it on a RAID 10 we gained a huge amount of io back to the system. The problem with RAID 5 and 6 is in writing of the parity bits for all new data written to disk. It is also best practice in most orgs to put main DB files and trans logs on RAID 10 for redundancy and high io. You get the read and write speed of striping and redundancy of mirroring.

The other consideration in why i asked the question i did. Total io of all partitions, it need to be understood that in any array it does not matter how many disks you have if you share partitions on those disks and have heavy activity on most of the partitions. That your io will drop significantly, this is due to the read heads on the disks serve up io ofr all partitions on the array. DB servers should have all file groups on seperate sets of disks/arrays. This allows for individual purposing for the arrays.

For example our c: and d: is RAID 5 4 disks OS and Apps, Main DB drive is RAID 10 8 disks, Replicated report drive RAID 10 4 disks, Backup drive RAID 5 4 disks.

So you can see from example above OS and apps cannot interfere with any DB disk io. Replication is read only from main drive to reports drive etc...

RAID 5 and 6 is a good fit for mainly read only DB files it is very fast and gives best protection with most of the disk space usable.
russellb
New Member
New Member

--
20 Jan 2012 08:52 AM
On a high end SAN, you really don't have a choice. IO is shared across all disks as the SAN software sees fit. You should still setup your drives as usual, as the OS impacts IO as well, and for management purposes.

I've never heard of a Network Engineer and/or a DBA settling on a RAID 6, but if you use a NetApp SAN, you're on a RAID 6 whether you like it or not. And it will outperform any RAID 10 on commodity hardware exponentially.

When I first met with the NetApp engineers a few years ago, I told them that I was going to be difficult to convince that they could dish out IO (while sharing ALL disks accross volumes) more efficiently than I could do it. Well fact is that they can and do.
darko_73
New Member
New Member

--
20 Jan 2012 02:16 PM
Thank you all for comments.
 All I know so far it is SAN, RAID 6. Once I find more details, I might ask more questions.


Acceptable Use Policy
---