Saving Attachments Outside Database

• Attachments can be saved also outside the database using the filestream technology. Attachments size is then not counted into the database size itself.

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);
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.