July 4, 2010 at 10:03 am
Someone at another company I know asked me about setting up a small SQL installation in which their file storage will reside on a hosted server and they will access it using a mapped drive therefore eliminating an onsite server. They want to know if SQL will work in the solution and if there are performance implications or not. Can the database be located on a network drive and the engine like sql express or workgroup edition reside on their local pc?
I haven't used this type of setup, so not sure if it's practical or not. Thoughts ?
July 4, 2010 at 10:45 am
No. It won't even work.
SQL has a lot of rules about where it can create files for database and network shares are disqualified by those rules. There is a traceflag (I don't know which one) that permits it, but it is a very bad idea.
All you need is one network glitch while SQL is accessing the file and the DB goes read-only or even suspect.
If you try to create a DB via the GUI, you'll notice that SSMS doesn't even give you the option of selecting a mapped network share.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 4, 2010 at 4:16 pm
I second Gail's advice. This is a bad idea, and can cause problems if there are hiccups. It is not worth it, IMHO.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 4, 2010 at 10:57 pm
Thanks a lot for the feedback !
July 5, 2010 at 3:16 am
I think that Trace flag 1807 is the creature that will inhibit the usual criteria that SQL Server applies to storage locations.
Very bad idea though, as the esteemed posters above have already said.
July 5, 2010 at 8:22 am
Well, there is a supported option to run your SQL database remotely --where the whole DB engine is running remotely (in Microsoft's data center) -- it's called SQL Azure.
You can run your app locally, and have it talking to a remote SQL database. They take care of backups, high-availability, etc.
Of course, hosting like that is not free. And yes, there are "performance implications" for _ANY_ approach where you geographically separate your data storage from your app.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply