Just a day ago, while I was surfing Wikipedia about SQL Server, I came across this generic architecture image. I found it interesting. Click on image to view it in large size.
The physical structure of the database is divided into the MDF and LDF. The part of MDF contains file group, data files, tables and indexes, extended and page. The LDF file contains a transaction log file. The physical architecture is about how the data is actually stored in the file system. Page, extend, database files are physical architecture.
The logical architecture is about how the data are logically grouped and presented to the end user. It contains tables, constraint, stored procedure, function and other logical architecture.
The page is the smallest unit of stored in SQL Server with the size of 8 KB. There are two kinds of extended and each of the extended contains 8 adjacent pages. There are two types of extended uniform extended and mixed extend. The new table or index is allocated pages usually form mixed extents. Once the table or index overgrows the size of 64 KB or 8 pages, it will change its storage from mixed extended to uniform extent.
Reference: Pinal Dave (https://blog.sqlauthority.com), Wikipedia – SQL Server
20 Comments. Leave new
Hi Pinal,
It seems like in all the SQL Server DBA interviews they ask can you explain the SQL Server Architecture. Dose that mean they are expecting to explain the above architecture is full i mean each component ??
Many Thanks,
Arun
hi pinal ,
just give me explanation about architecture diagram..
Hi Pinal,
Need to know more about the architecture from you.
Regards
Manish
Hi Pinal,
Could you briefly explain the above diagram.
Regards
Ramesh
I required explanation because i want it in my project
Hi Pinal Dave,
please give me explanation about each topic in above diagram.
Hi Pinal Dave,
please give me explanation about each topic in above diagram.
From
Tirumala
Hi Pinal,
Would request you to provide the brief explanation of the above SQL server architecture diagram.
Thanks & Regards,
Sarika
Hi Pinal,
I attended your today’s (Teched) session in Pune, and it was a very nice experience to be there. I learned so many new thing.
Thanks a lot for all your efforts for the community.
And I am looking forward to attend your upcoming session in coming future.
and once more I would like to request you to please write some more about about the above mentioned SQL Server architecture.
Thanks
Manish
Hi Pinal
i am working as a sql server developer.
u r blog will helps and give more stuff who are intrested to know about database.
how funny u see even i dodnt know sql server architecture
but i am working as a sql server developer
please give me explanation for the above architecture
Thanks Pinal thanks for u r blog
u konw u r the hero for our people, like me most of the people
we admire u
Hi Pinal,
Please give me the explaination of architecture diagram…
Thanks
shall we install sql server in linux
@Pavan, this is one of the drawback of Sql server. i.e., it doesn’t support any other OS except Windows Versions.
Hey Pavan,
No I believe, you can’t install SQL Server in linux.
Thanks
Manish
Vishwa
even i was asked in interview how query fetches data from Database engine
plz explain the above diagram..
For the folks who are interested in the ‘explanation’ of the above diagram, the diagram is straight (the only difference is above is colorful) from Kalen Delaney’s book “inside Microsoft SQL Server 2000” Chapter 3.1. Looks for the complete explanation there.
great prashant,
Pinal you have done great job .