This article introduces you to Microsoft SQL Server or MS SQL.
Table of Contents
What is SQL Server?
SQL Server is a Relational database management system (RDBMS). It is also known as MS SQL/ Microsoft SQL Server etc. It is designed, developed and maintained by Microsoft. SQL Server is fully-featured, scalable, performance-packed and secured database management system. It runs on Windows and with its latest version can run on Linux and Docker containers
Like all other major players like Oracle, MySQL, etc. The MS SQL supports ANSI SQL. ANSI SQL is the standard SQL language for interacting with relational databases.
What is a database?
The database is an organized collection of data and it is stored in such a way that we can query, retrieve and use that data whenever needed. The data can be the availability of seats on a flight, available rooms of a hotel or the address of your customer. It can be in the form of text, image, numbers, audio & video.
What is a Database Management System?
The software that manages the database (collection of data) is the database management system (DBMS). A good database management system must support. It sits between the end-user and the database.
We do not access the data directly. We use DBMS to that. It takes care of details like storing the data in the file system. Retrieving it from the file system etc.
Create & maintain databases
- It should provide a systematic way to create, retrieve, update and manage data.
- Allow to create & manage tables, Indexes, Views, triggers, stored Procedure
- Provide the concurrency
- Maintain the integrity of the data stored
- Provide option to Create & Restore backups
- Manage database security by creating users, roles, etc
- Provide an interface between the database and end-users or application programs
- Provide Simple administration procedures to carry out the day to day management of the database
SQL Server is a relational database
A relational database is where data is Organized by tables, rows, and columns (like a spreadsheet). A Relational database must follow the Codd’s 12 rules (actually 13 rules numbered from 0 to 12) to be called as Relational database management system. [https://en.wikipedia.org/wiki/Codd%27s_12_rules]
SQL Server Database Engine
SQL Server is much more than a DBMS. It comes with many more features. But the core part of its RDBMS functionality comes from the Database Engine. It is responsible for storing, processing and securing data. It supports transaction processing and support for sustaining high availability.
SQL
We talk to the Database Engine using Structured Query Language ( SQL) (pronounced sequel). it is a special-purpose programming language designed for relational database management systems.
The SQL Server uses the T-SQL to query/manage the database. T-SQL is Short for Transaction-SQL. It is an extended form of SQL
Client/Server Database Systems
SQL Server is a client/server RDBMS.
The SQL Server Database Engine acts as a server. The clients connect it over the network and requests for data. The engine receives the request, retrieves the data, and sends it over the network to the client. The clients do not have access to the database files.
MS-Access is an example of desktop-based DBMS. Each client gets access to the database
Versions of SQL Server
The Supported Versions of SQL Server
- SQL Server 2012
- SQL Server 2014
- SQL Server 2016
- SQL Server 2017
From SQL Server 2016 onward, the product is supported on x64 processors only
The current version is Microsoft SQL Server 2017, released on October 2, 2017.
SQL Server 2019 has been released as a community technology preview and is slated for release to manufacturing in the second half of 2019
SQL Server Technologies
The SQL Server is not only about the maintaining & managing database. It is a feature-packed product that comes bundled with several technologies. The latest version of SQL Server has the following technologies
Machine Learning Services
Machine Learning Services is a feature in SQL Server that gives the ability to run Python and R scripts with data stored in SQL Server. You can use it to prepare and clean data, do feature engineering, and train, evaluate, and deploy machine learning models within a database. The feature runs your scripts in the database and eliminates the transfer of the data across the network to another server.
Integration Services
Popularly known SSIS or SQL Server Integration Services that provide data integration services. It provides packages that extract, transform, and load (ETL) processing for data warehousing.
Analysis Services
Microsoft SQL Server Analysis Services or SSAS is an online analytical processing (OLAP) or data mining tool. It can query data from various data sources, process a large amount of data and makes sense of information and uncover the patterns and relationships hidden inside large volumes of data. It is a tool for personal, team and corporate business intelligence.
Reporting Services
The SQL Server Reporting Services or commonly called as SSRS delivers reporting functionality. It is web-enabled and enterprise-ready and can be used to generate & publish reports in various formats and from various data sources
Replication
Replication is a set of technologies for copying and distributing data and database objects from one database to another, and then synchronizing between databases to maintain consistency. By using replication, you can distribute data to different locations and to remote or mobile users by means of local and wide area networks, dial-up connections, wireless connections, and the Internet.
Data Quality Services
SQL Server Data Quality Services (DQS) is a knowledge-driven data quality product. DQS enables you to build a knowledge base and use it to perform a variety of critical data quality tasks, including correction, enrichment, standardization, and de-duplication of your data. It enables you to perform data cleansing by using cloud-based reference data services provided by reference data providers. DQS also provides you with profiling that is integrated into its data-quality tasks, enabling you to analyze the integrity of your data.
Master Data Services
Master Data Services enables you to manage a master set of your organization’s data. You can organize the data into models, create rules for updating the data, and control who updates the data. With Excel, you can share the master data set with other people in your organization.
