It’s all just data.


April 17, 2019 by Kenneth Fisher

Tables, stored procedures, views, logins, constraints, etc, etc. It’s all just data. In every database system I’ve ever worked on there are system tables underneath everything that contain 99% of all of the metadata. (Metadata, in this case, is the data describing the databases and their contents as well as system information.)

In fact, in some older systems, you could even edit that data. Can you imagine being able to change the width of a column buy updating a row in a system table? It was never a good idea (oh the fun ways we broke things) and these days I don’t see that ability anymore. For that matter, in SQL Server, you can’t even see the system tables except under very very specific circumstances.

You might be asking “Why does this matter?”

Quick example. Server level principals (logins) are stored in the master database. Database level principals (users) are stored in each individual database (including master). If I take a database and restore it to a new instance can the database level permissions change? Only if I deliberately change them. They are part of the database so are restored with the database. Will the server level principals be there? Only if I create them.

If you think about where the metadata is stored, then you will have fewer questions when things move around.

Another example. Jobs are stored in msdb, along with job permissions, job history etc. If I create a new instance and restore all of my user databases to it, but none of the system databases, will I have my agent jobs? Of course not. Will my users have their job permissions? Nope, not those either.

There are lots of examples that trip people up. Particularly when moving between instances. Restores, AGs, replication and the like. Next time you are confused about why something didn’t move from one place to another think about where it’s stored and what you actually moved.

2 thoughts on “It’s all just data.

  1. Brent Ozar says:

    Trace flags are another classic gotcha example of something that looks like it’s in metadata, but not quite exactly. Curses!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,746 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: