This is a tutorial about SQL Server for beginners.
You can also check this tutorial in the following video:
1. What is SQL Server
SQL Server is a relational database management system (RDBMS) developed by Microsoft. The primary function of the SQL Server is to store and retrieve data used by other applications running on the same system or even across the Internet. There are at least a dozen of different editions of Microsoft SQL Server, from small to big workloads based on the number of people they want access to its data.
2. SQL Server Architecture
SQL Server is a client-server architecture. The client application sends a request, then the SQL Server accepts, processes, and replies to the request with processed data.
2.1 Protocol Layer
The protocol layer implements the external interface of the SQL Server. All the operations are communicated to it via the Tabular Data Stream (TDS). TDS is an application layer protocol, used to transfer data between a database server and a client. TDS packets can be encased in other physical transport-dependent protocols, like TCP/IP, named pipes, and shared memory, giving access to SQL Server through these protocols.
2.2 Database Engine
The Database Engine consists of two parts, the Relational Engine, and the Storage Engine.
The Relational Engine contains the components that determine the best way to execute a query and it is also known as the query processor. Its main goal is to request data from the storage engine based on the input query and then process the results. The query is expressed using a variant of SQL called T-SQL, a dialect Microsoft SQL Server shares with Sybase SQL Server due to its legacy. It also contains a cost-based query optimizer which is responsible for creating an execution plan for the user’s query. This is the plan that will determine how the user query will be executed. There is also the Query Executor. This is where the query will be executed step by step with the help of the execution plan and also the storage engine will be contacted. Some tasks of the relational engine include memory management, thread, and task management, buffer management, and distributed query processing.
The Storage Engine is in charge of storage and retrieval of data from the storage systems, such as disks and SAN. Data storage is a database, which is a collection of tables with typed columns. SQL Server supports different data types, including primitive types such as Integer, Float, Decimal, Char, Varchar, binary, and Text. In addition to tables, a database can also contain other objects including views, stored procedures, indexes, and constraints, along with a transaction log. The data in the database are stored in primary data files with an extension .mdf. Secondary data files, identified with a .ndf extension, are used to allow the data of a single database to be spread across more than one file, and optionally across more than one file system. Log files are identified with the .ldf extension. The Storage Engine also contains the Buffer Manager hat looks for Data in Buffer in Data cache. If present, then this Data is used by Query Executor. This improves the performance as the number of I/O operations is reduced when fetching data from the cache as compared to fetching data from Data storage.
3. SQL Server Operating System
The SQL Operating System (SQLOS) was introduced in SQL Server 2005. SQLOS provides many operating system services such as memory and I/O management. Other services include exception handling and synchronization services. Some of them are :
- Scheduler and IO completion. The SQLOS is responsible for scheduling threads for CPU consumption. Most IO is asynchronous so the SQLOS is responsible for signaling threads when IO is completed.
- Synchronization primitives:
SQL serveris a multi-threaded application, so SQLOS is responsible for managing thread synchronizations. - Memory management: Different components within
SQL Server, like CLR, lock manager etc, require memory, therefore, the SQLOS can control the memory a component is consuming. - Deadlock detection and management..
- Exception handling.
4. SQL Server Services and Tools
Microsoft provides both data management and business intelligence (BI) tools and services together with SQL Server.
- The SQL Server Machine Learning services operate within the SQL server instance, allowing people to do machine learning and data analytics without having to send data across the network or be limited by the memory of their own computers.
- The Service Broker is used inside an instance, programming environment. For cross-instance applications, Service Broker communicates over TCP/IP and allows the different components to be synchronized, via exchange of messages.
- The SQL Server Analysis Services (SSAS) adds OLAP and data mining capabilities for SQL Server databases. Analysis Services supports the XML for Analysis standard as a communication protocol.
- The SQLCMD is a command line application and exposes the management features of SQL Server. It allows SQL queries to be written and executed from the command prompt. It can also act like a scripting language to create and run a set of SQL statements as a script.
- The SQL Server Management Studio is a GUI tool included with SQL Server 2005 and later for configuring, managing, and administering all components within Microsoft SQL Server. The tool includes both script editors and graphical tools.
5. SQL Server Editions
There are multiple SQL Server Editions, with different feature sets and targeting different users. These editions are:
- SQL Server Enterprise Edition includes both the core database engine and services for creating and managing a SQL Server cluster as large as 524 petabytes and addresses 12 terabytes of memory and supports 640 logical processors.
- SQL Server Standard Edition includes the core database engine, along with the stand-alone services.
- SQL Server Business Intelligence is focusing on Self Service and Corporate Business Intelligence. It includes the Standard Edition capabilities and Business Intelligence tools: PowerPivot, Power View, the BI Semantic Model, Master Data Services, Data Quality Services, and xVelocity in-memory analytics.
- SQL Server Developer Edition includes the same features as SQL Server Enterprise Edition but is limited by the license to be only used as a development and test system, for free.
6. Summary
In this tutorial, we learned about the SQL Server, including its architecture, services, tools, and editions. You can find more articles about SQL Server here.
Last updated on Dec. 17th, 2021
