SQL Server System databases

 

Types of system databases

Introduction

Each time you install any SQL Server instance on a server, by default/automatically 4 primary system databases are created.

  • master
  • model
  • msdb
  • tempdb

  

Master database

It is a critical database, because SQL Server uses this database to store all the information about SQL Server instance, like

·        File location of the user database -> If you create any user database all the information and configuration details

·        Login accounts

·        Server configuration setting

·        Link Servers information

·        Start-up stored procedures

If for any reason, master database is not accessible or doesn’t exist or can not be read then the SQL Server instance can not start.

It’s important to backup the master database whenever there is a configuration change, logins creation and etc.

Model database

SQL Server uses the model database as a template to create new user defined databases.

Note:

Creating a user’ objects in the model database is not suggested/recommended unless there is a business requirement, because every new database that will be created will have those objects as well, that means new user defined databases get everything from the model database.

Additionally, tempdb is also created by using the model database as a template every time the server restarts. So, any object created in template database will exists in temp database also.

Msdb database

This database is another critical database, It is also known as “the SQL server Agent database” because it stores information about all the SQL Agent jobs like their configuration details and execution history.

Functionality of the msdb database:

·        Support SQL Server Agent

·        History for SQL Server Agent

·        Database Mail

·        Service Broker

·        Backup and restore history for the databases

It is required to backup whenever any changes at the SQL Server Agent level and other changes.

Temp database

It is one of important system database, the name itself indicates it is used to store temporary user objects, like temporary tables that are defined by the users using prefix of # or ## then those are created in the tempdb database.

·        Tempdb is a shared resource and used by all the SQL Server database users  

·        Tempdb is used for temporary objects, worktables, online index operations, cursors and many other

·        It is recreated every time the server is restarted

·        As this is a temporary storage (non-permanent) so no option or it’s doesn’t allow to backup the database.

·        It’s a workforce for SQL Server databases

Below image shows that no backup option available when we select “Task” option in the tempdb:



Resource database:

 It is a read-only database that contains all the system objects that are included with SQL Server. The system objects like sys.objects, are physically stored in the resource objects, but they are logically listed in the sys schema of every database.

The resource database doesn’t contain user data or metadata information.

Benefit of Resource database:

In earlier versions of SQL Server, upgrading requires dropping and creating system objects. Now, it’s does not require to drop and recreate the system objects as the resource database file contains all system objects so the upgrade can be accomplished by just copying the single resource database file to the local server. This way the resource database makes upgrading to a new version of SQL Server an easier and faster procedure.

sys object’s location:

 

Resource database location:



Comments

Post a Comment

Popular posts from this blog

Auditing in Oracle database

Unified Auditing: Oracle Database 12c Release 1 introduces a new auditing feature called Unified Auditing (12.1). Oracle Unified Auditing modifies the database's fundamental auditing capabilities. In earlier versions of Oracle, each component has its own audit trail. With unified auditing, all auditing is compiled into a single repository and view. Due to the fact that all audit data is now in a single location and a single format, there is a double simplification as a result. This data is made available in a consistent format in the UNIFIED AUDIT TRAIL data dictionary view, which is made possible by the unified audit trail, which is stored in a read-only table in the AUDSYS schema in the SYSAUX tablespace.  DBAs can build audit rules and then assign them to various users using Oracle 12c Unified Auditing, which has the same advantages as giving users varying system capabilities based on their responsibilities. The unified_audit_trail database will contain all of the audit records....
Read more

rs.stepDown() in MongoDB replication

It instructs the primary of the replica set to become a secondary. After the primary steps down,the eligible secondaries will hold an election for primary. Generally, the rs.stepDown() does not immediately step down the primary. If no electable secondaries are up to date with the primary, the primary waits up to secondaryCatchUpPeriodSecs (by default 10 seconds)  for a secondary to catch up. Once an electable secondary is available, the method steps down the priamry.  Once stepped down, the original primary becomes a secondary and is ineligible from becoming primary again for the remainder of time specified by stepDownSecs. The rs.stepDown() method attempts to terminate long-running user's operations that block the primary from stepping down, such as an index build, a write operation, or a map-reduce job. Syntax: rs.stepDown(stepDownSecs, secondaryCatchUpPeriodSecs)
Read more

Tuples in Python

 Tuples Basically Tuples in Python are very similar to Lists, but, Tuples are immutable which means that they can not be modified/changed. We generally use Tuples to make sure the values are not changed like, dates, Year, and days of the week or dates on a Calendar.  Creating a Tuple: >>> my_t1=(1,2,3) >>> my_t2=('one','two','three') >>> my_t1 (1, 2, 3) >>> my_t2 ('one', 'two', 'three') -- check the length of tupple: >>> len(my_t1) 3 >>> len(my_t2) 3 -- Tuples can have mixed objects as like Lists. >>> my_t3=(1,'two',3,'four') >>> my_t3 (1, 'two', 3, 'four') -- indexing with tuples  >>> my_t3=(1,'two',3,'four') >>> my_t3[2] 3 Immutable Tuples We can not modify the value. Here is the example: >>> my_t3=(1,'two',3,'four') >>> my_t3[2]=4 Traceback (most recent call last):  ...
Read more