FILESTREAM and FileTable with Always On Availability Groups (SQL Server)
Published: May 17, 2016
Updated: May 17, 2016
Applies To: SQL Server 2016
THIS TOPIC APPLIES TO: SQL Server (starting with 2016)Azure SQL DatabaseAzure SQL Data Warehouse Parallel Data Warehouse
This topic contains information about the using the FILESTREAM and FileTable features with Always On Availability Groups in SQL Server 2016.
All FILESTREAM functionality is supported. After a failover, FILESTREAM data is accessible on both readable secondary replicas and on the new primary.
FileTable functionality is partially supported. After a failover, FileTable data is accessible on the primary replica, but FileTable data is not accessible on readable secondary replicas.
In this Topic:
- Before adding a database that uses FILESTREAM, with or without FileTable, to an availability group, ensure that FILESTREAM is enabled on every server instance that hosts an availability replica for the availability group. For more information, see Enable and Configure FILESTREAM.
When you enable FILESTREAM on an instance of SQL Server, an instance-level share is created to provide access to the FILESTREAM data. You access this share by using the computer name in the following format:
In an Always On availability group, however, the name of the computer is virtualized by using a Virtual Network Name, or VNN. When the computer is the primary replica in an availability group, and databases in the availability group contain FILESTREAM data, then a VNN-scoped share is also created to provide access to the FILESTREAM data. This does not affect Transact-SQL access to FILESTREAM data. However applications that use file system APIs have to use the VNN-scoped share, which has a path in the following format:
This VNN-scoped share is created when one of the following events occurs.
You add a database that contains FILESTREAM data to an Always On availability group on the primary replica. In this case, the share
\\<computer_name>\<filestream_share_name>already exists. The share
You enable FILESTREAM for file i/o streaming access on a primary replica that has availability groups. The following shares are created:
\\<VNN1>\<filestream_share_name>for availability group 1.
\\<VNN2>\<filestream_share_name>for availability group 2.
These VNN-scoped shares are also propagated to all secondary replicas.
When the database that contains FILESTREAM or FileTable data belongs to an Always On availability group:
The FILESTREAM and FileTable functions accept or return virtual network names (VNNs) instead of computer names. For more information about these functions, see Filestream and FileTable Functions (Transact-SQL).
All access to FILESTREAM or FileTable data through the file system APIs should use VNNs instead of computer names.
If your application tries to access the share by using the computer name in the format
\\<computer_name>\<filestream_share_name> when the database is part of an availability group, then an error is raised.
If your application tries to access the share by using a VNN-scoped path when the database is not part of an availability group, then the request may succeed. In this case, the virtual network name is resolved to the computer name. However this usage is strongly discouraged, since the VNN-scoped path will stop working if the availability group is dropped.