I plan to change the hardware for may SQL Server, and upgrade it to SQL Server 2016 Enterprise. AlwaysOn AG will be built on top of two nodes + dr.

There are two options I have in mind for storage:

have only local disks, ssd's in RAID1, with separate disks for Windows, Data, Logs and TempDb have a hybrid of local storage with ssd's for TempDb (RAID1) and the rest of the disks for Windows, Data and Logs to be provisioned from a SAN over the network I personally prefer the option with everything on local storage, because:

you get rid of single point of failure (the SAN) faster speeds on the local storage, the SAN will not have ssd's no network bottlenecks Are there any major disadvantages of using local storage?

Is using SAN for storage a better option?

Regardless of the solution, the hardware will be rented from a hardware provider. So there will be no buying involved.

Thanks in advance!

  • 2
    There are way too many variables for an easy answer. What type of SAN? "Single point of failure" on a high-end SAN is quite likely a lot more reliable than even a cluster of commodity x86 servers. How are backups done - is that integrated into the SAN, or would you have to configure backups either way? How good are your SAN administrators? Are they doing things like creating huge 31-drive RAID6 arrays and slicing that one 60+ TB RAID array into tens or hundreds of LUNs - so everyone using the SAN has to fight for disk IO time with everyone else's IO? Then can't figure out why it's S-L-O-W? – Andrew Henle Mar 31 '17 at 14:57
  • 1
    The infrastructure will be hosted by a third party hardware provider. All the maintenance will be done by them. So, I have no idea how good their SAN admins are, or how constant the SAN speed will be.(even if they guarantee a specific speed) The backup plan is going to be done separately, regardless of whatever backup they do. I am aiming for local storage because there are less "parts" and I'm hoping for a higher speed. – silviu.f Apr 3 '17 at 13:51
  • 1
    SANS is single point of failure, IMHO – Net Runner Apr 4 '17 at 18:31
  • 1
    Local disks have their points of failure too. Redundant hot swap controllers are standard on storage arrays. You might not need that with a database replicated application. Its perfectly fine to rely on OS and application availability solutions, but understand that during design. – John Mahowald Apr 5 '17 at 12:18

With regards to Microsoft's docs you should go with either local storage, or shared storage or Microsoft Storage Spaces Direct.

Assuming you what achieve availability for MSSQL Server, I would suggest you go with local storage, identical setup for each host wich would provide you same performance for the case of node failure.

Go with shared storage if you would go with MSSQL FCI.

As for SAN, its SPoF. IMHO

| improve this answer | |

As always, there are many advantages and disadvantages to everything.

A storage array provides high capacity and centralized management. You can snapshot some volumes and present them to another host. Although, the switches and cabling to connect the SAN up can be a bit more complicated.

Local storage has fewer server nodes involved and may be more familiar to the OS administrator. There may be limits on capacity that fit in the server, unless you add something like a disk enclosure.

Database level replication makes it relatively easy to use two independent storage systems for a business continuity solution. A storage level failure then can be mitigated by activating DR. Single point of failure analysis is still a good idea, checking for redundant power supplies and SAN paths and such. The DR copy, possibly at a remote site, makes recovery faster if the primary copy becomes unavailable.

| improve this answer | |
  • We plan to rent the equipment for a period of max 2 years. So, the size of the storage it is not that hard to predict. Probable we will add more than the estimates from the start, just to be safe. I was more worried about possible risks for going with everything on local storage. Things that I did not take in consideration and can bite back after a while. – silviu.f Apr 3 '17 at 14:01

Personally, I'm seeing SANs becoming a relic of a bygone era. Don't get me wrong - like the mainframe they have their place, and used appropriately they are hugely valuable. Large virtualisation installations wouldn't be possible without them. Once upon a time, shared storage like a SAN was the only way to make things like SQL Server or VMWare ESXi highly available.

But to get a SAN that is as fast as local SSD storage is very expensive. To get one that's as fast as NVMe storage (such as an Intel P3608) is into six figures. Compared to $8,000 for a top of the line 4TiB P3608 - cheaper if you don't need 4TiB of storage.

With SQL Server's Always On providing a good high availability option, with local storage, synchronous or asynchronous replication options, and with Distributed Availability Groups, there is no reason to be deploying a SAN for the vast majority of new SQL Server installations.

There is no downside as to using local storage as far as SQL Server is concerned.

| improve this answer | |
  • What about costs? AGs are part of Enterprise only, Standard has BAGs but they are limited (very limited). – BaronSamedi1958 Jun 6 '17 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.