Join Martin Guidry for an in-depth discussion in this video Using multiple files, part of Designing Database Solutions for SQL Server 2012.
- View Offline
- In this section, I'd like to demonstrate creating and using new files and new file groups in a SQL Server database. In the Adventureworks database, I'm going to open up Tables, and you'll see the very first table listed is AWBuildVersion, and that is a table that keeps track of which version of the Adventureworks database is currently implemented. To me, that information is not as important as some of the other information in the database, and therefore wouldn't need to be backed up as often.
Right now, this table is stored in the primary data file, and therefore is backed up in every backup routine. I'd like to move it to a new secondary data file that we won't back up as often. So, I'll right click on this table, and we'll look at the Properties. And in Properties I want to look at Storage, and under Storage we'll see that it is part of the Primary file group. Notice it doesn't tell me exactly which file in that file group.
I don't have that much granular control. I can just say that it is part of a particular file group. And also notice from this interface I can't change it. I can just see that it's part of that file group, but I can't change that it's part of the file group. We'll have to type in some SQL statements to change the file group. So hit Cancel here. First, we'll need to add our new files and file groups to the database. So I will highlight Adventureworks2012, right click go to Properties, and I have menu options for both files and file groups.
Right now we just have the one file group primary. I'm going to add by clicking the Add button, and I'm going to call my new file group Low Priority. And obviously you can call your file group whatever you want. It won't affect the functionality. My intention here is to take data that is a low priority and move it to this file group. That file group could then be backed up less often, or we could move that to a less expensive, and therefore less reliable, disk, or we might just see a little bit of a performance increase by separating out that data into a new file group.
Notice I have options to make it Read Only or Default. I don't want to do either one of those things right now so I'll just hit Okay at the bottom. So that gave me the file group, and now to create the file I'll right click again, Properties, go to Files. It shows me that I currently have two files, an Adventureworks2012_Data and Adventureworks2012_Log. One of them is for row data in the Primary file group, and one of them is for Logs and not in any file group.
I want to add a new one so I'll go down to the bottom, click Add, and I'll need to give it a name. The name doesn't affect the functionality at all so I'll call mine Adventureworks_LowPriority. And then, very important, I select the proper File Group, and that would be the Low Priority file group. I have some options for setting an Initial Size and the AutoGrowth. I'm going to go ahead and stick with the defaults, Initial Size of 4 MB and it will Autogrow by 1 MB increments, Unlimited amount.
I'll need to give it a file name, and I'll stick with the naming convention of earlier, Adventureworks2012 _LowPriority.ndf. And I'll hit Okay at the bottom. Now I'd like to move that table to that new file group so I'll open up Tables, and I'll need to select a new Query. I have some code staged for you in the Exercise files so we'll open up the code for this section, and copy and paste it into Management Studio.
And we'll see that starting on line 3 we are going to Alter the Table, and then it says the name of the table. First, we're going to need to drop the constraint so line 4 drops the constraint of the Primary key. We cannot move a table from one file group to another file group while it has a primary key so we'll need to drop that constraint. And then line 5, we'll move it to the new file group. Again, we can't move it to a specific file.
We can only move it to a specific file group. So lines 3, 4, and 5 accomplish the move, but we also drop the Primary key there, and obviously we would like to get that back so lines 8, 9, and 10 are just rebuilding that primary key to get the table back in the condition it was beforehand. So I'll go ahead and execute this. It says Command(s) completed successfully. That's good news, but let's go ahead and double check. I'll right click on the table, go down to Properties at the bottom, and under storage it now says it is in the File Group Low Priority.
That is exactly what we had hoped for.
- Planning the database infrastructure
- Designing databases vs. data warehouses
- Designing the physical database implementation
- Planning for partitioning
- Adding FileStream data
- Monitoring server health
- Managing SQL Server with PowerShell