SQL Server 2008 data file issue

Hello everyone,

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

From serverfault George2

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

  BradC : Yep. Can't put a DB file on a shared drive. Needs to be local storage.

  Chopper3 : 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 : Can the down-voter explain what I've done wrong please?

  tony roth : not sure if it worked, but I tried to correct the down vote!

  George2 : @Chopper3, do you have any document to prove DB can not be created on file-level share?

  George2 : Hi @@Chopper3, do you have any documents which describes what are block-level storage, and what are file-level share?

  George2 : @BradC, do you have any document to prove DB can not be created on file-level share?

  George2 : 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?

  Chopper3 : 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; http://support.microsoft.com/kb/967576/

  George2 : Thanks Chopper3, could you let me know what kinds of H/W is block level storagge, and what kinds are not please? Do you have any documents for this topic?

  Chopper3 : 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 : I ran out of characters above :) - if you have no need for clustering then you're best simply adding local disks to your existing server via your current disk controller. If you do need clustering then you'll need to use a minimum of a small FC or iSCSI SAN array - products depend on budgets to be honest.

  George2 : Question answered! I have a related new issue, and I posted here, appreciate if you could take a look. http://serverfault.com/questions/156306/sql-server-2008-data-file-issue
  From Chopper3

• see the following

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

  in otherwords doable but highly not recommended

  George2 : 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?

  tony roth : @George2 I'd say don't do it, thats all. if you set the trace option correctly you can manually edit the path and it won't error out. But it will be very unstable. If the device 10.10.10.123 can do iscsi I'd do that.

  BradC : 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."

  tony roth : 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.

  George2 : @BradC, do you have any document to prove what you mentioned? Where do you copy the statements from?

  George2 : @tony roth, "convert the 10.10.10.123 to an iscsi device if possible" -- after convert, I should see the shared location as a local disk from My Computer, correct?

  tony roth : what type of device is 10.10.10.123, is it a windows server, linux or nas etc?

  BradC : @George2 The quote in my comment is straight from the MS KB article linked in Tony Roth's answer above.
  From tony roth