5

Is it a normal practice with MS SQL Server to place databases in remote shared folders on a fast local network rather than on the physical HDD where the server software is installed? What problems shall one fear that local placement avoids? For example, can a network glitch cause .mdf and .ldf to become inconsistent through successful modificaion of the one and failure to modify the other?

EDIT: Whereas Microsoft recommends employment of Storage-Area Network (SAN) or SCSI instead of simple shared folders, could you please tell me the disadvantages of the latter?

Improve this question
1

2 Answers 2

Reset to default
10

The key differentiator between the recommended locations (e.g. local storage, a SAN, or an iSCSI-based network) and a network share is quite simply, redundancy. All of the recommended approaches provide an option for redundant paths for I/O to take to persistent storage.

For instance, you can RAID local storage, providing redundancy if any disk fails. With either the SAN or iSCSI-based network storage approaches, these technologies use Multipath Input/Output (MPIO) drivers, providing redundancy to the storage.

A network drive, in contrast, does not employ or allow for any redundant I/O paths. If a Network Interface Controller (NIC) fails on either end, the share likely disappears. Even if you have multiple NICs, there will still be a brief outage as a different IP address will now host that share, so any data sent to the old/failed IP will timeout and disappear. Basically a network share wasn't designed with this level of redundancy, and a loss of data mid stream may corrupt your database (or worse just get lost without a trace). The whole point of a database is to reliably store data and a network share brings that whole reliable aspect into question.

Improve this answer
1

Its not a good practice for production, but for reporting and staging you can use, Even though SAN and iSCSI are related to a network share. As Dan Guzman(who commented below question) Microsoft recommends keeping the files on SAN, Local Server or iSCSI.

enter image description here

But if you are using a Network share then that causes a single point of failure.

Improve this answer
4
  • re:*single point of failure*: what kind of failure? Can it leave the database files in a corrupt state or not, or will all work again as soon as the network comes online?
    – Anton Shepelev
    Commented Sep 8, 2017 at 12:43
  • So lets think about the server where the network share is placed, If that server goes down then your database is not accessible also it may be going to corrupt.
    – TheDataGuy
    Commented Sep 8, 2017 at 12:47
  • Why cannot the same happen with a SAN, if it suddently goes down?
    – Anton Shepelev
    Commented Sep 8, 2017 at 12:57
  • You should be able to easily find online a "typical entry-level SAN" and see a diagram that answers why SANs don't easily go down. They are built with multiple redundant components because a SAN is at the core of many enterprise server technologies (including databases). High availability is at the core of a SAN admin's job.
    – Fredric Shope
    Commented Sep 8, 2017 at 20:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.