In this post we are going to show the implementation of a PowerShell script using SMO to move data between filegroups on SQL Server. This article is the second of our two part series on “Moving SQL Server data between filegroups – Database Structures”, you can find the first article here.

The Challenge

Looking around on the web, I couldn’t find a solution to the problem of moving data between filegroups that I liked. Further, many of those solutions are T-SQL based, which I thought were very complex. So I went off to write it myself. The problem lends itself to an iterative solution and I felt that T-SQL was not the right tool for the job. Enter PowerShell, which give us the ability to easily iterate over sets of data with minimal code, couple that with the SQL Server Management Object model and we have the makings of an elegant solution.

SQL Server Management Objects (SMO)

The SQL Server Management Objects (SMO) are a collection of objects developed for programming solutions for the management of SQL Server. SMO allows us to represent the state of the SQL Server configuration quickly and accurately by instantiating an object that represents the particular attribute of SQL Server that we are interested in. For example, at the core of the script below, we easily make a collection of indexes and iterate over that collection.

The code snippet below shows how to build an object array of indexes to move to the new filegroup. We get a list of all the tables, then iterate over all of the indexes on that table, if the index is clustered or non-clustered it’s added to an object array. If the table is a heap, it’s added to a second object array. 

$tables = $db.Tables | Where-Object {$_.Name -like $tablesToMove -and $_.Schema -like $schemaToMove -and $_.IsPartitioned -eq $FALSE}
$indexesToMove = @()
$heapsToMove = @()
#build a list of tables to be moved
foreach( $table in $tables )
{
    #get a list of all indexes on this table
    $indexes = $table.Indexes
    #iterate over the set of indexes
    foreach( $index in $indexes )
    {
#if this table is a clustered or Non-Clustered index. 
#Ignore special index types.
       if ( $index.IndexType -eq "ClusteredIndex" -or 
$index.IndexType -eq "NonClusteredIndex" ) 
       {
#if this index is not already in the destination FG, 
#add the index to the array
           if ( $index.FileGroup -ne $fileGroup )
           {
             Write-Output( $table.Schema + '.' + $table.Name + " " + $index.Name)
             $tableCount++
             $indexesToMove += $index
           }
       }
    }
    if ($table.HasClusteredIndex -eq $FALSE)
    {
        Write-Output( $table.Schema + '.' + $table.Name + " as a heap")
        $tableCount++
        $heapsToMove += $table
    }
} 

Implementing this code in a T-SQL based solution would involve complex looping constructs and deep knowledge of how SQL Server stores the metadata about tables and indexes. Using SMO, we’re able to leverage the object model and hide the implementation details of the SQL internals and build a simple code block to accomplish our task. This concept is extended further in that SMO functionality can change as SQL Server changes. With the two arrays from the code above we drive another block of code that actually moves the index or heaps to the destination filegroup.

The disclaimer

Listen, no really…lean in closer to your monitor and listen…this script moves data around in your database, it can generate a ton of IO and adds and deletes things. Test this script on a non-production system. Please do not blindly run this code on any system, in fact read the code first so you know exactly what it does…you’re going to have to because I left a safety net in the code.

The script

OK enough background information, let’s get to the meat of the topic, the script.  By itself it is pretty straightforward, set some variables in the text file, then run the script in Powershell. It will ask you for a destination filegroup so make sure that exists first. Here is it: MoveIndexes_1.ps1.txt

Here is a description of the variables in the script.

• $server – the server to connect to
• $dbName – the database in which we want to move data
• $doWork – a boolean variable used as a safety net. Set to true to move data, set to false to print out the objects that will be moved
• $onlineOpt – a boolean variable used if we want an online index rebuild on Enterprise edition
• $tablesToMove – a string used to select which tables to move. It uses a trailing wildcard * for example r* will move all tables beginning with r. Leave it set as * to move all tables. 
• $schemaToMove – a string used to select which schemas to move. It uses a trailing wildcard * for example dbo* will move all schemas beginning with dbo. Leave it set as * to move all schemas.

Example Execution

To get started, we will need to add an new file group and add some files to the filegroup.

• Add a new filegroup

USE [master];
GO
ALTER DATABASE [TestDB] ADD FILEGROUP [FG1];
GO

• Add files to the new filegroup

ALTER DATABASE [TestDB]
ADD FILE (NAME = N'TestDB1', FILENAME = N'C:\DATA\testdb1.ndf' , 
SIZE = 10240KB , FILEGROWTH = 10240KB )
TO FILEGROUP [FG1];
GO
ALTER DATABASE [TestDB]
ADD FILE ( NAME = N'TestDB2', FILENAME = N'C:\DATA\testdb2.ndf' , 
SIZE = 10240KB , FILEGROWTH = 10240KB )
TO FILEGROUP [FG1];
GO
ALTER DATABASE [TestDB]
ADD FILE (NAME = N'TestDB3', FILENAME = N'C:\DATA\testdb3.ndf' , 
SIZE = 10240KB , FILEGROWTH = 10240KB )
TO FILEGROUP [FG1];
GO
ALTER DATABASE [TestDB]
ADD FILE (NAME = N'TestDB4', FILENAME = N'C:\DATA\testdb4.ndf' , 
SIZE = 10240KB , FILEGROWTH = 10240KB )
TO FILEGROUP [FG1];
GO

• Example output of the powershell script

PS C:\Windows\system32> .\MoveIndexes_1.ps1
Please enter the destination Filegroup: FG1
Database: TestDB
dbo.t1 t1_cl_c1
dbo.t2 as a heap 
dbo.t3 t3_cl_c1
Are you sure you want to move the 3 objects listed above to the destination filegroup? (y/n): y
Moving: t1_cl_c1
Moving: t3_cl_c1
Moving Heap: t2
Filegroup contents
ParentID FileName          Name SizeUsedSpace
———-- --------          ---- ---- ---------
[PRIMARY]1 C:\DATA\TestDB.mdf TestDB 10240   2688
[FG1]3 C:\DATA\testdb1... TestDB110240   256
[FG1]4 C:\DATA\testdb2... TestDB210240   192
[FG1]5 C:\DATA\testdb3... TestDB310240   192
[FG1]6 C:\DATA\testdb4... TestDB410240   256
Tables
ParentSchemaNameFileGroup           
-------------------------           
[TestDB]dbot1FG1             
[TestDB]dbot2FG1             
[TestDB]dbot3FG1

Key observations

Looking at the output below here are a few key observations 

• You can see the redistribution of pages across the files in filegroup FG1 when compared with the initial setup from the first blog post here
• [t1], the table with the clustered index and [t2], the heap, are completely contained within the new filegroup FG1
• [t3], the table with the LOB data is split across PRIMARY and FG1. The LOB data remained in PRIMARY the IN_ROW_DATA is moved to FG1, more on that in a second.

Future enhancements and Notes

• LOB data – I intentionally left this unimplemented, but given the power of SMO, we could certainly develop a solution to this problem. Of particular concern would be indexes, foreign key constraints and other table settings that would need to be copied to the destination table.

• Partitions – I plan to make the script partition aware, but left this out of the current implementation due to time constraints.

• The script was developed and tested on SQL 2014, I do know that the script does not work on 2005 as CREATE INDEX WITH DROP_EXISTING did not exist yet. This is how the Recreate() method implements the index move under the hood.  

Summary

I hope this script proves helpful to you in several ways

• Highlights the potential of using SMO and PowerShell to simplify complex operations and tasks

• Provides a starting point for you to extend the code for your particular filegroup migration situation

The post Moving SQL Server data between filegroups – Part 2 – The implementation appeared first on Centino Systems Blog.