SQL Server model database and custom filegroups

As I wrote in the “Designing databases for rapid resilience” article, it is a good idea to keep the user data away from the PRIMARY filegroup.

When installing a new system which relies on several databases, it comes to mind to automate somehow the creation of additional filegroups and setting one of them as a default.

One way to do it could be to alter the model database and thus to modify the newly created databases when they are created.

The problem is, however, that the model database does not support custom filegroups.

TITLE: Microsoft SQL Server Management Studio
------------------------------
Alter failed for Database 'model'.  (Microsoft.SqlServer.Smo)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
User-defined filegroups are not allowed on "model". (Microsoft SQL Server, Error: 1826)

So, we end up in a trap of a poor design once again.

 

Comments are closed.