Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, this is a software product with the main function of storing and retrieving data as requested by other software applications - which can run on the same computer or on other computers on the network (including the Internet).
Microsoft markets at least a dozen different editions of Microsoft SQL Server, aimed at different audiences and for workloads ranging from single to small apps to large applications that face the Internet with multiple users at the same time.
Video Microsoft SQL Server
Histori
The history of Microsoft SQL Server started with the first Microsoft SQL Server product - SQL Server 1.0, a 16-bit server for OS/2 operating system in 1989 - and extends to today.
Milestones
- MS SQL Server for OS/2 started as a project to port Sybase SQL Server to OS/2 in 1989, by Sybase, Ashton-Tate, and Microsoft.
- SQL Server 4.2 for NT was released in 1993, marking an entry to Windows NT.
- SQL Server 6.0 was released in 1995, marking the end of collaboration with Sybase; Sybase will continue to develop their own variant of SQL Server , Sybase Adaptive Server Enterprise, regardless of Microsoft.
- SQL Server 7.0 was released in 1998, marking the source code conversion from C to C.
- SQL Server 2005, released in 2005, completed the complete revision of the old Sybase code into Microsoft code.
- SQL Server 2017, released in 2017, adds Linux support for this Linux platform: Red Hat Enterprise Linux, SUSE Linux Enterprise Server, Ubuntu & amp; Docker Machine.
Currently
In October 2017 the following versions were supported by Microsoft:
- SQL Server 2008
- SQL Server 2008 R2
- SQL Server 2012
- SQL Server 2014
- SQL Server 2016
- SQL Server 2017
From SQL Server 2016 onwards, products are only supported on x64 processors.
The current version is Microsoft SQL Server 2017, released October 2, 2017. The RTM version is 14.1709.3807.1.
Maps Microsoft SQL Server
Issue
The protocol layer implements an external interface to SQL Server. All operations that can be run on SQL Server are communicated through a Microsoft-specified format, called Tabular Data Stream (TDS). TDS is an application layer protocol, used to transfer data between database servers and clients. Originally designed and developed by Sybase Inc. for their Sybase SQL Server relational database engine in 1984, and then by Microsoft in Microsoft SQL Server, TDS packages can be wrapped up in protocols that depend on other physical transport, including TCP/IP, named pipes, and memory sharing. As a result, access to SQL Server is available on top of this protocol. In addition, SQL Server API is also exposed through web services.
Data storage
Data storage is a database, which is a collection of tables with typed columns. SQL Server supports various data types, including primitive types such as Integer , Float , Decimal , Char (including character string ), Varchar (variable length character string), binary (for unstructured data clumps), Text (for textual data) among others. Rounding buoys to whole numbers using Symmetric Rounding Arithmetic or Symmetrical Longitude ( fix ) depends on the argument: Round Select (2.5, 0) gives 3.
Microsoft SQL Server also allows user-defined composite types (UDT) to be defined and used. It also makes server statistics available as virtual tables and views (called Dynamic Management Views or DMVs). In addition to tables, the database can also contain other objects including views, stored procedures, indexes and constraints, along with transaction logs. The SQL Server database can contain up to 2 31 objects, and can reach several OS-level files with a maximum file size of 2 60 bytes (1 exabyte). The data in the database is stored in the primary data file with the .mdf extension. Secondary data files, identified with the .ndf extension, are used to allow data from one database to spread over more than one file, and optionally in more than one file system. Log files identified with .ldf extensions.
The storage space allocated to the database is divided into sequential numbers of pages , each measuring 8 KB. A page is the basic unit of I/O for SQL Server operations. A page is marked with a 96-byte header that stores metadata about the page including page number, page type, blank space on the page and the ID of the object that owns it. The page type defines the data contained on the page: data stored in the database, index, allocation map that stores information about how the page is allocated to tables and indexes, changes the map that stores information about changes made to another page since the last backup or record, or contains large data types such as images or text. Although the page is the basic unit of an I/O operation, space is actually managed within the extent limit of 8 pages. Database objects can include all 8 pages in a level ("uniform area") or share up to 7 other objects ("mixed level"). The rows in the database table can not be more than one page, so are limited to 8 in KB size. However, if the data exceeds 8 KB and the line contains varchar or varbinary data, the data in that column is moved to a new page (or perhaps a page order, called allocation unit ) and replaced with a pointer to the data.
For the physical storage of the table, the rows are divided into series of partitions (numbered 1 to n). Partition size is determined by the user; by default all rows are in one partition. A table is divided into multiple partitions to spread the database through the computer cluster. The rows in each partition are stored in a B-tree or heap structure. If the table has an associated index and is clustered to allow for fast line retrieval, the rows are stored in sequence according to their index values, with B-tree providing the index. The data is in the leaf node, and the other node stores the index value for leaf data that can be reached from each node. If the index is not classified, the row is not sorted by index key. An indexed view has the same storage structure as an indexed table. A clustered table without index is stored in an irregular stack structure. However, the table may have a non-clustered index to allow for fast line retrieval. In some situations the heap structure has a performance advantage over the cluster structure. Both heap and B-tree can reach multiple allocation units.
Buffer management
SQL Server page buffer in RAM to minimize disk I/O. Every 8-KB page can be buffer-in-memory, and the set of all current buffer pages is called cache buffer. The amount of memory available for SQL Server decides how many pages will be cached in memory. The buffer cache is managed by Buffer Manager . Either read from or write to any page copy it to cache buffer. Further reading or writing is transferred to an in-memory copy, rather than an on-disc version. This page is updated on disk by Buffer Manager only if the cache in memory has not been referenced for some time. When writing the pages back to disk, asynchronous I/O is used where I/O operations are performed in the background thread so that other operations do not have to wait for I/O operations to complete. Each page is written together with a checksum when it is written. When reading the page again, the checksum is recalculated and matched with the stored version to make sure the page is not damaged or tampered with for a while.
Concurrency and lock
SQL Server allows multiple clients to use the same database simultaneously. Thus, it is necessary to control concurrent access to shared data, to ensure data integrity - when multiple clients update the same data, or the client attempts to read data that is in the process of being changed by another client. SQL Server provides two modes of concurrency control: pessimistic concurrency and optimistic concurrency. When pessimistic concurrency control is in use, SQL Server controls concurrent access by using a key. Keys can be shared or exclusive. Exclusive locks give users exclusive access to data - no other users can access the data as long as the key is held. Shared keys are used when some data is being read - many users can read from data locked with shared keys, but do not get exclusive locks. The latter must wait for all shared keys to be released.
The key can be applied to different levels of granularity - across tables, pages, or even on a per line basis in the table. For index, can be whole index or index leaf. The level of detail to be used is determined on a per-basis basis by the database administrator. While a good locking system allows more users to use tables or indexes simultaneously, it requires more resources, so it does not automatically generate higher performance. SQL Server also includes two lighter mutual exclusion solutions - hooks and spinlocks - less powerful than keys but less resource intensive. SQL Server uses it for DMV and other resources that are usually not busy. SQL Server also monitors all worker threads that acquire keys to ensure that they do not end in deadlock - in this case, SQL Server takes corrective steps, which in most cases is to kill one of the threads entangled in the deadlock and roll back the transaction started. To implement locking, SQL Server contains Lock Manager . Lock Manager maintains a table in memory that manages database objects and locks, if any, on them along with other metadata about keys. Access to any shared object is mediated by the key manager, which gives access to resources or blocks them.
SQL Server also provides an optimistic concurrency control mechanism, similar to multipurpose concurrency controls used in other databases. This mechanism allows a new version of the row to be created each time the row is updated, as opposed to overwriting the row, that is, the row is also identified by the ID of the transaction that creates the row version. Both the old and new versions of the line are stored and maintained, even if the old version is moved from the database into the system database identified as Tempdb . When the row is in the process of being updated, other requests are not blocked (unlike locking) but are executed on older line versions. If another query is an update statement, it will generate two different row versions - both of which will be stored by the database, identified by each transaction ID.
Data retrieval and program capabilities
The main mode of retrieving data from a SQL Server database is a query for it. Query is expressed using a SQL variant called T-SQL, a dialect of Microsoft SQL Server sharing with Sybase SQL Server due to inheritance. The declarative query determines what to take. This is processed by the query processor, which describes the sequence of steps that will be required to retrieve the requested data. The sequence of actions required to execute a query is called a query plan. There may be multiple ways to process the same query. For example, for a query that contains a merged statement and a select statement, executing a merge on both tables and then executing select on the result will give the same result as selecting from each table and then executing the join, but resulting in a different execution plan. In such cases, SQL Server selects the expected plan to produce the results in the shortest time possible. This is called query optimization and is performed by the query processor itself.
SQL Server includes a cost-based query optimizer that tries to optimize costs, in terms of resources needed to execute a query. By query, the query optimizer looks at the database schema, database statistics, and system load at that time. It then decides which order to access the table referenced in the query, which sequence to execute the operation and what access method is used to access the table. For example, if the table has a related index, whether the index should be used or not: if the index is in a column that is not unique for most columns ("low selectivity"), it may not be useful to use the index to access the data. Finally, it decides whether to execute the query simultaneously or not. While concurrent execution is more expensive in terms of total processor time, since actual execution shared to different processors might mean faster execution. Once the query plan has been generated for the query, it's temporarily stored. For further requests of the same query, the cache plan is used. Unused packages will be discarded after some time.
SQL Server also allows stored procedures to be defined. The stored procedure is a T-SQL queries parameter, which is stored on the server itself (and is not issued by the client application as is the case with common questions). Stored procedures can accept values ​​sent by the client as input parameters, and resend the results as output parameters. They can call the specified function, and other stored procedures, including the same stored procedure (up to a certain number). They can selectively grant access to. Unlike other questions, the stored procedure has an associated name, which is used at runtime to resolve to the actual question. Also because the code does not need to be sent from the client every time (because it can be accessed by name), it reduces network traffic and slightly improves performance. Execution plans for stored procedures are also cached as needed.
T-SQL
T-SQL (Transact-SQL) is a secondary means of programming and managing SQL Server. It exposes keywords for operations that can be performed on SQL Server, including creating and changing database schemas, inserting and editing data in the database as well as monitoring and managing the server itself. Client applications that consume data or manage servers will utilize SQL Server functions by sending queries and T-SQL statements which are then processed by the server and the results (or errors) are returned to the client application. SQL Server allows to be managed using T-SQL. This describes read-only tables from readable server statistics. The management function is exposed through stored procedures in the system that can be called from a T-SQL request to perform a management operation. It is also possible to create a linked Server using T-SQL. Linked servers allow one request to process operations performed on multiple servers.
Native Server SQL Client (a.k.a. SNAC)
SQL Server Native Client is a client-side data access library for Microsoft SQL Server, 2005 versions and so on. It natively implements support for SQL Server features including the implementation of Tabular Data Stream, support for mirrored SQL Server databases, full support for all data types supported by SQL Server, asynchronous operations, query notifications, encryption support, as well as receiving multiple result sets in one database session. SQL Server Native Client is used under the hood by SQL Server plug-ins for other data access technologies, including ADO or OLE DB. SQL Server Native Client can also be directly used, passing through the generic data access layer.
On November 28, 2011, the preview of the SQL Server ODBC driver for Linux was released.
SQL CLR
Microsoft SQL Server 2005 includes a component called SQL CLR ("Common Language Runtime") with which it integrates with the.NET Framework. Unlike most other applications that use the.NET Framework, SQL Server itself hosts the.NET Framework runtime, that is, the memory, threading and resource management requirements of the.NET Framework are met by the SQLOS itself, rather than the underlying Windows operating system. SQLOS provides deadlock detection and resolution services for.NET code as well. With SQL CLR, stored procedures and triggers can be written in a managed.NET language, including C # and VB.NET. The managed code can also be used to define UDT (the specified user type), which can persist in the database. The managed code is compiled into CLI assemblies and once verified for type security, registered in the database. After that, they can be called like any other procedure. However, only a portion of the Available Class Base Library, when running code under SQL CLR. Most APIs related to user interface functionality are not available.
When writing code for SQL CLR, the data stored in the SQL Server database can be accessed using the ADO.NET API like other managed applications that access SQL Server data. However, doing it creates a new database session, different from the one in which the code is executing. To avoid this, SQL Server provides some enhancements to ADO.NET providers that allow connections to be redirected to the same session that already hosts the current code. This kind of connection is called a context connection and is set by setting the context connection parameter to true in the connection string. SQL Server also provides some other enhancements to the ADO.NET API, including classes to work with tabular data or a single row of data and classes to work with internal metadata about data stored in the database. It also provides access to XML features in SQL Server, including XQuery support. This increase is also available in T-SQL Procedures as a result of the introduction of the new XML Datatype (query, value, node function).
Services
SQL Server also includes a variety of additional services. Although this is not essential for the operation of database systems, they provide value-added services on top of the core database management system. The service is either running as part of some SQL Server component or outside the process as a Windows Service and presents their own APIs to control and interact with them.
Machine Learning Service
SQL Server Machine Learning services operate in SQL server instances, allowing people to perform machine learning and data analytics without having to send data over the network or be limited by their own computer memory. This service comes with a Microsoft R and Python distribution that contains packages commonly used for data science, along with some proprietary packages (eg revoscalepy, RevoScaleR, microsoftml) that can be used to create large-scale machine models.
Analysts can configure their client machines to connect to remote SQL servers and push script execution to it, or they can run R or Python scripts as external scripts in T-SQL queries. Trained machine learning models can be stored in the database and used for assessment.
Service Broker
Used inside an instance, the programming environment. For cross-instance applications, Service Brokers communicate over TCP/IP and allow various components to be synchronized, via message exchange. The Service Broker, which runs as part of a database engine, provides reliable messaging and message queuing platforms for SQL Server applications.
Replication Service
The SQL Server Replication Service is used by SQL Server to replicate and synchronize database objects, either in whole or a subset of existing objects, in the replication agent, which may be another database server across the network, or database cache on the client side. Lulla follows the publisher/subscriber model, i.e., changes sent by one database server ("publisher") and received by others ("customer"). SQL Server supports three different types of replication:
- Transaction replication
- Every transaction made to the publisher database (parent database) is synchronized to the customer, who updates their database with the transaction. Transactional replication synchronizes the database in the near future.
- Merge replication
- Changes made both to publishers and customer databases are tracked, and periodically changes are synchronized bi-directional between publishers and customers. If the same data has been modified differently in both the publisher and the customer database, the synchronization will result in a conflict to be resolved, either manually or by using a predefined policy. rowguid must be configured on the column if the merge replication is configured.
- Replicate snapshot
- Replication snapshot publishes a copy of the entire database (data snapshot) and replicates out to the customer. Further changes to the snapshot are not tracked.
Analysis Services
SQL Server Analysis Services adds OLAP and data mining capabilities to SQL Server databases. OLAP engines support MOLAP, ROLAP, and HOLAP storage mode for data. The Analysis Services support XML for Analysis standards as the underlying communication protocol. Cube data can be accessed using MDX and LINQ queries. The specific functionality of data mining is exposed through the DMX query language. Analysis Services include various algorithms - decision trees, grouping algorithms, Naive Bayes algorithms, time series analysis, sequence grouping algorithms, linear and logistic regression analysis, and neural networks - for use in data mining.
Reporting Service
SQL Server Reporting Services is a report generation environment for data collected from SQL Server databases. This is managed through a web interface. The reporting service features a web service interface to support custom reporting application development. The report is created as an RDL file.
Reports can be designed using the latest version of Microsoft Visual Studio (Visual Studio.NET 2003, 2005, and 2008) with Business Intelligence Development Studio, installed or with the included Report Builder. Once created, RDL files can be displayed in various formats, including Excel, PDF, CSV, XML, BMP, EMF, GIF, JPEG, PNG, and TIFF, and HTML Web Archive.
Notifications Service
Originally introduced as a post-release add-on for SQL Server 2000, the Notification Service is bundled as part of the Microsoft SQL Server platform for the first and only time with SQL Server 2005. The SQL Server Notification Service is a mechanism for generating data-driven notifications, which sent to the Notification Service subscriber. A customer registers for a particular event or transaction (which is registered on the database server as a trigger); when the event occurs, the Notification Service may use one of three methods to send a message to the customer informing them about the occurrence of the event. This method includes SMTP, SOAP, or by writing to a file in the file system. The Notification Service was stopped by Microsoft with the launch of SQL Server 2008 in August 2008, and is no longer an officially supported component of the SQL Server database platform.
Integration Services
SQL Server Integration Services (SSIS) provides ETL capabilities for SQL Server for data import, data integration, and data warehousing needs. The Integration Services include GUI tools for building workflows such as extracting data from multiple sources, querying data, changing data - including aggregation, de-duplication, de-/normalization and merging data - and then exporting the converted data into destination databases or files.
Full Text Search Service
SQL Server Services Full Text Search is a dedicated indexing and search service for unstructured text stored in SQL Server databases. Full-text search index can be created in any column with character-based text data. This allows the words searched in the text field. Although it can be done with the SQL operator LIKE , using SQL Server Full Text Search service can be more efficient. Full allows for improper matching of source strings, indicated by a Rank value that can range from 0 to 1000 - higher rankings mean more accurate matches. It also allows linguistic matching ("inflexional search"), ie, the linguistic variant of a word (like a verb in a different tense) would also be appropriate for a given word (but with a lower rank than the exact same). Distance search is also supported, that is, if the searched words do not occur in the order specified in the request but close to each other, they are also considered suitable. T-SQL describes a special operator that can be used to access FTS capabilities.
The Full Text Search Engine is divided into two processes: the Filter Daemon ( msftefd.exe ) process and the Search ( msftesql.exe ). These processes interact with SQL Server. The Search process includes the indexer (which creates the full text index) and the full-text query processor. The indexer scans through text columns in the database. It can also index through binary columns, and use iFilters to extract meaningful text from binary blobs (for example, when Microsoft Word documents are stored as unstructured binary files in a database). The IFilters are hosted by the Daemon Filter process. Once the text is extracted, the Daemon Filter process breaks it into word order and passes it to the indexer. The indexer filters out sounds , that is, words like A , And etc., which are frequent and useless for search. With the remaining words, an inverted index is created, connecting each word with the found column. SQL Server itself includes a Collector component that monitors changes to the table and invokes the indexer in case of an update.
When the full text request is received by the SQL Server query processor, it is submitted to the FTS request processor in the search process. The FTS processor breaks queries into compiler words, filters out noise words, and uses inbuilt tests to find out the linguistic variants for each word. The words are then asked against the upside index and the precision ranking is calculated. The results are returned to the client through the SQL Server process.
SQLCMD
SQLCMD is a command-line application that comes with Microsoft SQL Server, and exposes SQL Server management features. This allows the SQL query to be written and executed from the command prompt. It can also act as a scripting language to create and run a set of SQL statements as scripts. The script is stored as a .sql file, and is used either for database management or for creating database schema during database deployment.
SQLCMD is introduced with SQL Server 2005 and it continues with SQL Server 2012, 2014 and 2016. Its predecessors for earlier versions are OSQL and ISQL, which are functionally equivalent due to TSQL execution, and many command line parameters are identical, although SQLCMD adds extra versatility.
Visual Studio
Microsoft Visual Studio includes native support for data programming with Microsoft SQL Server. It can be used to write and debug code to be executed by SQL CLR. It also includes a data designer that can be used to create, view, or edit graphics schemas graphically. Queries can be created visually or using code. SSMS 2008 and beyond, provides intellisense for SQL queries as well. SQL Server Management Studio
SQL Server Management Studio is a GUI tool that is included with SQL Server 2005 and later to configure, manage, and manage all the components in Microsoft SQL Server. These tools include script editors and graphical tools that work with server objects and features. SQL Server Management Studio replaces Enterprise Manager as the main management interface for Microsoft SQL Server since SQL Server 2005. The SQL Server Management Studio version is also available for SQL Server Express Edition, known as SQL Server Management Studio Express (SSMSE) ).
The main feature of SQL Server Management Studio is Object Explorer, which allows users to browse, select, and act on objects inside the server. This can be used to visually observe and analyze the query plan and to optimize database performance, among others. SQL Server Management Studio can also be used to create new databases, change existing database schema by adding or modifying tables and indexes, or analyzing performance. This includes a query window that provides a GUI-based interface for writing and executing queries.
SQL Server Server Operation
SQL Server Operations Studio (Preview) is a cross-platform request editor available as an optional download. This tool allows users to write questions; export query results; perform SQL scripts into the GIT repository and perform basic server diagnostics. SQL Server Operations Studio supports Windows, Mac, and Linux systems.
Business Development Intelligence Studio
Business Intelligence Development Studio (BIDS) is an IDE from Microsoft that is used to develop data analysis and Business Intelligence solutions utilizing Microsoft SQL Server Analysis Services, Reporting Services and Integration Services. It is based on the Microsoft Visual Studio development environment but is tailored to the extensions and specific types of SQL Server service projects, including tools, controls and projects for reports (using Reporting Services), Cube and data mining structures (using Analysis Services). For SQL Server 2012 and later, this IDE has been renamed to SQL Server Data Tools (SSDT).
See also
- Comparison of relational database management system
- Comparison of object-relational database management systems
- Comparison of data modeling tools
- List of relational database management systems
- SQL compliance
References
Further reading
External links
- Official website
- 2nd official website at Microsoft TechNet
- Beginner's guide to SQL Server 2012
Source of the article : Wikipedia
0 Comments