SQL Server is a RDBMS (Relational Database Management System) software developed and owned by Microsoft. This software supports ANSI SQL which is the standard SQL language. Transact-SQL or T-SQL is Microsoft’s proprietary extension to SQL. Today, we walk you through the SQL Server architecture and the various services of this powerful and popular RDBMS platform. You may want to take this introductory course to Microsoft SQL Servers before we go ahead.
SQL Server Editions
SQL Server has several editions that have different features and pricing designed to meet the different user needs.
- Enterprise– this includes add-on services and can manage databases with sizes up to 524 petabytes.
- Standard-It supports fewer active instances as compared to the enterprise edition. It does not allow memory to be added when the server is running.
- Express– This is a scale down and free edition of SQL Server. It can use maximum one processor and has 1 GB memory and 10 GB database files.
You can learn more about SQL Servers with this course. Let’s take a look at the important functionality of this important RDBMS.
Functionalities of SQL Server
- Data Storage
It supports storing large databases, which are collections of tables with columns and rows. SQL server supports many different data types such as integer, float, varchar and more. Along with tables a database can contain views stored procedures, indexes and constraints.
- Buffer Management
The buffer manager manages the buffer cache. SQL Server, buffers pages in RAM for the sake of efficiency. The group of all pages currently buffered is termed the buffer.
- Concurrency and Locking
SQL Server enables concurrent access to the same database with various measures for control. There are two modes of concurrency control. They are pessimistic and optimistic concurrency. In the pessimistic mode, concurrent access is provided via locks. you can either have shared locks or exclusive locks. Exclusive locks give only the user access to the data. No other user can access the data. IN the case of shared locks, multiple users can read the data. But no one user has an exclusive lock. Locks can be applied on entire tables or even rows of a table.
- Data Retrieval
Here T-SQL is used to retrieve data from this database. One of the major components used for data retrieval is the cost based query optimizer. Its goal is to optimize the resources in order to execute a particular query. Stored procedure is a prominent SQL server feature. Its benefits include, reduced network traffic and improved performance.
SQl Server Service Components
SQL Server has various service components. Some services are linked directly to various SQL server components while others function as external processes. The main components/services are as follows
1. Relational Engine
The relational engine has a list of services which can be started, stopped, paused or disabled. There are exists two major services in relational engine. They are the SQL Server service and SQL Server Agent.
- SQL Server services
This service takes care of high level items such as databases, security, replication and more. Here we have two types of databases. They are system databases and user defined databases. System databases help to manage the SQL Server. The user-defined databases are built by DBAs and are useful for business applications.
- SQL Server Agent
It is responsible to schedule jobs for executing operations at specific times. Also it will notify operators in case of specific errors, job failures or business criteria.
The other key components of the Relational Engine of SQL Server are :
- Backup and Recovery
- Full Text Search
- Service Broker
- Database mirroring
2. SQL Server Integration Services
This is responsible to perform data extraction, transformation and loading (ETL). This service is managed with in the Business Intelligence Development Studio. The latter is a visual tool to help manage code, error handling, change management and more. The SQL Server agent can execute or schedule the integration services package.
3. SQL Server Reporting Services
This service provides powerful features for report authoring, rendering and management. Often, this is installed on a separate SQL server in order to exclusively take care of reporting needs. Reports are generated as RDL files which can be rendered in a wide variety of formats. Some of these include Microsoft Excel, PDF, XML and more.
4. SQL Server Analysis Services
This is a powerful business intelligence tool. It can be used to build and query multi-dimensional data. This service provides support for OLAP (Online Analytical Processing) and data mining capabilities. Many algorithms are used by this service such as neural networks, Decision trees and more to be used in data mining.
5. Full Text Search Service
This is a indexing and querying service for the unstructured text stored in the databases. The search engine consists of two processes. They are the filter Daemon process and the search process. The search process has the indexer and the full text query processor. Upon the extraction of text, the filter daemon process breaks up the text into a series of words and passes it on to the indexer.
This is a command line application which executes SQL queries. It is possible to create and run a set of SQL statements as a script using this facility.
7. Replication services
This service enables the server to replicate and synchronize database objects. This can be done completely or as a subset. There is support for three different types of replication. They are as follows.
a) Transaction replication- This is used to synchronize databases. Transactions affecting the master database are updated in the subscriber databases.
b) Merge replication- In case of synchronization conflicts between the publisher and the subscriber databases they can be resolved manually or by leveraging pre-defined policies. The relevant column’s rowguid needs to be configured, if merge replication is activated.
c) Snapshot replication- A snapshot of the entire database is taken and replicated to the subscribers. Even if the database changes, the changes are not tracked by the snapshot.
8. Service broker
It is used for synchronizing the different components of the SQL server together by means of exchanging messages. This service is responsible to provide a reliable messaging and message queuing platform in SQL server.
Here we’ve given you an overview of SQL Servers. However, if you’re planning to work with SQL Servers much, you may want to go further and take this SQL Server Certification training.