Recently I have heard a lot of people discussing SQL Server System Databases.  The topic of system databases seems to be a deceptively basic one.  But how many people truly take the time to understand what system databases are and what purpose they serve?  Come along with me and let’s explore system databases.

What are System Databases and what do they do?

System Databases are needed for your SQL Server to operate.  These include Master, Model, MSDB, Resource, and TempDB.  For Azure SQL Database, only Master and TembDB apply.

  • Master – The Master Database records all the system-level information for and instance of SQL Server. This information includes logon accounts, linked servers, and system configuration settings.  The Master also records the existence of all other databases and the location of those files, and records the initialization information for SQL Server.  This means that SQL Server CANNOT START if the Master database is unavailable.  Think of this like the master key to your SQL Server door.
  • Model – The Model Database is used as the template for all databases created on the instance.  Modifications can be made to the Model DB that will be applied to all databases created after the Model DB has been altered.  These changes include database size, collation, and recovery model.  A full list of options that can/cannot be modified on a Model DB for SQL Server 2016 is available hereThe list for SQL Server  2014 Model DB options is located here. And for SQL Server 2012 the options are here.
  • MSDB – The MSDB database is used by SQL Server Agent for scheduling alerts and jobs. It is also used by Service Broker, Database Mail, SSIS, data collector, and policy based management.   SQL Server maintains a complete history of all online backups and restores within the tables in MSDB.  This history includes the name of the person or program that performed the backup, the time of the backup, and the drives of files where the backup is stored.  SQL Server Management Studio then uses this information to propose a plan for restoring a database and applying any transaction log backups.
  • Resource –  The Resource database is a read-only database that contains all the system objects that are included with SQL Server.  The Resource database does not contain user data or metadata.  Since it is a read-only database you will not see it listed on your instance as the other databases in the photo above.  
  • TempDB – The TempDB Database is a database that is available to all users connected to the instance of SQL Server.  It is used to hold objects that are created by users such as temporary tables and indexes, temporary stored procedures, table variables, and cursors.  It also stores objects that are created internally such as work tables, work files, and sort results for operations such as creating or rebuilding indexes.  Think of  TempDB like the “junk drawer” in your home.  Each item is needed at specific times, then is thrown into the drawer to sit a while.  More items are thrown in the drawer.  Everyone throws items in the drawer.  Eventually the drawer becomes too full and it begins to spill out.  No one ever wants to clean out the junk drawer, and eventually you need a bigger drawer.
    • Restrictions – Despite all of the operations that can be performed on the TempDB, the following are operations that CANNOT:
        • Adding filegroups.
        • Backing up or restoring the database.
        • Changing collation. The default collation is the server collation.
        • Creating a database snapshot.
        • Dropping the database.
        • Dropping the guest user from the database.
        • Enabling change data capture.
        • Participating in database mirroring.
        • Removing the primary filegroup, primary data file, or log file.
        • Renaming the database or primary filegroup.
        • Running DBCC CHECKALLOC.
        • Running DBCC CHECKCATALOG.
        • Setting the database to OFFLINE.
      • Setting the database or primary filegroup to READ_ONLY.

You must backup your TempDB!  True or False?

In a previous blog, I discussed that the Most Important Role of a SQL Server DBA  is the ability to understand and perform backups and recovery.  I went on to discuss backups in A Beginner’s Guide to SQL Server Backups as well as Recovery Models.   Despite the importance of backups and recovery, the only database that cannot be backed up or restored is TempDB!  Why can it not be backed up or restored? TempDB is recreated each time the server is restarted, so any temporary objects like tables, indexes, etc., are cleared automatically.  As seen here, backup nor recovery are even an option!

Should System Databases be backed up?

       Before I answer this right off, ask yourself these questions: “Do I want to lose access to all other databases on the instance?  What would happen if I lose the model my company demands for the specific way every new database must be created?  Would anyone notice if I had to restore after a disaster and no one had correct passwords to the database?”  That shiver that just ran up your spine is your answer.  Absolutely YES, the system databases (Master, MSDB, and Model) should have backups!  

It is recommended that the Master be backed up as often as necessary to protect the data: a weekly backup with additional backups after substantial updates is highly recommended.  If for some reason the Master becomes unusable, restoring from the backup is the best way to get up and running.  Go here for information on Restoring the Master. If you do not have a valid backup of the Master, rebuilding the Master is the only option.  You can click here to find more information about what it takes to Rebuild System Databases.

     Best practices recommend creating full backups of the Model Database, and doing so only when necessary for your business needs.  It is a small database that rarely sees changes; however, it is important to make sure it is backed up especially immediately after customizing its database options.  Microsoft also recommends to perform backups on the MSDB database whenever it is updated.  

Feeling like this might be too much trouble?  As with any other backup, these too can be automated by using a SQL Agent job!   There is absolutely no reason NOT to back up your system databases as often as you backup your regular databases.  If you feel otherwise, might I suggest you keep an updated resume close at hand.

Which recovery model should be used?

This brings us down to recovery modes.  As a default the Master and MSDB are set to Simple recovery model, the Model is user configurable; however best practices recommends setting MSDB to Full recovery model, especially if the backup and restore history tables are used. 

*Note that if you change the recovery model to Full, transaction log backups will need to be performed as well.  You don’t want your Master or MSDB logs to become full and risk losing all your data!

Pop quiz:  How often should you back up your TempDB?
(a) once a month
(b) weekly
(c) daily
(d) yearly on February 30th

Answer:  Trick question!  TempDB cannot be backed up.

SQLSatHou 2018

We are back this year!  Not only are we sponsoring SQL Saturday Houston, we are also speaking!  All three of us are presenting!  Go here to see the full schedule.

What is SQL Saturday?

SQL Saturday is a free training event for Microsoft Data Platform professionals and those wanting to learn about SQL Server, Business Intelligence, and Analytics.  SQL Saturday Houston will be held on June 23, 2018 at San Jacinto College – South Campus, 13735 Beamer Road, Houston, Texas  77089.  Check-in and breakfast starts at 7:30am.  The first sessions begin at 8:30 am.  There are sessions for beginners, intermediate, and advanced levels.  Topics covered at this SQL Saturday are:

  • Powershell
  • Application & Database Development
  • BI Platform Architecture, Development & Administration
  • Cloud Application Development & Deployment
  • Enterprise Database Administration & Deployment
  • Professional Development
  • Strategy & Architecture

Remember, this is a FREE event, but only a few spots remain!  Don’t wait, click here to register!

Where will we be?

We will each be at the Procure SQL booth with smiling faces, fun giveaways, and answers to your SQL Server questions!  Please stop by and say hello.  If not at the booth, you can find us attending a session or giving one of our own!

Angela will start out the day at 9:45 am in room 117.  She will be presenting her professional development session “Becoming the MVP: Soft Skills for the Hard Market.”  In this interactive, round-table discussion, Angela explores how soft skills are important at all levels of a person’s career.  The importance of soft skills in the job market, specific skills, and how to hone them will be top priority.  She has been known to give away prizes, so make sure to say hello!

Jay comes in next at 11:00 am in room 149.  Jay’s presentation is “Linux for SQL Server” and is a high-level overview of the differences and similarities between Linux and Windows for those who haven’t been exposed or may need a refresher.  Don’t be mistaken, even though this session is a high-level overview, it is fantastic for beginners!  Jay will introduce the Linux version of Windows commands used on a daily basis for administering SQL Server. Next, he will explore updating Linux, updating SQL Server, moving files between Windows and Linux, and backing up and restoring databases from one system to another. He will round out the session by taking a look at default file locations for SQL Server and what can be moved and how to accomplish that.

John is waking up the afternoon crowd at 1:30 in room 113.  He is presenting “Automate the Pain Away with Query Store and Automatic Tuning” which is an intermediate level presentation which explains how execution plans get invalidated and why data skew could be the root cause of seeing different execution plans for the same query. He will further explore options for forcing a query to use a particular execution plan. Finally, he will discuss how this complex problem can be identified and resolved simply using new features in SQL Server 2016 and SQL Server 2017 called Query Store and Automatic Tuning.  You won’t want to miss out on that!

What happens after the sessions are done?

Stick around after the last sessions because at 5:00 we all gather together for final remarks and sponsor raffles!  We will be giving away a new Super Nintendo SNES GiveawayEntertainment System Classic Edition!  To enter, just drop your raffle ticket in the bucket at our booth. 

But wait, there’s more!

The fun doesn’t stop here.  We leave from the event to an after party which is being held at Main Event, 1125 Magnolia Ave., Webster, Texas 77598.  Party starts at 6!  The after parties are a great way to unwind, network,  and chat up the speakers and new SQL friends you made during the sessions!

John Sterrett shares his thoughts on successfully transitioning to an entry-level IT job.

Recently, I worked with  AITP to share some tips that helped me successfully transition from a Computer Engineering student at the University of Oklahoma to an entry-level information technology professional. Once, I completed the interview I noticed that my advice was more of a secret sauce to being a successful informational technology professional than just a how-to guide to getting that first entry-level information technology job.   Therefore, I wanted to share with everyone.

To see the words of wisdom four tips to successfully enter the IT workforce or continue your successful IT career check out the following AITP article.  If you are not a member, I highly recommend joining AITP. AITP has been a game changer for my IT career.