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?

database

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.

  1. USE [master]  
  2.   
  3. GO  
  4. ALTER DATABASE [model] SET COMPATIBILITY_LEVEL = 110  
  5. GO  
  6. ALTER DATABASE [model] SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT  
  7. GO  
  8. ALTER DATABASE [model] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT  
  9. GO  
  10. ALTER DATABASE [model] SET RECOVERY SIMPLE WITH NO_WAIT  
  11. GO  
  12. ALTER DATABASE [model] MODIFY FILE ( NAME = N'modeldev', FILEGROWTH = 1024000KB )  
  13. GO  
  14. ALTER DATABASE [model] MODIFY FILE ( NAME = N'modellog', FILEGROWTH = 1024000KB )  
  15. GO  
  16. ALTER DATABASE [model] SET ALLOW_SNAPSHOT_ISOLATION ON  
  17. GO  
  18.   
  19. ALTER DATABASE model  
  20.   
  21. SET QUERY_STORE (   
  22.   
  23.     OPERATION_MODE = READ_WRITE,   
  24.     CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),   
  25.     DATA_FLUSH_INTERVAL_SECONDS = 3000,   
  26.     MAX_STORAGE_SIZE_MB = 500,   
  27.     INTERVAL_LENGTH_MINUTES = 15,   
  28.     SIZE_BASED_CLEANUP_MODE = AUTO,   
  29.     QUERY_CAPTURE_MODE = AUTO,   
  30.     MAX_PLANS_PER_QUERY = 1000  
  31.   
  32. );  

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 statements that were run on a database. For it to work, the trigger needed to exist on all databases.

Final Words

We all know that 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 at 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.

X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now