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

);

What Other Things Can You Do?

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.

About Monica Rathbun

Monica Rathbun lives in Virginia, is a Microsoft MVP for Data Platform and Microsoft Certified Solutions Expert. She has nearly two decades of experience working with a wide variety of database platforms with a focus on SQL Server and the Microsoft Data Platform. She is a frequent speaker at IT industry conferences on topics including performance tuning and configuration management. She is the Leader of the Hampton Roads SQL Server User Group. She is passionate about SQL Server and the SQL Server community, doing anything she can to give back. Monica can always be found on Twitter (@sqlespresso) handing out helpful tips. You can find Monica blogging at sqlespresso.com 
Bookmark the permalink.

5 Comments

  1. Hey SQLExpresso,

    Some great ideas here that I will try & implement. 🙂

    I tried to run the script on my own local instance and getting an error at:
    WAIT_STATS_CAPTURE_MODE = ON line.

    Otherwise – good work!

    Dan

  2. Pingback: Configuring The Model Database – Curated SQL

  3. Good advice!!!

    Thanks Monica,
    Thomas

  4. we have been ‘auto-magically’ adding 2 database roles to the model database when initially installing and configuring a SQL Server:

    – exec stored procedure
    – view definition

    when will Microsoft get around to this ?
    we’ve been doing this since ‘roles’ became available !

Comments are closed