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:
|
1 2 3 4 5 6 |
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.
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.
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.
Update 2021-01-16: This didn’t catch on.
Despite increasing network bandwidth, this feature just never became much of a thing. I’ve run into a few shops that used it, but we’re talking one-in-a-thousand SQL Servers type thing.
71 Comments. Leave new
Great article Brent! I’m constantly amazed at how many system admins will ding a solution “just because” or without really understanding how it works and the pros and cons, but then elevate another solution as “the answer” but don’t have any greater understanding of that solution either.
In the end, its “what they know” even though they don’t really understand why things work the way the do.
Heresy, complete unabridged Heresy.
Oh wait, the NetApp is calling…….
One issue that I ran into is if you are backing up your databases with DPM 2010, it cannot handle the underlying database files being on UNC paths.
Scott – yep, because you can’t take snapshots of a remote UNC path. Great point. I’ve blogged about my challenges with DPM before too:
https://www.brentozar.com/archive/2008/08/microsoft-dpm-2007-review/
Very nice read on SQL 2012 and virtualization.
Thanks for the informative writeup and Nice blog by the way.
Thanks, glad you liked it!
Great article.
A thought though, would I be able to run a DB in SQL 2000 compatibility using a network share. Could enable quick DR data testing without changing application, DNS Server names or ODBC type definitions.
Cheers
Paul
Paul Tormey
January 13, 2012 | 2:44 PM
Brent,
Left a question for you a couple of days ago:
A thought though, would I be able to run a DB in SQL 2000 compatibility using a network share. Could enable quick DR data testing without changing application, DNS Server names or ODBC type definitions.
Any comments?
Paul
Paul – yep, I did see that, but is this something you think you might be able to try yourself to get the answer you’re looking for? I bet it’d actually be easier to try this yourself rather than typing in the question once – let alone twice. 😉
https://www.brentozar.com/wp-includes/images/smilies/icon_wink.gif
Yep Brent,
Will give it a go when I get to the office.
Good read. Thanks again Brent, adding a much-needed additional LDF to a network share is a great temporary fix.
Excellent article! See, SQL server is never boring. There is always something interesting to do and learn.
Cheers!
Rudy
Thanks for pointing out some new nice tricks to have in your toolchest, I have at times been restoring to a secondary server and moving SAN LUNs, but this approach makes it even easier when I don’t have to involve the SAN admin.
Hi Brent,
What about configuring an iSCSI target on the storage server rather than mapping a network drive? It’s free and might be more resilient
http://www.microsoft.com/download/en/details.aspx?id=19867
Regards
Paul
Paul – it’s only free if you’ve got iSCSI set up, and most folks don’t. The reason this feature is attractive to me is its instant flexibility. You don’t need anything set up in order to do it. Tell your network admins that you’re running iSCSI over the regular Windows network, though, and you’re liable to have a mutiny on your hands. 😉 If you want more resiliency, I’d definitely go the iSCSI route but with a separate network for the traffic (which will also help the resiliency angle.)
Brent,
I wrote a technical article for sqlservercentral about ‘How to configure a virtual SQL Server 2012 cluster using an iSCSI SAN’ which they are publishing on Monday 13th. I really wish I’d put a footnote in the article saying ‘Think carefully before routing your SAN related Disk IO over your virtual IP network’.
Best wishes
Paul
Paul – why not contact SQLServerCentral and edit the article? I’m sure they’d be glad to let you add more.
This will give an awful lot of flexibilty for on the fly “what the? …” moments when you discover the BAs really badly underestimated database growth and activity.
In our situation, would I run a db for any period of time off the network? Unlikely – the frequency with which our network breaks or does odd things ( yes we’d like to sack a few people, but I digress ) makes it inadvisable.
A case in point where we had so many outages for a network segment that had mirrored SQL 2008 servers that when the proverbial dust settled, we have half our databases on the principal server and half on the mirror because the main switch went up and down so many times in one night.
But in general, yes very useful. Thanks for bringing it up.
Great Article. For sure , someone will come along and be critical of work you do to save resources.
But it’s great to see the tape solutions.
If fact the older guys used a lot of tape in IT.
/:>
I am running SQL Server 2008R2 on NFS because we are (re)building iSCSI solution with our crappy HP MSA SAN. I did not have room on the ESX host or on physicals for the data, so I provisioned tiny 4TB NAS and then attached databases to it from the SQL (running physical on small 67GB 1U pizza box).
To minimize network footprint I attached separate 1GB switch between SQL host and NAS, and used separate 172.28.0/24 network to connect them. So no database traffic is ever going over the public switch, which I was pretty sure would eliminate disk probs for me.
I’m not in the office right now but I just got an email from coworker saying that SQL reported “LogWriter: Operating system error 64 (the specified network name is no longer available)”.
Any perf tuning I should do at the SQL Server level to account for NFS drives?
Thanks for the article…
Andrew – if you get an error saying the network name for the shared storage is no longer available, that’s actually an availability problem, not a performance problem. It’s failing to perform period, heh. (I shouldn’t be laughing, but…) This is not an unusual problem with low-end (sub-$5k) network-attached storage devices. Depending on how much load you’re trying to put on them, they may just fall over and reboot – which of course is bad news for SQL Server.
I don’t have a quick and easy silver bullet here. I would avoid running SQL Server on a NAS unless you’ve load tested the NAS under extreme conditions and you know that it won’t reboot no matter what.
Hi,
I have a client that generates a fair volume of data, running SQL 2008R2, requesting for a storage device. From your article, seems a SAN will work just fine, rather than investing on a SAN server….right?
Nice read.
Feyi – it depends on what you mean by a storage device and a fair volume of data. Can you be more specific?
Thank you Brent for your response.
Well, they need a high availability server for SQL server 2008 backup. I would have recommended a NAS but really cost is not an issue to my client. So I am considering either HP Storageworks x1400 or X3400. Does this make sense?
Hi All, Finally got my first SQL 2008 R2 cluster built (VMs under ESX 3.5 as well). Cluster is running on sub-par after-market dedicated iSCSI switches using DataCore SANsymphony-V to wrap the two legacy HP MSA1XXX SANs I had available (I know, I know…what a mess).
The original SQL drive share problem I had with the NAS device was that with databases over 11GB: DBCC and other calls would fall over mid-processing. The NAS was connected to the (physical) SQL server using dedicated 1GB switch, Windows Updates turned off, and problems were observed even in simple test scenarios (e.g. running dbcc and other maintenance commands from query manager).
In my shiny new SAN setup where the network connections are even worse due to slowness imposed by ESX vswif and general PE2650 crappiness as an (x86!) ESX host, the same failing functions…work great!
I can only assume that iSCSI protocol, in conjunction with full 60second Windows timeout at guest OS level, is able to handle many “hiccups” on the iSCSI fabric that a standard Windows Share is not.
Based on this admittedly small set of empirical data, I have to say I won’t be trying a NAS again for SQL database of any size (small databases were working fine over the share). Instead, a proper SAN – albeit low end – appears more reliable.
Setting up the cluster is easy – once you’ve done it a few times. That first time was a great way to burn late-night oil, though…
I would like to know what happens to a database actively running on an NFS fileshare when the NAS service goes down. I have a HA NAS environment (failovers do occur) and would only consider running databases on an NFS share if a NAS service failure doesn’t result in database corruption.
Wayne – hmm, let’s take a step back here. In any shared storage system, there’s a possibility that the shared storage will suddenly disappear. This is true with even the most redundant storage systems. I’ve got not-so-fond memories of a SAN vendor coming onsite to do a firmware upgrade (because they said I’d done the last one incorrectly) and they proceeded to accidentally bring down the entire storage network before all of the servers were down. Anytime there’s humans, there’s the chance for human error.
Anytime storage disappears without warning, there’s a chance for bad things to happen to the storage. But again, let’s take a step back – even when the storage doesn’t disappear, there’s a chance for corruption. You can never just say, “We’re okay, I don’t need to worry about corruption.”
Thankyou for the reply. I probably didn’t word the question well – I’m a Linux system administrator not a DBA. Would a NAS service failure lead to a need for restoration from overnight dumps?
I’m trying to decide whether to spend $15K to upgrade storage hardware (add controllers etc.) or present an NFS share to the person running the database.
hi
I have run a database from network share on Windows server 2008, and after I restarted the OS, I found the database can’t be access with the error “objectexplore”. Do you know the reason?
Thanks.
Stacey
Hey Brent,
Know where this is super useful? The cloud, where you run VMs that have all kinds of hard drive limitations put on by the cloud vendor.
Ike
Ike – that’s interesting. So you’ve found networks to be very reliable in cloud VMs?
Reliability from cloud vendors? Ugh..you ask the tough questions.
Who’s to say that they aren’t doing that underneath when they offer locally attached storage on the cloud? It’s all obfuscated from us.
You know what would be crazy? If the network-attached was FASTER than whatever layer they’re using with local attached. In fact, I’m testing that tonight.
Heh – well, it’s actually fairly easy to tell with cloud providers like Amazon when you get a machine with local SSDs. If you load test it, it’s quite clear that it is indeed local storage.
On a Windows Azure VM at the bottom, you have an “Attach Device” button that allows you to attach up to 16 VHDs to an Azure VM. That gives you 500 IOPS per VHD. Those VHDs are stored in Azure Blog Storage. They have a unique driver that allows them to mount those blog store files as local drives. As far as I know, Azure Blob Storage only has a REST interface….super slow.
Brent, good explanation on why one should not use NAS with SQL Server. However, I was thinking about your view on storing just SQL Server database backups onto the NAS, rather than the actual data and log files.
Do you see any issues there? I understand that we have to use the UNC path into the backup command and provide the access to the SQL Server service account (a domain account) to a backup folder on NAS.
Thakwani – great question. I address that in our backup pages at https://www.brentozar.com/go/backup and we actually do recommend storing the backups somewhere other than the SQL Server. A NAS can be a good approach for that, although of course you’ll still want to get those backups off to tape or offsite. Hope that helps!
hi brent,
could you run sql server “Express edition” in NAS with trace 1807 on.
Brent – Just curious how your view points may have changed regarding running SQL Server from a SMB share. We are about to start rebuilding some VM’s for our data warehouse with Server 2012 R2 and SQL 2014 and I would like to explore the possibility of using SMB shares from our new NetApp SAN.
I have started trying to do some research into how would we best configure the SQL boxes, specifically when it comes to how best to configure the network adapters. Like are we better off creating a configuring seperate NIC’s/VLAN’s to handle access to the file shares or providing two front end NIC’s teamed together going through the primary VLAN.
Do you happen to know of any white papers that describe a successful implementation of this? Something that can be used to start the conversation with our network folks/system administrators on if we were to go this route, what would the ideal configuration look like.
Ian – no, I don’t know of any white papers that describe a successful implementation of that. It varies so much depending on what kind of networking gear you use, hypervisor platform, etc.
Interested in your thoughts on the upcoming release of Amazon EFS (https://aws.amazon.com/efs/details/)
Hello Brent,
I tried to create a database on my NAS with SQL Server 2012 as shown in your command above.
But I always receive the error message 5133 with windows error 1326 “user name or password incorrect”.
It seems to occur because of the different login data for the nas and my pc.
Would you have any advice for me how to siolve this problem, how did you handle it?
Terence – unfortunately, I can’t troubleshoot security issues on your NAS.
Understood, I just wondered if you use the same login data for your NAS as for your PC.
This may give me an idea of what could be different in your setup regarding SQL Server and the NAS user administration towards mine.
Hey, thought I’d try this to relieve some hd pressure on my workstation for test databases. tested it with AdventureWorks. I have a Z: drive, mapped to a network share so this otta work right?
Create database AdventureWorks2012
on (filename = ‘Z:\AdventureWorks\AdventureWorks2012_Data.mdf’)
FOR Attach;
Note that I am looking at Windows Explorer open to that folder (Z:\AdventureWorks) and there is the file I’m trying to attach.
Running this SQL results in:
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file “Z:\AdventureWorks\AdventureWorks2012_Data.mdf” failed with the operating system error 3(The system cannot find the path specified.).
So I think I must be missing something. Any clues?
Gerald – note that the article doesn’t talk about mapped drives. Mapped drives don’t always work for running services. Instead, use UNC paths as shown in the posts.
True, mapped drives not mentioned (but not excluded!). Also true: In my case it doesn’t work but the reason is simple. Sql Server is running under a virtual account and can’t access network shares anyway! (this is just on my workstation — a testing db).
Pelase let us know if we can use https://aws.amazon.com/efs/ for storing SQL Server data and log files.
Sathish – we haven’t tried that. By all means, give it a shot and let us know what you find out.
I’ve got a potential usecase for NAS backed DB: for restoring backup databases with read-only access. This might be for testing backups or creating a replica for long running reports which you don’t want to affect the live transactional system, and need cheap disk.
Great article. We have a large Read Only DB they’re we’re restoring to a NAS as a current duct tape solution. I had wondered why the restore process seemed to be hanging.
I believe I’ve identified that Instant File Initialization may not available when creating or restoring to a UNC path (or may need additional setup). SQL code below demonstrates that
DBCC TRACEON(3004,3605,-1)
GO
CREATE DATABASE [DestinedForGreatness]
GO
DROP DATABASE DestinedForGreatness
GO
EXEC sp_readerrorlog
GO
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
DROP DATABASE DestinedForGreatness
GO
EXEC sp_readerrorlog
GO
DBCC TRACEOFF(3004,3605,-1)
I’d be interested to know if there’s any workaround for this to take advantage of IFI.
Sounds like a great question for DBA.StackExchange.com!
Thanks for the advice – confirmed here that Instant File Initialization is not available for network paths:
http://dba.stackexchange.com/questions/126164/enabling-sql-instant-file-initialization-when-restoring-to-a-network-path
Thank you for the tips. I would like to know if it is possible to run the code on another machine besides the server itself.
When I run the CREATE DATABASE on the SSMS on the server machine, it works fine.
When I run the same CREATE DATABASE sql code on a network machine (connect to the same DB instance with the same login credential), it gives an error 5 Access denied.
Is there any different using the SSMS on the DB Server machine itself and on other machine?
Lai – those should both work, so it makes me wonder more about the accounts you’re using to connect to SQL Server, or the exact syntax of the create database command.
Both the account used to login SSMS and the account to run SQL Server Service are domain account. I used windows athentication to login SSMS. And they both have full control to the MDF/LDF files and to the folder contining them.
On the network machine, I am able to create the database on the first time (Using the syntax you have provided in the article: CREATE DATABASE / ON PRIMARY / LOG ON). But if I detach it, I cannot attach the MDF again (I set it offline before detaching so that the file permission is retained). It can be done if I login the server, which runs the SQL Server, and use the SSMS on that server.
It is very puzzling. So I wonder if there is setting issue on the accessing side.
The syntax for attaching is:
CREATE DATABASE [DestinedForGreatness] ON
( FILENAME = N’\\fileserver\myfolder\DestinedForGreatness.MDF’),
( FILENAME = N’\\fileserver\myfolder\DestinedForGreatness_log.LDF’)
FOR ATTACH
ps. No access denied problem if the MDF is moved to the database server rather than in network location.
ps2. I have tried the ‘Run As Admin’ option but in vain.
HI Brent,
Can we use filetable documents in Cloud (Azure Blob Storage) and use it like normal on premise SQL Server with SQL 2014/2016. if yes, is there any limitation of this approach. The size of Filetable document is expected to 10/20 TB. Will it support HA and DR scenario. Kindly Suggest
Thanks
Neeraj Mittal
I wouldn’t recommend storing tens of terabytes of files in SQL Server. Store the files on a file server, and just store pointers to the files in the database.
Thanks Brent, document storing on NTFS using file table will be best bet but can ordinary sql server can support it >20TB? Wishlist is to use cloud storage (Azure Blob Storage) is that feasible ?
No, not filetable, on a file server.
Hello Brent! I would like your thoughts/opinions on locating SQLServer2008R2 EE Filestream filegroups on NAS drive storage. The size of this filestream data with pointers in the table VARBINARY column, but files stored on NAS drive folders would total about 100 TB. Current design looks like there will be 3 dbs in each of 2 instances, each db will filestream out about 15 TB. I preferred to use good 3PAR SAN attached to these HA SQL cluster configured instances owning SAN mount volumes to host this data, but due to SAN procurement delays, there is a push to use NAS for now. If the transactions are going to be busy, then is there risk of getting timeouts and other errors by putting filestream filegroups like this on NAS? What could be the gotchas to watch out for? Any advice? Thanks so much for your input if you could spare some.
Chandrashekar – this kind of architecture design is exactly what we do in our paid consulting. It’s way beyond the scope of a blog post comment. If you’d like to engage us for consulting, click Consulting at the top of the page, and you can read about what we do and our rates. Thanks!
Hello Brent,
our users are not so concerned with SQL performance.
How about running SQL Server on Linux with NFS synchronous IO?
Thank you.
Ung Ho Yi – I haven’t tried it, sorry.
Would like to note that AWS actually supports “Using Amazon FSx for Active SQL Server Data Files” https://docs.aws.amazon.com/fsx/latest/WindowsGuide/sql-server.html.
I’d like to hear about anyone’s experience regarding this. Putting active SQL Server data files on a network share still sounds iffy to me.
Did this once with a dual controller MSA2040, two Zyxel 10Gb switches and IP over 10Gb fibre for a poor man’s cluster. All in same cabinet so cables as safe as any fibre.
I’ve have seen people saying another reason to not use a NFS share for live database storage is something to do with file locking not being supported in the same way local storage does and concurrent access from multiple sources not being possible, hoping you can shed some light here?
These are some quick sources I found about this:
https://serverfault.com/questions/177982/can-i-use-a-nas-for-sql-server
https://serverfault.com/questions/591/sql-server-files-local-or-nas-or-san
Multiple sources shouldn’t be accessing your database files at the same time to begin with.
Good point, I came across that reason when looking into the file locking reason so did not really think about it.
Do you have any insight into the file locking reason I see thrown about without any explanation all over the Internet?
Jake, I’m going to be brutally honest because I’m sure you’re as busy as I am: I don’t have the time to screw around debunking strangers. Hope that’s fair. Cheers!
That is fair, but as file locking seems to be mentioned in nearly all posts I have found regarding databases on nfs, I wondered if you had experience with this reason or whether it is a case of people just mimicking what others have said without any evidence and thus not an issue.
Thank you for the quick responses anyhow!