SQL Server

What is an SQL Server?

SQL Server is a relational database management system, or RDBMS, developed and marketed by Microsoft.

Now, you must be wondering, what is a relational database management system?

The software that is used to store, manage, query, and retrieve data stored in a relational database is called a relational database management system (RDBMS). The RDBMS provides an interface between users and applications, the database, as well as the administrative functions. It helps in managing the data storage, access, and performance. The data is often stored in many tables, also called relations. These tables are further divided into rows, also called records and columns (also known as fields). There can be millions of rows in a database. Columns are made up of one specific data type, for example, name, price, or id.

Some notable points regarding SQL Server are given below :

• The primary function of the SQL Server is of storing and retrieving data as requested by the software applications, which can run over the same or another wide range of networks.
• SQL Server is built on top of SQL, which is a standard programming language for interacting with relational databases.
• SQL Server comes with its own implementation of the SQL language i.e., T-SQL (Transact-SQL).
• T-SQL is a Microsoft propriety Language. It provides further capabilities for declaring variables, exception handling, stored procedures, etc.
• The main IDE that we use for SQL Server is the SQL Server Management Studio (SSMS). Apart from that, we have a wide range of IDEs that can be used for SQL Server, such as the Azure Data Studio, SQL Server Data Tools (SSDT), Navicat for SQL Server , mssql Extension in VS Code, etc.

What is a SQL Server Used for?

SQL Server has numerous applications in the business world. Below discussed are a few of the major applications of SQL servers :

1. SQL Server is mainly designed manage and store data into the databases as requested by the software applications. SQL Server databases are stored in files in the file system. It supports different operations for example, analytics operations, transaction processing, etc.

2. SQL server databases can be used to store and manage tons of data.

3. SQL Server allows the sharing of data files by computers in the same network. Due to this, SQL server is believed to increase reliability.

4. The effective data mining, disk partitioning, and data management tools of SQL server help to maintain the crucial data. It also makes the storage space available for highly sensitive information.

5. The SQL Server has a very reliable backup system since the data is stored in the databases. It is used for performing regular backups. Regular backups help to restore and recover any sensitive business information from disasters.

6. SQL Server consists of several advanced features to help restore and recover any lost or damaged data. With the help of advanced recovery tools, it is possible to recover the complete database.

7. The SQL Server schedules backups of the transaction logs. Although this can be done manually, scheduling regular backups of transaction logs, without misses, is very important. Hence, it ensures regular backup scheduling and in turn helps in the maintenance of log files and eliminates the risk of performance issues during high traffic times.

8. The SQL Server database is highly secure and uses very advanced encryption algorithms, which makes it virtually impossible to break any security layers. It uses the advanced firewall.

Version History of SQL Server

The evolution of MS SQL Server started in 1988, when Microsoft co-operated with Ashton-Tate and Sybase, to create a variant of Sybase SQL Server. This was the first version of Microsoft SQL Server. For example, SQL Server 2019 is the latest edition offered by Microsoft. Its functionalities are exceptionally amazing. It is one of the most developed database management tools. It is also called an Intelligent Database, with superb performance and scalability than any of the previous versions could provide.

SQL Server Architecture

SQL Server is a client-server architecture. So, what is client-server architecture?

The client-server model is a distributed application structure ( one or more clients that communicate with servers on several machines linked through a network) that divides task or work-load between the servers, and the clients. In the client-server architecture, when the client computer sends a request for data to the server through the internet, the server accepts the requested process and delivers the data packets requested back to the client.

A few examples involve - Email, World Wide Web, etc.

Since SQL Server is a client-server architecture, its process starts with the client application sending a request. The SQL Server accepts, processes, and replies to the request with processed data.

There are three major components in SQL Server Architecture :

1. Protocol Layer
2. Relational Engine
3. Storage Engine

Apart from that, SQL Server consists of two main components :

1. Database Engine
2. SQLOS

Let us discuss the above two major components of SQL Server in detail:

Database Engine

The database engine is the core component of the SQL server. It is used for storing, processing, and securing data. The Database Engine provides controlled access and rapid transaction processing to meet the requirements of the most demanding data-consuming applications in the enterprise.

It consists of a relational engine for processing queries. Apart from that, it also contains a storage engine that manages database files, pages, indexes, etc. The database engine also creates and executes the database objects such as the stored procedures, views, and triggers.

The below diagram shows the main components of the database engine - the relational engine and storage engine.

So, what are relational engines and storage engines?

Let us learn more about them.

Relational Engine :

The relational engines consist of the components that determine the best way to execute (or run) any query. Hence, it is also termed the query processor. Based on our input query, the relational engine requests the data from the storage engine and processes the final result. Apart from that, the job of the relational engine also includes query processing, memory management, buffer management, thread and task management, and distributed query processing.

Storage Engine:

A SQL server storage engine is software that is used to create, read, and update data between the disk and memory. The SQL server maps the database with files that store database objects, tables, and indexes. Those files can then be stored on file systems such as either a FAT or NTFS file system. The storage engine is mainly responsible for the storage and retrieval of data from different storage systems such as disks and the SAN.

Now, let us discuss the second important component of the SQL Server - SQL Server Operating system (SQLOS).

SQLOS

SQLOS has also termed the SQL Server Operating system. It is under the relational engine and storage engine.

More clearly:

• It is an application layer between SQL Server components and the OS.
• It provides more filtered control of resource allocation.
• It centralizes resource allocation to provide more efficient management.

As the name suggests, SQLOS is responsible for providing many operating system services such as memory and I/O management. Other important functions of the SQLOS include :

• Synchronization
• Deadlock detection
• SQL Server exception handling (dealing with user errors and system-generated errors)
• Centralize common low-level tasks within the SQL Server process

SQL Server Editions

The SQL server consists of 5 primary editions, which come with a different set of services and tools. Few of them are paid, whereas few editions are available free of charge.

The five editions of SQL Server are listed below :

1. Standard Edition :
   SQL Server Standard edition includes the core functionalities required for most of the applications. For example, development, data management , DBA tools, etc for effective database management with minimal resources.

2. Web Edition :
   SQL Server Web edition provides a low-cost option and it is different from the Standard edition, basically in the terms of maximum memory for the buffer pool and maximum computing capacity. It provides scalability, affordability, and manageability capabilities for small to large-scale Web properties.

3. Enterprise Edition :
   Enterprise edition is the premium version of SQL Server and offers high-end performance, unlimited virtualization, and real-time business intelligence capabilities. Additionally, it has several other great features like data compression, enhanced security, and support for large database sizes. It is the best choice for companies focusing on scalability and performance.

4. Developer Edition :
   SQL Server Developer edition allows the developers to build any kind of application on top of SQL Server. It includes all the functionality of Enterprise edition but is licensed for use as a development and test system, not as a production server. SQL Server Developer is an ideal choice for people who build and test applications.

5. Express Edition :
   Express edition is the entry-level, free database edition. It is ideal for learning and building desktop and small server applications. It is only for use by individuals or small organizations and is least functional. It does not include any of the advanced functionality of the other four editions.

Key Components and Services of SQL Server

In this topic, we are going to learn about the key services of the SQL Server. They

Below given are the main components and services of the SQL server :

• Database Engine:
  The Database Engine is a component that mainly handles data storage, rapid transaction processing , and data security . We have learned in detail about this previously in our article.

• SQL Server:
  The SQL Server starts, stops, pauses, and continues an instance of Microsoft SQL Server. The executable name of SQL Server is sqlservr.exe.

• SQL Server Agent :
  The SQL Server Agent is kind of a task scheduler in the SQL server. The SQL Server Agent uses SQL Server to store job information. The jobs contain one or more job steps. Each step contains its task (for example, backing up a database) .SQL Server Agent can run these jobs on a schedule, in response to a specific event, or on demand. The executable name of SQL Server Agent is sqlagent.exe.

• SQL Server Browser :
  The SQL Server Browser listens to the incoming request and connects to the desired SQL server instance. The executable name of SQL Server Browser is sqlbrowser.exe.

• SQL Server Analysis Services (SSAS) :
  The SQL Server Analysis Services provides the Data analysis, Data mining and Machine Learning capabilities . SQL server is also integrated with R and Python language for advanced analytics . The executable name for SQL Server Analysis Services is msmdsrv.exe.

• SQL Server Reporting Services (SSRS) :
  The SQL Server Reporting Services is known for the reporting features and decision-making capabilities. The SSRS generally provides a set of tools and services that create, deploy, and manage mobile and paginated reports . The users can consume the reports via a web browser, on their mobile device, or via email. The executable name for SQL Server Reporting Services is ReportingServicesService.exe.

• SQL Server Integration Services (SSIS) :
  The SQL Server Integration Services converts any raw information into useful information. It also provides the Extract-Transform and Load capabilities of the different types of data from one source to another.

As we learned above about the services of SQL Server in detail, let us see where can we access them. Below given is the SQL Server Configuration Manager in the SQL Server IDE : SQL Server Management Studio (SSMS).

In the SQL Server Configuration Manager, we get access to different SQL Server services such as the integration services, analysis services, reporting services, SQL server browser, etc.

Few Examples of SQL Server Services

Let us see an example where we are connecting to a SQL Server through the SQL Server Management Studio (SSMS) : the popular IDE for SQL Server.

Below given is an image that displays what we will be presented once we will connect to any SQL Server in SSMS. Over there, we will be given the option to connect to any particular SQL Server service out of a given set of services:

In the above image, you can see all the services we discussed above, namely : Database Engine, Analysis Service, Integration Service, and the Reporting Service.

After we connect to any of the above services, we will be presented with a prompt where our SQL Server service will be displayed in the server type field.

For example, in the above image, the server type or the SQL Server service we are connecting to is the Analysis Service. We can proceed to connect to our server by providing our server name and credentials in the respective fields.

SQL Server Tools

Microsoft provides both the data management and business intelligence (BI) tools along with the SQL Server. For data management, SQL Server includes the SQL Server Integration Services (SSIS), SQL Server Data Quality Services, and SQL Server Master Data Services.

For developing the databases, SQL Server provides the SQL Server data tools. SQL Server has the SQL Server Management Studio (SSMS) to manage, deploy, and monitor databases. Other similar tools include Azure Data Studio, SQL Server data tools , and the mssql extension for VS Code.

SQL Server Instances

What is SQL Server Instance?

Each database engine installation is known as the SQL Server instance. An SQL Server instance is a complete SQL server and we can install many instances on a machine. However, we can have only 1 default instance. Also, more than one SQL Server instance can run at a time parallelly (it kind of processes parallelly), it's just there should be a unique name for each instance that you install.

Basically, each instance is a complete SQL Server installation along with its copy of server files, database engine, databases, users, and credentials. Instances can be of the same or different versions.

The SQL Server is so flexible that it allows us to run multiple services simultaneously, with each service having separate logins, ports, databases, etc. From SQL Server 2005 and above, we can run up to 50 instances simultaneously on a server. Each instance runs independently of the other instances. We can even run each instance concurrently.

SQL Server instances are divided into the below two parts :

1. Primary Instance :
   We can connect to the primary instance either by using the server name or by using its IP address .

   

   In the above image, you can see we are trying to connect to the primary instance of a server. We have provided the server name to connect to the same. Alternatively, we may also provide the IP address to connect to the server.

2. Named Instance :
   They are accessed by providing the server name, followed by a backslash \ and the instance name. For example, to connect to an instance named "dev" on the local server, we can use 127.0.0.1\dev.

   

   For example, in the above image, we are trying to connect to the dev instance of our server myserver . Hence following the naming conventions, we provide the server name as myserver\dev.

Advantages of SQL Server Instances

There are multiple advantages of SQL Server Instances. A few of them are stated below.

1. We can install different versions of SQL server on one machine. And, each installation works independently from the other installations.

2. SQL Server instances help us in reducing the cost of operating SQL Server. We can get different services from different instances, so there is no need of purchasing one license for all services.

3. SQL Server instances help in maintenance of production, test, and development environments separately on every machine.

4. It can reduce the temporary database problems. Because services running on a single SQL Server instance can cause recurring problems, which can be avoided by separating the SL server instances.

5. A SQL Server instance can fail, leading to an outage of services. Hence we need a standby server to be brought in if the current server fails. This can easily be achieved using SQL Server instances.

6. Other great advantages include that it is reliable and easy to use, and it works great with .net and others.

Examples of SQL Server Alternatives

Some alternatives to Microsoft SQL server are:

• Oracle
• PostgreSQL
• Apache Aurora
• MariaDB
• Microsoft Access