Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Feedback On Proposed Drive/RAID Configuration For New SQL 2008R2 System Expand / Collapse
Author
Message
Posted Wednesday, July 25, 2012 12:29 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 9:58 AM
Points: 886, Visits: 1,545
I am no hardware expert and so when it comes to things like RAID configurations and Channels I'm not versed enough to provide feedback. Would some of your more seasoned/experineced DBA's take a look at the below propsoed drive configuration (from our IT staff) for a new SQL 2008 R2 box and give your thoughts on any reocmended changes or if you think the ropsed sounds like a good configruation?

For example: Would the beefit of placing the user DB (Main SQL Data drive) and temp DB on seperate drives be negated by their being on the same RAID Channel?

If I need to request changes to this thing then I have to do it befopre we get the thing in and setup.

Thanks


SERVER: Windows 2008 R2 server (64)
SQL PLATFORM: SQL 2008 R2 Enterprise (64)

Drive config is as follows

C: OS drive - 80GB
D: Main SQL data drive - 1000GB
L: Main SQL Log drive - 200GB
X: SQL-system DB and System log drive - 20GB
Y: TempDB log drive - 20GB
Z: Tempdb data drive - 20GB

These drives are sharing controllers as follows:

C: and X: (will be on channel2 RAID10 array)
D: and Z: (will be on channel1 RAID5 array)
L: and Y: (will be on channel0 RAID10 array)


Kindest Regards,

Just say No to Facebook!
Post #1335363
Posted Wednesday, July 25, 2012 12:34 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:16 PM
Points: 23,299, Visits: 32,049
I'd want D and Z to also use RAID-10 as well.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1335368
Posted Wednesday, July 25, 2012 12:37 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:53 PM
Points: 33,204, Visits: 15,353
You want your data and tempdb data to be on fast drives, so I'd go R10 there, as Lynn mentioned.

How busy is the system for writes? R5 has a penalty, but it you aren't saturating things, the logs can work here.

Backups? Where are they going? Please not with the data.

The Raid channels may or may not matter. It depends on the throughput of your drives and how much you're pushing down from the various areas. Really needs measurement to determine if this could be better. Can you get an idea of the load on the current system?







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1335371
Posted Wednesday, July 25, 2012 1:56 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 9:58 AM
Points: 886, Visits: 1,545
Thanks Lynn and Steve. I had already asked for the guys to switch the RAID 5 drives over to RAID 10 and forgot to note that in my post. I feel better now seeing that the recomendations in line with the chnages I had planned to request anwyay; makes me fell less RAID ignorant.

That said I do have on follow-up question regarding Virtualization via VMWare, something I know even less about.

When we first started looking at new SQL Servers the IT admin wanted to VM the system. I argued against a full VM because of things I had read about VM and SQL Server performance. We have a relatively large DB (just under 300GB) with hundreads of users on line at one time and we get a lot of I/O activity. It was my understanding that while you could place SQL Sertver on a VM and get good performance there were still scenarios where it was recomended to not put SQL Server on a VM. One of these was a high load (very busy with Read/Write operations) database whcih we have.

The compromise form IT was that only the OS would be on the VM and the the the database files (the log and data drives) woudl be on local drives (as opposed to the SAN) . Do either of you have any thoughts about using VMWare for the OS piece only of a new SQL Server box? I didn't even know you could VM just the OS on a new server but thats what I've been told.


- BACKUPS -
Backups are done via Microsofts DPM (Data protetcion Manager) and stored on anotehr server. Thers no native SQL backups done locally. I don't know the inner workings of DPM and how it does SQL DB backups across the network but it does. It does take longer then a native SQL Backup done locally does and I guess thats the trade off with using DPM but it gets the job done and our recovery time currently under DPM is 30 minutes; we can restore to the last 30 min log back that has completed or the last half hour increment.

Thanks for the feedback.


Kindest Regards,

Just say No to Facebook!
Post #1335423
Posted Wednesday, July 25, 2012 3:26 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 3:26 PM
Points: 316, Visits: 1,497
YSLGuru (7/25/2012)
Thanks Lynn and Steve. I had already asked for the guys to switch the RAID 5 drives over to RAID 10 and forgot to note that in my post. I feel better now seeing that the recomendations in line with the chnages I had planned to request anwyay; makes me fell less RAID ignorant.

That said I do have on follow-up question regarding Virtualization via VMWare, something I know even less about.

When we first started looking at new SQL Servers the IT admin wanted to VM the system. I argued against a full VM because of things I had read about VM and SQL Server performance. We have a relatively large DB (just under 300GB) with hundreads of users on line at one time and we get a lot of I/O activity. It was my understanding that while you could place SQL Sertver on a VM and get good performance there were still scenarios where it was recomended to not put SQL Server on a VM. One of these was a high load (very busy with Read/Write operations) database whcih we have.

The compromise form IT was that only the OS would be on the VM and the the the database files (the log and data drives) woudl be on local drives (as opposed to the SAN) . Do either of you have any thoughts about using VMWare for the OS piece only of a new SQL Server box? I didn't even know you could VM just the OS on a new server but thats what I've been told.


- BACKUPS -
Backups are done via Microsofts DPM (Data protetcion Manager) and stored on anotehr server. Thers no native SQL backups done locally. I don't know the inner workings of DPM and how it does SQL DB backups across the network but it does. It does take longer then a native SQL Backup done locally does and I guess thats the trade off with using DPM but it gets the job done and our recovery time currently under DPM is 30 minutes; we can restore to the last 30 min log back that has completed or the last half hour increment.

Thanks for the feedback.


What does it mean that "only the OS would be on the VM and the database files would be on local drives"? I'm guessing that's as opposed to

A large percentage of system/san admins don't really understand how SQL server uses resources (cpu, ram, io) , and the ramifications of using VM's for sql server.
Sysadmins like VM's because they can oversubscribe the resources, but is a bad thing for high load SQL servers.

For an OLTP system, you might be best served with as much ram as you can shove into the machine, and one big RAID 10 array (like 20+ drives) for all your db files.
Unless you know for sure that your IO load is too high for one big raid 10 array, then I wouldn't bother getting fancy with multiple volumes. However, if you know that tempdb usage will be very high, then local SSD storage makes a lot of sense.

For a SAN, ensuring that caching is enabled, and set to 0% read/100% write caching for sql server can do more for write performance then raid5 vs raid10 or separate volumes for tempdb (not always, of course).

In order to work with system administrators about IO requirements of SQL Server successfully, you need to talk in terms of IOPS. Calculate how many you need, then test whatever server you're given to determine if you're actually getting them. If they can deliver your IOPS, then it doesn't matter what raid level it uses or how it's split up. If they can't deliver those IOPS (very likely in the first pass) then you can troubleshoot it from there. If you need an large amount of iops (i.e. more than one HBA can provide) then you'll need to get very very friendly with your SAN admin.

As for a VM vs physical HW, you need to understand how balloon drivers work, how sql server memory allocations work, how sql server gives memory back to the os when requested, min/max memory settings, the pitfalls of over subscribing ram among several vm's, etc. Then you can argue for or against.

Post #1335453
Posted Wednesday, July 25, 2012 4:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:53 PM
Points: 33,204, Visits: 15,353
SpringTownDBA (7/25/2012)


...
As for a VM vs physical HW, you need to understand how balloon drivers work, how sql server memory allocations work, how sql server gives memory back to the os when requested, min/max memory settings, the pitfalls of over subscribing ram among several vm's, etc. Then you can argue for or against.



yep, well said.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1335484
Posted Thursday, July 26, 2012 9:42 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 9:58 AM
Points: 886, Visits: 1,545
SpringTownDBA (7/25/2012)
YSLGuru (7/25/2012)
Thanks ...

Thanks for the feedback.


What does it mean that "only the OS would be on the VM and the database files would be on local drives"? I'm guessing that's as opposed to

A large percentage of system/san admins don't really understand how SQL server uses resources (cpu, ram, io) , and the ramifications of using VM's for sql server.
Sysadmins like VM's because they can oversubscribe the resources, but is a bad thing for high load SQL servers.

For an OLTP system, you might be best served with as much ram as you can shove into the machine, and one big RAID 10 array (like 20+ drives) for all your db files.
Unless you know for sure that your IO load is too high for one big raid 10 array, then I wouldn't bother getting fancy with multiple volumes. However, if you know that tempdb usage will be very high, then local SSD storage makes a lot of sense.

For a SAN, ensuring that caching is enabled, and set to 0% read/100% write caching for sql server can do more for write performance then raid5 vs raid10 or separate volumes for tempdb (not always, of course).

In order to work with system administrators about IO requirements of SQL Server successfully, you need to talk in terms of IOPS. Calculate how many you need, then test whatever server you're given to determine if you're actually getting them. If they can deliver your IOPS, then it doesn't matter what raid level it uses or how it's split up. If they can't deliver those IOPS (very likely in the first pass) then you can troubleshoot it from there. If you need an large amount of iops (i.e. more than one HBA can provide) then you'll need to get very very friendly with your SAN admin.

As for a VM vs physical HW, you need to understand how balloon drivers work, how sql server memory allocations work, how sql server gives memory back to the os when requested, min/max memory settings, the pitfalls of over subscribing ram among several vm's, etc. Then you can argue for or against.




"What does it mean that "only the OS would be on the VM and the database files would be on local drives"?

Its my understanding that the Windows OS will run inside a VMware session (or whatever its called) which means that any application installed will also run within that same VM. I'm told that this VM session is the only one that will run on the host server (the new server we're getting) and so the VM session will get %100 of the resources as opposed to sharing with other VM sessions. The OS drive is C. The other drives are locally attached RAID drives (as opposed to SAN based which is how the current server is setup).

I fought against moving to the use of VMware or anything else virtualized because I had read about issues with high load RDBMS on VM solutions even when the VM is properly configured for use with SQL Server. Originally the plan was to do everything within VMware and use SANs based drives. Using Red-Gates SQL Monitor I was able to show why this was a very bad idea since our current I/O is terrible because of our SANS setup which needs updating/replacing. The Avg Read/Write times are just awful but until I had something to show as proof (i.e. SQL Monitors stats/metrics it captures and stores for reporting purposes) I couldn’t make the case for moving off the SANS.

Once I got them to agree to local drives the next battle was the use of VMware. The compromise made was that we would still use VMware but that the VM Session hosting the Windows Server OS + SQL Server would get %100 of the resources on that server and not be shared with any other VM anything on the network. In addition the drives that the data and log files are on would be local attached RAID configurations and not virtualized.

Now I know very little about VMware or virtualization so the above may sound silly or dumb and I wouldn't know. The statement that the data & log drives would not be virtualized may ne non-sesnsical and I wouldn’t know it because I don’t understand how VMware works at that level. The pro-VM admin (who has pushed everything onto VMware so far but our SQL Server ) could be taking advantage of my lack of VM knowledge and feeding me a line of bull so as to make it sound like he's made some sort of compromise with the VM thing when in fact he hasn't and I would not know the difference.

Even though my knowledge in VMware is lacking I have seen enough VMware related problems to know that the Virtualization sales pitch the admins give is not as “You can’t tell the difference between a VM Hosted server and a real non-virtualized server” as they say.

Thanks for replying and if the above helps clarify any of your questions or what I said and you have any additional comments I'd love to hear them.

Thanks


Kindest Regards,

Just say No to Facebook!
Post #1335944
Posted Thursday, July 26, 2012 11:19 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 3:26 PM
Points: 316, Visits: 1,497
YSLGuru (7/26/2012)

...

"What does it mean that "only the OS would be on the VM and the database files would be on local drives"?

Its my understanding that the Windows OS will run inside a VMware session (or whatever its called) which means that any application installed will also run within that same VM. I'm told that this VM session is the only one that will run on the host server (the new server we're getting) and so the VM session will get %100 of the resources as opposed to sharing with other VM sessions. The OS drive is C. The other drives are locally attached RAID drives (as opposed to SAN based which is how the current server is setup).

I fought against moving to the use of VMware or anything else virtualized because I had read about issues with high load RDBMS on VM solutions even when the VM is properly configured for use with SQL Server. Originally the plan was to do everything within VMware and use SANs based drives. Using Red-Gates SQL Monitor I was able to show why this was a very bad idea since our current I/O is terrible because of our SANS setup which needs updating/replacing. The Avg Read/Write times are just awful but until I had something to show as proof (i.e. SQL Monitors stats/metrics it captures and stores for reporting purposes) I couldn’t make the case for moving off the SANS.


Ok, I understand what you're describing.

If the VM host is ONLY going to host 1 VM EVER, then what is the value added by vmware? It's not free, and does introduce some (slight) overhead so it needs to be justified to be thrown into the mix. What is their justification?

Of course, if I was a shady sysadmin, I could say it would be dedicated to sql server, then later sneak some additional VM's onto it. That pesky DBA would never notice his server slowing down one day, and if he did complain, I could move the extra VM's off....
Post #1336002
Posted Thursday, July 26, 2012 11:31 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:53 PM
Points: 33,204, Visits: 15,353
SpringTownDBA (7/26/2012)

Ok, I understand what you're describing.

If the VM host is ONLY going to host 1 VM EVER, then what is the value added by vmware? It's not free, and does introduce some (slight) overhead so it needs to be justified to be thrown into the mix. What is their justification?

Of course, if I was a shady sysadmin, I could say it would be dedicated to sql server, then later sneak some additional VM's onto it. That pesky DBA would never notice his server slowing down one day, and if he did complain, I could move the extra VM's off....


hardware abstraction. Zero issues in moving hardware from the windows side. Ease of migration to new hardware.

There are some nice benefits on 1:1 host:vm planning. Especially if you install this as a single node cluster (and you should think about it).







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1336015
Posted Thursday, July 26, 2012 11:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 9:33 PM
Points: 359, Visits: 908
The hypervisor is free.

There are a lot of reasons they may want to do this. Management, monitoring, flexibility... It's much easier to move the whole VM to another physical host than to migrate the databases to fresh SQL instance.
Post #1336016
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse