SQL-Introduction
In this tutorial, we will discuss the following important points about SQL Server.
- SQL Server Introduction
- SQL Server Version History
- Uses of SQL Server
- SQL Server Editions
- SQL Server as Client-Server architecture
- SQL Server Architecture
- Network Protocols
- Database Engine
- SQL OS
- SQL Server Instances
- Why use multiple Instances of SQL Server?
1. SQL Server Introduction
Microsoft created the relational database management system known as Microsoft SQL Server. It is a software product known as a database server, and its main job is to store and retrieve data as requested by other software applications, which may operate on the same computer or on a different computer over a network (including the Internet). Microsoft sells Microsoft SQL Server in at least a dozen different editions.
The history of Microsoft SQL Server begins with the first Microsoft SQL Server database product SQL Server v1. 0, a 16-bit relational database for the OS/2 operating system, released in 1989.
2. SQL Server Version History
-
MS SQL Server for OS/2- In 1989, Sybase, Ashton-Tate, and Microsoft worked together on a project to port Sybase SQL Server to OS/2.
-
SQL Server 4.2 for NT - The introduction of Windows NT is marked by the release of SQL Server 4.2 for NT in 1993.
-
SQL Server 6.0 - The 1995 release of SQL Server 6.0 signalled the end of Microsoft's partnership with Sybase; Sybase would go on to independently build their own version of SQL Server, Sybase Adaptive Server Enterprise.
-
SQL Server 7.0 - The source code was changed from C to C++ with the introduction of SQL Server 7.0 in 1998.
-
SQL Server 2000 - Microsoft releases SQL Server 2000
-
SQL Server 2005 - Released in 2005, SQL Server 2005 completes the conversion of the outdated Sybase code into Microsoft code.
-
SQL Server 2008 - Microsoft releases SQL Server 2008
-
SQL Server 2010 - Microsoft releases SQL Server 2010
-
SQL Server 2012 - Columnar in-memory storage, or xVelocity, was included to SQL Server 2012, which was launched in 2012.
-
SQL Server 2014 - SQL Server 2014 was released to manufacturing on March 18, 2014, and released to the general public on April 1, 2014,
-
SQL Server 2017 - Red Hat Enterprise Linux, SUSE Linux Enterprise Server, Ubuntu, and Docker Engine are among the Linux platforms that are now supported by SQL Server 2017, which was released in 2017.
-
SQL Server 2019 - Big Data Clusters, improvements to the "Intelligent Database," improved monitoring functions, an updated developer experience, and updates/enhancements for Linux-based instals are all components of SQL Server 2019, which was released in 2019.
-
SQL Server 2022 - Improved analytics, disaster recovery (to Azure), the Purview unified data governance and management service, and plumbing enhancements that make the database faster and more resilient are all included in the 2022 edition of SQL Server.
3. Uses of SQL Server
The following are the main usage of MS SQL Server:
-
To creat databases
-
To maintain databases
-
Analyzing data through SQL Server Analysis Services (SSAS)
-
Creating reports with SQL Server Reporting Services (SSRS)
-
Carrying out ETL operations (Extract, Transform, and Load) with SQL Server Integration Services (SSIS)
Microsoft offers several editions of SQL Server, each with a unique feature set and intended audience. These are the editions:
4. SQL Server Editions
There are four main editions of SQL Server, each with a unique set of included services and tools. There are two editions available without charge:
-
SQL Server Developer - This edition was created primarily for non-production like database testing and development. It enables the building, testing, and demo of things.
-
SQL Server Express - Small-scale databases and applications can be run on SQL Server Express, which supports disc storage capacity of up to 10 GB.
The following versions are used for business purposes: -
SQL Server Enterprise - This edition includes every feature of SQL Server, including high-end security, advanced analytics, and machine learning. It is used in high-end, larger, and more critical businesses.
-
SQL Server Standard - Edition has partial feature sets of the Enterprise Edition and limits on the Server regarding the numbers of processor core and memory that can be configured.
-
SQL Server Web - This edition is suitable for Web hosters who choose a low total cost of ownership. For small to large-scale online properties, it includes scalability, management, and affordability qualities.
5. SQL Server as Client-Server architecture
The client is a programme or application that submits requests to a particular machine MS SQL Server. On the basis of the request, the server can process input data. The server finally returns processed output data. The main part of MS SQL Server is the SQL Server Database Engine, which controls data processing, storage, and security.
- Client - Where the request initiated.
- Server - Where SQL Services got installed and databases reside.
6. SQL Server Architecture
The Protocol Layer, Relational Engine, and Storage Engine are the three main components that make up the SQL Server architecture. Here is a diagram of the architecture of Microsoft's SQL Server.
According to its architecture, the SQL Server primarily consists of three key parts:
- Network Protocols
- Database Engine
- SQLOS
1. Network Protocols
In SQL Server there are four protocols.
-
Shared memory - It is used for local connections and troubleshooting purpose. So It works on the same system where SQL Server is installed.
For example - At the home. You ask for tea and Mom is able to prepare and will serve in 5 minute. -
Named pipes - It is used for connections which are in LAN connectivity.
For example - You want to have a light green tea which her neighbor, Ritesh prepare very well. -
TCP/IP - It is used for connections which are in WAN connectivity.
For example - You want a tea ordered from a well-known tea Shop. The Coffee shop is located 10 km away from his home. -
VIA-Virtual Interface Adapter - It is used to requires special hardware to set up by vendor and also deprecated from SQL 2012 version.
2. Database Engine
The Database Engine is the main part of the SQL Server. The database engine is made up of a relational engine for handling queries and a storage engine for keeping track of database files, pages, indexes, etc. The Database Engine also creates and runs database objects including stored procedures, views, and triggers.
- Relational Engine - It is responsible for evaluating user requests and performs execution. It decides the most efficient way to run a query. It is also named the query processor. Query processing, memory management, thread and task management, buffer management, and distributed query processing are all main tasks performed by this engine.
- Storage Engine - It shows the physical database architecture, The storage engine is in charge of storage and retrieval of data from the storage systems such as disks and SAN.
3. SQL OS
SQL OS was first used with SQL 2005. Prior until this, it was only considered for small and medium applications. SQL Server is upgraded by Microsoft in SQL 2005 to handle high-end enterprise database load. It is a layer that sits between the windows operating system and the database engine. SQLOS is responsible for managing many operating system functions, such as memory and I/O management, scheduling, threading, exception handling, and synchronisation.
7. SQL Server Instances
The SQL Server installation is one instance. On a given system, we can set up multiple instances, but only one can be the default. The server's files, databases, and security credentials are perfect replicas. In SQL Server, there are two categories:
-
Default or Primary Instances - The default instance name is MS SQL Server. There are two ways in which we can access the primary instance. The server name is used in the first case, and its IP address is used in the second. It's always different.
-
Named Instances - A Unique name must be given to a named instance. You can install SQL Server as a named instance without installing the default instance first. By adding a backslash and the instance name, we can access it.
8. Why use multiple Instances of SQL Server
Multiple instances are useful if you need different server configurations, or even different versions. Say for example that you have a database made for Sql 2008 and want a new one for a new application with Sql 2012. You could host both on the same machine and use features for the most advanced version while keeping the old one working as it is.
Next