0

When I create a database in SQL Server 2008 Enterprise on Windows Server 2008 from SQL Server Management Studio, I want to assign the data and log file to be stored on a network mapped drive Z (I map \\10.10.10.123\shared to drive Z). But I can only see local drive C and D from SQL Server Management Studio UI to select to put data file to, can not see drive Z. Anything wrong?

I have two ideas, not sure whether they are correct,

  1. I run SQL Server service instance LocalService account, could that be the cause that network mapped drive can not be accessed? (my confusion is I am using administrator to log into Windows Server 2008, and administrator could access network shared drive from My Computer. When we select drive from SQL Server Management Studio, is administrator acconut being used or LocalService account being used?)
  2. The network shared location is protected by username and password -- i.e. when open the network mapped drive from \\10.10.10.123\shared, we need to put a username and password, and according to the permission of username, we could write or read or no permission.

thanks in advance, George

Improve this question

3 Answers 3

Reset to default
5

Your DB needs to live on block-level storage, not a file-level share.

Improve this answer
14
  • 1
    Yep. Can't put a DB file on a shared drive. Needs to be local storage.
    – BradC
    Jun 30, 2010 at 14:08
  • 1
    It can be block-level shared such as FC/iSCSI (in fact MS clustering REQUIRES this), just not file-level such as SMB/CIFS, NFS etc.
    – Chopper3
    Jun 30, 2010 at 14:10
  • 2
    not sure if it worked, but I tried to correct the down vote!
    – tony roth
    Jun 30, 2010 at 14:15
  • 2
    well, as 'tony roth' links to a document showing that it can just about be made to work if you absolutely need to I still state that you need block level storage. Here's the best root document about the matter I've found; support.microsoft.com/kb/967576
    – Chopper3
    Jun 30, 2010 at 16:38
  • 2
    Block-level access is provided by direct-attached storage (DAS) such as SCSI/ATA/SATA/SAS disks, Fibre-Channel (FC) based disk arrays (FC SANS), ethernet based disk arrays such as iSCSI and Fibre-Channel over Ethernet (FCoE) and Infiniband based storage. File-level storage usually comes via network-attached storage (NAS) devices, often sharing using the Windows-derived SMB/CIFS protocols, the *nix-derived NFS protocol plus FTP/SFTP/HTTP/HTTPS as well a few lesser known ones too.
    – Chopper3
    Jul 1, 2010 at 6:19
3

see the following

http://support.microsoft.com/kb/304261

in otherwords doable but highly not recommended

Improve this answer
8
  • Hi @tony roth, do you mean you can store DB on shared network drive? If so, why I can not see network drive Z from SSMS?
    – George2
    Jun 30, 2010 at 16:32
  • 1
    Ah, very on-point article. "Microsoft generally recommends that you use a Storage Area Network (SAN) or locally attached disk for the storage of your Microsoft SQL Server database files because this configuration optimizes SQL Server performance and reliability. By default, use of network database files (stored on a networked server or Network Attached Storage [NAS]) is not enabled for SQL Server." However, you can configure SQL Server to store a database on a networked server or NAS storage server."
    – BradC
    Jun 30, 2010 at 20:43
  • 1
    Even with that said, don't do it! convert the 10.10.10.123 to an iscsi device if possible, if its a windows server starwind etc have free iscsi targets.
    – tony roth
    Jun 30, 2010 at 20:53
  • 1
    what type of device is 10.10.10.123, is it a windows server, linux or nas etc?
    – tony roth
    Jul 1, 2010 at 5:11
  • 2
    @George2 The quote in my comment is straight from the MS KB article linked in Tony Roth's answer above.
    – BradC
    Jul 1, 2010 at 14:35
0

To make this work (see comments on why not to under block storage being required) will require a domain login that sql server runs as (without complicating the answer to say how to do it without a domain login). If you are ONLY trying to do this to save some $ don't, you're going to get yourself into a support situation that isn't going to work out well and be headaches - If the client doesn't have a domain, a reliable file server, and a REASON to use a network share for the storage then just avoid it and do the suggestions already posted about SAN (even iSCSI and StarWind would be fine and easy).

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .