SQL Server 2008 access disk issue

Hello everyone,

I am using SQL Server 2008 Enterprise on Windows Server 2008 Enterprise and I have a network shared drive Z (which the host machine of SQL Server 2008 could access from resouces explorer). My questions are,

1. Could SQL Server store database files (mdf/ldf) on network shared drive if enough permission is granted?

2. If the answer to 1 is yes, then as the network drive is protected by some user name and password (in order to write to the network drive). But my SQL Server instance services runs under some other Windows account. How to enable SQL Server to access/write such kinds of account/password protected network drive in this situation? I am not sure if we could set some special network drive permission so that no password is needed in order to access/write network drive? Or we could use "net use" command to store network drive access password?

thanks in advance, George

From serverfault George2

• 1: yes, in theory. Very unadvisable for many reasons.

  2: No. That definitely is not possible.

  George2 : Thanks TomTom. For 2, do you mean it is impossible for SQL Server 2008 to access password protected network drive or impossible for something else? And why?

  TomTom : well, lets see - I am sure you can somehow USE a network drive, but entering the password simply is out of the question. In genral, using a network drive (or drives - never put your log in the same drive as the database if you want any performance) is simply so unprofessional I do not even do it in development.

  George2 : Thanks TomTom, how to set a Windows network drive as non-password protected so that everyone can access? I think if we could enable this feature, we could access the network drive by SQL Server, correct?

  joeqwerty : @George2: Why do you want to do this? It's very unorthodox.

  TomTom : It is not only unorthodox, it breaks any guidelines and will result in a very bad performance. In general, someone made a decision that should result in his termination - especially if he tries to defend it.

  johnh : if sql server is running as a domain account, sql server can access file server shares as that user.

  George2 : @joeqwerty, because local disk is not big enough for the host machine of SQL Server. In my specific application scenario, performance is less an issue, but storage is a bigger issue. Any solution to my problem?

  George2 : @TomTom, because local disk is not big enough for the host machine of SQL Server. In my specific application scenario, performance is less an issue, but storage is a bigger issue. Any solution to my problem?

  George2 : @johnh, if not running under domain account, any solutions? Could I use "net use" to let Windows Server 2008 remember password for network share, so that when SQL Server 2008 accesses network drive, no need to provide password?

  joeqwerty : IMO, a better solution would be to add storage to the SQL server than to attempt to run a database from a network share.

  TomTom : Then get either a SAN or a larger DAS. Network share databases are an invitation for SERIOUS trouble, as well as a "goodbye to performance". And not "a little slower". But mostly - you WILL run into corruptions most likely.

  George2 : TomTom, do you have any document to prove SAN or a larger DAS is better than network shared drive?

  George2 : @joeqwerty, do you have any document to prove what you mentioned? Thank you!

  George2 : Thanks, question answered!
  From TomTom