Create a SQL Server Database on a network shared drive

(原文地址：http://blogs.msdn.com/b/varund/archive/2010/09/02/create-a-sql-server-database-on-a-network-shared-drive.aspx)

 

Create a SQL Server Database on a network shared drive

（创建基于网上邻居共享路径的数据库）

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