BRIEF OF SQL SERVER ARCHITECTURE

 

We can define SQL server architecture comprises of three layers as follows

1.      Query processor (Shown at the top)
2.      Storage engine   (Shown in the middle)
3.      SQL OS                   (Shown at the bottom)

1.      Query Processor:  As the name justifies this layer determines that how does query going to be execute and the best optimized way to execute it. It communicates with middle layer(Storage engine) to access and manipulate data. Following are the different task of query processor.

1a. Parser

                  1b. Optimizer
                    1c. SQL Manager
                    1d. Database Manager
                    1e. Query Executor

 

2.      Storage engine: This engine is responsible for storage and retrieval of data on hard        drive. SQL server needs to keep it data somewhere physically on the drive for anytime data use. SQL server populates data basically in files one is MDF( metadata file) and another is LDF (Log data File). These datafile and logfile further store data into more logical granular unit are called Pages . Pages which stores data called datapages.

 

                  1 PAGE = 8KB

1 EXTEND= 8 PAGES = 64 KB

 

Each page begins with a 96-byte header that is used to store system information about the page. This information includes the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page.

The following table shows the page types used in the data files of a SQL Server database.

Page type	Contents
Data	Data rows with all data, except text, ntext, image, nvarchar(max), varchar(max), varbinary(max), and xml data, when text in row is set to ON.
Index	Index entries.
Text/Image	Large object data types: text, ntext, image, nvarchar(max), varchar(max), varbinary(max), and xml data Variable length columns when the data row exceeds 8 KB: varchar, nvarchar, varbinary, and sql_variant
Global Allocation Map, Shared Global Allocation Map	Information about whether extents are allocated.
Page Free Space	Information about page allocation and free space available on pages.
Index Allocation Map	Information about extents used by a table or index per allocation unit.
Bulk Changed Map	Information about extents modified by bulk operations since the last BACKUP LOG statement per allocation unit.
Differential Changed Map	Information about extents that have changed since the last BACKUP DATABASE statement per allocation unit.
Note: Log files do not contain pages; they contain a series of log records.

 

3.      SQL OS: This is the powerful OS which is responsible for all kind of activities to database management. It has Monitors, writers ,Memory manager, lock manager, buffer pool, synchronization services and IO.

 

Introduction to SQL server and how it is differ from other RDBMS

Welcome to SQL world !!!

MSSQL is a RDBMS developed by microsoft and is a software product whose primary function is to store and retrieve data as requested by other software applications

SQL Server is not just a database, but is instead a complete database platform consisting of numerous services and hundreds of capabilities which can be avail by giving extra cost in other RDBMS.

SQL Server was originally the Microsoft rebadged version of the Sybase relational database. It is only available on Windows platforms. Current version (Mar 2012) is SQL Server 2012.

SQL Server has optional components which come at no extra cost (if hosted on same machine):

· SQL Server Reporting Services – Business Intelligence reporting, Microsoft‟ alternative to OBIEE, Discoverer, Crystal Reports. Has very good integration with Microsoft client products such as Excel which is a significant for end-user usability.

· Analysis Services – OLAP (on line analytical processing), cubes, data mining.

· SQL Server Integration Services – ETL (extract transform load) and export/import tools. Is equivalent to export/import, data pump, sql loader, DBA shell scripts. This was called DTS (Data Transformation Services) in older versions.

SQL servers comes with different editions for different audience and workloads. Following are the versions available with their release date.

Version History: Name	Number	Release Date
Sybase	-	-
SQL Server 6.5	6.5	1996
SQL Server 7	7.0	1998
SQL Server 2000	8.0	2000
SQL Server 2005	9.0	2005
SQL Server 2008	10.0	2008
SQL Server 2008 Release 2	10.5	2010
SQL server 2012	11.0	2012

Following are the 10 quick differences between SQL and oracle.

1. SQL server can have many databases in one instance while oracle can have only one database per instance
2. Oracle memory structure completely defined with in SGA while SQL server memory may go beyond maximum memory thresholld set
3. In oracle we have tablelspaces while in SQL we have file groups
4. Oracle support RAC but SQL server doesn't have real application cluster capability
5. All objects associated with database lies with in the oracled database while in SQL associated objects like linked server, logins, jobs etc lies outside the database on instance
6. Oracle have logical export/import with much more control capabilities like flat file dumps,tablespace remap,schema remap etc but SQL export/import can be done through wizard and is limited to funtionalities
7. Tables can be reorganised in Oracle but not in SQL (other way possible like cluster index rebuilt/reorganise)
8. SQL server came with many freeware services like SSRS,SSIS,SSAS but in oracle they came in seperate packges(selectively)
9. In Oracle access can be granted only on object levels while in SQL server this can be done on schema level
10. Redo and Undo segments/files are seperate in Oracle but in SQL it lies in same transaction log file