04 - Overview of SQL Server

4.1 Introduction to SQL Server

SQL Server is a data engine introduced by Microsoft. It provides an environment used to create and manage databases. It allows secure and efficient storage. It provides other components and services that support the business intelligence platform to generate reports and help analyze the data.

4.2 SQL Server components

SQL Server contains a number of components. Each component is provided with the specific services and support to the clients connected to the server.

The following diagram shows the components of the SQL Server.

The server contains the following components:

a) Database Engine

b) Integration Services

c) Analysis Services

d) Reporting Services

a) Database Engine: The component provides support to store query, process and secure data on the server. It allows user to create and manage database objects. The following background services are provided by the engine.

Service Broker: It provides support for asynchronous communication between clients and the database server. The client sends a request to the server and continues to work. The broker ensures that the request is processed whenever the server is available.

Replication: It allows the user to copy and distribute data and database objects from one server to another. The servers can be located at remote locations to provide fast access to users.

Full Text search: It allows the user to implement fast and intelligent search in large databases. It allows searching records containing words and phrases.

Notification services: It allows generating and sending notification messages to the user or administrators about the event.

b) Integration Services: The service allows gathering and integrating varied data in a consistent format in a common database. The database is known as data warehouse. The warehouse contains integrated databases, text files or flat files.

c) Analysis Services: The warehouses are designed to facilitate reporting and analysis. The applications are widely using this data store for analytical purpose. The applications used for this purpose are known as BI applications.

d) Reporting Services: They provide support to generate complete reports on data in the database engine in the data warehouse. These services provide a set of tools that help in creating and managing reports in different formats.

4.3 SQL Server integration with the .NET Framework

The SQL Server is integrated with the .NET framework as shown in the following figure.

The .NET framework is an environment used to build, deploy, and execute business applications. The server used various services provided by the framework. The component uses the framework services to generate and send notification messages.

The .NET framework consists of the following components:

a) Development tools and languages

b) Base Class Library

c) Common Language Runtime

a) Development tools and language: They are used to create interface for Windows forms, Web forms, and console applications. They include Visual Studio and Visual C# developer. The languages that can be used are VB.NET, C#, and F#.

b) Base Class Library: The framework consists of the class library that acts as a base class for any .NET language such as VB.NET or C#. The library is object oriented.

c) Common Language Runtime: It is the most important component in the framework. It provides the following features:

1) Automatic memory management: It is used for allocating and de-allocating the memory of an application.

2) Standard type system: It provides the user with some common data types known as Common Type System (CTS).

3) Language Interoperability: It helps the user to create applications that can be used with many programming languages.

4) Platform Independence: It allows the code execution from the platform that is supported by the CLR.

4.4 Features of SQL Server

The features provided by the SQL Server are as mentioned below:

1) Scalability: It allows distributing data in the large tables into different file groups. The server can access the file groups simultaneously.

2) CLR integration: It allows user to use the CLR features of the .NET Framework into the server database.