Attachment storage in database

Overview

Users (End User and Analyst) can upload multiple attachments, and the application saves attachments in binary files rather than physical file format in the database. This feature is implemented on the following pages of the Incident Management, Service Request Management, Change Management, Release Management, Service Level Agreement, Problem Management, Knowledge Management, IT Asset Management, Discovery modules, and Admin & WhatsApp attachments in the Summit application.  

Prerequisite

If you have enabled this feature, you must execute the below upgrade script to use the Attachment storage in database functionality:

https://summitbuild.blob.core.windows.net/elbrussp3/FileStream_Upgradescripts_ELBRUS_SP3_B022.zip

Benefits 

• Ensures security, efficiency, and scalability in managing file uploads linked to diverse records. 
• Enhances the reliability and performance of the attachment handling capabilities of the application. 

Refer to the table below for more details about Functionality and its description.

Table 

 What is SQL FILESTREAM?

To store unstructured data, such as documents and images on the file system, SQL Server-based application named FILESTREAM is used. FILESTREAM integrates the SQL Server Database Engine with an NTFS or ReFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system. 

Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. 

When to use FILESTREAM?

In SQL Server, BLOBs can be standard varbinary(max) data that stores the data in tables, or FILESTREAM varbinary(max) objects that store the data in the file system. The size and use of the data determine whether you should use database storage or file system storage.

 If the following conditions are true, you should consider using FILESTREAM:

• When stored objects are on average, larger than 1 MB.
• When fast read access is important.
• Applications that use a middle tier for application logic.

For smaller objects, storing varbinary(max) BLOBs in the database often provides better streaming performance.

Prerequisites:

This section contains the list of prerequisites for installing and configuring FILESTREAM on SQL Server and at DB level:

Enable and configure FILESTREAM on SQL server

 To enable FILESTREAM on the instance of the SQL Server Database Engine, perform the following steps:

1. On the Start menu, navigate to All Programs > SQL Server > Configuration Tools, and then select SQL Server Configuration Manager.
   
   
2. On the list of services, right-click SQL Server Services, and select Open.
   
   
3. In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM. Right-click the instance, and then select Properties.
   
   
4. In the SQL Server Properties dialog box, navigate to the FILESTREAM Select the Enable FILESTREAM for Transact-SQL access check box.
   
   
5. 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.
   
   
6. If remote clients want to access the FILESTREAM data that is stored on this share, then select Allow remote clients to have streaming access to FILESTREAM Select Apply.
   
   
7. In SQL Server Management Studio, select New Query to display the Query Editor.
   
   
8. In Query Editor, enter the following Transact-SQL code: EXEC sp_configure filestream_access_level, 2; RECONFIGURE;
   
   
9. Select Execute. Restart the SQL Server service.
   
   

Create FILESTREAM filegroup in database

This section contains the ways of creating FILESTREAM filegroup using SQL Server Management Studio and T-SQL query.

To create a FILESTREAM filegroup using SQL Server Management Studio, perform the following steps:

1. Right click on the database and select Properties.
   
   
2. In the Database Properties dialog box, navigate to Filegroup and click Add Filegroup. This adds a row in a FILESTREAM grid view. Specify the following parameters in the grid view.
   a. Name - Specify the Name of the Filegroup.
   
   b. FILESTREAM files - Total number of FILESTREAM The default value is zero (0).
   
   c. Read-Only - If you want to make the new Filegroup a read-only filegroup, then check the Read-Only.
   
   d. Default - If you want to make the new Filegroup a default FILESTREAM filegroup, then check the Default column.
   
   
   Figure: Properties
   
   
3. Specify the path of the FILESTREAM container in which you want to store the files. Click Files in Database Properties. On the files screen, configure options as follows and then click ok to create a FILESTREAM container as specified below:
   a. Logical Name - Specify the name of the FILESTREAM.
   
   b. File Type - Select FILESTREAM Data from the drop-down box.
   
   c. Filegroup - Select the FILESTREAM filegroup which we created in step 2.
   
   d. Path - Specify the location of the directory in which files are stored.
   
   Figure: Database Properties

To create a FILESTREAM filegroup using T-SQL query, add the following syntax:

1. The syntax to add a FILESTREAM filegroup is shown below:
   USE [master]
   GO
   ALTER DATABASE [DB_Name] ADD FILEGROUP [Filegroup_Name] CONTAINS FILESTREAM
   GO
2. Then, we must specify the location of the file stream container.
   USE [master]
   GOALTER DATABASE [EltechDB] ADD FILE (NAME = N' DF_Employeedocuments', FILENAME = N' D:\EltechDB\EmployeeDocuments\' ) TO FILEGROUP [EmployeeDoucment]
   GO
3. You can run the following query to view the details of the FILESTREAM filegroup.
   USE eltechdb
   go
   SELECT df.NAME AS [Logical Name], df.size / 128    AS [File Size],fg.NAME  AS [FileGroup Name], df.physical_name AS [Physical Path]
   FROM   sys.database_files AS df
   INNER JOIN sys.filegroups AS fg ON df.data_space_id = fg.data_space_id;
   
   

Enable key in config file

Add the following key in web.config, jobs: app.config and scheduler: app.config

<add key="App:StoreFilesInDBFilestream" value="True" />

Make sure AzureModel key as False. See the code block for more details.

<add key="App:AzureModel" value="False" />

To disable this feature, <add key="App:StoreFilesInDBFilestream" value="False" />