What is System Databases? What are the use of System database? What is Master database & their usage

Microsoft has developed a relational database management system (RDBMS) which is MS SQL Server. MS SQL Server is designed & developed to manage large volumes of data which is suitable for small businesses to large organizations.

In MS SQL Server, two types of databases are there:

1. System Databases and

2. User Databases.

System Databases:

System databases are important databases & they are automatically created during MS SQL Server installation. System databases store server information.

There are four main system databases in MS SQL Server but 2 other DBs are also available:

Master Database:-

1. The master database is one of the system databases and keeps details of MS SQL Server instances, system-level information, metadata, logins, and configurations.

2. If the Maser database is not online or up. SQL server service will not start.

3. The Recovery Mode of the master database is Simple, hence we can’t take the T-Log backup of this database.

4. The master is the first database; hence the ID of this database is 1.

5. Never store the master database-related files on the C drive.

6. We can't add files or file groups.

7. We can't change the database owner of the master database from SA to any other.

8. We can't drop the master database.

9. We can't set the database to OFFLINE.

10. We can't set the database or primary file group to READ ONLY.

11. We can't enable CDC (Change Data Capture) on the master database.

12. The master database will not be a part of mirroring or log shipping.

Advantages of the Master Database:

Centralized Management: The Master Database acts as a hub for essential system-wide data, including logins, connected servers, and configuration settings. This centralization makes maintenance easier and guarantees uniformity throughout the SQL Server instance.

Metadata Repository: The Master Database serves as a repository for all other databases in the SQL Server instance's metadata. Database management is made simpler by the fact that it maintains track of the presence, size, and placement of data and log files.

System-Wide Configuration: The Master Database contains all of the configuration information for SQL Server, including server-level settings and database setup. Applying uniform settings across the whole SQL Server instance is made simpler as a result.

Backup and Restore Information: Information about backup and restoration operations: The Master Database contains details about database backups and restores. This contains specifics like the history of backups, device information, and database restore routes.

Resource Availability: For SQL Server to launch and run properly, the 

Master Database is required. SQL Server won't start if the Master Database is damaged, however, it can frequently be fixed with programs like "SQL Server Setup" or "Rebuild Master Utility."

Disadvantages of the Master Database:

Single Point of Failure: The Master Database is essential to the SQL Server's operation. The entire SQL Server instance may be affected if it becomes damaged or unavailable, which might result in downtime for all databases housed on that instance.

Complexity: Making changes directly to the Master Database might be dangerous since it holds crucial system information. To decrease the likelihood of problems, it is advised to adjust server-level settings using SQL Server Administration Studio or other administration tools.

Limited User Data Storage: User data cannot be stored in the Master Database. It may be difficult to execute maintenance or upgrade operations on SQL Server if you attempt to keep a significant volume of user data in this database.

Version Compatibility: The Master Database may need to be modified to provide room for new system tables or settings when conducting SQL Server upgrades or moving to a different version. Sometimes, this procedure is difficult and has to be carefully planned.

Security Risks: The Master Database has to be securely guarded against unauthorized access since it houses important system data. Giving this database the wrong permissions might cause security flaws.

Components of the Master Database:

The master database contains lots of important components like system tables, system views, system stored procedures, system functions, etc.

A few essential components of the master database are given:

System Tables: System tables store configuration and status-related information of the SQL Server instance. For example- sys. sysobjects, sys. sysprocesses, sys. configurations, sys. sysdevices, sys. sysindexes, sys. databases, and sys. syslogins.

System Views: This database also manages system views.  It provides an easy way to access system tables. For Example:- [sys].[certificates], [sys].[server_principals], [sys].[backup_devices], [sys].[server_role_members], and [sys].[server_permissions].

System Stored Procedures: This database also contains a few system-stored procedures that help administrators perform their. For example:-sp_who, sp_readerrorlog, sp_ sp_helpdb, sp_addlogin, and sp_configure.

Functions of the Master Database:

As specified earlier, this database is very critical & manages and administrates the SQL Server instance. A few important functionalities of the master database are given below:

1. Store Metadata

2. Manage Logins and Security

3. Store Configure of Server Settings

4. Recover from Disasters

5. Control Server Startup

Suggestions:

As you know, the master database is very critical so always take a backup of this database & store it at a safe location.

FAQs:-

Q. What is the master database in SQL Server?

Ans. The master database is one of the system databases and keeps details of MS SQL Server instances, system-level information, metadata, logins, and configurations.

Q. Why do we change the master database file location in an SQL Server?

Ans. If the data & log file are on the C drive, we must change the master database file location. As per best practice, data or log files shouldn't be on the C drive.

Also, keep the files on separate drives. 

Q. Is it possible to create new objects in the system database, especially in the master database?

Ans. Yes, we can create new objects like tables, SP, etc. in system databases.

Q. Is it possible to create a full-text catalog or full-text index in the master database?

Ans. No, it is not possible to create a full-text catalog or full-text index in the master database.

Q. Can we rename the master database?

Ans. No, We can't.

Q. Can we restore the master database in SQL Server?

Ans. Yes, we can.

Q. Can we shrink the master database in SQL Server?

Ans. Yes. we can shrink files as well as databases. But be careful with the Production database.

To Shrink File:- 

USE [master]

GO

DBCC SHRINKFILE (N'master', 4)

GO

To Shrink Log File:-

USE [master]

GO

DBCC SHRINKFILE (N'mastlog', 4, TRUNCATEONLY)

GO

To Shrink database:-

USE [master]

GO

DBCC SHRINKDATABASE(N'master' )

GO

Q. Can we delete a master database in SQL Server?

Ans. No, we can't. 

Q. What is stored in the master database SQL Server?

Ans. The master database stores details of MS SQL Server instance, system-level information, metadata, logins, and configurations.

Q. What is the use of the master database in SQL Server?

Ans. Uses of the Master database are as follows:-

i. Master database stores metadata and configuration information.

ii. Stores details of other databases which are available in the SQL instance.

iii. Also stores details of server-level configuration for example- network settings, server & DB level settings, memory settings, and backup-related settings.

Q. How to rebuild the master database in SQL Server?

Ans. We can rebuild the system database. Click here to learn all the steps.

Q. How to move the master database in SQL Server?

Ans. We can move the system database. Click here to learn all the steps. 

Q. What happens when the master db is down in SQL Server?

Ans. SQL Server will not start if the master database is down.

Related Articles:-

What are System Databases? What are the uses of a System database?

What is a master Database? What are the uses of a master database?

What is the TempDB database? What are the uses of the TempDB database?

What is the MSDB database? What are the uses of the MSDB database?

What is the Model database? What are the uses of the Model database?

What is a Resource database? What are the uses of the Resource database?

What is a Distribution database? What are the uses of a Distribution database?