| title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | helpviewer_keywords | monikerRange | |||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Use FILESTREAM and FileTable with availability groups |
Steps to use either FILESTREAM or FileTable with databases participating in an Always On availability group. |
MashaMSFT |
mathoma |
randolphwest |
10/02/2023 |
sql |
availability-groups |
how-to |
|
>=sql-server-2016 |
[!INCLUDE sql windows only]
This article contains information about the using the FILESTREAM and FileTable features with [!INCLUDE ssHADR] in [!INCLUDE ssnoversion].
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 isn't accessible on readable secondary replicas.
- 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.
- On a [!INCLUDE winserver2012-md]-based failover cluster, you should apply the hotfix discussed in Can't access VNN FILESTREAM share when you use the FILESTREAM and FileTable features on a Windows Server 2012-based failover cluster to access file share using Virtual Network Name (VNN). This hotfix is also available at Microsoft Update Catalog.
When you enable FILESTREAM on an instance of [!INCLUDE ssNoVersion], 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:
\\<computer_name>\<filestream_share_name>
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 doesn't 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:
\\<VNN>\<filestream_share_name>
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\\<VNN>\<filestream_share_name>is created. -
You enable FILESTREAM for file i/o streaming access on a primary replica that has availability groups. The following shares are created:
\\<computer_name>\<filestream_share_name>.\\<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 isn't 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 stops working if the availability group is dropped.