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

Functionality	Persona	Description	Acceptance Criteria
Upload attachments of various types.	End User / Analyst	Core functionality for associating files with records.	Support common document formats (PDF, Word, Excel), images (JPEG, PNG), and videos (MP4).
Upload single and multiple files simultaneously with progress indicators and error handling.	End User / Analyst	Improves user experience and efficiency for the upload process.	Provide informative error messages and solutions for upload failures.
View and download the attached files.	End User / Analyst	Essential for accessing and reviewing the attached information.	Display previews or thumbnails for supported file types within the system.
Restrict access to attachments based on user roles and permissions.	End User / Analyst	Crucial for data security and information control.	Define access levels for different user roles (view, download, upload, rename, delete).

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.

Notes

FILESTREAM is not automatically enabled when you install or upgrade SQL Server.

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:

On the Start menu, navigate to All Programs > SQL Server > Configuration Tools, and then select SQL Server Configuration Manager.
On the list of services, right-click SQL Server Services, and select Open.
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.
In the SQL Server Properties dialog box, navigate to the FILESTREAM Select the Enable FILESTREAM for Transact-SQL access check box.
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.
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.
In SQL Server Management Studio, select New Query to display the Query Editor.
In Query Editor, enter the following Transact-SQL code: EXEC sp_configure filestream_access_level, 2; RECONFIGURE;
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:

Right click on the database and select Properties.
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
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:

The syntax to add a FILESTREAM filegroup is shown below:
USE [master]
GO
ALTER DATABASE [DB_Name] ADD FILEGROUP [Filegroup_Name] CONTAINS FILESTREAM
GO
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
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

Config Key

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

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

Config Key

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

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

Notes

Attachment embeddings are not supported for Import/Export and Master Import functionalities under Infrastructure, Import under Users, even when the key is enabled.
File upload functionality will affect the following modules: IM, SR & SCM, CM, PM, RM, SLA, KM, ITAM, Discovery and Admin & WhatsApp attachments.

Dos & Do not's

To enable this feature, we need to enable FILESTREAM at SQL server and DB level with related keys in the config files.
Switching to FILESTREAM in the middle of the process may present difficulties in ensuring the continuity and integrity of data between conventional storage and database storage.

Loading