What about moving LOB data while keeping the table online?

In my last post, I wrote about moving LOB data using OFFLINE operations: What about moving LOB data? It all started with the following question/discussion…

QUESTION: How can I move LOB data associated with a table?

The short answer is that there really isn’t a simple way—even with the SQL Server 2012 ability to rebuild indexes that include LOB data as an online operation. The key reason is the behavior of LOB data during these operations. To help you better understand how LOB data works, I created a series of scripts for SQL Server 2008. SQL Server 2008 R2 and SQL Server 2012 (which leverages ONLINE moves).

So, while SQL Server 2012 supports online rebuilds for tables that have LOB columns (and that’s FANTASTIC), it does not really offer us many options for moving LOB data easily. But, before you think there’s no hope—Brad Hoff (@SQLPhilosopher) came up with a way to move LOB data physically while keeping it in the SAME filegroup. So, we can achieve something similar but with different thinking… The best part is that is does NOT require the Enterprise Edition of SQL Server!

Moving LOB Data with ONLINE Operations

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. if your table is isolated and what you really want to do is restructure a filegroup OR move the filegroup to a new series of hard drives (but without dettaching/copying/moving) you can do this another way. @SQLPhilosopher blogged his strategy here: Moving a Filegroup to a New Disk Array with No Downtime.

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.

Once all of the other files are removed then the new files (on the new hardware) can be added (again, to the SAME filegroup). Once added, you can remove the single large file that was added previously. When this large file is removed, SQL Server will be required to rebalance all of the data within the filegroup across the newly added files. It’s not going to guarantee perfect balancing and if there are other objects in this filegroup then you’re going to be moving around A LOT of data. And, the process of moving the objects around does NOT try to keep these objects contiguous. So, your objects may end up more fragmented upon the completion of these operations. But, if the object (with LOB data) is isolated then this might be perfect! And, you can do all of this in ANY edition and with no downtime. So, it’s actually very compelling.

In summary, if you can’t yet upgrade to SQL Server 2012 or you want to move your LOB data as an ONLINE operation and you don’t have Enterprise Edition, this is certainly a cool trick to know. Thanks Brad!

Enjoy!
Kimberly