July 25, 2012 at 12:29 pm
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!July 25, 2012 at 12:34 pm
I'd want D and Z to also use RAID-10 as well.
July 25, 2012 at 12:37 pm
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?
July 25, 2012 at 1:56 pm
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!July 25, 2012 at 3:26 pm
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.
July 25, 2012 at 4:33 pm
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.
July 26, 2012 at 9:42 am
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!July 26, 2012 at 11:19 am
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....
July 26, 2012 at 11:31 am
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).
July 26, 2012 at 11:31 am
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.
July 27, 2012 at 8:12 am
1) the "disk" configuration you gave us is almost useless from an "is it configured well" perspective because you gave us no idea about the underlying IO subsystem configuration other than RAID type. A 1000MB "disk" could be a single 7200RPM SATA drive shared by lots of other stuff or it could be a slice of a 3-PAR 1000-spindle SAN that can support 300K IOPS. You, as the SQL Server DBA, simply MUST know EXACTLY what underlies each of your "disks" and also know anything else that might share the same IO pieces-parts.
2) VMWare can support MASSIVE amounts of RAM, CPU and IO now. IIRC 2TB RAM, 32vCPUs and a system was benchmarked at 1MILLION IOPS on the IO side. I assure you that a properly configured VMWare system can support your 300GB database with a few hundreds or a few thousands of concurrent users. I note that that doesn't actually mean your database and applications can support that! :hehe:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 27, 2012 at 12:01 pm
Steve Jones - SSC Editor (7/26/2012)
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).
Steve/SpringTownDBA - The reasons listed above by Steve are the same that I was given for the use of VMWare and to my understanding it is a single node cluster.
One thing that this is NOT is cheaper. The additional license costs to use VMWare almost tripled the total cost of the server; at least thats what I was told. I admit I can not speakk about VMWare from a knowelagable perspective but it does NOt sound like a good deal to me when it drives up the total cost that much.
Thanks
Kindest Regards,
Just say No to Facebook!July 27, 2012 at 12:12 pm
TheSQLGuru (7/27/2012)
1) the "disk" configuration you gave us is almost useless from an "is it configured well" perspective because you gave us no idea about the underlying IO subsystem configuration other than RAID type. A 1000MB "disk" could be a single 7200RPM SATA drive shared by lots of other stuff or it could be a slice of a 3-PAR 1000-spindle SAN that can support 300K IOPS. You, as the SQL Server DBA, simply MUST know EXACTLY what underlies each of your "disks" and also know anything else that might share the same IO pieces-parts.2) VMWare can support MASSIVE amounts of RAM, CPU and IO now. IIRC 2TB RAM, 32vCPUs and a system was benchmarked at 1MILLION IOPS on the IO side. I assure you that a properly configured VMWare system can support your 300GB database with a few hundreds or a few thousands of concurrent users. I note that that doesn't actually mean your database and applications can support that! :hehe:
1) Well thats all I've been told so far and since I don't get to make the call (just provide input) I'm not so sure knowing more about the plan would result in any changes being made should it turn out that one or more of those decisiosns are bad. As far as knowing whats under the hood, pro-VM admins (at least in my experience) seem to take a "you don't need to know because VM is no different then non-VM" approach to the whole thing. I guess they figure if you know exactly whats behind the VW screen it might skew your perception of how its relaly performing. All I can do is make my reocmendations and show the metrics for our existing setup (Server + SANS storage) and maker recomendations on what changes shoudl be made from that for the new system.
2) I have read from pro-VM SQL gurus that running SQL Server on VMis fine so long as its done right and under the right scenario. My concern is, as you light heartidly joked about, that the app which uses the DB is not optimized to work with large DB's and so any additonal action taken that can impact performance will be magnified.
Thanks
Kindest Regards,
Just say No to Facebook!July 27, 2012 at 12:48 pm
YSLGuru (7/27/2012)
Steve Jones - SSC Editor (7/26/2012)
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).
Steve/SpringTownDBA - The reasons listed above by Steve are the same that I was given for the use of VMWare and to my understanding it is a single node cluster.
One thing that this is NOT is cheaper. The additional license costs to use VMWare almost tripled the total cost of the server; at least thats what I was told. I admit I can not speakk about VMWare from a knowelagable perspective but it does NOt sound like a good deal to me when it drives up the total cost that much.
Thanks
Just curious, do you have specs on the new server? mfg/model, number and type of processors, total ram, etc.
I wouldn't think that vmware would make up a huge percentage of the total cost of a large sql server but I'm not very familiar with vmware's pricing model.
From research I've done for our planned 2012 upgrade of our 2-node cluster, sql license costs are going to run 60-65% of the total.
July 27, 2012 at 1:15 pm
SpringTownDBA (7/27/2012)
Just curious, do you have specs on the new server? mfg/model, number and type of processors, total ram, etc.
I wouldn't think that vmware would make up a huge percentage of the total cost of a large sql server but I'm not very familiar with vmware's pricing model.
From research I've done for our planned 2012 upgrade of our 2-node cluster, sql license costs are going to run 60-65% of the total.
I'm curious too. Depending on the # processors & total RAM, it sounds like you could get away with an Essentials Plus license which clocks in just under 5 grand and is good for up to 3 bare metal hosts. Of course, if you add in all the other possible management bells and whistles the costs can add up pretty quickly. Then again you're using that across your entire infrastructure not just on this one host so the costs should be distributed.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply