Filestream Removal For Maximizer Databases

Each table

• Disable the Audit logging trigger to reduce overhead and minimize additions to 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

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. In on-premises versions of Maximizer the script is located in \Maximizer\DbScripts\SqlServer\Drop_FTS_Indexes_script.sql

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, which 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 red with the uniquely named constraint
   ALTER TABLE [dbo].[AMGR_Letters_Tbl] DROP CONSTRAINT [DF__AMGR_Letter__UId__14F1071C]
   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]
   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
   
   

1. 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.
2. 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.