SQL SERVER ARCHITECTURE
Sql server is having Physical database & logical database
On Physical Database contians file groups and Transcation
log
in file group Contains data files-36,767 TB
data file contains of tabel -
tabel containts extents
Extents contains Pages
pages Contains rows
Transcation log is Userdefined filegroup by default it's
primary file group
Logical - database objects like
view,Sp,functions,Triggers,Indexes and roles etc
buffer manager/buffer cache --> writes in a pages or memory [oracle shared pool/SGA]
log writer --> When a page is modified in the buffer cache, it is not immediately written back to disk; instead, the page is marked as dirty. This means that a page can have more than one logical write made before it is physically written to disk. [oracle
log writer]
db writer --> after writing the transaction log it will write in datafile [oracle db writer]
check point --> The checkpoint process periodically scans the buffer cache for buffers with pages from a specified database and writes all dirty pages to disk. [oracle check point]
Resource Governor use to session handling [oracle smon/RMAN]
The incoming connection request for a session is classified by a classifier UDF and is routed to an appropriate workload group. This workload group in turn uses the resource pool associated with it and finally the resource pool provides and limits on the resources
required by the session.
Process monitor --> sp_who2 will give the processes details and we can kill the processess [oracle pmon]
metadata --> stored in master database [oracle metadata]
memory --> extents --> pages --> bytes [oracle extents --> blocks --> bytes]
data files --> .mdf files [oracle .dbf files]
log files --> .ldf files [oracle redolog files .log]
filegroups --> logical structure it will assign to files or table [oracle tablespace]
server properties --> sql server properties contains the initial setting for the server and databases [oracle init.ora initial file]
see some high level differences between sql server and oracle
http://www.sql-server-performance.com/articles/dba/oracle_sql_server_comparison_p2.aspx
http://www.sql-server-performance.com/articles/dba/oracle_sql_server_comparison_ii_p3.aspx
some good sites for sqlserver architecture
http://msdn.microsoft.com/en-us/library/cc280361.aspx
http://msdn.microsoft.com/en-us/library/aa214422(SQL.80).aspx
http://technet.microsoft.com/en-us/library/aa226174(SQL.70).aspx
http://msdn.microsoft.com/en-us/library/ms179355.aspx
http://www.dotnetheaven.com/UploadFile/prvn_131971/arch02252005164321PM/arch.aspx
http://www.irfanrizvi.com/2010/03/database-architecture-with-sql-server_28.html
