I have a database that is about 1TB in size. It's currently on a 2TB hard drive and I would like to replace it with a few 512GB SSD. I need to split the existing database file (.mdf) into multiple smaller files with file size limited at just below 512GB each.
Ive been doing some research online. It is not clear to me whether I should add filegroups or just add files to my primary filegroup.
How do I move data from the existing file (1TB) to the smaller files (512GB) safetly?
Has anyone done anything similar?
-
Do you have > 1 TB of free space on the current drive?Aaron Bertrand– Aaron Bertrand2015年08月01日 18:54:05 +00:00Commented Aug 1, 2015 at 18:54
-
will use another large HD for building the smaller files, space wont be an issueHoward– Howard2015年08月01日 20:06:33 +00:00Commented Aug 1, 2015 at 20:06
-
Add a new filegroup, with 'x' number of files, then migrate the existing data onto the new filegroup.Hannah Vernon– Hannah Vernon ♦2015年08月01日 20:44:47 +00:00Commented Aug 1, 2015 at 20:44
-
how do I migrate the existing data onto the new filegroup?Howard– Howard2015年08月01日 23:05:46 +00:00Commented Aug 1, 2015 at 23:05
-
1You have to drop and recreate the clustered indexes on all of the tables and indexes. If you expand the table in the object explorer then expand the indexes folder for the table you can right click on the index and script it with the drop and create command. You also maybe able to use the ALTER INDEX command but I'm not 100% sure you can move file groups with it. Here is a detailed reference on a few ways to do it.Aaron– Aaron2015年08月02日 00:58:18 +00:00Commented Aug 2, 2015 at 0:58
1 Answer 1
Just adding files to the existing file group will not redistribute the existing data.
I would go the route @Max is suggesting and ensure that the new files you are creating are the same size to maximize the proportional fill feature. And before you begin take a full backup.
Once you've migrated all of the tables, indexes, etc over to the new file group you should be able to shrink the mdf down to be under the 512GB point or smaller if you are not going to use the default file group anymore.
At that point take another full backup and then restore that to the new disks with the MOVE TO option in the restore command. This will let you take the new ndf files in the new file group and put them across the SSDs.
Depending on how you are managing the database you might want to set the new file group to be the default for all of the new objects that are created. Otherwise they will still default to the old file group and since it only has one file in it the objects/data in it won't benefit from the proportional fill feature that SQL Server has.