Windows Server 2012 R2 Tiered Storage for MS SQL?

KapsZ28

2[H]4U
Joined
May 29, 2009
Messages
2,114
I need to build a couple of physical servers to run MS SQL databases for eDiscovery software. I was thinking about using Windows Server 2012 R2 with the tiered storage pools to take advantage of SSD caching. I was curious if anyone has done this for SQL and has any recommendations.

I was thinking of using four 400-480 GB SSDs and eight 900 GB SAS to create the storage pool with an additional SSD and HD as a hot spare.

For the SSDs I started looking at the Samsung 845DC EVO for a decent price vs performance. The I noticed that the TBW is only 300 TB, which seems a bit low especially compared to Crucial's Micron M500DC which has a TBW of 1.9 TB. As far as price, I believe both the Samsung and Crucial are around the same price. Besides the TBW number, I have always been a bigger fan of Samsung over Crucial and I also noticed that Crucial sets aside 42% of the NAND for over-provisioning and RAIN.

Then I was also looking at the Intel DC S3700 which I don't see an exact TBW, but the lifetime endurance is "Up to 10 Drive Writes Per Day" on the 400 GB SSD. Not sure the length of time that is over, but even at 1 year that would be 1.5 PB. But the Intel is a bit more expensive.

So I am not sure what would be the best SSD to choose.

For the SAS drives I was thinking of going with the Hitachi Ultrastar C10K900 900GB 10000 RPM 64MB Cache SAS 6Gb/s 2.5" drives.

Since RAID is handled in Windows, I would simply go with an HBA.

So far does any of this even sound like a good idea?
 
All I know is that I looked at Storage Solutions recently and even with SSD caching (not SSD's as part of the data pool, JUST as cache) the parity write performance was terrible compared with pretty much every alternative.
 
I've been fairly happy with the Raid10 performance of Storage Spaces(Mirrored). The tiering in SS only happens once a day though so unless you have fairly static or slowly changing data the SSD tier is almost worthless. For me it works fairly well as its a Hyper-v Server and the busy parts tend to stay in cache but it is still a gotcha.

Depending on what the budget is. I would also look at an LSI card with Cachecade enabled. Same idea but done in hardware.
 
I need to build a couple of physical servers to run MS SQL databases for eDiscovery software. I was thinking about using Windows Server 2012 R2 with the tiered storage pools to take advantage of SSD caching. I was curious if anyone has done this for SQL and has any recommendations.

I was thinking of using four 400-480 GB SSDs and eight 900 GB SAS to create the storage pool with an additional SSD and HD as a hot spare.

For the SSDs I started looking at the Samsung 845DC EVO for a decent price vs performance. The I noticed that the TBW is only 300 TB, which seems a bit low especially compared to Crucial's Micron M500DC which has a TBW of 1.9 TB. As far as price, I believe both the Samsung and Crucial are around the same price. Besides the TBW number, I have always been a bigger fan of Samsung over Crucial and I also noticed that Crucial sets aside 42% of the NAND for over-provisioning and RAIN.

Then I was also looking at the Intel DC S3700 which I don't see an exact TBW, but the lifetime endurance is "Up to 10 Drive Writes Per Day" on the 400 GB SSD. Not sure the length of time that is over, but even at 1 year that would be 1.5 PB. But the Intel is a bit more expensive.

So I am not sure what would be the best SSD to choose.

For the SAS drives I was thinking of going with the Hitachi Ultrastar C10K900 900GB 10000 RPM 64MB Cache SAS 6Gb/s 2.5" drives.

Since RAID is handled in Windows, I would simply go with an HBA.

So far does any of this even sound like a good idea?
All this, but no mention of the working file size? How big is the database?
 
I've been fairly happy with the Raid10 performance of Storage Spaces(Mirrored). The tiering in SS only happens once a day though so unless you have fairly static or slowly changing data the SSD tier is almost worthless. For me it works fairly well as its a Hyper-v Server and the busy parts tend to stay in cache but it is still a gotcha.

Depending on what the budget is. I would also look at an LSI card with Cachecade enabled. Same idea but done in hardware.

Hmm, I guess that would kind of suck if most of the data is not being cached.
 
All this, but no mention of the working file size? How big is the database?

There is going to be two SQL services. Both have just over 100 databases each but most databases don't even seem to be used. I believe it depends on the case they are working on that determines which database is used. One SQL server has 562 GB in databases. Smallest being 100 MB, and largest is 100 GB. The second SQL server has 1.34 TB in databases. Smallest being 100 MB, and largest is 350 GB.

In their current setup they are frequently getting LockTimeouts, but the databases are stored on a SAN using 1Gbps iSCSI LUNs attached to Windows Server 2008 R2.
 
Depending on what the budget is. I would also look at an LSI card with Cachecade enabled. Same idea but done in hardware.

That CacheCade looks interesting. I've never used it before. Budget is around $10k per server. Was thinking of getting a 2U chassis that hold 24 disks. Which LSI card and SSD's would you recommend?
 
There are a lot of things to consider when doing Storage Spaces and a lot of bad information out there. Just to clear up some things, you can only use a mirrored space with tiering, no parity yet. Also, you can schedule the tiering optimization to occur whenever you want and however often you want; it's simply a task scheduler job running a powershell command. Next, when sizing your SSD tier you also need to consider what your stripe size will be, since you will need the same stripe size in your SSD tier. For example, using your current spec, you would have to use two columns with two copies, which would create a four drive stripe, since you have only speced four SSDs. If you had eight SSDs, you could have a four column, two copy stripe, for a total of eight (which is the recommended optimum stripe size).

With four SSDs you will also get a striped Write Back Cache in the form of two mirrors, which will greatly help write performance. With eight drive it would be four mirrors, which increases writes at a pretty linear rate. What's really going to matter, more than the size of your databases, is what is your ACTIVE working set size? Usually it isn't completely random a ross the whole database, but it really depends. You will want to size your SSD tier for this data set size. Another thing to consider will be the block size of the stripe, and matching it up with your applications write size, which will optimize writes and reduce creating multiple write across stripe boundaries.

Storage Spaces is pretty complex and very customizable, but you need to know what you are doing and have a good handle of what your dataset needs. It's not as simple as creating a pool with some SSDs and slapping your data on it.

Jake
 
In their current setup they are frequently getting LockTimeouts, but the databases are stored on a SAN using 1Gbps iSCSI LUNs attached to Windows Server 2008 R2.

Your biggest problem is here. a 1Gbps link for collection of databases totaling 1 or 2TB blows and with one of the databases being 300 GB in size it's even worse.

What I would do would be to put the databases in RAID 10 locally and evict out to the SAN for backup, or upgrade that link (which should probably be done anyway). That alone will improve things drastically. I would do either of those things first before doing anything else.
 
Well, in the current system what does the iSCSI connection look like in terms of utilization? Do you ever max out?

Also, I wouldn't put any SSD's into an environment like this other than Intel 3600 or 3700 SSD's - or their newer versions.
 
Just an FYI if you happen to have sharepoint 2013 it has a built ediscovery service (just incase you do you wouldn't have to mess with even more software)
 
Your biggest problem is here. a 1Gbps link for collection of databases totaling 1 or 2TB blows and with one of the databases being 300 GB in size it's even worse.

What I would do would be to put the databases in RAID 10 locally and evict out to the SAN for backup, or upgrade that link (which should probably be done anyway). That alone will improve things drastically. I would do either of those things first before doing anything else.

The SAN and our servers are now 10Gb, but their server is still 1Gb. The plan is to move them to a different datacenter which also has 10Gb and a much better SAN, but most likely because of cost constraints, they are moving to local storage instead of our SAN.
 
Well, in the current system what does the iSCSI connection look like in terms of utilization? Do you ever max out?

Also, I wouldn't put any SSD's into an environment like this other than Intel 3600 or 3700 SSD's - or their newer versions.

January shows an average 217/402Mbps for in/out with a peak of 689Mbps. Plus there is a decent amount of other VMs running on the same SAN.
 
You can get a 4 port gig Intel NIC off ebay for ~50 or so. Teaming it up would go a long way to avoid any future bottlenecks. Hell, get 2 and have 8 gig bonded.
 
The SAN and our servers are now 10Gb, but their server is still 1Gb. The plan is to move them to a different datacenter which also has 10Gb and a much better SAN, but most likely because of cost constraints, they are moving to local storage instead of our SAN.

Local storage it is then. I would just buy the hard drives first and leave the cache drives alone until you can assess performance.
 
Local storage it is then. I would just buy the hard drives first and leave the cache drives alone until you can assess performance.

Well, now I am being told that SAN may still be an option. So let me see what you think.

SAN is NetApp FAS8040. Tier 1 is 46 SAS 10k RPM disks with 600 GB of SSD Flash Pool. Each controller has a 20Gbps link and IOPS is supposed to be around 50k. Personally this is where I would like to put the databases.

The other option is local storage. All SSD is way too expensive. Even 10x 10k SAS drives is only about 1,500 IOPS I believe. I am not totally sure how the SSD caching will work but the LSI CacheCade does seem interesting.

Right now our NetApp is underutilized, but sometime next month we will be moving two 1Tb Oracle databases to the same Tier 1 storage.
 
Hmm, I guess that would kind of suck if most of the data is not being cached.

Its one of the gray area things. If the same database is always the hard hit and it just lives in the cache it works pretty well. But it if the data is fairly dynamic it doesn't always do the job but that is the case is a lot of tiering solutions.

That CacheCade looks interesting. I've never used it before. Budget is around $10k per server. Was thinking of getting a 2U chassis that hold 24 disks. Which LSI card and SSD's would you recommend?

I used a 9265-8i is the LSI card I used but that is a few years old. I'm not sure of the current model.

Intel Datacenter drives tend to be my choice there.
 
Well, now I am being told that SAN may still be an option. So let me see what you think.

SAN is NetApp FAS8040. Tier 1 is 46 SAS 10k RPM disks with 600 GB of SSD Flash Pool. Each controller has a 20Gbps link and IOPS is supposed to be around 50k. Personally this is where I would like to put the databases.

The other option is local storage. All SSD is way too expensive. Even 10x 10k SAS drives is only about 1,500 IOPS I believe. I am not totally sure how the SSD caching will work but the LSI CacheCade does seem interesting.

Right now our NetApp is underutilized, but sometime next month we will be moving two 1Tb Oracle databases to the same Tier 1 storage.

Well keeping the databases on the SAN is beneficial for a lot of reasons. If it were me I would keep them there. The disks on your SAN are plenty fast for the job at hand. The problem is the link between the server and the SAN. Hitting 75% - 80% utilization on a 1Gbps DB link sounds about right to me with the left over being taken up by latency and overhead especially considering the number of requests and the number of databases. Essentially as you stated the SAN is woefully underutilized. Depending on the day you are maybe using 1/10 of what that appliance is capable of. Start there and work backwards.
 
Last edited:
SSD's look to Intel DC 3700 Series, don't use desktop drives in servers, most don't have proper power control if power is lost suddenly.

Why do you need a SAN, you likely don't and if you do NEED a san then you need 2, otherwise welcome to the inverted pyramid of doom scenario

http://www.smbitjournal.com/2013/06/the-inverted-pyramid-of-doom/

Start reading about SAN's
http://www.smbitjournal.com/2013/06/when-to-consider-a-san/

Local storage is always a better option vs a SAN and using something like vsan/ starwind if you need shared storage.
 
I can't imagine Server 2012 Storage Tiering being that great with databases.

I'd rather use a raid controller with flash cache.
 
I have skimmed over the content of the thread and as much as I use SS, this application is not for small office or home use. You are talking serious sized databases and thus should not be messed with.
Hardware RAID with decent drives is the answer that comes to my mind. SSD's are a lot better than years ago but the lack of TRIM in RAID cards still suck while I am still asking why vendors are so slow at making it happen. Mind you, I have had Intel 520 SSD's on HW RAID for about 2.5 years, no issues.
 
it's not indeed

attempts to keep log transactions in flash by tiering level are ridiculous

msft storage team and sql server team are enemies ))

I can't imagine Server 2012 Storage Tiering being that great with databases.

I'd rather use a raid controller with flash cache.
 
Back
Top