How can I move LOB_DATA ( varbinary/image/text) to different Filegroups

Question

LOB_DATA : varbinary(max),varchar(max), image , text columns.

I have 4 tables with large amount of LOB_DATA in it. I would like to move LOB_DATA in these tables to different File group in a different hard disk.

I have created a new filegroup and file in this filegroup. I also used sql command from this question. But varbinary column storage remains where it was before.

-- To move table data to new filegroup
CREATE UNIQUE CLUSTERED INDEX PK_YourTableName 
ON dbo.YourTableName(YourPKFields)
WITH (DROP_EXISTING = ON) ON [NewFilegroup]

I use following sql to see filegroup of lob_data.

-- To see lob_data filegroups
SELECT OBJECT_NAME(object_id) as OBJECT_NAME, FILEGROUP_NAME(data_space_id) as 
FILE_GROUP_NAME, type_desc
FROM sys.partitions p
JOIN sys.allocation_units a
on p.partition_id = a.container_id
WHERE
type_desc = 'LOB_DATA'

I am using SQL Server 2008.

I have found following solution how-to-move-the-lob-data-from-one-file-group-to-other. Do other approaches exists?

Answer 1

Kimberly Tripp & Paul Randal has a couple of excellent posts on this very subject:

• https://www.itprotoday.com/what-about-moving-lob-data

However, there is an interesting trick, one that does NOT require dropping/recreating keys but will still be an OFFLINE operation in versions prior to SQL Server 2012. It’s to create the table on a partition scheme using the DROP_EXISTING option. This moves the data and the LOB data to the SAME filegroup (you do NOT have an option to separate them) but you can still move them and with little work. What will be required are a partition function and a partition scheme. The data does not have to be physically partitioned but the data will only move when the table/clustered index goes from partitioned to non-partitioned or vice versa OR from one partition scheme to another.

• https://www.itprotoday.com/what-about-moving-lob-data-while-keeping-table-online

As an alternative strategy (and this might be difficult depending on how many other objects reside on the same filegroup), you can change the location of the files in the filegroup on which your table resides. >... Basically, it consists of adding a new file to existing filegroup which is the total size of all of the existing files. Once this new file is added, ALL of the other files are emptied into this new file by using the EMPTYFILE parameter of DBCC SHRINKFILE. When the file is emptied, all allocations are moved to the file with the most free space. So, you must make the newly added file the total size of the filegroup. Once everything has been emptied, you can remove these files from the database using ALTER DATABASE… REMOVE FILE.

Answer 2

The sp_MoveTablesToFileGroup procedure by Mark White (available for example at https://github.com/YPermitin/SQLServerTools/blob/master/SQL-Server-File-Groups/Move-Data-Between-Filegroups/sp_MoveTablesToFileGroup.sql) automates the move between filegroups (you can move it directly or generate a script to move the tables).

It uses the same idea as the marked answer (creating a partition function), but instead of having to find out the biggest / smallest ID for partitioning it just places both partitions into the target filegroup.

CREATE PARTITION FUNCTION PF_MOVE_HELPER_B0DE29720A214194B51D0D190927A58D ([bigint] ) AS RANGE RIGHT FOR VALUES (0);
CREATE PARTITION SCHEME PS_MOVE_HELPER_B0DE29720A214194B51D0D190927A58D AS PARTITION PF_MOVE_HELPER_B0DE29720A214194B51D0D190927A58D TO ([TARGET],[TARGET]);

And then it re-creates the index once with partitioning, and once without.