Preamble
Filestream a storage method for binary objects in MS SQL databases. Rather than storing the binary object in directly the data file of the database, Filestream stores the file in the file structure of the server it's-self. 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 the following:
- 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
- Open the SQL Management Studio and connect to the SQL Server instance that is in use.
- Use the navigation to find the database that you are going to upgrade.
- Right click on the database and check the properties.
- From the properties dialog, choose the Filegroups option and look at the FILSTREAM section.
- If there are files listed in the Filestream section, then you will need to remove Filestream from the database.
Process Outline
Each database
- Switch the database to Simple recovery mode so the log does not grow excessively
- Remove all full-text indexes.
- Turn of Filestream on AMGR_Letters_Tbl and AMGR_Documents_Tbl
- Remove the Filestream file
- Remove the Filestream Filegroup
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
- Ensure there is enough disk space (2 times the current database .mdf size free)
- Switch the database to Simple recovery mode (under Options in the database properties) if this is not already done.
- 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
- 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 triggerDisable 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_TblADD tmp_TextCol varbinary(max) nullGO
--Note that the next step is to copy data and may take a long time and use considerable disk spaceUPDATE AMGR_Letters_Tbl SET tmp_TextCol = TextColGO
ALTER TABLE AMGR_Letters_Tbl DROP COLUMN TextColGO
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 constraintALTER 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 tableALTER TABLE AMGR_Letters_Tbl set (Filestream_on = "NULL")GO
--Re enable audit log triggerENABLE trigger AMGR_Letters_tbl_auditLog_trigger_all on dbo.amgr_letters_tblGO-- Letters Table complete-- Now start on the Documents Table-- Create a temp Column to hold FS data in AMGR_Documents_TblALTER TABLE AMGR_Documents_TblADD tmp_Data varbinary(max) nullGO
UPDATE AMGR_Documents_Tbl SET tmp_Data = DataGO
ALTER TABLE AMGR_Documents_Tbl DROP COLUMN DataGO
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_TblALTER TABLE AMGR_Documents_Tbl set (Filestream_on = "NULL")GO
- 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.
- 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.
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. |