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.
Note:
Setting attachments to be stored outside the database does not affect Alvao performance.
Deactivate the option to save attachments outside the database
- Enable filestream on MS SQL server
- Enable filestream during MS SQL Server installation
- Open the Database Engine Configuration page.
- 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
- Continue installation.
- Enabling filestream after MS SQL Server installation
- Start SQL Server Configuration Manager.
- Right-click on the SQL Server Service instance you want to enable filestream.
- On the FILESTREAM tab, turn on all options one by one.
- Save by clicking OK.
- Launch SQL Server Management Studio
- Run the following SQL script over the target DB Alvao:
- EXEC sp_configure filestream_access_level, 2
GO
RECONFIGURE
GO
- Adding a filestream to the database
- Launch SQL Server Management Studio.
- 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.
- Adding filestream to tDocument table
- Launch SQL Server Management Studio.
- 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
- Removing filestream from tDocument table
- 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")
- Removing the filestream from the database
- In SQL Management Studio, run the script:
- ALTER DATABASE [<database_name>]
REMOVE FILE fstDocument
GO
ALTER DATABASE [<database_name>]
REMOVE FILEGROUP fstDocumentGroup
GO
- Disabling filestream on MS SQL Server
- Disabling in SQL Configuration Manager
- Launch SQL Server Management Studio.
- Run the Alvao SQL script over the target DB:
- EXEC sp_configure filestream_access_level, 0
GO
RECONFIGURE
GO
- Launch SQL Server Configuration Manager.
- Right-click the SQL Server Service instance on which you want to disable the filestream.
- On the FILESTREAM tab, disable all options one by one.
- Save by clicking OK.
Did not find what you were looking for? Ask our technical support team.