Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share
add comment

3 Answers

up vote 5 down vote accepted

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

share
1  
Yep. Can't put a DB file on a shared drive. Needs to be local storage. –  BradC Jun 30 '10 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 '10 at 14:10
2  
not sure if it worked, but I tried to correct the down vote! –  tony roth Jun 30 '10 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 '10 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 '10 at 6:19
show 9 more comments

see the following

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

in otherwords doable but highly not recommended

share
    
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 '10 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 '10 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 '10 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 '10 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 '10 at 14:35
show 3 more comments

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).

share
add comment

This site is currently not accepting new answers.

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