August 21, 2013 at 1:22 pm
Hello,
I've created a VM (using Windows Virtual PC) running Windows 7, 32-bit, and allocated a virtual hard drive of 126 MB.
The virtual hard drive is quite limited, and that's the way I want it.
On the VM, I have setup SQL Server 2012, 32-bit.
I am about to conduct an experiment using SQL Server 2012 Filetables.
Is it possible to setup a Filetable where the data for it is on a separate device (NAS) with a separate IP address?
The VM can connect to the NAS through it's fixed IP address. I can not only ping it from the VM, but I can also map a network drive to it.
However, I cannot create the Filestream device for the database on it.
This works:
Create Database
RRTest
On Primary
(
Name = RRTestData
, Filename = 'C:\Data\MSSQL\RRTest.mdf'
, Size = 10MB
, FileGrowth = 1MB
)
, FileGroup FSPics Contains FileStream Default
(
Name = FSPics
, Filename = 'C:\Data\MSSQL\FSPics\'
)
Log On
(
Name = RRTestLog
, Filename = 'C:\Data\MSSQL\RRTest.ldf'
, Size = 1MB
, FileGrowth = 1MB
)
But this doesn't:
Create Database
RRTest
On Primary
(
Name = RRTestData
, Filename = 'C:\Data\MSSQL\RRTest.mdf'
, Size = 10MB
, FileGrowth = 1MB
)
, FileGroup FSPics Contains FileStream Default
(
Name = FSPics
, Filename = '\\192.168.0.32\Volume_1\Data\MSSQL\FSPics\'
)
Log On
(
Name = RRTestLog
, Filename = 'C:\Data\MSSQL\RRTest.ldf'
, Size = 1MB
, FileGrowth = 1MB
)
I have followed these instructions written in BOL:
' filestream_path '
For a FILESTREAM filegroup, FILENAME refers to a path where FILESTREAM data will be stored. The path up to the last folder must exist, and the last folder must not exist. For example, if you specify the path C:\MyFiles\MyFilestreamData, C:\MyFiles must exist before you run ALTER DATABASE, but the MyFilestreamData folder must not exist.
The filegroup and file (<filespec>) must be created in the same statement.
The SIZE and FILEGROWTH properties do not apply to a FILESTREAM filegroup.
I have already created a folder on the NAS, "Data\MSSQL\", but not "Data\MSSQL\FSPics\
Can this be done at all?
Thanks for any suggestions and pointers.
Richard
August 21, 2013 at 3:42 pm
So what you error message do you get?
Generally, SQL Server does not support databases on remote servers as I recall.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 21, 2013 at 5:21 pm
This is the error message I'm getting:
Msg 5135, Level 16, State 2, Line 4
The path '\\192.168.0.32\Volume_1\Data\MSSQL\FSPics' cannot be used for FILESTREAM files.
For information about supported paths, see SQL Server Books Online.
Msg 1802, Level 16, State 2, Line 4
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
I tend to think that this isn't possible. This is the difference between a NAS (what I have) and a SAN (it appears as a local device).
I still have an external hard drive connected via e-Sata, that appears as the local "E:" drive. Maybe I'll try that.
Thanks for your assistance,
Richard
August 21, 2013 at 6:23 pm
The original specs for Filestream in 2008 (on which Filetable is based) stated that storage must be local. Paul Randal wrote about this.
http://www.sqlskills.com/blogs/paul/misconceptions-around-filestream-storage/
There was support for RBS, remote blob store devices, but that was through API access, and not just a UNC share.
SQL Server has added support for NAS storage of databases, but I'm not sure that applies to Filestream. I don't see a specific note in the docs, but you'd have to see if you can add a filegroup (with and without filestream) to verify.
August 21, 2013 at 11:34 pm
How interesting.
I`ve enabled Trace Flag 1807, and made progress.
This works:
Create Database
RRTest
On Primary
(
Name = RRTestData
, Filename = '\\192.168.0.32\Volume_1\Data\MSSQL\RRTest.mdf'
, Size = 10MB
, FileGrowth = 1MB
)
, FileGroup FSPics Contains FileStream
(
Name = FSPics
, Filename = 'C:\Data\MSSQL\FSPics'
)
Log On
(
Name = RRTestLog
, Filename = '\\192.168.0.32\Volume_1\Data\MSSQL\RRTest.ldf'
, Size = 1MB
, FileGrowth = 1MB
)
With
FileStream
(
Non_Transacted_Access = FULL
, Directory_Name = N'FileStreamFolder'
)
However, this doesn`t:
Create Database
RRTest
On Primary
(
Name = RRTestData
, Filename = '\\192.168.0.32\Volume_1\Data\MSSQL\RRTest.mdf'
, Size = 10MB
, FileGrowth = 1MB
)
, FileGroup FSPics Contains FileStream
(
Name = FSPics
, Filename = '\\192.168.0.32\Volume_1\Data\MSSQL\FSPics'
)
Log On
(
Name = RRTestLog
, Filename = '\\192.168.0.32\Volume_1\Data\MSSQL\RRTest.ldf'
, Size = 1MB
, FileGrowth = 1MB
)
With
FileStream
(
Non_Transacted_Access = FULL
, Directory_Name = N'FileStreamFolder'
)
I can hardly believe that the data and log files can be on a UNC Pathname, but not the Filestream.
I am incredulous!!! Is there some other trace flag that I haven`t seen to enable Filestreams on a UNC Pathname?
Richard
August 22, 2013 at 12:32 am
Procmeister (8/21/2013)
I can hardly believe that the data and log files can be on a UNC Pathname, but not the Filestream.
FILESTREAM data can be exposed to remote clients on a share. Can machine A expose a share where the data in in fact on a share on another machine? I don't think so. There you have it.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 22, 2013 at 2:09 am
Procmeister (8/21/2013)
I can hardly believe that the data and log files can be on a UNC Pathname, but not the Filestream.I am incredulous!!! Is there some other trace flag that I haven`t seen to enable Filestreams on a UNC Pathname?
Although you can enable it with a trace flag, using NAS storage for a database is usually pretty inadvisable. There are some stringent requirements for the underlying I/O sub-system that are hard to guarantee from NAS (it would have to be provisioned by an enterprise-class SAN that's specifically supported for this configuration).
This trace flag was created several versions ago and I suspect it's only really there to help out with temporary migrations and emergencies. The risk of corruption from misconfiguration is high and it's unlikely to see support from new features since there are better solutions to present data off-server via a SAN (iSCSI or Fibre-channel).
August 22, 2013 at 6:59 am
I've run into this issue before with FILESTREAM, and it HAS to be locally attached storage. you just can't add a FILESTREAM location that is any kind of UNC path unfortunately.
August 22, 2013 at 8:46 am
Erland Sommarskog (8/22/2013)
Procmeister (8/21/2013)
I can hardly believe that the data and log files can be on a UNC Pathname, but not the Filestream.FILESTREAM data can be exposed to remote clients on a share. Can machine A expose a share where the data in in fact on a share on another machine? I don't think so. There you have it.
This is likely the reason.
Thanks, Erland.
August 22, 2013 at 8:47 am
HowardW (8/22/2013)
Procmeister (8/21/2013)
I can hardly believe that the data and log files can be on a UNC Pathname, but not the Filestream.I am incredulous!!! Is there some other trace flag that I haven`t seen to enable Filestreams on a UNC Pathname?
Although you can enable it with a trace flag, using NAS storage for a database is usually pretty inadvisable. There are some stringent requirements for the underlying I/O sub-system that are hard to guarantee from NAS (it would have to be provisioned by an enterprise-class SAN that's specifically supported for this configuration).
This trace flag was created several versions ago and I suspect it's only really there to help out with temporary migrations and emergencies. The risk of corruption from misconfiguration is high and it's unlikely to see support from new features since there are better solutions to present data off-server via a SAN (iSCSI or Fibre-channel).
True, but this is native in SQL 2012 (And R2, IIRC), so MS recognizes that technologies have advanced and this is not necessarily a problem. Not sure I'd put it on the general network, but a 2nd NIC, privately hitting a NAS can work well.
August 22, 2013 at 8:55 am
Steve Jones - SSC Editor (8/22/2013)
True, but this is native in SQL 2012 (And R2, IIRC), so MS recognizes that technologies have advanced and this is not necessarily a problem. Not sure I'd put it on the general network, but a 2nd NIC, privately hitting a NAS can work well.
Yeah, I did read after posting this that SQL 2012 has added a lot of support and documentation around using SMB for database files, so will admit my knowledge here is a little out of date.
August 22, 2013 at 8:58 am
HowardW (8/22/2013)
Steve Jones - SSC Editor (8/22/2013)
True, but this is native in SQL 2012 (And R2, IIRC), so MS recognizes that technologies have advanced and this is not necessarily a problem. Not sure I'd put it on the general network, but a 2nd NIC, privately hitting a NAS can work well.Yeah, I did read after posting this that SQL 2012 has added a lot of support and documentation around using SMB for database files, so will admit my knowledge here is a little out of date.
Not necessarily out of date. If you have one NIC and one network, it's certainly an issue based on what you said. If you are SQL 2K8 or previous, it's certainly something to be careful of.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply