CODEWITHSUNDEEP
sql-serversql-server-2012sanfiletable
amateur
amateur

Reputation: 44635

create a file system group pointing at a SAN

I am working with creating a FileTable in sql server 2012. I want to store the files on a SAN (Storage Area Network). I have mapped it to a drive on sql server.

To use the FileTable, I need to set up a file system group. I am using the following script but failing to get it correct:

ALTER DATABASE [MyDatabase]
ADD FILE 
(
    NAME = FileStreamFile,
    FILENAME= '\\123.456.789.001\MyFolder',
    MAXSIZE = UNLIMITED
    )
TO FileGroup MyFileStreamGroup
END

The entry I have for the FileName always seems to fail. How can I set up a FileStreamGroup to point at SAN and which in turn I will use to create a file table from it?

Upvotes: 1

Views: 1204

Answers (2)

steoleary
steoleary

Reputation: 9298

As you have found, you can't put filestream files onto a remote drive using a UNC path. Your best bet is to create a LUN on your SAN and connect to this via iSCSI, that way the drive will appear as local to SQL server but will still be physically located on the SAN.

You probably want to have a chat with your storage admin about doing this really.

Upvotes: 1

TheQ
TheQ

Reputation: 7017

First, you need to create a filegroup:

ALTER DATABASE [MyDatabase]
ADD FILEGROUP [MyFileStreamGroup]

Then you can add files to it:

ALTER DATABASE [MyDatabase]
ADD FILE ( 
    NAME = N'NewDataFile'
    ,FILENAME = N'\\123.456.789.001\MyFolder\NewDataFile.mdf' 
    ,SIZE = 1024KB
    ,FILEGROWTH = 1024KB
) TO FILEGROUP [MyFileStreamGroup]

And finally you can create tables:

CREATE TABLE [MyDatabase].[dbo].[NewTable] (
    [Id] int NOT NULL,
    [Text] varchar(50) NOT NULL
) ON [MyFileStreamGroup]

By specifying ON [MyFileStreamGroup] you force it to be created in that filegroup, which in turn will use the newly created file on the SAN. If you don't specify filegrop, the table will end up in the default one, which is usually called PRIMARY. If you want the new filegroup to be default, you can use:

ALTER DATABASE [MyDatabase]
MODIFY FILEGROUP [MyFileStreamGroup] DEFAULT

Upvotes: 0

Related Questions