In this article, we will see the following Introduction to SQL Server.
Human needs have increased tremendously. Now people are doing much more composite tasks than ever before.
Society has become very complex if a person has to work in a huge amount of information every day.
To work with enormous information, we must have a system where we can store, manipulate, and share information all over the world.
It is one of the core reasons for introducing database management systems (DBMS) as well as relational database management (RDBMS) nowadays.
So one thing is clear to us that we store and manipulate data/ information into a database, where the database contains various types of tables for storing various types of data/information.
SQL Server is an RDBMS product it was designed and developed by Microsoft Company.
SQL Server will provide graphical user interface (GUI) facilities it means that users can interact with the database using icons without remembering any commands.
SQL Server is a collection of databases where the database is a collection of various objects like tables, views, procedures, and functions, etc.
To work SQL Server we use SQL Server management studio. It is a tool used for the SQL Server.
The first version of SQL SERVER is 1.0 was released in 1989.
The Main Topics in SQL are:
👉 Installation and configuration
👉 Backup and resource
👉 Architecture and isolation levels
👉 Database maintain and monitoring
👉 HADR (log shipping, mirroring, replication, clustering always on…
👉 Performance tuning
Few features were introduced in the year 2012 :
Here a few types of editions in a SQL Server
Developer Edition :
It is a relational database management system.
It has been designed in this kind of way that can be run on any tool ranging from a pc to a huge multi processer server.
Enterprise Edition :
It includes basic auditing, contained databases, encryption and backups, and user-defined roles.
It surpasses the standard edition and extensible key management.
Standard Edition :
It is relational database server software offering tools for data storage, management, analysis, and reporting.
It is limited to 16 cores and 64GB of RAM. It provides the core relational database engine and basic intelligence capabilities.
Word page Edition :
nowadays we are not using this edition
Express Edition :
It is a relational database management system that is free to download, distribute, and use.
It comprises a database specifically targeted for embedded and smaller-scale applications.
Evolution Edition :
The sql server evaluation edition is a great way to get a fully functional and free instance of sql server for learning and developing solutions.
This edition has a built in expiry of 6 months from the time that we install it.
SQL server DBMS versions
From 1989 to 2019
In2017 and 2019 we are using the windows and Linux support
In before (means 2012, 14, 16) we are using only windows
Now will discuss in which year and what type of versions are released and a comparison
This version was introduced by SSMS that what we used now
The BIDS (business intelligence development studio) is an offer made by an investor, trader, or dealer to buy a security, commodity, or currency
A BID stipulates the price the potential buyer is willing to pay as well as the quantity he or she will purchase, for that purposed price
SQL Server 2008
Database backup compression is a new feature in SQL server 2008
It significantly reduces operations. By default backup compression is turned off on the server instance level.
Unlike other third party software which provides different levels of compression in sql server.
Another important feature is CDC (change data capture) to track the changes in the table
Another feature was introduced here is transparent data encryption (the data saved like MDF, LDF, those MDF, and LDF will be encrypted why because those files are secured by the statics) and some of the encryption functions are RANDOM, GEN, KEY NAME
SQL Server 2012
A few features are column store indexes, business intelligence, sequence objects, windows server core support, error handling, new T SQL functions, enhanced, lock higher shipping, mirroring, replication, clustering in SQL 2012it was the best feature introduced Always-on feature
SQL Server 2014
In this version, Microsoft wanted to sort out the OLTP problems from many angles slow disk performance with in-memory tables, slow log performance with delayed durability, and maintenance concurrency with lock priority.
The new SQL server releases the addresses memory issues with an SSD buffer pool extension and increases availability through integration with the Azure cloud.
It is the most exciting new feature. It allows moving individual tables to special in-memory structures.
There are several limitations and special requirements for these tables.
This is better than other in-memory solutions that require the entire database to be placed in memory.
Now you can get more performance by converting exciting stored procedures into in-memory procedures.
It automatically backups your database to ensure you don’t lose data when your system fails.
As you work on your workload the system analysis and determine that you have significant changes and go back to work on azure
This feature only works on azure blob storage.
Stretch database, basic availability groups, query storage, statistics, in-memory OLTP.
This was introduced in the previous version but now the improvements were made in this version.
To reduce the IO usage, the ALTER TABLE was updated minimizing the number of log writers such as updated automated several DDL and DML commands were added (null values, foreign keys, and DML triggers).
One of the most features is dynamic database masking
Is when we wanted to restrict the data means actual data (we don’t want to show the data) like xxxxx
Emails ID will we creating the tables we use the database masking
SQL Server 2017
It is a known platform of our choice SQL server 2017 is several Linux distros, it can run on docker containers and SQLVM azure and SQL server windows.
It supports graph database, automatic database tuning, and machine learning by adopting python.
Python support was the first database management system to be al enabled in SQL server 2017.
SQL Server 2019
Database engine integration services reporting analyses services (core DBA part) integration (atl part) msba part
In 2019 here we can connect any feature sql and no sql support (unstructured data also we can read)
Polybase (external multiple database integration parts
👉 Support for persistent memory (regular and faster quires we can use the persistent memory)
👉 Intelligent query processing any issue query is something else itself, Suppose it is going on the bad plan itself take to a good plan (automatically the plan without our interruption
👉 In memory temp DB is created in desk in-memory DB any temporary memory will keep in temp DB it is very much faster
👉 Security and compliance part
👉 Big Data cluster was enabled in 2019
Data classifications gdpr padata (here we can restrict the data by using the data classification).
Always encrypted improvements client-side only instead of doing us.
Tools azure, post grade sql, my sql servers, and note books (they have in azure database) but now they are not using.
Linkedin : Venkat Vinod Kumar Siram