Introduction to Microsoft SQL Server
SQL Server is an RDBMS developed by Microsoft. SQL Server is a platform that provides enterprise-class data management services. SQL Server has many features and tools to help interact, analyze data, easily expand for Online Transactional Processing (OLPT) systems, data warehousing, e-commerce applications…
SQL Server is used a lot because it is optimized for high-speed data interaction, high security, supports cloud database storage, improves performance and reduces pressure on hardware devices.. .
SQL Server Features and Benefits
- SQL Server policy-based management to detect non-compliant security policies. This feature allows only authorized users to access the database. Security events and controls can be automatically logged to log files.
- SQL Server has built-in transparent data compression along with encryption. SQL Server provides access control along with powerful rights management tools. It also offers enhanced performance when collecting data
- Microsoft has provided SQL server versions suitable for different types of users. These versions are also priced accordingly. Therefore, from hobbyists to professional developers to business users, there is a version suitable for everyone.
- SQL Server supports graphical interface, simple installation process.
Basic Architecture of SQL Server
There are many components and tools that come together to form the basic architecture of SQL Server. These components can be represented under the three main components shown in the figure:
There are a number of tools provided in SQL Server for database query development and management. The SQL Server Installation Center must be downloaded to install SQL Server tools and features. Features and tools can be edited or removed using the SQL Server Installation Center. Here is a list of tools in SQL Server:
|SQL Server Management Studio (SSMS)||One of the most important tools of SQL Server in most editions (SSMS). SSMS is an interface application that provides SQL Server with features to create databases, create objects (tables) in the database, query data, and manage data in SQL Server.|
|SQLCMD||This is a command-line tool (command-line interface), which can be used as an alternative to SSMS. It provides similar features to SSMS but only using the command line interface (black screen).|
|SQL Server Installation Center||SQL Server Installation Center can be used to add, remove and edit SQL Server tools and programs|
|SQL Server Profiler||SQL Server Profiler is used to monitor an instance (roughly translated as an instance) of a database or analysis operations.|
|SQL Server Data Tools (SSDT)||SSDT is an Integrated Development Environment (IDE), which helps to design databases using a tool called Microsoft Visual Studio (IDE for C# code).|
|Connectivity Tools||Connectivity tools include DB-Library, Open Database Connectivity (ODBC), Object Linking and Embedding Database (OLE DB) … This tool is used to communicate between client, server and library networks.|
Various services are executed on the computer running SQL Server, these services run concurrently with Windows servies and are visible in the task manager.
Some services in SQL Server:
|SQL Server Database Engine||Database Engine is a core service used to store, process and secure data. It is also used to handle replication, full-text search, and data quality (DQS) operations. It contains tools for relational management and XML data|
|SQL Server Analysis Services (SSAS)||Analysis Service contains tools to help create and manage Online Analytical Processing (OLAP). It is suitable for individuals, teams, or organizations to deal with business intelligence problems. Analytics services can also be used for data mining applications.|
|SQL Server Reporting Services (SSRS)||Reporting Services is used to create, manage, publish, and deploy reports. This report can be in tables, matrices, graphs, or other formats. Reporting applications can be created through Reporting Services|
|SQL Server Integration Services (SSIS)||Integration services are used to move, copy, and change data using graphical tools and program objects. The DQS component also includes integration services. Integration services support to build high performance data integration solutions|
|SQL Server Master Data Services||Master Data Services (MDS) is used to manage top-level data (master). MDS is used to analyze, manage, and report information such as stratification, security, transactions, business rules.|
All the programs and resources of the application are stored inside the instance (which can be roughly understood as the data instance). An instance consists of a memory area, a configuration file, and a CPU. Multiple instances can be used by different users in SQL Server. Although multiple instances can be represented within a computer, they do not affect each other. Cps means isolated operating instances. Each instance can be customized as required. Access and interaction rights for each instance can be assigned to different individuals and users. Resources are also allocated on an instance basis. It can be understood that instances are basically a repository of one or more different databases as the usual way of calling programmers.
In other words, instances can be called as a larger container and contain child containers, the main child containers are the database, security configuration, server objects…
SQL Server Versions
- Express/Web edition: free and provides basic data tier for web/mobile app
- Standard/Web edition: provides full features for a mid-tier application
- Enterprise/Web edition: provides full features for complex business and enterprise applications.
- Developer Edition: Free and includes all enterprise features, but only licensed for development and testing of databases in non-production environments.