Note:
Making it clear to anyone reading this but this post is about SQL Server even though I start off talking a bit about PostgreSQL.
…I know, weird right?…
Back Story:
I have a PostgreSQL instance on my home laptop that I haven’t used yet.
I intend to start using it soon as a sort of hobby as I feel that there are things to be learned about databases from it. Like comparing features available to PostgreSQL that are not in SQL Server and vice-versa or the different ways the same tasks are accomplished in both platforms.
However SQL Server is the platform used in my work, I still have so much to learn with it (especially with 2016 coming out!!!), and I just find it so damn interesting so I haven’t touched PostgreSQL yet.
All that being said, I have signed up to few newsletters from PostgreSQL (General, Novice, etc) and they are fascinating.
Unfamiliar words like pglogical and rsync are combined with known words like publisher and subscriber and the community itself is so vast and supportive that it rivals the #SQLFamily (from what I’ve seen and yes. I am being biased to SQL Server 🙂 ).
Problem:
One of those newsletters was regarding a problem a user was having with creating databases.
When he would create a new database it was not empty as he expected but was filled with user tables, logins, etc.
What was going on?
Explanation:
The explanation was pretty much what you would expect, just called by a different name.
He had basically written to his Model database (called template1 in PostgreSQL) sometime ago without realising it.
PostgreSQL has the following syntax with creating databases:
PostgreSQL:
CREATE DATABASE DatabaseName WITH TEMPLATE TemplateName
The new database settings are created from whatever template is specified using the WITH TEMPLATE syntax (defaults to template1 apparently).
This works the same as SQL Server, the new databases inheriting the settings from the Model system database, but in our case it is implicit. There is no call for WITH TEMPLATE Model.
This is perfectly valid syntax.
SQL Server:
CREATE DATABASE DatabaseName
The only difference that I can tell at the moment is that PostgreSQL can have multiple different templates while SQL Server has just one; Model.
Is this restriction on database templates a good thing or a limitation? Personally I go with the former but you may feel differently.
…Multiple Models?…
Take Aways:
This brought me back to the system databases and there was something that I realised.
A lot of new users, and I was included in this list not too long ago, do not think about the system databases.
I’m not sure I can fault them as well as it’s probably not a priority. There is so much to learn with regard to DDL statements, DML statements, Deadlocking, General T-SQL, etc, that the system databases are just a little folder under Databases that does not get opened.
However, and I can’t stress these enough, these are important!
- Master: Logon accounts, sys info, etc
- MSDB: backup history, restore history, job history etc,
- Tempdb: temp tables, cursors, ordering, etc
- Model: new databases, creation of tempdb
And that is just scratching the surface!
Take care of these databases, do not limit yourself to looking after just the user databases.
They are not the only databases that need to be backed-up and they are not the only databases that can experience corruption.
Parting Gift:
I’m hoping that you believe me with this but, unfortunately, the best lessons are learned.
You should have a destructive sandbox SQL Server, (NOT PRODUCTION!!!), hopefully a little laptop at home to call your own; something that nooby else would mind you destroying basically.
Choose a system database, anyone will do; delete that database, drop it, whatever you want just make it unavailable and see how far you can get using SQL Server.
…Hell isn’t it?…
Now imagine that happened unexpectantly and unwanted on a Monday morning because you weren’t taking care of your system databases.
Take care of your System Databases.