May 2, 2012 at 2:13 pm
Hi folks,
Trying to ensure my current knowledge of best storage solutions for SQL Server is up to date, in light of recent developments at my company. We have some emergency hardware coming in so that we can shift some older, dying SQL 2000 and 2005 servers to it.
Additionally, we are about to build a massive Data Warehouse of around 8tb of data and I want it to perform super fast.
1. The Data vs Logs debate - on virtualise/san servers. Its taken me a long time to get our Logs and Data on separate physical disk sets, despire my benchmarking showing the benefits. BUT now with our new DWH solution the claim from our technical partners is that there will be so many fast disks that separating the disks into Data and Log LUNs is not necessary. I claim otherwise. Also I think we should have a data LUN PER SQL SERVER not for ALL SQL Servers on the 1 host. Is that true?
2. I'm concerned that we are going to have 3 Physical hosts containing around 10 SQL Servers. Regardless of disk setup at storage level, isn't there a risk of bottlenecks between the Hosts and the Storage area?
3. SSD disks. The emergency solution is going to be a storage area built on solaris. It is going to contain a bunch of SSD disks using the ZFS file system . I don't have a clue of the impact of this on SQL Storage (specificially the ZFS compression).
Also I gather this could lead to switching the strain from disk to bus level. The alternative of using a PCI card for the bus is not open to us as multiple SQL servers will be using these disks. Another issue could be that not enough RAM is in place to service the SSD. I'm struggling to find enough information on using a Solaris SSD ZFS storage area for multiple SQL Servers to use. And how to separate the disks.
4. On the subject of ZFS. Is there something regarding Read Cache and Write Cache? I'm trying to work out on paper the benefits of each. I'm thinking using the Write Cache on a bunch of disks for the logs perhaps with Read Cache for the data.
Any help anyone can provide would be greatly appreciated as I piece this together (in the case of questions 3 and 4) and reconfirm my beliefs (questions 1 and 2!)
Thanks
Shark
May 3, 2012 at 3:56 am
Pretty please? :hehe:
May 3, 2012 at 8:07 am
On points 3 and 4, I'm not sure you're going to find much real-world experience of this setup - it sounds pretty niche.
On the one hand, SSD's can be blindingly fast, but putting a VM layer and ZFS layer between SQL Server and it is likely to hinder the performance. AFAIK, ZFS is more useful as an actual file system serving a high number of files (e.g. a large CIFS share with thousands of users), this way, the caching/journaling mechanisms are top class. In your scenario though, you'll have a very small number of large files (Virtual Disks), with no access at the ZFS layer to be able to proactively cache anything.
Compressing this data at the file system level with ZFS serving VM's and VM's serving SQL Server though sounds like a disaster. What's the point of going for ultra fast SSD's, then bottlenecking everything in CPU? If you really need compression, plump for Enterprise Edition and do it at the SQL Server level (sounds like you have plenty of cash if you're throwing SSD's at it :-))
On point 1, it'll depend on the hardware solution, but it can be argued that you'll get better recoverability from physically seperating the spindles.
Point 2 - it depends, but yes, there are I/O bottlenecks at the host level, but no idea if you'll reach them
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply