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, WAIT_STATS_CAPTURE_MODE = ON );
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.
