Moving Tables Between Filegroups

  • Comments 2

I got an e-mail in response to the Best Practices blogs on Filegroups. I've gotten other e-mails similar to this one, so I thought I would answer (or try to) all of them here. Here's the e-mail:

 

"I recently started a new DBA job.  When I started the company had recently purchased new hardware and had moved their existing SS 2000 db to SS 2008.  But they did not change the file structure (location of data, index, log files) they kept them on the same drive except for the temp db is on a separate drive.  There are three separate drives (in a RAID 5 of course) C:\, D:\, F:\

 

C:\ contains the OS and some Primary data filegroups.  D:\ contains data file groups, index files groups, and the backup files are saved here until moved off to a backup server.  F:\ contains the tempdb files.

 

How does one modify a production db server to utilize the three drives without harming transactions?  In other words, how is this done seamlessly?

 

Thank you for you insight in advance!"

There are a few things happening here at once, so let's tease this out a bit. First, if there is a SAN with one Logical Unit (LUN) presented to the server as a physical drive, then the drive letters and the separation really don't matter. Once again, you might try and speak with the SAN admin to have them understand how SQL Server uses the file system, and they might be willing to break up that LUN for you into smaller LUNs, which you can map to drive letters, and then you can map the SQL Server Filegroups to those, and then put Files and then Objects in that (whew!).

That brings up a point that people sometimes get confused on. Files are not the same thing as Filegroups. There's always one Filegroup created by default, and multiple files (which might even be on separate drives) can go in that. You can create more Filegroups, which contain yet more files. The only thing you can specify for a table or index (those are the main data-bearing objects) is the Filegroup, not the files. The files fill up automatically, like water poured into glasses at the same rate. So you're working here with the Filegroup, not the files.

Now to the next issue. How do you move things once they are already created? There are a few options - some easy, some, well, less easy. To move the tempdb database (often a good idea) you can use the ALTER DATABASE statement. There's a decent article on that here: http://www.tech-recipes.com/rx/2342/sql_server_2005_move_tempdb/ 

For backups and other maintenance tasks that create data (like exports), just change the statement that backs up the database to another location. Easy Peasy.

How about Indexes? These are also easy to move - sometimes. Just follow the steps here: http://msdn.microsoft.com/en-us/library/ms175905(SQL.90).aspx. Note that if the index is Clustered, then the table *is* the index. So by recreating the index onto another Filegroup, you've effectively moved the table. When is it not easy? Well, if the index is huge, it can take a really long time. And as always, you should test and backup everything before you make changes like this.

The most difficult? Tables. You can use SQL Server Management Studio and just change the file location. That works (almost) every time, so once again, back up before you start. And also make sure you have the downtime for larger tables, as they will possibly be unavailable during the move. You can also script the table, create it on the new Filegroup, and then transfer the data over. In effect, that's what SSMS is doing under the covers. But it isn't trivial to do this - you need to worry about keys, renaming the tables and so on.

There's more on this process in this great article on SQL Tips: http://www.mssqltips.com/tip.asp?tip=1112

Leave a Comment
  • Please add 5 and 3 and type the answer here:
  • Post
  • Nice summary, Buck.  Last weekend I was looking for a reference documenting "...how SQL Server uses the file system...break up that LUN...into smaller LUNs..."  Do you have anything handy?  Thanks!

  • @Jimmy May - Each SAN works differently, and that subsystem is what presents the LUNs to the OS. The OS then sees them as devices, creates partitions on that, and then creates drive letters on that. SQL Server uses the drive letters.

    Or perhaps you're asking something else?

Page 1 of 1 (2 items)