What are the System databases and it uses in SQL Server?

-

System data bases are defined by Microsoft and required for SQL server to function. These data bases include the Master, Model, MSDB, Temp DB, Resource, and Distribution databases, Which are utilized in replication, as well as the ReportServer and ReportServerTempDB databases for reporting services.

USES are:

1. Master database-

  • PURPOSE; stores metadata for  the SQL Server instance, including system configuration settings, login accounts, and database information.
  • SIGNIFICANCE; The master database is essential for SQL Server to start  and function; without it, the server wont operate. 
  • CONTENT; Contains information about linked servers, TCP endpoints, and master keys.

2. Model database-

  • PURPOSE; serves as the template for all new databases created  on the SQL server instance.
  • SIGNIFICANCE; configuring the model database  efficiently can help ensure consistent settings across newly created database.
  • CONTENT; contains default configurations  and objects that will will be present in any new database.

3. MSDB Database-

  • PURPOSE; Used by SQL server agent for  scheduling alerts and jobs, and stores information related to backups, database mail, and service broker.
  • SIGNIFICANCE; msdb is vital for managing scheduled tasks, alerts and backup/ restore operations.
  • CONTENT; Contains data on SQL Server agent jobs, backup and restore history, maintenance plans, and other system-related operations.

4.Tempdb Database-

  • PURPOSE; Stores temporary user objects like global and temporary tables, temporary stored procedures, table variables, cursors, and tables returned in table-valued functions.
  • SIGNIFICANCE; tempdb is used  for internal SQL server operations, including sorting, and is automatically recreated on server startup.
  •  CONTENT; Stores temporary tables ( Both local and global), table variables, temporary stored procedures, cursors, and other work tables.

























Comments

Post a Comment

Popular posts from this blog

SQL Server Service Account Password Rotation (Including SPN Updates and AAG Handling)

-
  Runbook: SQL Server Service Account Password Rotation (Including SPN Updates and AAG Handling) Environment Primary Server: ServerA Secondary Server: ServerB SQL Server: AlwaysOn Availability Group (AAG) with Automatic Failover Task Breakdown 1. Before Change Start Tasks ✅ 1.1 Raise Change Ticket Open a Change Request (CR) in ITSM Jira tool. Add details: SQL Service account password rotation. Update SPNs. Application validation. Attach: Impact Analysis Backout Plan 1.2 Get CAB Approval Submit CR for CAB review and approval. Get approvals from: CAB QA Application owners 1.3 Create New AD Service Account. Request AD team to create: Username: SQLService_ServerA Password: Strong and non-expiring. Permissions: Add to Domain Users Add to Local Administrators Group Command to Add Account to Local Admin Group: net localgroup administrators "DOMAIN\SQLService_ServerA" /add 1.4 Grant SQL Server Access Connect to SQL Server as sys...
Read more

How to use Azure Automatic performance tuning ?

-
  Automatic performance tuning in Azure SQL Database is a powerful feature that helps optimize query performance without manual intervention. It includes capabilities like automatic plan correction , index tuning , and query regression detection . Here's a step-by-step guide to enable and manage it, especially focused on query performance tuning : Step-by-Step: Enable Automatic Performance Tuning in Azure SQL Step 1: Open Azure Portal Go to https://portal.azure.com Sign in with your Azure account. Step 2: Go to Your SQL Database Navigate to "SQL databases" Click on the target database you want to enable tuning for. Step 3: Enable Automatic Tuning In the left-side menu, find and click on "Automatic tuning" You will see the following options: Force Last Good Plan – Automatically reverts to a known good query plan if performance regresses. Create Index – Azure creates recommended indexes automatically. Drop Index – Azure drops unused or dupl...
Read more

How to Upgrade SQL Server from 2014 to 2022, Apply the Latest Patches Post-Upgrade, and Install the Latest SSMS Tool?

-
Firstly, ensure that you have completed this on in a non-production environment. Assume infrastructure is set up for SQL Server mirroring: - Principal A - Primary - Mirror B - Secondary - Commit mode - Synchronous commit. change ticket. Before change steps: 1. Create a change ticket. 2. Conduct a peer review of the change ticket. 3. Obtain approval from the Change Advisory Board (CAB). 4. Get QA approval for application testing following the SQL Server version upgrade, installation of SSMS, and the latest cumulative update (CU) patch. 5. Download the latest patch executable from the Microsoft website and transfer the CU to both servers. 6. Move the existing SQL Server 2022 installation media to both servers. 7. Download the latest version of SSMS from the Microsoft website and transfer it to both servers. 8. Schedule stop of alerts for unnecessary tickets from the monitoring tool. During the change window steps: 1. Create backups of the system databases. 2. Take a snapshot of the opera...
Read more