At PASS Summit last week, I presented “The What, Why, And How of Filegroups” to a packed room. It was great to see so many people eager to learn about something that is fundamental to every SQL Server database, but often not understood well enough.
As always, during and after the session, there were a lot of questions. One really piqued my interest: “When creating a table or index, is there a policy in Policy-Based Management to force a query to specify the filegroup instead of using the default?”
Let’s find out!
I’m going to use my DBA database on my development instance, DBAInfo. The database currently has one filegroup, PRIMARY.
USE DBAInfo; GO SELECT * FROM sys.filegroups;
Next, I’ll add another filegroup and file, and make it the default.
ALTER DATABASE DBAInfo ADD FILEGROUP PBMTest; ALTER DATABASE DBAInfo ADD FILE (NAME = 'SecondaryData', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SecondaryData.ndf') TO FILEGROUP PBMTest; ALTER DATABASE DBAInfo MODIFY FILEGROUP PBMTest DEFAULT;
I double-check my filegroups.
SELECT * FROM sys.filegroups;
At this point, any table added without an “ON FilegroupName” clause should be created on the default. Let’s check.
CREATE TABLE Test1 (ID INT);
I run my “What filegroup is that on?” query:
SELECT OBJ.OBJECT_ID, OBJ.name AS ObjectName, OBJ.type_desc, PA.index_id, FG.name AS FilegroupName FROM sys.filegroups FG INNER JOIN sys.allocation_units AU ON AU.data_space_id = FG.data_space_id INNER JOIN sys.partitions PA ON PA.partition_id = AU.container_id INNER JOIN sys.objects OBJ ON OBJ.object_id = PA.object_id WHERE OBJ.OBJECT_ID = (SELECT OBJECT_ID(N'dbo.Test1'));
It has been created on the default, PBMTest, as expected.
Now, I’ll add a third, non-default filegroup, PBMTest2.
ALTER DATABASE DBAInfo ADD FILEGROUP PBMTest2; ALTER DATABASE DBAInfo ADD FILE (NAME = 'TertiaryData', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TertiaryData.ndf') TO FILEGROUP PBMTest2;
I’ll check my filegroups again.
SELECT * FROM sys.filegroups;
Next, I want to create a policy that says “When a new table is created, put it on PBMTest2, even if that’s not the default.” In SSMS, I expand Management > Policy Management and right-click Policies > New Policy.
I give the policy a name, “CreateTableOnFilegroup”, and then click next to “CheckCondition” to set a condition. I name it “OnFilegroup”. The Facet is Table. I set the Field to @Filegroup, the Operator to “=”, and the Value to ‘PBMTest2’.
It’s set to every table in every database. I only want it to apply to my DBAInfo database, so I select Every > New Condition.
I give the Name DatabaseDBAInfo. I set the Facet to Database, the Field to @Name, the Operator to =, and the Value to ‘DBAInfo’. When I click OK, I’m back at the Create New Policy screen. I want to set Evaluation Mode to On demand and Server restriction to None. I click OK.
The one policy and two conditions are created.
Now, I go back to the query window and create another table, without specifying a filegroup to place it on.
CREATE TABLE PBMTestTable (ID INT);
What I want this to be created on PBMTest2. What filegroup is it on?
SELECT OBJ.OBJECT_ID, OBJ.name AS ObjectName, OBJ.type_desc, PA.index_id, FG.name AS FilegroupName FROM sys.filegroups FG INNER JOIN sys.allocation_units AU ON AU.data_space_id = FG.data_space_id INNER JOIN sys.partitions PA ON PA.partition_id = AU.container_id INNER JOIN sys.objects OBJ ON OBJ.object_id = PA.object_id WHERE OBJ.OBJECT_ID = (SELECT OBJECT_ID(N'dbo.PBMTestTable'));
It’s created on PBMTest.
This comes down to the Evaluation Mode setting of my policy. There are a total of four modes available in PBM – On Demand, On Schedule, On Change – Log only, and On Change – Prevent. What we are looking to do here is enforce On Change – Prevent – if the table isn’t going to be created on the filegroup we specify, we want it to fail. Unfortunately, not all policies can use this mode.
I had to do some research to figure this out. First, read this blog post on Facets by the PBM team. At the bottom, there is a table showing the facets and what modes are supported. Then, read this further explanation of the types by Lara Rubbelke, Policy Evaluation on a Single SQL Server 2008 Instance. Lastly, there is this blog, Policy Evaluation Modes,by the PBM team.
For On Change – Log and On Change – Prevent to exist for a facet, there must be a DDL event triggered by it. This is not the case with every operation in SQL Server. The CREATE TABLE commands in one such example. Because of this, I cannot create a policy to check what filegroup a table is being created on before it is created.
In this case, I can still create the policy and check it either on a schedule or on demand. It will then tell me which tables were not created on that filegroup.
Thank you Simon for the great question!