다음을 통해 공유

Create a SQL Server Database on a network shared drive

  • 아티클

 

Hello All,

Sometime back, I have written blog post on ‘Backing-up SQL Server Database on a network shared drive’. This can be found here. I have received numerous queries on an another related topic i.e. ‘How to Create a SQL Server Database on a network shared drive’. It took sometime (a brand new SQL Release) before I could get the answer as follow:

1. Create a database on Network Share in SQL Server 2008 / SQL Server 2005

In SQL 2008 and SQL 2005, By default, you cannot create a new database on a network share drive.

This restriction is primarily due to fact that, On a network file share, there is always a risk on network errors compromising database integrity, along with I/O performance  issues which might partial or total data loss or corruption. Reference >> Microsoft KB # 304261

However, there’s a “workaround”, if in case you still want to go ahead. Follow below steps:

Step 1. Enable the Trace Flag 1807: (Bypasses the check and allows you to configure SQL Server with network-based database files)

DBCC TRACEON(1807, -1)

Step 2. Identify a file share, where SQL Server Service start-up account has FULL access

Step 3. Create the database

CREATE DATABASE [networked] ON PRIMARY
( NAME = N'networked', FILENAME = N'\\varund-win7\ATOMNETWORKDB\networked.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'networked_log', FILENAME = N'\\varund-win7\ATOMNETWORKDB\networked_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

2. Create a database on Network Share in SQL Server 2008R2

Starting SQL Server 2008R2, you are now allowed to cerate database on network file share (UNC path), without the need to Trace 1807

Step 1. Identify a file share, where SQL Server 2008R2 Service start-up account has FULL access

Step 2. Create the database

CREATE DATABASE [networked_r2] ON PRIMARY
( NAME = N'networked_r2', FILENAME = N'\\varund-win7\ATOMNETWORKDB\networked_r2.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'networked_r2_log', FILENAME = N'\\varund-win7\ATOMNETWORKDB\networked_log_r2.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

While the above options will surely help you to manage space used by database file across your networked servers, please be aware of the risk involved (as I discussed above). Once such known issue has been discussed in this CSS post. It is therefore advised to run SQLIOSIM.exe that ships with SQL Server 2008 R2, this has now been updated to allow testing against a UNC locations.

Microsoft recommends that you store database files either on Local Disk or on Storage Area Networks (SANs).

Enjoy and Thanks of reading!

Disclaimer: I work at Microsoft. Everything here, though, is my personal opinion and is not read or approved by Microsoft before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.

Comments

  • Anonymous
    September 20, 2010
    I've try the same way as above example. If the share folder is on Vista PC, it succeeded. If the share folder is on XP PC, it failed. I really dont know the reason. I've turn off firewall and UAC.

  • Anonymous
    September 20, 2010
    Hi Giang, I'm not aware of any specific issues on WinXP. Few quick checks:

  1. Run below 2 commands togtheg and verify if the trace flag has been enabled propely DBCC TRACEON(1807, -1) DBCC TRACESTATUS(-1)
  2. Now try creating the database Kindly confirm! VarunD
  • Anonymous
    April 01, 2011
    Hi Varun. this script allows to add DB on network share. but does it works on mapped drives? If I try to create on same location via net share - OK, via mapped drive - error...