How to set remote location for File table directory in SQL server 2016?

Question

How to set remote location for File table directory in SQL server 2016?

manavairajan

Right there with Babe

Points: 781
More actions
October 26, 2017 at 7:02 am

#329389

Hi All,
I need a help. I want to change File table directory with different location(Remote location) on the file stream server. How to set in using script (or) through properties window.
Thanks
Rajan

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

manavairajan - Thursday, October 26, 2017 7:02 AM
Hi All,
I need a help. I want to change File table directory with different location(Remote location) on the file stream server. How to set in using script (or) through properties window.
Thanks
Rajan

You can change the file table directory using alter table using something like:
ALTER TABLE FileTableName SET ( FILETABLE_DIRECTORY = N'Directory name' );

A file table directory is an extension of filestream so the root of the directory is the server name followed by the share name for the instance. So that share name for filestream is local to the SQL Server instance. You can change the filestream share name but the location would be local to the server.

Sue

manavairajan Right there with Babe Points: 781 More actions · Answer 2

Hi Sue,
Thank you so much your response.

I tried with alter table script. but, Its able to change only folder name. I have requirement need to use remote location instead of local path.
Can you please confirm do we have possibility to set any external drive path on the same machine?

ALTER TABLE FileTableName  
SET ( FILETABLE_DIRECTORY = N'Directory name' );

Thanks in advance for your assistance.

Sue_H SSC Guru Points: 90860 More actions · Answer 3

manavairajan - Friday, October 27, 2017 3:28 AM
Hi Sue,
Thank you so much your response.
I tried with alter table script. but, Its able to change only folder name. I have requirement need to use remote location instead of local path.
Can you please confirm do we have possibility to set any external drive path on the same machine?
ALTER TABLE FileTableName  
SET ( FILETABLE_DIRECTORY = N'Directory name' );  
Thanks in advance for your assistance.

Correct. As I indicated my first reply, you can change the directory but it would still be local to the server.
A filetable is not a directory in the same way as directories or folders are on something like your C: drive. Filetables use NTFS containers so are not like a directory mapped to a drive. The share that is created for the filestream group is not like a share mapped to a folder or directory like a typical shared file.
You can change the name of the filestream share, the name of the filetable directorry but it will still point to \\ServerName\FilestramShareName which is really just a virtual reference to the NTFS data container. It's not referencing a real directory like a regular file share of \\SomeServer\SomeShareName
If you need to physically move the filestream filegroup, you can do that like you would for other LOB filegroups using backup and restore, detach and attach, creating a new filegroup, etc. You can find examples on moving the filegroup in this blog post:
Moving FileStream data to new location

Sue