The Database Design Resource Center

SQL Server Overview

SQL Server overview: This series of articles will describe and provide guidance on the installation process of SQL Server. We will begin by providing a brief overview of the product and what it consists of. Most of the topics raised here will be discussed at length in the articles that follow this SQL Server overview.
SQL Server is a client / server Relational Database Management System (RDBMS) which has been developed by Microsoft and is made up several different client and server programs that make up the entire product.

SQL Server Database Engine (Server)

SQL Server's database engine is the primary server application of the SQL sever package. Its main functions are:

  • Provide reliable storage
  • Rows of data are stored as pages, each 8kb in size.
  • Provide a means to rapidly access the data; this is done through utilizing indexes both clustered and non-clustered to search for data, which removes the need for all data to be scanned from the database tables.
  • Ensure consistent access to the data; Consistent access basically means only allowing one client to modify/changing the data at anyone time
  • Implement security; Microsoft SQL Server has multiple security levels Server Level, database level and database object level. Access to the server can be controlled by a Username or password or through Windows security in LAN/Networked environment.
  • Enforce data integrity; ensure the data stays consistent.

SQL Service Manager (Server)

The Service Manager is a small applet that allows easy controls of the SQL Services on the SQL Server:

  • SQL Server
  • SQL Agent
  • SQL Search, a full text search engine
  • Distributed Transaction Coordinator
  • OLAP Server; a separate service used for warehousing

SQL Server Agent (Server)

SQL Server Agent

In this SQL Server overview, The SQL Server Agent is a scheduler tool used to manage the timely execution of jobs and scripts. These can be used for any purpose, but one of the main uses is the scheduling of maintenance tasks such as Nightly backups that are better (due to performance) to be run out of hours or off peak times.

SQL Profiler

The SQL Profiler monitors activity on the select SQL Server and can be used for many tasks including performance monitoring (how efficiently queries are running) and real time auditing of the SQL Server overview.

Data Transformation Services (Client)

Data Transformation Services (DTS) is a useful tool that allows data to be moved from one data source to another. Its simple GUI interface is the DTS Designer which can be accessed through Enterprise Manager and allows the tasks to be developed quickly; on the fly.

Data Transformation Services

SQL Server Enterprise Manager (Client)

SQL Server Enterprise Manager (EM) is one of the client tools supplied with SQL Server that provides users with a GUI interface to manage all SQL Servers installed within your environment and also allows you to manage the databases that reside on the servers. This is discussed in more detail in a later article.

SQL Server Enterprise Manager (Client)

Query Analyser

Microsoft’s Query Analyser allows you to write and run your own Transact SQL statements. It allows you to parse your code before running it to check for syntax errors and also provides some useful colour co-ordination that changes the colour of key words etc.

Query Analyser

Replication (Server)

Replication is one of the more advanced features available in SQL Server and although not covered in this series of articles is worth mentioning at this point. SQL Server Replication allows data to be synchronised between two or more databases on different SQL Servers or even other platforms as long as they accessible by ODBC or OLEDB

Microsoft Full Text Searching Services

This service provides full text searching capabilities and is useful for searching large text fields.

SQL Server Analysis Services

Is an essential service when implementing a data warehouse solution using SQL Server, these Service are often referred to Online Analytical Processing (OLAP).


In this SQL Server overview, we have been looking at the main components of a SQL Server database, and briefly discussed the role of each. The next articles will look at the installation and configuration of these in further detail and provide advice on potential problems and guidance on optimal configuration.

Return to installing the SQL Server database

Exclusive interviews with:
Steven Feuerstein, PLSQL expert
Donald Burleson, Top IT consultant

Free eBook

Subscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:

What visitors say...

"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.

I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."

Mike, USA

Read more Testimonials

Database Normalization eBook:

Database Normalization eBook

Copyright © /
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: does not warrant any company, product, service or any content contained herein.

Return to top

Copyright acknowledgement note:

The name Oracle is a trademark of Oracle Corporation.
The names MS Access/MS SQL Server are trademarks of Microsoft Corporation.
Any other names used on this website may be trademarks of their respective owners, which I fully respect.