SQL Server Overview

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)

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.

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.

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.

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).

Conclusion

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