System databases for SQL Server

System databases for SQL Server


This is yet another article  regarding SQL Server system databases Management. In last articles of the series, I talk about the tempdb database, the master database and the msdb database.



This article focuses on the model database, the last this series about SQL Server system databases. In each SQL Server instance you will search at least the next system databases:

Usage Model

SQL Server uses the model database as a sample to create new databases.

Creating user objects in the model database is possible, but remember that after doing that every new database that will be created will have those objects as well, meaning that new databases inherit everything (to be accurate, mostly everything as you will see forward) from the model database.

Note: tempdb is also created using the model database as demo so keep in mind that any object created in the model database it will exist in the tempdb on the next time that SQL Server instance restarts since tempdb is recreated every time the SQL Server service starts.
How to Customize

When performing a configuration change in the model database it will affect the new databases created after the configuration change so, as a DBA, is always a good practice to change some of the default settings of the model database to ensure that new databases will follow a desired customization policy.

For example, the default Recovery Model for this system database is Full. If you change it to Simple, the next database that you will create will be created with the Simple Recovery Model. This is a good change to perform on non-Production SQL Server instances when you do not need to restore to a certain point in time.

Besides the above example of changing the Recovery Model you can also configure the data and transaction log files to make new databases created with a minimum file sizes and with an auto growth value that guarantee a minimal impact of the large number of Virtual Log Files (VLF).

These configuration customisations will be only used as default values for the CREATE DATABASE command. You can always overwrite them by providing a different value (the tempdb database is always created with Simple Recovery Model no matter the Recovery Model set in the model database).

The following is an example of how to create a new user database in SQL Server 2016 by overwriting the default values existing in the model database for this SQL Server version. In this example, I am providing an initial file size of 64MB instead of the default 8MB, with 128MB for the file auto growth instead of the default 64MB and then set the database to a Simple Recovery Model instead of the default Full Recovery Model:
Note: One of the configuration changes that do not affect the new databases is the Read-Only status. You can set the model database to a Read-Only nut the new databases will be always created without this status, i.e. with Read-Only = False.
Using Operations
Apply Permissions

Regular users by default, cannot access the model database. If they need to access to the model database, keep in mind that the user will be also have the same permissions on any new databases that will be created since the CREATE DATABASE will use the model database as a template.

How Backups work

Usually changes in the model database only occur when a customization is performed. When any of these changes occurs, it is also recommended to perform a backup of the model database.

Alternatively, you can have regular backups of the model database as part of the regular system databases backup plan.
Moving file locations

Model data and transaction log files can be moved to another location if and when needed to. The following code is an example on how to move the default data and transaction log files into a new location. In this case I have defined the folder S:SysDB:
After executing the above command, stop the respective SQL Server instance service and copy the model default data and transaction log files into the new folder location. When the copy finishes successfully, the respective SQL Server service can be started. After that verify if there are no issues with the database.
Applied Restrictions

The model database has restrictions. I will explain some of them.
Drop the database

The model database cannot be dropped. If you try to drop it an error will be raised informing you that a system database cannot be dropped:

Set offline

Setting the model database offline is not allowed. If you try to do it the related error will be raised:

Renaming Database

Renaming the model database is not possible. When trying to do it the respective error will be raised informing you that action is not allowed:

Change database ownership

Changing the owner of the model database is not possible. When trying to do it the respective error will be raised informing you that action is not allowed:

Change Data Capture (CDC)

Enabling the CDC feature on the model database is not possible. Trying to do it the respective error will be raised informing you that action is not supported:

Add filegroup

It is not possible to add a filegroup for the model database name. If you try to do it you will receive an error stating that adding a filegroup is not allowed on the database:

Add file

It is not possible to add a file for the model database name. If you try to do it you will receive the error stating that cannot have files added to the database:

Adding additional restrictions

There are some other restrictions that are worth to be known:

        The model database’s primary file group cannot be set to READ_ONLY status (but the database can);



        The model database does not allow to rename the primary file group;



        The guest user, the primary file group and the primary data and primary log files of model database cannot be deleted;



        The default collation for the model database is the SQL Server instance collation and cannot be changed;



        The model database cannot be part of a database mirroring solution.