Removing Filestream from your Maximizer Database

Updated 1 month ago by Patrick Wong

Filestream removal for Maximizer databases

 Please read this article in its entirety before proceeding. Be aware that there are TWO SQL scripts to run.

Filestream is a storage method for binary objects in MS SQL databases. Rather than storing the binary object directly in the data file of the database, Filestream stores the file in the file structure of the server itself. There are pros and cons to the use of Filestream but with the withdrawal of support for SQL Express edition in Maximizer some versions ago, it is time to retire the use of Filestream.

Time to Completion Dependency

An important reminder is that removing FILESTREAM within the database is dependent upon:

  • Total Size of your FILESTREAM data
  • Total number of documents in your database
  • Capabilities of your SQL Server (ie. processing power, memory, disk space)
    • If you are removing FILESTREAM on a relatively new SQL Server, removing FILESTREAM will be relatively faster
    • If your SQL Server is on a virtual machine, you could temporarily allocate more resources to it to accommodate the FILESTREAM removal process.
    • The recommended available disk space is at least twice the size of your FILESTREAM.

Checking whether Filestream is in use.
  1. Open the SQL Management Studio and connect to the SQL server instance that is in use.
  2. Use the navigation to find the database that you are going to upgrade
  3. Right-click on the database and check the properties.
  4. From the properties dialog, choose the Filegroups option and look at the FILESTREAM section
  5. If there are files listed in the Filestream section, then you will need to remove Filestream from the database.

Process outline

The process for each database is:
  • Switch the database to Simple recovery mode so the log does not grow excessively
  • Remove all full-text indexes.
  • Turn off Filestream on AMGR_Letters_Tbl and AMGR_Documents_Tbl
  • Remove the Filestream file
  • Remove the Filestream Filegroup

The process outline for each table is:

  • Disable Audit logging trigger
    • This will reduce overhead and minimize additions to the audit log tables
  • Add a temporary varbinary(max) column to the table
  • Copy the contents of all documents into the temp column
  • Remove the old data column
  • Rename the temp column to the data column
  • Drop 2 constraints on the Uid column and drop the Uid column that was used by Filestream
  • Turn off Filestream at the table level
  • Re-enable Audit logging trigger

Steps

Steps to Prepare the database.
  1. Ensure there is enough disk space (2 times the current database .mdf size free)
  2. Switch the database to Simple recovery mode (under Options in the database properties), if this is not already done.
  3. Remove the full-text indexes.  For on-premise versions of Maximizer, the script is located in the following directory: \Maximizer\DbScripts\SqlServer\Drop_FTS_Indexes_script.sql

 

Steps for Filestream Removal
  1. Step through the Filestream Removal script below running just one command at a time in SQL Management studio.  You will need to replace the constraint names in red with the actual constraints in the database. They can be found by expanding AMGR_Letters_Tbl (or AMGR_Documents_Tbl) and expanding Constraints to look for AMGR_xxx_UId_xxx, or you can delete the constraint manually instead of running that script line.
    --Run step by step in the SQL Management studio --ensure recovery mode is set to simple.  Edit the database name specified (XXXXX)
    ALTER DATABASE [XXXXX] SET RECOVERY SIMPLE;
    --disable audit log trigger
    Disable trigger AMGR_Letters_tbl_auditLog_trigger_all on dbo.amgr_letters_tbl
    --Create a Temp Column to hold Filstream Data in SQL Table
    ALTER Table AMGR_Letters_Tbl ADD tmp_TextCol varbinary(max) null
    GO
    --Note that the next step is to copy data and may take a long time and use considerable disk space
    UPDATE AMGR_Letters_Tbl SET tmp_TextCol = TextCol
    GO
    ALTER TABLE AMGR_Letters_Tbl DROP COLUMN TextCol
    GO
    sp_Rename 'AMGR_Letters_Tbl.tmp_TextCol', 'TextCol', 'COLUMN'
    GO
    -- Remove constraints from the database. Constraints are uniquely named and you will need to update this command for each database
    -- in the example below, navigate to the constraints of the table, and update the part in bold red with the uniquely named constraint
    ALTER TABLE [dbo].[AMGR_Letters_Tbl]
    DROP CONSTRAINT [DF__AMGR_Letter__UId__14F1071C]
    --Note: this next step is remove the UID column. If it fails, there may be some other object that refers to this. Common reasons for failure is that an index has been created for the letters table that includes UID. Explore the indexes of AMGR_Letters_Tbl to confirm. If there is an index that refers to UID, delete it.
    DROP INDEX [AMGR_Letters_Tbl_UId_Unique] ON [dbo].[AMGR_Lettters_Tbl] WITH (ONLINE = OFF)
    GO
    ALTER TABLE AMGR_Letters_Tbl DROP COLUMN [Uid]
    GO
    --Finally, turn off Filestream for the Letters table
    ALTER TABLE AMGR_Letters_Tbl set (Filestream_on = "NULL")
    GO
    --Re enable audit log trigger
    ENABLE trigger AMGR_Letters_tbl_auditLog_trigger_all on dbo.amgr_letters_tbl
    GO
    -- Letters Table complete
    -- Now start on the Documents Table -- Create a temp Column to hold FS data in AMGR_Documents_Tbl
    ALTER TABLE AMGR_Documents_Tbl ADD tmp_Data varbinary(max) null
    GO
    UPDATE AMGR_Documents_Tbl SET tmp_Data = Data
    GO
    ALTER TABLE AMGR_Documents_Tbl DROP COLUMN Data
    GO
    sp_Rename 'AMGR_Documents_Tbl.tmp_Data', 'Data', 'COLUMN'
    GO
    -- This Constraint name will need to be updated for each Database.
    ALTER TABLE AMGR_Documents_Tbl DROP CONSTRAINT [DF__AMGR_Docume__Uid__16D94F8E]
    DROP INDEX [AMGR_Documents_Tbl_UId_Unique] ON [dbo].[AMGR_Documents_Tbl] WITH (ONLINE = OFF)
    GO
    ALTER TABLE AMGR_Documents_Tbl DROP COLUMN [Uid]
    GO
    --Remove FILESTREAM assignment for AMGR_Documents_Tbl
    ALTER TABLE AMGR_Documents_Tbl set (Filestream_on = "NULL")
    GO
  2. In SQL Management Studio, right-click on the database and open Properties. Under Files, locate the file of type “FILESTREAM Data” and remove it.  Click OK to finish the file removal.
  3. In SQL Management Studio, right-click on the database and open Properties. Under Filegroups, remove all Filegroups in the FILESTREAM section.  Click OK to finish the filegroup removal.

NB. You may find that it takes a few moments for the SQL management studio to update and allow the removal of the Filegroup

If all the steps are completed, you will be able to upgrade the Maximizer database in the normal way.

How did we do?