Skip to content

Latest commit

 

History

History
88 lines (55 loc) · 4.75 KB

enable-and-configure-filestream.md

File metadata and controls

88 lines (55 loc) · 4.75 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic helpviewer_keywords
Enable and configure FILESTREAM
To use FILESTREAM, first enable it on the SQL Server Database Engine instance. Learn how to enable FILESTREAM by using SQL Server Configuration Manager.
MikeRayMSFT
mikeray
randolphwest
09/28/2023
sql
filestream
conceptual
FILESTREAM [SQL Server], enabling

Enable and configure FILESTREAM

[!INCLUDE SQL Server]

Before you can start to use FILESTREAM, you must enable FILESTREAM on the instance of the [!INCLUDE ssDEnoversion]. This topic describes how to enable FILESTREAM by using SQL Server Configuration Manager.

Enable FILESTREAM

  1. On the Start menu, navigate to All Programs > [!INCLUDE ssnoversion] > Configuration Tools, and then select SQL Server Configuration Manager.

    [!NOTE]
    On newer versions of Windows, follow these instructions to open SQL Server Configuration Manager.

  2. In the list of services, right-click SQL Server Services, and then select Open.

  3. In the SQL Server Configuration Manager snap-in, locate the instance of [!INCLUDE ssNoVersion] on which you want to enable FILESTREAM.

  4. Right-click the instance, and then select Properties.

  5. In the SQL Server Properties dialog box, select the FILESTREAM tab.

  6. Select the Enable FILESTREAM for Transact-SQL access check box.

  7. If you want to read and write FILESTREAM data from Windows, select Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.

  8. If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.

  9. Select Apply.

  10. In [!INCLUDE ssManStudioFull], select New Query to display the Query Editor.

  11. In Query Editor, enter the following [!INCLUDE tsql] code:

    EXEC sp_configure filestream_access_level, 2;
RECONFIGURE;

  12. Select Execute.

  13. Restart the [!INCLUDE ssNoVersion] service.

Best practices

Physical configuration and maintenance

When you set up FILESTREAM storage volumes, consider the following guidelines:

  • Turn off short file names on FILESTREAM computer systems. Short file names take significantly longer to create. To disable short file names, use the Windows fsutil utility.

  • Regularly defragment FILESTREAM computer systems using magnetic storage.

  • Use 64-KB NTFS clusters. Compressed volumes must be set to 4-KB NTFS clusters.

  • Disable indexing on FILESTREAM volumes and set disablelastaccess. To set disablelastaccess, use the Windows fsutil utility.

  • Disable antivirus scanning of FILESTREAM volumes when it's not necessary. If antivirus scanning is necessary, avoid setting policies that will automatically delete offending files.

  • Set up and tune the RAID level for fault tolerance and the performance that is required by an application.

RAID level Write performance Read performance Fault tolerance Remarks
RAID 5 Normal Normal Excellent Performance is better than one disk or JBOD; and less than RAID 0 or RAID 5 with striping.
RAID 0 Excellent Excellent None
RAID 5 + striping Excellent Excellent Excellent Most expensive option.

Physical database design

When you design a FILESTREAM database, consider the following guidelines:

  • FILESTREAM columns must be accompanied by a corresponding uniqueidentifier ROWGUID column. These kinds of tables must also be accompanied by a unique index. Typically this index isn't a clustered index. If the databases business logic requires a clustered index, you have to make sure that the values stored in the index aren't random. Random values will cause the index to be reordered every time that a row is added or removed from the table.

  • For performance reasons, FILESTREAM filegroups and containers should reside on volumes other than the operating system, [!INCLUDE ssNoVersion] database, [!INCLUDE ssNoVersion] log, tempdb, or paging file.

  • Space management and policies aren't directly supported by FILESTREAM. However, you can manage space and apply policies indirectly by assigning each FILESTREAM filegroup to a separate volume and using the volume's management features.