Saving Attachments Outside Database

Attachments, which usually occupy the largest part of the database, can also be saved outside the database using the filestream technology. Attachments size is then not counted into the database size itself.

Enable this option if the requests often contain attachments that considerably add to the size of the database and you want to use ALVAO on Microsoft SQL Server Express where the database size is limited.

Note:

Saving attachments outside database has no influence on the performance of the ALVAO system.

Enabling the Option to Save Attachments Outside Database

Enabling Filestream on the MS SQL Server

Enabling Filestream During the MS SQL Server Installation
1. Open the Database Engine Configuration page.
2. Enable the following options on the FILESTREAM tab gradually:
  - Enable FILESTREAM for Transact-SQL access
  - Enable FILESTREAM for file I/O streaming access
  - Allow remove clients to have streaming access to FILESTREAM data
3. Continue the installation.
Enabling Filestream After the MS SQL Server Installation
1. Launch SQL Server Configuration Manager.
2. Right-click on the SQL Server Service instance which you want to enable filestream to.
3. Enable all the options on the FILESTREAM tab gradually.
4. Click OK to save.
5. Launch SQL Server Management Studio
6. Launch the following SQL script over target DB ALVAO:
  - EXEC sp_configure filestream_access_level, 2
    GO
    RECONFIGURE
    GO
Enabling Filestream from Command Line
1. Download the script enabling filestream from command line here.
2. Run the following command in the command line:
  - cscript filestream_enable.vbs /Machine:<machine name> /Instance:<sql server instance> /Level:3
    
    Parameters:
        Machine: Target machine name. If not specified, local machine is selected.
        Instance: Sql server instance. If not specified, default one (MSSQLSERVER) is used.
        Level: FILESTREAM functionality level which will be enabled. If not specified, 3 will be selected.
            0 – FILESTREAM functionality is disabled.
            1 – Enables FILESTREAM for T-SQL access.
            2 – Enables FILESTREAM for T-SQL and I/O streaming access, but does not enable remote I/O streaming access.
            3 – Enables FILESTREAM for T-SQL and I/O streaming access and also for remote I/O streaming access.
3. Launch SQL Server Management Studio
4. Launch the following SQL script over target DB ALVAO:
  - EXEC sp_configure filestream_access_level, 2
    GO
    RECONFIGURE
    GO

Adding Filestream to Database

Launch SQL Server Management Studio.
Launch the following SQL script over target DB ALVAO:
- ALTER DATABASE [<database_name>]
  ADD FILEGROUP fstDocumentGroup contains filestream;
  go
  declare @db_name varchar (MAX)
  declare @filestream_loc varchar (MAX)
  SELECT @db_name = physical_name FROM sys.master_files WHERE database_id = DB_ID(DB_NAME()) AND type_desc = 'ROWS'
  set @db_name = REVERSE(RIGHT(REVERSE(@db_name),(LEN(@db_name)-CHARINDEX('\', REVERSE(@db_name),1))+1))
  set @filestream_loc = @db_name + 'AlvaoFileStream'
  
  DECLARE @AddFileSql varchar(max);
  SET @AddFileSql = 'ALTER DATABASE [<database_name>]
  ADD FILE
  ( NAME = ''fstDocument'', FILENAME = ' + QuoteName( @filestream_loc, '''' ) + ')
  TO FILEGROUP [fstDocumentGroup];
  ';
  EXEC (@AddFileSql);
Note:
The script will create a folder for saving the filestream in the folder containing database files.
If you want to save the filestream in a custom defined folder, enter its name including absolute path in the @filestream_loc variable:
- ALTER DATABASE [<database_name>]
  ADD FILEGROUP fstDocumentGroup contains filestream;
  go
  declare @filestream_loc varchar (MAX)
  set @filestream_loc = 'C:\<path>\<folder_with_filestream>'
  
  DECLARE @AddFileSql varchar(max);
  SET @AddFileSql = 'ALTER DATABASE [<database_name>]
  ADD FILE
  ( NAME = ''fstDocument'', FILENAME = ' + QuoteName( @filestream_loc, '''' ) + ')
  TO FILEGROUP [fstDocumentGroup];
  ';
  EXEC (@AddFileSql);
Notification:
The target folder must not exist when you run the script. It will be created automatically after the script is run.

Adding Filestream into tDocument Table

Launch SQL Server Management Studio.
Launch the following SQL script over target DB ALVAO:
- ALTER TABLE dbo.tDocument SET (FILESTREAM_ON = fstDocumentGroup)
  GO
  ALTER Table dbo.tDocument
  ADD uniqueGUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL
  CONSTRAINT UQ_tDocument_uniqueGUID UNIQUE
  CONSTRAINT DF_tDocument_uniqueGUID DEFAULT newid()
  GO
  
  ALTER TABLE dbo.tDocument
  ADD TempColumn varbinary(max) FILESTREAM NULL
  GO
  
  DECLARE @curId INT
  DECLARE @db_cursor CURSOR
  SET @db_cursor = CURSOR FOR
  SELECT iDocumentId FROM dbo.tDocument WHERE oDocument IS NOT NULL
  
  OPEN @db_cursor
  FETCH NEXT FROM @db_cursor INTO @curId
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
  UPDATE dbo.tDocument SET TempColumn = oDocument WHERE iDocumentId = @curId
  UPDATE dbo.tDocument SET oDocument = NULL WHERE iDocumentId = @curId
  
  FETCH NEXT FROM @db_cursor INTO @curId
  END
  CLOSE @db_cursor
  DEALLOCATE @db_cursor
  GO
  
  ALTER TABLE dbo.tDocument DROP COLUMN oDocument
  GO
  
  EXEC sp_rename 'dbo.tDocument.TempColumn', 'oDocument', 'COLUMN'
  GO

Disabling the Option to Save Attachments Outside Database

Removing Filestream from tDocument Table

Launch the following script over target DB ALVAO in the SQL Server Management Studio:
- ALTER TABLE dbo.tDocument ADD TempColumn varbinary(max)
  GO
  
  DECLARE @curId INT
  DECLARE @db_cursor CURSOR
  SET @db_cursor = CURSOR FOR
  SELECT iDocumentId FROM dbo.tDocument WHERE oDocument IS NOT NULL
  
  OPEN @db_cursor
  FETCH NEXT FROM @db_cursor INTO @curId
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
  UPDATE dbo.tDocument SET TempColumn = oDocument WHERE iDocumentId = @curId
  UPDATE dbo.tDocument SET oDocument = NULL WHERE iDocumentId = @curId
  
  FETCH NEXT FROM @db_cursor INTO @curId
  END
  CLOSE @db_cursor
  DEALLOCATE @db_cursor
  
  GO
  ALTER TABLE dbo.tDocument DROP COLUMN oDocument
  GO
  EXEC sp_rename 'dbo.tDocument.TempColumn', 'oDocument', 'COLUMN'
  GO
  ALTER TABLE dbo.tDocument set (filestream_on = "NULL")
  GO
  ALTER TABLE dbo.tDocument ALTER COLUMN uniqueGUID DROP ROWGUIDCOL
  GO
  ALTER TABLE dbo.tDocument DROP CONSTRAINT DF_tDocument_uniqueGUID
  GO
  ALTER TABLE dbo.tDocument DROP CONSTRAINT UQ_tDocument_uniqueGUID
  GO
  ALTER TABLE dbo.tDocument DROP COLUMN uniqueGUID

Removing Filestream from Database

Launch the following script in the SQL Management Studio:
- ALTER DATABASE [<database_name>] REMOVE FILE fstDocument
  GO
  ALTER DATABASE [<database_name>] REMOVE FILEGROUP fstDocumentGroup
  GO

Disabling Filestream on the MS SQL Server

Disabling in the SQL Configuration Manager
1. Launch SQL Server Management Studio.
2. Launch the following SQL script over target DB ALVAO:
  - EXEC sp_configure filestream_access_level, 0
    GO
    RECONFIGURE
    GO
3. Launch SQL Server Configuration Manager.
4. Right-click on the SQL Server Service instance which you want to disable filestream to.
5. Disable all the options on the FILESTREAM tab gradually.
6. Click OK to save.
Disabling in Command Line
1. Launch SQL Server Management Studio.
2. Launch the following SQL script over target DB ALVAO:
  - EXEC sp_configure filestream_access_level, 0
    GO
    RECONFIGURE
    GO
3. Download the script setting filestream from command line here.
4. Run the following command in the command line:
  - cscript filestream_enable.vbs /Machine:<machine name> /Instance:<sql server instance> /Level:0

Did not find what you were looking for? Ask our technical support team.