SQL Server Database Architecture

Describe in brief Databases and SQL Server Databases Architecture.

Databases
- A database is a structured collection of data.
- Database can be thought as simple data file.
- It is more powerful than data file which stores data in organized way.
- Database organizes the data into a table. It is made up of several tables with rows and columns.

SQL Database Architecture
SQL database is divided into logical and physical components.

Logical Component
Logical components comprises of following:
Database objects,
Collation,
Login, Users, Roles and groups.

Physical component
Physically components exist as two or more files on disk.
Physically files can be of three types.

Primary data files
This is starting point of the database and contain pointer to point other files. Extension is mainly .mdf .

Secondary data files
It consists of data other than primary data file. Extension is mainly .ndf .

Log files
It contains log information which is used to recover database.

What are database files and filegroups?

Database files are used for mapping the database over some operating system files. Data and log information are separate.

SQL server database has three types of database files:
Primary: starting point of a database. It also points to other files in database. Extension: .mdf
Secondary: All data files except primary data file is a part of secondary files. Extension: .ndf
Log files: All log information used to recover database. Extension: .ldf

Describe in brief Databases and SQL Server Databases Architecture.

SQL Server consists of a set of various components which fulfill data storage and data analysis needs for enterprise applications. Database architecture: All the data is stored in databases which is organized into logical components visible to the end users. It’s only the administrator who needs to actually deal with the physical storage aspect of the databases, whereas users only deal with database tables.

Every SQL Server instance has primarily 4 system database i.e. master, model, tempdb and msdb. All other databases are user created databases as per their needs and requirements.

A single SQL Server instance is capable of handling thousands of users working on multiple databases.