SQL Server Architecture

1
SQL Server Architecture
2
Objective

• Relational Database Components
• Database Architecture
• Logical Database Components
• System Databases and Data
• Physical Database Architecture
• Physical Database Files and Filegroups
• Transaction Log Architecture

3
Introduction

• In 1980s Microsoft Sybase teamed up to create
  SQL Server.
• After release of version 6.5, they both
  separated.
• It is SQL compliant RDBMS. ( Uses ANSI version
  of SQL)
• It supports the Client Server Model.

4
Relational features of SQL Server

• Information representation.
• Unique definition of rows.
• Systematic treatment of NULL values.
• Guaranteed access
• High level Update, Insert and Delete.
• Security
• Login authentication
• Permissions validation on user database.

5
Relational Database Components

• The database component of Microsoft SQL Server
  2000 is a Structured Query Language (SQL)based,
  scalable, relational database with integrated
  Extensible Markup Language (XML) support for
  Internet applications.
• Each of the following terms describes a
  fundamental part of the architecture of the SQL
  Server 2000 database component

6
Database

• A database is a storage place for data. The
  database does not present information directly to
  a user the user runs an application that
  accesses data from the database and presents it
  to the user in an understandable format.
• A database typically has two main parts first,
  the files holding the physical database and
  second, the database management system (DBMS)
  software that applications use to access data.

7

• The DBMS is responsible for enforcing the
  database structure, including
• Maintaining relationships between data in the
  database.
• Ensuring that data is stored correctly, and that
  the rules defining data relationships are not
  violated.
• Recovering all data to a point of known
  consistency in case of system failures.

8
Scalable

• SQL Server 2000 supports having a wide range of
  users access it at the same time. An instance of
  SQL Server 2000 includes the files that make up a
  set of databases and a copy of the DBMS software.
  
• Applications running on separate computers use a
  SQL Server 2000 communications component to
  transmit commands over a network to the SQL
  Server 2000 instance.

9
Structured Query Language

• To work with data in a database, you have to use
  a set of commands and statements (language)
  defined by the DBMS software the most common is
  SQL.
• The American National Standards Institute (ANSI)
  and the International Standards Organization
  (ISO) define software standards, including
  standards for the SQL language.

10

• The dialect of SQL supported by Microsoft SQL
  Server is called Transact-SQL (T-SQL). T-SQL is
  the primary language used by Microsoft SQL Server
  applications.
• Elements of Transact SQL
• DDL
• DML
• DCL
• TCL

11
Extensible Markup Language

• Although most SQL statements return their results
  in a relational, or tabular, result set, the SQL
  Server 2000 database component supports a FOR XML
  clause that returns results as an XML document.
  XML documents can be added to SQL Server
  databases, and the OPENXML clause can be used to
  expose data from an XML document as a relational
  result set.

12
Database Architecture

• Microsoft SQL Server 2000 data is stored in
  databases. The data in a database is organized
  into the logical components visible to users. A
  database is also physically implemented as two or
  more files on disk.
• When using a database, you work primarily with
  the logical components such as tables, views,
  procedures, and users. The physical
  implementation of files is largely transparent.

13

• Each instance of SQL Server has four system
  databases (master, model, tempdb, and msdb) and
  one or more user databases.
• It is not necessary to run multiple copies of the
  SQL Server database engine to allow multiple
  users to access the databases on a server.
• An instance of the SQL Server Standard or
  Enterprise Edition is capable of handling
  thousands of users working in multiple databases
  at the same time.

14
Logical Database Components

• The data in a Microsoft SQL Server 2000 database
  is organized into several different objects.
• Tables
• Constraints
• Indexes
• Defaults
• Triggers, Stored procedures, Views
• Keys
• User-defined functions
• User-defined data types

15
Physical Database Architecture

• The fundamental unit of data storage in Microsoft
  SQL Server is the page. In SQL Server 2000, the
  page size is 8 KB.
• The start of each page is a 96-byte header used
  to store system information, such as the type of
  page, the amount of free space on the page, and
  the object ID of the object owning the page

16
Continued..

• Data pages contain all the data in data rows
  except text, ntext, and image data, which is
  stored in separate pages.
• Data rows are placed serially on the page
  starting immediately after the header.
• A row offset table starts at the end of the page.
  The row offset table contains one entry for each
  row on the page and each entry records how far
  the first byte of the row is from the start of
  the page.

17

• Rows cannot span pages in SQL Server. In SQL
  Server 2000, the maximum amount of data contained
  in a single row is 8060 bytes, not including
  text, ntext, and image data
• Extents are the basic unit in which space is
  allocated to tables and indexes. An extent is 8
  contiguous pages, or 64 KB.

18
Physical Database Files and Filegroups

• SQL Server 2000 databases have three types of
  files
• Primary data files
• This file contains all of the system tables and
  other system objects. Also contains links to all
  secondary data files log files.
• Microsoft recommends that most databases will
  work very well with only a single primary data
  file log file.
• The recommended file name extension for primary
  data files is .mdf.

19

• Secondary data files
• Secondary data files comprise all of the data
  files other than the primary data file. Some
  databases may not have any secondary data files,
  while others have multiple secondary data files.
• Used to spread data over multiple disks.
• The recommended file name extension for secondary
  data files is .ndf.

20

• Log files
• Log files hold all of the log information used to
  recover the database. There must be at least one
  log file for each database, although there can be
  more than one.
• If the server fails because of a power outage or
  other problem, SQL Server automatically recovers
  the database by applying committed transactions.
• The recommended file name extension for log
  files is .ldf.

21

• SQL Server 2000 files have two names
• logical_file_name is a name used to refer to the
  file in all Transact-SQL statements.
• The logical file name must conform to the rules
  for SQL Server identifiers and must be unique to
  the database.
• os_file_name is the name of the physical file.
• It must follow the rules for Microsoft Windows NT
  or Microsoft Windows 98, and Microsoft Windows 95
  file names.

22
Database Filegroups

• Database files can be grouped together in
  filegroups for allocation and administration
  purposes.
• Some systems can improve their performance by
  controlling the placement of data and indexes
  onto specific disk drives.
• The administrator can create filegroups for each
  disk drive, then assign specific tables, indexes,
  or the text, ntext, or image data from a table,
  to specific filegroups.

23

• There are two types of filegroups
• Primary
• The primary filegroup contains the primary data
  file and any other files not specifically
  assigned to another filegroup. All pages for the
  system tables are allocated in the primary
  filegroup.
• User-defined
• User-defined filegroups are any filegroups
  specified using the FILEGROUP keyword in a CREATE
  DATABASE or ALTER DATABASE statement.

24

• One filegroup in each database operates as the
  default filegroup. When SQL Server allocates a
  page to a table or index for which no filegroup
  was specified when they were created, the pages
  are allocated from the default filegroup.
• Only one filegroup at a time can be the default
  filegroup. If no default filegroup is specified,
  the primary filegroup is the default filegroup.

25
The Syntax

• CREATE DATABASE database_name ON PRIMARY
  ( ltfilespecgt ) LOG ON
  ( ltfilespec .. )

26
Creating Database

• CREATE DATABASE MyDB
• ON PRIMARY
• ( NAME'MyDB_Primary',
• FILE NAME 'c\data\MyDB_Prm.mdf',
• SIZE4,
• MAXSIZE10,
• FILEGROWTH1)

27

• LOG ON
• ( NAME'MyDB_log',
• FILE NAME 'c\data\MyDB.ldf',
• SIZE1,
• MAXSIZE10,
• FILEGROWTH1)