SQL on Remote Server, via Mapped Drive ?

Question

SQL on Remote Server, via Mapped Drive ?

homebrew01

SSC Guru

Points: 55548
More actions
July 4, 2010 at 10:03 am

#139623

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 ?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736524 More actions · Answer 2

I second Gail's advice. This is a bad idea, and can cause problems if there are hiccups. It is not worth it, IMHO.

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

homebrew01

SSC Guru

Points: 55548

July 4, 2010 at 10:57 pm

#1188658

Thanks a lot for the feedback !

Andy Hogg SSCarpal Tunnel Points: 4585 More actions · Answer 4

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.

tplas SSC Enthusiast Points: 148 More actions · Answer 5

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.