Saving attachments outside the database

The attachments that usually take up the largest part of the entire database can be stored outside the database using filestream technology. The size of the attachments is then not counted in the size of the database itself.

Check this option if the queries often contain attachments, which then significantly increase the size of the database, and you want to use Alvao on Microsoft SQL Server Express, where the database size is limited.

Deactivate the option to save attachments outside the database

1. Enable filestream on MS SQL server
   • Enable filestream during MS SQL Server installation
     1. Open the Database Engine Configuration page.
     2. On the FILESTREAM tab, enable the following options in turn:
        • 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 installation.
   • Enabling filestream after MS SQL Server installation
     1. Start SQL Server Configuration Manager.
     2. Right-click on the SQL Server Service instance you want to enable filestream.
     3. On the FILESTREAM tab, turn on all options one by one.
     4. Save by clicking OK.
     5. Launch SQL Server Management Studio
     6. Run the following SQL script over the target DB Alvao:
        • EXEC sp_configure filestream_access_level, 2
          GO
          RECONFIGURE
          GO
2. Adding a filestream to the database
   1. Launch SQL Server Management Studio.
   2. Run the Alvao SQL script over the target DB:
      • 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 above script will create a folder to store the filestream in the database files folder.
      If you want to store the filestream in a custom defined folder, then enter the folder name including the absolute path into 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);

      Caution:

      The target folder must not exist at the time the script is run. It will be automatically created when the script is run.
3. Adding filestream to tDocument table
   1. Launch SQL Server Management Studio.
   2. Run the Alvao SQL script over the target DB:
      • ALTER TABLE dbo.tDocument SET (FILESTREAM_ON = fstDocumentGroup)
        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 the database

1. Removing filestream from tDocument table
   1. In SQL Server Management Studio, run the following script over the target DB Alvao:
      • 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")
2. Removing the filestream from the database
   1. In SQL Management Studio, run the script:
      • ALTER DATABASE [<database_name>] REMOVE FILE fstDocument
        GO
        ALTER DATABASE [<database_name>] REMOVE FILEGROUP fstDocumentGroup
        GO
3. Disabling filestream on MS SQL Server
   • Disabling in SQL Configuration Manager
     1. Launch SQL Server Management Studio.
     2. Run the Alvao SQL script over the target DB:
        • EXEC sp_configure filestream_access_level, 0
          GO
          RECONFIGURE
          GO
     3. Launch SQL Server Configuration Manager.
     4. Right-click the SQL Server Service instance on which you want to disable the filestream.
     5. On the FILESTREAM tab, disable all options one by one.
     6. Save by clicking OK.

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