Posts

Demystifying System Databases

, ,

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 here.  The 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.

/0 Comments/by

Where is my Availability Group Primary Replica?

In SQL Server 2012 we got this new high availability feature called availability groups. With readable secondaries under the covers, it can be harder to figure out the answer to the following two questions. When did the availability group failover? Where did the availability group go when the failover occurred? The goal of this blog post is to help simplify answering these questions.

Availability Group Extended Event

One of the things we really like about Availability Groups is that there is a built-in extended event named “ALwaysOn_health” that runs and captures troubleshooting information. We took a look at the extended event and noticed that there are several error numbers that were included in the filter for this extended event. This is shown below as we scripted out the default extended event for a quick review.


CREATE EVENT SESSION [AlwaysOn_health] ON SERVER
ADD EVENT sqlserver.alwayson_ddl_executed,
ADD EVENT sqlserver.availability_group_lease_expired,
ADD EVENT sqlserver.availability_replica_automatic_failover_validation,
ADD EVENT sqlserver.availability_replica_manager_state_change,
ADD EVENT sqlserver.availability_replica_state_change,
ADD EVENT sqlserver.error_reported(
WHERE ([error_number]=(9691) OR [error_number]=(35204) OR [error_number]=(9693) OR [error_number]=(26024) OR [error_number]=(28047)
OR [error_number]=(26023) OR [error_number]=(9692) OR [error_number]=(28034) OR [error_number]=(28036) OR [error_number]=(28048)
OR [error_number]=(28080) OR [error_number]=(28091) OR [error_number]=(26022) OR [error_number]=(9642) OR [error_number]=(35201)
OR [error_number]=(35202) OR [error_number]=(35206) OR [error_number]=(35207) OR [error_number]=(26069) OR [error_number]=(26070)
OR [error_number]>(41047) AND [error_number]<(41056) OR [error_number]=(41142) OR [error_number]=(41144) OR [error_number]=(1480)
OR [error_number]=(823) OR [error_number]=(824) OR [error_number]=(829) OR [error_number]=(35264) OR [error_number]=(35265))),
ADD EVENT sqlserver.lock_redo_blocked
ADD TARGET package0.event_file(SET filename=N'AlwaysOn_health.xel',max_file_size=(5),max_rollover_files=(4))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

SQL Server Availability Groups got us interested in learning why these specific errors are included in the extended event session explicitly created for managing Availability Groups. Knowing that the descriptions for messages are in the sys.messages table, we did a little digging.

System Messages

Taking the error numbers from the AlwaysON_health extended event we were able to build the following query to get the description of the errors included in the extended event.


SELECT *
FROM sys.messages m where language_id = 1033 -- English
--AND m.message_id =1480
AND ([message_id]=(9691) OR [message_id]=(35204) OR [message_id]=(9693) OR [message_id]=(26024) OR [message_id]=(28047)
OR [message_id]=(26023) OR [message_id]=(9692) OR [message_id]=(28034) OR [message_id]=(28036) OR [message_id]=(28048)
OR [message_id]=(28080) OR [message_id]=(28091) OR [message_id]=(26022) OR [message_id]=(9642) OR [message_id]=(35201)
OR [message_id]=(35202) OR [message_id]=(35206) OR [message_id]=(35207) OR [message_id]=(26069) OR [message_id]=(26070)
OR [message_id]>(41047) AND [message_id]<(41056) OR [message_id]=(41142) OR [message_id]=(41144) OR [message_id]=(1480)
OR [message_id]=(823) OR [message_id]=(824) OR [message_id]=(829) OR [message_id]=(35264) OR [message_id]=(35265))
ORDER BY Message_id

Now we will focus on one particular error message. The error message 1480. Looking at the description below you will see that every time a database included in an availability group or database mirroring changes its role this error occurs.

The %S_MSG database “%.*ls” is changing roles from “%ls” to “%ls” because the mirroring session or availability group failed over due to %S_MSG. This is an informational message only. No user action is required.

When did my AlwaysOn Availability Group Failover?

By now it should not be a big surprise to see how you can figure out when your availability group failed over. To answer this question, we are going to filter the “AwaysOn_health” extended event for error_number 1480.

The “AlwaysOn_health” extended event target is to a text file, and by default, it will utilize the default log folder for SQL Server. Keep in mind, that by default the target does a rollover four times when a file reaches 5 MB for a total of 20 MB. If you continually have failovers occur data will get purged.

The server used for this blog post my path is “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\” if this is not your path you will need to modify line 2 in the script below.


;WITH cte_HADR AS (SELECT object_name, CONVERT(XML, event_data) AS data
FROM sys.fn_xe_file_target_read_file('C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\AlwaysOn*.xel', null, null, null)
WHERE object_name = 'error_reported'
)
SELECT data.value('(/event/@timestamp)[1]','datetime') AS [timestamp],
data.value('(/event/data[@name=''error_number''])[1]','int') AS [error_number],
data.value('(/event/data[@name=''message''])[1]','varchar(max)') AS [message]
FROM cte_HADR
WHERE data.value('(/event/data[@name=''error_number''])[1]','int') = 1480

Below you will see an example of the result set which shows my last failover.

AG Failover

You could also utilize the Extended Event GUI to watch data. We will skip that today as we would recommend using T-SQL so you can find failovers in multiple Availability Groups on different servers. We will go into more detail about this process a little later in the blog post.

How Do We Become Proactive?

If you want an action to occur when a database inside an availability group changes roles to be proactive, you can configure an SQL Agent Alert. An SQL Agent alert can perform actions like sending an email to your DBA team or running another SQL Agent job to complete your required task.

The following shows you how to configure this alert via the SSMS user interface.

AG Alert

How Do We Report failovers across the Enterprise?

Central Management Server (CMS) is your best friend for building reports to show Availability Group failovers across the enterprise. You can create a CMS group for your SQL instances in your Availability Group and copy and paste the query above to detect Availability Group failovers.

NOTE: This assumes you have an standard install process that keeps the default log path the same across your SQL Server instances. We strongly encourage that you have an automated SQL Install process that keeps using the same path for all your installs but we will keep that blog post for another day.

Need more info on Building Your First Availability Group?

Join us for pre-conference training brought to us by SQL Saturday Nashville! John Sterrett will be presenting a half day precon in Nashville, TN on Thursday, January 11, 2018, from 1:00 pm to 4:30 pm!

In this half-day session, you will learn how to build your first availability group while also learning how availability groups work with other components like active directory, storage, and DNS. You will walk away with a checklist to help your future deployments while also learning how to implement, monitor, troubleshoot and use availability groups.

Space is limited so act fast and Register here now!

Nashville Availability Group Training

Nashville Availability Group Training

/0 Comments/by

Building Your First Always On Availability Group

,

Have you wanted to build an availability group but didn’t know where to start? 
If so, we have discounted SQL Server Availability Group training for you!

Nashville, TN learn how to implement and monitor Always On Availability Group Solutions!

Nashville, TN learn how to implement and monitor Always On Availability Group Solutions!

Join us for pre-conference training brought to us by SQL Saturday Nashville! John Sterrett will be presenting a half day precon in Mufreesboro, TN on Thursday, January 11, 2018, from 1:00 pm to 4:30 pm!

SQL Server Availability Group Training in Nashville, TN

In this half-day session, you will learn how to build your first availability group while also learning how availability groups work with other components like active directory, storage, and DNS. You will walk away with a checklist to help your future deployments while also learning how to implement, monitor, troubleshoot and use availability groups.

In this session we will cover:

  • Understanding the difference between Availability Groups and Failover Cluster Instances
  • Configure Windows Failover Cluster Service (WFCS)
  • Understanding Quorum in WFCS
  • Pre-staging Active Directory Objects
  • Learn how Availability Groups use DNS
  • Build Availability Groups
  • Implementing Planned Downtime Failovers
  • Troubleshoot Common Availability Group Problems
  • Proactive monitoring Availability Groups
  • Backups for your Availability Groups Databases
  • Managing Connectivity
  • Handling SQL Agent Jobs
  • Making SQL Server Reporting Services Highly Available Utilizing Availability Groups

Space is limited so act fast and Register here now!

Come for the SQL Server Availability Group Pre-con and stay for the full day of free SQL training on Saturday!

SQLSaturday is a free training event for Microsoft Data Platform professionals and those wanting to learn about SQL Server, Business Intelligence, and Analytics. Join us on Jan 13, 2018, at Middle Tennessee State University (MTSU), 1301 East Main Street, Murfreesboro, Nashville, Tennessee, 37132.

Nashville Availability Group Training

Nashville SQL Peeps get Your Learn On!

/1 Comment/by