Skip Navigation LinksALVAO 10.0ALVAO Service DeskSystem Implementation in an OrganizationCustom Edits and ExtensionsSaving Attachments Outside Database Skip Navigation Links. Skip Navigation Links Skip Navigation Links.


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

  1. 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
  2. Adding Filestream to Database
    1. Launch SQL Server Management Studio.
    2. 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);
      Warning:
      The target folder must not exist when you run the script. It will be created automatically after the script is run.
  3. Adding Filestream into tDocument Table
    1. Launch SQL Server Management Studio.
    2. 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

  1. Removing Filestream from tDocument Table
    1. 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
  2. Removing Filestream from Database
    1. 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
  3. 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.