Database Engine vs. Database location

Posted on

Question :

I’m a new user of SQL Server 2008 R2.

We work in a Windows domain environment and I have access to multiple servers – by this I mean I have various drives mapped to various servers.

I have SQL Server 2008 R2 installed in my local machine and have been able to create a database on my local machine using Windows Authentication.

My question is: Given that I have SQL Server in my local machine, can I create a database and store it in any other server I have access to? Or does this other server need to have SQL Server installed as well?

Thank you in advance!

Answer :

Installing SQL Server 2008 DB files on network shares really isn’t fully supported properly until you get to SQL 2012. I’m not sure how it’ll work as I’ve never tried that in prod but you can do it.
http://www.brentozar.com/archive/2012/01/sql-server-databases-on-network-shares-nas/

To answer your security question though, no, the user authenticating against SQL Server does not need access to that file location, that file, or anything like that. The SQL Server service account that manages that DB does need access to it though across the network.

Also, No, the remote storage server does not need SQL Server engine or client installed.

Yes, you can create or attach to a database on a network share. See:

https://support.microsoft.com/en-us/kb/304261

http://blogs.msdn.com/b/varund/archive/2010/09/02/create-a-sql-server-database-on-a-network-shared-drive.aspx

Leave a Reply

Your email address will not be published.