SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is primarily designed and developed to compete with MySQL and Oracle database. SQL Server supports ANSI SQL, which is the standard SQL (Structured Query Language) language. About servers to know the SQL Server comes with its own implementation of the SQL language, T-SQL (Transact-SQL).
SQL Server Management Studio (SSMS) is the main interface tool for SQL Server, and it supports both 32-bit and 64-bit environments.
SQL Server Editions
- SQL Server Enterprise: It is used in the high end, large scale and mission Critical business. It provides High-end security, Advanced Analytics, Machine Learning, etc.
- SQL Server Standard: This is for Mid-Tier Application and It includes basic reporting and analytics
- SQL Server Developer: It is similar to an enterprise edition for the non-production environment. It is mainly used for build, test, and demo purposes.
- SQL Server Express: It is for small scale applications and free to use any work.
- SQL Server WEB: It is designed for a low total-cost-of-ownership option for Web hosters. It provides scalability, affordability, and manageability capabilities for small to large scale Web properties.
Version History of SQL Server
- Microsoft and Sybase released version 1.0 in 1989.
- However, the partnership between these two ended in the early 1990s.
- Microsoft maintained ownership rights to the name SQL Server.
- Since the 1990s, subsequent versions of SQL Server have been released including SQL Server 2000, 2005, 2008, 2012, 2014, 2016, 2017, and 2019.
MS SQL Server as Client-Server Architecture
A Client is an application that sends requests to the MS SQL SERVER installed on a given machine. The server is capable of processing input data as requested. Finally, respond with processed data.
Key Components and Services of SQL Server
Below are the main components and services of SQL server:
- SQL Server: This service starts, stops, pauses, and continues an instance of Microsoft SQL Server. Executable name is sqlservr.exe.
- SQL Server Agent: It performs the role of Task Scheduler. It can be triggered by any event or as per demand. Executable name is sqlagent.exe.
- Database Engine: This component handle storage, Rapid transaction Processing, and Securing Data.
- SQL Server Full-Text Search: This lets user running full-text queries against Character data in SQL Tables.Executable name is fdlauncher.exe.
- SQL Server VSS Writer: This allows backup and restoration of data files when the SQL server is not running.Executable name is sqlwriter.exe.
- SQL Server Analysis Services (SSAS): Provide Data analysis, Data mining and Machine Learning capabilities. SQL server is integrated with R and Python language for advanced analytics. Executable name is msmdsrv.exe.
- SQL Server Reporting Services (SSRS): Provides reporting features and decision-making capabilities. It includes integration with Hadoop. Executable name is ReportingServicesService.exe.
- SQL Server Integration Services (SSIS): Provided Extract-Transform and Load capabilities of the different type of data from one source to another. It can be view as converting raw information into useful information. Executable name is MsDtsSrvr.exe.
SQL Server Instances
SQL Server allows you to run multiple services at a go, with each service having separate logins, ports, databases, etc. These are divided into two:
- Primary Instances
- Named Instances
There are two ways through which we may access the primary instance. First, we can use the server name. Secondly, we can use its IP address. Named instances are accessed by appending a backslash and instance name.