What's the best storage for SQL Server 2000?

Question

I want to access my sql server database files in a INTEL SS4000-E storage. It´s a NAS Storage. Could it be possible to work with it as a storage for sql server 2000? If not, what is the best solution?

Answer 1

local is almost always faster than networked storage.

Your performance for sql will depend on how your objects, files, and filegroups are defined, and how consumers use the data.

Answer 2

It can work but a dedicated fiber attached SAN will be better.

Local will usually be faster but it has limited size and won't scale easily.

I'm not familiar with the hardware but we initially deployed a warehouse on a shared NAS. Here's what we found.

We were regularly competing for resources on the head unit -- there was only so much bandwidth that it could handle. Massive warehouse queries and data loads were severely impacted.

We needed 1.5 TB for our warehouse (data/indexes/logs) we put each of these resources onto a separate set of LUNS (like you might do with attached storage). Data was spanning just 10 disks. We ran into all sorts of IO bottlenecks with this. the better solution was to create one big partition across lots of small disks and store data, index and logs all in the same place. This sped things up considerably.

If you're dealing with a moderately used OLTP system, you might be fine but a NAS can be troublesome.

Answer 3

I strongly recommend against it.

Put your data files locally on the server itself, with RAID mirrored drives. The reasons are twofold:

• SQL Server will run much faster for all but the smallest workloads
• SQL Server will be much less prone to corruption in case the link to the NAS gets broken.

Use the NAS to store backups of your SQL Server, not to host your datafiles. I don't know what your database size will be, or what your usage pattern will be, so I can't tell you what you MUST have. At a minimum for a database that's going to take any significant load in a production environment, I would recommend two logical drives (one for data, one for your transaction log), each consisting of a RAID 1 array of the fastest drives you can stomach to buy. If that's overkill, put your database on just two physical drives, (one for the transaction log, and one for data). If even THAT is over budget, put your data on a single drive, back up often. But if you choose the single-drive or NAS solution, IMO you are putting your faith in the Power of Prayer (which may not be a bad thing, it just isn't that effective when designing databases).

Note that a NAS is not the same thing as a SAN (on which people typically DO put database files). A NAS typically is much slower and has much less bandwidth than a SAN connection, which is designed for very high reliability, high speed, advanced management, and low latency. A NAS is geared more toward reducing your cost of network storage.

Answer 4

Well "best" means different things to different people, but I think "best" performance would be a TMS RAMSAN or a RAID of SSDs... etc

Best capacity would be achieved with a RAID of large HDDs...

Best reliability/data saftey would be achieved with Mirroring across many drives, and regular backups (off site preferably)...

Best availability... I don't know... maybe a clone the system and have a hot backup ready to go at all times.

Best security would require encryption, but mainly limiting physical access to the machine (and it's backups) is enough unless it's internet connected.

Answer 5

My gut reaction - I think you're mad risking your data on a NAS. SQL's expectation is continuous low-latency uninterrupted access to your storage subsystem. The NAS is almost certainly none of those things - you local or SAN storage (in order of performance, simplicity and therefore preference) - leave the NAS for offline file storage/backups.

The following KB lists some of the constraints and issues you'd encounter trying to use a NAS with SQL - while the KB covers SQL 7 through 2005, a lot of the information still applies to SQL 2008 too.

http://support.microsoft.com/kb/304261

Answer 6

As the other answers point out, there will be a performance penalty here.

It is also worth mentioning that these things sometimes implement a RAM cache to improve I/O performance, if that is the case and you do trial this config, the NAS should be on the same power protection / UPS as the server hardware, otherwise in case of power outtage the NAS may 'loose' the part of the file in cache. ouch!