Blog

Geeks love duct tape.  Sure, we pride ourselves on building a rock-solid solution the right way, but when the brown stuff hits the moving metal stuff, we love to show off our ingenuity.  We carry our money in duct tape wallets, wear duct tape shirts, practice ductigami, and hang our duct tape from custom metal brackets.

I’m about to discuss a feature of SQL Server that’s probably going to horrify you at first, but take a step back and think of it as duct tape.  I don’t want you to build a solution from scratch with it, but it’s a killer tool that deserves a prominent spot in your bag of tricks.  Since I’ve started using SQL 2012 in my lab, not a week has gone by that I haven’t used this feature to do something faster and easier.

A Brief History Lesson on Trace Flag 1807

In the past, Microsoft SQL Server required its database files to be stored on local drives or SAN storage.  The SQL Server 2000 I/O Basics whitepaper explains in stern terms why not just any storage will do – we have to be sure our data will hit the disk in a calm, orderly fashion.  Server vendors got the point, and everybody’s local storage and SAN storage followed the guidelines.  Over time, we stopped buying hardware from a Windows Hardware Compatibility List because, well, everything worked.  If you could buy it off the shelf from a major vendor, you were fine.

For ambitious people who wanted to gamble with their data, trace flag 1807 let you store your database files on network shares or mapped drives.  This was a pretty bone-headed idea – heck, it was hard enough to get our local and SAN storage to be reliable enough – so few people used it.  Database administrators are usually way too paranoid to let their databases get corrupted just because somebody tripped over a network cable or a $200 NAS box failed.  Even if you didn’t mind unreliability, performance was a major problem – network file shares just weren’t fast enough to handle database access.

Network attached storage gained mainstream credibility over the last few years, and it’s gained widespread use thanks to virtualization and the Network File System protocol (NFS).  Under virtualization, each drive on your virtual server is really just one big file (VMDK or VHD), and it’s easier to manage accessing big files on a file share rather than mapping a bunch of LUNs to VMware hosts.

With tuning, NFS performance is fine for virtualization, and this has a bit of a hidden meaning: if we’re running our entire SQL Server in VMware, then we’re already doing database access over NFS.  So why can’t we let physical SQL Servers access their own databases via NFS too?

SQL Server 2008R2 and 2012 <3 Your NAS

SQL Server 2008R2 did away with the trace flag and lets you put your data & log files anywhere.  This statement works fine:

CREATE DATABASE [DestinedForGreatness]
ON  PRIMARY
( NAME = N'DestinedForGreatness', FILENAME = N'\\LittleBlackBox\SQL2012data\MSSQL\Data\DestinedForGreatness.mdf' )
LOG ON
( NAME = N'DestinedForGreatness_log', FILENAME = N'\\LittleBlackBox\SQL2012data\MSSQL\Data\DestinedForGreatness_log.ldf')
GO

And just to make things really clear, that statement doesn’t just work fine – it doesn’t give you a single warning.  Just like that, you’ve got a database relying on a network share, along with all the gotchas that entails.  Its database icon looks just like the rest in SSMS, and there’s nothing to suggest that your hosting strategy just got a lot riskier.

The Six Million Dollar Man's children had smaller budgets to work with.

File share access goes over the same network paths as your client communications (queries, results, RDP sessions, etc).  A heavily accessed database could saturate your network cards, thereby slowing down everything else – or vice versa.  An unrelated file copy or backup could bring your network-attached database to its knees.

You also have to start monitoring the remote file share’s performance.  It can get slowed down by CPU or memory issues, or just by other activity happening on that same file share.  This means we have to capture Perfmon data on that remote file server, including the physical disk counters.  I don’t see this as a drawback – after all, I need to do this same level of performance monitoring on my SAN, too.

Use Cases for Databases on NAS

When I first read about this SQL Server feature, I had the same reaction you’re probably having: nobody in their right mind should use this, right?  As it turns out, though, I keep using it to make my life easier.

Low log file space emergency?  Add a file on a NAS.  With a few mouse clicks, disaster is averted.  Let your transactions continue (albeit slowly), then go back later to remove that extra log file.

Need to restore a database fast?  Attach it from anywhere.  I’ve often needed to restore just one table from a big database backup, but I haven’t had the room to restore the entire database in production.  No problem – just restore the database on another server (like development), but use a network share name like \\MyFileServer\TempFolder\DatabaseName.mdf as the target.  All of the disk-intensive and network-intensive work happens on the dev server.  When the restore finishes, detach the database in development, and the production server can attach the database while it sits on the network share.  Copy out the tables you need, detach the database, and you’re off.

Hello Kitty Duck Tape

Hate drive sizing hassles?  Use NFS.  I run a lot of SQL Servers in virtualization, and I move databases around from server to server.  Rather than worrying about whether a server has enough free drive space, I’ve just created a file share on my NAS, and all my SQL Server 2012 instances attach their databases from that share.  I can manage free space as a pool, and I never have to worry about an individual server running out of free space again.

Should You Run Databases Over the Network?

The amateur question is, “Do you feel lucky, punk?”

The professional question is, “Do the performance and reliability of your network file shares match up with the performance and reliability needs of the database?”

When I’ve asked myself the professional question, I’ve been surprised at some of my answers.  Network storage probably shouldn’t be your first default choice for new databases and files, but give it a chance.

The key to success is getting everybody to agree on both the capabilities of the NAS and the requirements of the database.