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 )
( NAME = N'networked_log', FILENAME = N'\\varund-win7\ATOMNETWORKDB\networked_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
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 )
( NAME = N'networked_r2_log', FILENAME = N'\\varund-win7\ATOMNETWORKDB\networked_log_r2.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
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).