Quick Model Database Tidbit

Are you using your Model Database to its full potential?

I am finding more and more that Database Admins are not using the Model database to its fullest potential and some not at all.

What is that Model Database for?

The model database is basically the default setup (template) for all other databases created on a SQL Server instance. All databases created after install will inherit the properties of this database.

Why Configure It?

Using the model can insure consistency within your environment and is a quick way to automate your database setups. Below is a list of things I’ve used in my environments and others.

Top (in no particular order) Settings I have Implemented Through Model

• Default Growth Settings
• Query Store Settings
• Recovery Models
• Read Committed Snapshot Isolation
• Allow Snapshot Isolation
• Auto Update Statistics Asynchronously
• Compatibility Levels

Now there are some things that databases will NOT inherit from the model, some of these I learned the hard way.

• File Groups
• CDC (Change Data Capture)
• Collations
• Database Owner
• Encryption

Scripts to turn these options on

USE [master]

GO
ALTER DATABASE [model] SET COMPATIBILITY_LEVEL = 110
GO
ALTER DATABASE [model] SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT
GO
ALTER DATABASE [model] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT
GO
ALTER DATABASE [model] SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE [model] MODIFY FILE ( NAME = N'modeldev', FILEGROWTH = 1024000KB )
GO
ALTER DATABASE [model] MODIFY FILE ( NAME = N'modellog', FILEGROWTH = 1024000KB )
GO
ALTER DATABASE [model] SET ALLOW_SNAPSHOT_ISOLATION ON
GO

ALTER DATABASE model

SET QUERY_STORE ( 

    OPERATION_MODE = READ_WRITE, 

    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), 

    DATA_FLUSH_INTERVAL_SECONDS = 3000, 

    MAX_STORAGE_SIZE_MB = 500, 

    INTERVAL_LENGTH_MINUTES = 15, 

    SIZE_BASED_CLEANUP_MODE = AUTO, 

    QUERY_CAPTURE_MODE = AUTO, 

    MAX_PLANS_PER_QUERY = 1000

);

Now, you can go above and beyond just the database properties. You can add tables, views, triggers, functions etc. to your model database and every time a new database is created those objects will also exist. Why is this useful? In the past, I’ve used this for tracking my DDL (data definition language) changes. I created a trigger that would insert into a table the user, object, date and time, text snippet of any ALTER\DROP\CREATE statement that was run on a database. For it to work, the trigger needed to exist on all databases.

Final Words

We all know each environment is different, so don’t just go and implement everything, tailor it to your needs. I suggest you take a look at yours and see if there is anything you can adjust. You may be surprised on what you can tweak.

Note:

*In testing this, I have found that if you create a new database using CREATE DATABASE with T-SQL the Auto-Growth sizes do not get inherited by new database, but everything else did. If I create new database using GUI these setting do propagate.  Not sure if this is by design or a bug.