SQL DBA SCHOOL

Founder Abubeker Refaw

SQL Server overview 

Exactly what is SQL Server 2008 and 2012 or later version. When you first install the product, what are all the pieces you get, what do they do, and which of them do you need? At its core, SQL Server 2008 is an enterprise-class database management system (DBMS) that is capable of running anything from a personal database only a few megabytes in size on a handheld Windows Mobile device up to a multiserver database system managing terabytes of information. However, SQL Server 2008 is much more than just a database engine. 


The SQL Server product is made up of a number of different components. This page describes each of the pieces that make up the SQL Server product and what role each plays. Each of these topics is dealt with in more detail later in the website. 


SQL Server Components and Features
The main component of SQL Server 2008 and 2012 or later version is the Database Engine. Before you can use the other components and features of SQL Server 2008, which are discussed in the following sections, you need to have an instance of the Database Engine installed.


The SQL Server Database Engine

The Database Engine is the core application service in the SQL Server package for storing, processing, and securing data with SQL Server 2008. The SQL Server 2008 Database Engine is a Windows service that can be used to store and process data in a relational format, as XML documents, and new for 2008, as spatial data. The following are the main responsibilities of the Database Engine: .


  • Provide reliable storage for data .
  • Provide a means to rapidly retrieve this data .
  • Provide consistent access to the data .
  • Control access to the data through security .
  • Enforce data integrity rules to ensure that the data is reliable and consistent.  Each of these responsibilities is examined in greater detail in later chapters in this website. For now, this chapter provides just a brief overview on each of these points to show how Microsoft SQL Server fulfills these core responsibilities.


Reliable Storage

Reliable storage starts at the hardware level. This isn’t the responsibility of the Database Engine, but it’s a necessary part of a well-built database. Although you can put an entire SQL database on a single IDE or SATA drive (or even burn a read-only copy on a CD), it is preferable to maintain the data on RAID arrays. The most common RAID arrays can survive hardware failures at the disk level without loss of data.


Using whatever hardware you have decided to make available, the Database Engine manages all the data structures necessary to ensure reliable storage of your data. Rows of data are stored in pages, and each page is 8KB in size. Eight pages make up an extent, and the Database Engine keeps track of which extents are allocated to which tables and indexes.


A page is an 8KB chunk of a data file, the smallest unit of storage available in the database. An extent is a collection of eight 8KB pages.

Another key feature the Database Engine provides to ensure reliable storage is the transaction log. The transaction log makes a record of every change that is made to the database. It is not strictly true that the transaction log records all changes to the database; some exceptions exist. Operations on binary large objects—data of type image and text— can be excepted from logging, and bulk copy loads into tables can be minimally logged to get the fastest possible performance.


Rapid Data Access

SQL Server allows the creation of indexes, enabling fast access to data. Another way to provide rapid access to data is to keep frequently accessed data in memory. Excess memory for a SQL Server instance is used as a data cache. When pages are requested from the database, the SQL Server Database Engine checks to see if the requested pages are already in the cache. If they are not, it reads them off the disk and stores them in the data cache. If there is no space available in the data cache, the least recently accessed pages (that is, those that haven’t been accessed in a while since they were read into memory) are flushed out of the data cache to make room for the newly requested pages. If the pages being flushed contain changes that haven’t been written out yet, they are written to disk before being flushed from memory. Otherwise, they are simply discarded.


Consistent Data Access

Getting to your data quickly doesn’t mean much if the information you receive is inaccurate. SQL Server follows a set of rules to ensure that the data you receive from queries is consistent. The general idea with consistent data access is to allow only one client at a time to change the data and to prevent others from reading data from the database while it is undergoing changes. Data and transactional consistency are maintained in SQL Server by using transactional locking. Transactional consistency has several levels of conformance, each of which provides a trade-off between accuracy of the data and concurrency.


Access Control

SQL Server controls access by providing security at multiple levels. Security is enforced at the server, database, schema, and object levels. Server-level access is enforced either by using a SQL Server username and password or through integrated network security, which uses the client’s network login credentials to establish identity. we will see this in  detail on topic “Security and User Administration"


Data Integrity

Some databases have to serve the needs of more than a single application. A corporate database that contains valuable information might have a dozen different departments wanting to access portions of the database for different needs. In this kind of environment, it is impractical to expect the developers of each application to agree on an identical set of standards for maintaining data integrity. For example, one department might allow phone numbers to have extensions, whereas another department may not need that capability. One department might find it critical to maintain a relationship between a customer record and a salesperson record, whereas another might care only about the customer information. The best way to keep everybody sane in this environment—and to ensure that the data stays consistent and usable by everyone—is to enforce a set of data integrity rules within the database itself. This is accomplished through data integrity constraints and other data integrity mechanisms, such as triggers. We will discuss more on the topic of  “Implementing Data Integrity,” for details.

Online learning

​fREE