Continuing on from this post, will be the SQL used to move our Blob Data to a different filegroup.
The first step in creating a new filegroup for your all of your data is to create the actual filegroup itself. This is a logical entity which you associate with a file, so that when you specify a tables data to go to particular filegroup, the data is sent to that file, instead of your primary db file. The syntax for this is below.
ALTER DATABASE [MyDb] ADD FILEGROUP [BlobData]
This doesn't actually create the relevant file for your filegroup, you need to do that yourself as well. Syntax below.
ALTER DATABASE [MyDb] ADD FILE ( NAME='blob_data', FILENAME='C:\Data\BlobData.ndf' SIZE=200MB, MAXSIZE=UNLIMITED, FILEGROWTH=200MB ) TO FILEGROUP [BlobData]
You might have noticed the extension of the filename. No, this isnt a typo. This is the extension for secondary file data for SQL Server Databases.
Now comes the fun part. At this point you might be in 1 of 3 situations (disclaimer: there could be more).
- You have a table with no ntext/image data and are just looking to move this simple table to a different filegroup.
- You have a table with ntext/image data and need to move this data to a different filegroup.
- You have a table with ntext/image data and need to move this data as well as all non-blob data to a different filegroup.
If you are in situation 1 (you lucky thing), moving the table's data to a different filegroup is a relatively painless operation, starting with dropping and recreating the clustered index for the table (this is usually the primary key).
ALTER TABLE [MyBlobTable] DROP INDEX [Idx_MyBlobTable_Id] WITH MOVE TO [BlobData]
Dropping the data in this fashion moves the data stored in the index from your primary filegroup to your secondary filegroup. However you will still need to physically recreate the index, as while you have moved the data from the old index to the secondary file group, the index itself doesn't exist and performance could (and most likely will) suffer.
ALTER TABLE [MyBlobTable] CREATE CLUSTERED INDEX [Idx_MyBlobTable_Id] ON [MyBlobTable] (Id) ON [BlobData]This ensures all data created and used by this index will now be located on your secondary file group.
Now comes situation 2 and 3, and this is where SQL Server is somewhat lacking. If you have ntext/image data on your table currently associated with your primary filegroup, you will need to actually recreate your entire table, specifying the new filegroup for ntext/image data. The only way to do this is to create a new table with the correct filegroup associations, copy all your data from your existing table into your new table, and then drop the old table and rename your new table to the old table name. This is messy and icky (and yes, that's a technical term!), but you really don't have a choice in this case.
CREATE TABLE [dbo].[MyBlobTable] ( Id INT IDENTITY (1,1), -- Identity columns obviously aren't necessary for this to work, this is just the standard for our work BlobData NTEXT, MoreBlobData IMAGE ) ON [Primary] TEXTIMAGE_ON [BlobData]
The TEXTIMAGE_ON clause is used to define, for the table in context, where ALL ntext/image data goes for any column within the table. If you are situation 3, you would change the ON [Primary] to ON [BlobData] as the code currently how it stands would split the data for that table across two filegroups. (Side note: there is nothing wrong with this, SQL Server is actually quite smart in how it handles this sort of physical split between 1 logical entity).
A caveat with moving data to different filegroups in this fashion, is that if you have any other existing non-clustered indexes you will need to recreate these on the new filegroup, after you have done whatever migration processes you feel necessary.
I hope this serves a purpose for some other poor sucker out there researching this particular area of SQL Server. Thankfully there is a wealth of information out there in the internet, some of which I'll link below for further research if you are interested. There are alot of extra tidbits within these articles, which I highly recommend.
Sources:
Comments