January 4, 2012 at 5:15 am
Hi all
I'm looking for some feed back in regards to the below mentioned challenges. In our organisation we have over the last 5 years mainly done scale up, i.e. tried to host as many DB's as possible on large clustered instances (SQL 2005 EE).
With a new IT manager this strategy have changed slightly into a combined scale up / scale out approach based on scale up on large VM-ware ESX hosts and scale out via more SQL instances on VM's as we have had before. The strategy is to have the entire datacenter in VM-Ware.
So I'm challenged with building the SQL template servers for this new infrastructure, so logically I'm looking into standardization as much as possible.
A bit background info:
We are a medium sized business with 450 users and around 200 servers in total including all test, acceptance test and production systems. The number of SQL instances we have is around 20, hosting around 150 databases, with the biggest being around 230 GB.
The hardware:
12 * 3GHz Xeon CPU's
100 GB of memory
Disk platform:
IBM V7000 SAN
Tier1 and Tier2 storage is identically duplicated at our DR site. Tier 1 storage has 150 spindles (560 GB), but not dedicated to SQL alone, thus shared with file servers and exchange. Tier 2 storage has 60 spindles (1.8 TB), mainly used for non critical production systems as well as acceptance test and test systems.
As of what I know now our SQL platform will be hosted on 4 servers of the above mentioed hardware, and have their storage in the tier 1 storage. The LUN presented to the VM-Ware hosts would have a size of 500GB to 1TB
Until now we have split tempdb, data and log files on our large database clusters as per Microsoft recomendations. Even though they were seperate LUN's inside the same SAN storage disk pool. For the disk layout in the new platform I was initially in favor of
a simpler setup with only a single drive, since all logical drives inside the VM is residing inside the same LUN. Or does it still make sense to split tempdb, data and log? I have seen statements on the later arguing that Windows will be able to perform parrallel disk access in case of multiple drive usage at the host level. But is this really the case?
I know this is a slightly religious discussion, but I of cause also don't want to create a foundation that are bad from the outset. On the other hand if there is no performance benefit, I would like to avoid the additional management and space loss with the three drive letter allocation per instance.
So what's your take on this what would you do?
Best regards
Søren Udsen Nielsen
January 19, 2012 at 9:36 am
Hi,
No one with any input on this ?
//Soren Udsen Nielsen
January 27, 2012 at 12:01 am
Soren Nielsen (1/4/2012)
Hi allI'm looking for some feed back in regards to the below mentioned challenges. In our organisation we have over the last 5 years mainly done scale up, i.e. tried to host as many DB's as possible on large clustered instances (SQL 2005 EE).
With a new IT manager this strategy have changed slightly into a combined scale up / scale out approach based on scale up on large VM-ware ESX hosts and scale out via more SQL instances on VM's as we have had before. The strategy is to have the entire datacenter in VM-Ware.
So I'm challenged with building the SQL template servers for this new infrastructure, so logically I'm looking into standardization as much as possible.
A bit background info:
We are a medium sized business with 450 users and around 200 servers in total including all test, acceptance test and production systems. The number of SQL instances we have is around 20, hosting around 150 databases, with the biggest being around 230 GB.
The hardware:
12 * 3GHz Xeon CPU's
100 GB of memory
Disk platform:
IBM V7000 SAN
Tier1 and Tier2 storage is identically duplicated at our DR site. Tier 1 storage has 150 spindles (560 GB), but not dedicated to SQL alone, thus shared with file servers and exchange. Tier 2 storage has 60 spindles (1.8 TB), mainly used for non critical production systems as well as acceptance test and test systems.
As of what I know now our SQL platform will be hosted on 4 servers of the above mentioed hardware, and have their storage in the tier 1 storage. The LUN presented to the VM-Ware hosts would have a size of 500GB to 1TB
Until now we have split tempdb, data and log files on our large database clusters as per Microsoft recomendations. Even though they were seperate LUN's inside the same SAN storage disk pool. For the disk layout in the new platform I was initially in favor of
a simpler setup with only a single drive, since all logical drives inside the VM is residing inside the same LUN. Or does it still make sense to split tempdb, data and log? I have seen statements on the later arguing that Windows will be able to perform parrallel disk access in case of multiple drive usage at the host level. But is this really the case?
I know this is a slightly religious discussion, but I of cause also don't want to create a foundation that are bad from the outset. On the other hand if there is no performance benefit, I would like to avoid the additional management and space loss with the three drive letter allocation per instance.
So what's your take on this what would you do?
Best regards
Søren Udsen Nielsen
Ok, you've got 4 large esx boxes, and a large shared pool disks (Raid 10, I hope!)
What number of IOPS do you require for all your sql servers? Can the ESX boxes handle the io with fewer HBA's?
As for splitting the files across multiple drives, I'd recommend putting your tempdb (both data and log) on a seperate volume. If you need to add another tier of storage that is SSD based, you can easily move tempdb to the faster tier.
January 27, 2012 at 1:30 pm
Hi SpringTown
Thanks for your reply, first a correction to the hardware used it seems to be 12 * 4 core, so 48 cores per server, and I'll get six one for each instance used today.
If I knew the IOPS it would not be that tricky. In that case one could just use SQLIO on old setup vs. new. But sadly I don't have a specific iops benchmark I need to satisfy, it's more soft thing like "the server should not be slower than when it was on physical hardware", compensating that the new hardware is 4 years newer.
The aproach for migratiing our existing cluster boxes on to the VM-ware platform is to move the VS of the cluster, into a newly installed instance of the same name as the old cluster VS.
For SAN performance I'm not that worried as the old HBA's were 2Gbit and the new ones is 8Gbit, thus quadrupling the potential throughput. The idea is to co host smaller low activity boxes with the SQL as memory and CPU load of the SQL permits.
I'm not a SAN wizard but as I can see the config we have used on the IBM V7000 is Raid5 but as I said with 150 spindles on fairly small disks 560 GB., But perhaps the LUN are stripped across smaller RAID5 arrays, I can't really figure out how the V7000 does this, as the LUN says striped and from the disk view they say Raid5.
For migration of existing boxes we have decided to keep all disk layout the same, to simply migration, i.e split disks (tempdb, data and log) before = the same after migration.
For the new layout I'm still think if not ones disk is the best, as the data would end up on the same spindles no matter how many LUN's I would create. And I can always later move tempdb anyway to a seperate LUN if I would see a performance issue.
Best regards
Søren Udsen Nielsen
April 25, 2013 at 9:02 pm
Hi Soren,
Seems as if this topic has gone off the boil!
What did you eventually decide to do?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply