I will soon be migrating to SQL Server 2017 and hence am working on beautifying some neglected aspects of our databases. One aspect is filegroups: my DBA predecessor has created multiple filegroups however as no one was really told about most objects just go to the default filegroup (`PRIMARY`). Now I would like to enforce that people creating tables and indexes actually name the filegroup where their objects should reside. Meaning: A `CREATE TABLE` or `CREATE INDEX` without an `ON [Filegroup]` should roll back with an error that the filegroup is missing. Is there any way to do this an would you recommend proceeding with such an approach? I have just come across that policy based management does [not work] in preventing objects being created on the default filegroup. Main point of doing this is that somebody should choose the filegroup for the objects in the first place instead of creating them in the default filegroup and afterwards have to move the objects based on rants by the DBA. I thought about two techniques; however, can't get started with either of them because of missing knowledge: - Use database triggers to check if there is an on clause and if not roll back --> this seems to be quite fragile as it would depend on text analysis of the command text - Make the primary filegroup really small in size (100KB?) in order for system tables still being able to fit in but other tables of moderate size would result in an error because there is not enough space left --> probably a bad idea as this could have severe side-effects on database availability and is not precise enough in preventing every user defined table or index to be created within the default filegroup. : https://www.brentozar.com/archive/2012/11/creating-objects-on-a-specific-filegroup-with-policy-based-management/
John K N (imported from SE)
You could possible create a new default filegroup with the Read_Only property set. Add database file: USE [StackExchange] GO ALTER DATABASE [StackExchange] ADD FILE ( NAME = N'StackExchange_DefRO', FILENAME = N'C:\SQL\SQL_DATA\StackExchangeRO.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [DEFAULTRO] GO Modify the new filegroup to `READONLY`: USE [master] GO declare @readonly bit SELECT @readonly=convert(bit, (status & 0x08)) FROM sysfilegroups WHERE groupname=N'DEFAULTRO' if(@readonly=0) ALTER DATABASE [StackExchange] MODIFY FILEGROUP [DEFAULTRO] READONLY GO Modify new filegroup to be `DEFAULT`: GO USE [StackExchange] GO IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'DEFAULTRO') ALTER DATABASE [StackExchange] MODIFY FILEGROUP [DEFAULTRO] DEFAULT GO If a user now creates an object without specifying a filegroup: USE [StackExchange] GO /****** Object: Table [dbo].[NewTable] Script Date: 21.12.2017 14:12:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[NewTable]( [ID] [int] NULL, [Test] [nchar](10) NULL ) GO They will receive the error message: Msg 1924, Level 16, State 2, Line 11 Filegroup 'DEFAULTRO' is read-only.