3

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 someboy 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

Thank you very much in advance for your help

Martin

  • Set the default filegroup? You can also use a policy for this.docs.microsoft.com/en-us/sql/relational-databases/… – Stijn Wynants Dec 21 '17 at 12:02
  • 1
    Well...my whole point is that I do not want to use a default filegroup at all. If I would use it I probably could get rid off all the extra filegroups and stick just with the default. – Martin Guth Dec 21 '17 at 12:06
  • I would think twice before implementing this. It's important that SQL Servers files grow at roughly the same rate. Are yours way different sizes? – James Dec 21 '17 at 13:24
7

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.
  • Sorry for the late reply...I was on holiday...nice solution...works perfectly for me – Martin Guth Jan 19 '18 at 14:52
  • I'd just like to point out that all this can be performed in SSMS with a nice and shiny GUI, but I thought I'd give you the basics. And I appreciate your feedback, thanks. – hot2use Jan 19 '18 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.