SQL SERVER – Generic Architecture Image

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.

SQL SERVER - Generic Architecture Image SQL_Server_s

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

, , , ,
Previous Post
SQL SERVER – FIX : Error : 3702 Cannot drop database because it is currently in use.
Next Post
SQLAuthority News – Microsoft SQL Server 2008 Community Technology Preview (November 2007) VHD

Related Posts

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

    Reply
  • hi pinal ,
    just give me explanation about architecture diagram..

    Reply
  • Hi Pinal,

    Need to know more about the architecture from you.

    Regards
    Manish

    Reply
  • Hi Pinal,

    Could you briefly explain the above diagram.

    Regards
    Ramesh

    Reply
  • I required explanation because i want it in my project

    Reply
  • Hi Pinal Dave,
    please give me explanation about each topic in above diagram.

    Reply
  • Hi Pinal Dave,
    please give me explanation about each topic in above diagram.

    From

    Tirumala

    Reply
  • Sarika Salunkhe
    April 26, 2011 12:34 pm

    Hi Pinal,

    Would request you to provide the brief explanation of the above SQL server architecture diagram.

    Thanks & Regards,
    Sarika

    Reply
  • 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

    Reply
  • 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

    Reply
  • u konw u r the hero for our people, like me most of the people

    we admire u

    Reply
  • Hi Pinal,

    Please give me the explaination of architecture diagram…

    Thanks

    Reply
  • shall we install sql server in linux

    Reply
    • @Pavan, this is one of the drawback of Sql server. i.e., it doesn’t support any other OS except Windows Versions.

      Reply
  • Hey Pavan,

    No I believe, you can’t install SQL Server in linux.

    Thanks
    Manish

    Reply
  • Vishwa

    Reply
  • even i was asked in interview how query fetches data from Database engine

    Reply
  • plz explain the above diagram..

    Reply
  • Prashant Kumar
    May 29, 2012 9:48 pm

    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.

    Reply
  • great prashant,
    Pinal you have done great job .

    Reply

Leave a Reply

Menu