SQL Server - Introduction

In this post we will get basic understanding about SQL Server. Before that we will get understanding about clients and servers and their types in brief.

Server is software that processes the request sent by client software. The machine on which server software is installed is known as server machine and the machine on which client software is installed is known as client machine. When client sends a request to the server then server processes the request, if the server is running, and sends the response back to the client machine. This request-response cycle is known as communication between client and server.

There are different types of server software used for different purposes; for example, database server software, web server software, email server software etc. For each type of server software, we require specific type of client software; for example, for web server we need client software called Browser. Similarly for email server we use email clients. For example, Gmail is an email server running at an IP address. Microsoft Outlook is an email client. Email clients use protocol like IMAP or POP3.

SQL Server is database software developed by Microsoft. In market there is other database software such as MySQL, MS Access, PostgreSQL etc. In this post we will focus on SQL Server and its functionality, irrespective of its versions.

Database software is used to manage different databases created inside the database server and their objects. To understand a database server we should first of all understand what database is.

Databases are created and managed by database server. A database is a container to store data. Different types of data can be stored in a database; they are known as database objects. In a database, data is stored, fundamentally, inside tables and to manage the data of tables database server creates other database objects such as stored procedures, index, Rules, Roles etc. You can create N number of databases in SQL Server and each database will have database objects.

Before going ahead we look at the distinction between file database server and client server databases. In a file server database, data is stored in files and retrieved from it. When any change is made to the data then the file is opened by client application and data is written into it. To display existing data, application opens the file and read the data. In case of file database server, multiple users can write to the same data file.

In a client server database data is still stored in files. Access to the files is controlled by a master program known as server. If client application wants to make use of the existing data, the client application sends a request to the server application. The server finds the proper data and sends it back to the client.

Similarly when the client wants to write some data into file then it sends request to the server. The server processes the request and writes the data into the file. So we find that there is a controller program that is database server software which takes the responsibility of reading data from file and writing data into file. The client applications send requests and it is the responsibility of the server to decide processing these requests.

The server software acts as a gatekeeper of clients' requests and enforces different kind of policies such as data integrity, data security, user management etc.

In client server architecture, server software e.g. database server resides at server machine and through the network, client machine communicates with the server machine. The network bandwidth and other such factors affect the communication.

The client server database includes feature of logging of transactions which allows recovery of data on disk failure or network error. This is not available in the file server database.

Transaction log: Logging transactions are an important feature of client server database to protect data. Transaction log is a separate file from data file. The log file is stored at database server to keep tracking of the operations performed like insertion, deletion or updating of the rows of table. When database server performs operation then first of all, entry is made into transaction log file and log file is saved and thereafter actual entry is made in to data file. So, record operation on table is a two step process in database. Transaction log is used for recovery of data, if case of data failure.

In client server database, the centralized server machine reads data from file and writes data into file but in file server database, different client machines have the responsibility to read from and write to file. So, reading and writing is decentralized in file server database while in client server database it is centralized.

Types of database

There are different types of databases available in the market based on different architectures and principles. There are different database models such as network database model, hierarchical database model, relational database model etc. The latest database model is relational database in which data is managed inside tables.

OLAP vs. OLTP

OLTP database mainly focuses on transactions processing; for example, insertion, deletion and updating of data. OLAP database mainly focuses on static data which are read to analyze the market trend etc. we will discuss about relational database in some other post which is based on relational principles.

SQL Server is relational database server

In a relational database, data are stored in tables based on relational database principles given by EF Codd. SQL Server is a relational database. Its core component is a database engine which is based on relational principles. The SQL Server database engine has query processor to execute a query and storage engine to store data securely. The storage engine uses file manager, lock manager, transaction manager and many utilities programs e.g. DBCC.

The query processor includes programs like Parser, Optimizer, SQL Manager, Database Manager and Query Executor.

Parser parses the SQL script and Optimizer provides query hints to optimize the script execution.

SQL Server Editions

Microsoft provides different editions of SQL Server.

The SQL Server Enterprise Edition is used in enterprise environment. It includes all features of SQL Server. The SQL Server Developer Edition has fewer features than Enterprise Edition. The SQL Server Express Edition is free edition.

Versions of SQL Server

Microsoft has released different versions of SQL Server editions over long period of time. The SQL Server Community is the latest edition.

Download SQL Server

Go to this link to download SQL Server latest edition.

In next post, we will learn about SQL Server Management Studio (SSMS).