Difference between Data and Log file

Data and Log file

In a database management system, data files & log files both are used for different purposes.

The structure of the data file & log file is also different.

Data files: Data files are physical files that are located on a disk or on the network to share & keep actual data.

Data files contain tables, indexes, views, stored procedures, and other objects. 

A database may have one or more than one data file.

A database may have one or more than one filegroup.

A file group is a collection of one or more than one data file. 

Internally, the data file contains multiple pages.

As per Microsoft(MS), place data files & log files on separate drives to get better performance.

The type of data files is given below:

1) Primary Data Files:-

The system creates this type of file for a database.MDF extension.

2) Secondary Data Files:-

This type of file is optional & is also used to store data. 

We can create/add more than one secondary data file in a database that has.NDF extension.

Log Files: Although they have a distinct structure, log files are still physical files.

The log file has the.LDF file extension.

The history of every transaction that has been carried out for the database is kept in the log file.

records all inserts, updates, deletions, backups, schema modifications, etc. 

Moreover, it guarantees data consistency and offers a means of recovering from system errors.

Transaction history is sequentially stored in the log file.

A database can have several log files added to it.

 

What is Distribution database? What are use of Distribution database?

Distribution database:-

The distribution database is another system database in SQL Server.

It is very useful in the replication of objects.

The utilization of this database is to store metadata of replication & information of distribution agents & subscribers.

This database contains details of the article, meta-data of replication, and data.

What is Resource database? What are use of Resource database?

Resource database:-

The 5th system database in SQL Server is the resource database. This database is hidden from users and is read-only.

Resource databases, which are used to offer a collection of system objects that are consistent across all editions of SQL Server, were first introduced with SQL Server 2005.

Here are some important points about it:

Purpose: The read-only Resource database serves as a storage space for the system objects and metadata that the SQL Server instance needs. It keeps track of system objects like functions, stored procedures, and system catalog views.

Hidden Database: Resource databases are concealed and not immediately accessible by users or administrators, in contrast to user databases. The SQL Server engine is intended to be used internally.

Single Instance: Each SQL Server instance only has one Resource database, which is shared by all the databases that are hosted by that instance.

Patches and Upgrades: The Resource database is modified to update system objects during the installation of a service pack or a SQL Server upgrade. This aids in maintaining compatibility between the installed version and the system catalog.

Version-specific: The Resource database only supports that particular version. A new Resource database is built to correspond with the system objects of the new version whenever an SQL Server instance is updated.

Recovery Model: Because the Resource database is designed to be read-only and does not require transaction log backups, it employs the Simple recovery model.

Security: The Resource database includes stringent security controls since it is necessary for the SQL Server instance to run. It is only accessible to the SQL Server service account and highly privileged administrators.

Avoid Modifications: Playing around with the Resource database might cause serious problems and possibly ruin the database. Any direct modifications to this database must be avoided.

System Object Access: When resolving system object references in queries, the SQL Server engine checks the Resource database. It aids in effectively retrieving data about system items.

File Location: The Resource database files (MDF and LDF) are kept in the 'MSSQLBinn' folder of the SQL Server installation directory.

By default, the location of resource database files are <drive>:\Program Files\Microsoft SQL Server\MSSQL1.<instance_name>\Binn\

The resource database's ID is 32767.

The T-SQL command cannot be used to back up and restore this database.