Microsoft offers a number of techniques to store data redundantly.

It is possible to run certain redundancy technologies in parallel at the same time - for example, database replication together with an AlwaysOn Availability Group. The "Database Replication" continues to be divided into the sub-variants "Snapshot Replication", "Transactional Replication", "Merge Replication" and "Peer-To-Peer Replication".

It is now also possible to drop SQL Server data files into Azure and host a SQL Server database on a virtual machine in Azure. Encoding of backups during the backup process using AES 128, AES 192, AES 256 and Triple DES has been added.

Support for failover cluster instances has been improved.

Create business-critical intelligent applications

The new SQL Server 2016 delivers breakthrough business-critical in-memory performance, real-time operational analysis, deeper insights into your data with integrated advanced analytics and new rich visualizations on all mobile devices. It's the first database built in the cloud and sets new standards in innovation speed. This hybrid cloud platform helps you create solutions that enable customers to complement their existing investments on the ground.
editions

Depending on the version of Microsoft SQL Server, there are several editions of the product. The editions differ either in their functionality or the maximum hardware support. For example, higher level editions have access to more memory or more processors, providing more performance. The respective name of an edition indicates its intended location or purpose. For example, the most expensive version of SQL Server 2008 Datacenter Edition is used for large data centers, while the SQL Server Web Edition is designed specifically for web hosts or websites.

The following table lists an overview of various SQL Server versions and their available editions:

The protocol layer implements the external interface to SQL Server. All operations that can be invoked on SQL Server are transferred to it through a Microsoft-defined format called the Tabular Data Stream (TDS). TDS is an application layer protocol used to transfer data between a database server and a client. Originally designed and developed by Sybase Inc. for their Sybase SQL Server relational database engine in 1984, and later by Microsoft in Microsoft SQL Server, TDS packages can be embedded in other physical transport-dependent protocols, including TCP / IP, Named Pipes and shared memory. As a result, you can access SQL Server through these protocols. In addition, the SQL Server API is also available through Web Services.
data storage

Datastore is a database, which is a collection of tables with typed columns. SQL Server supports several types of data, including primitive types such as integer, float, decimal, char (including strings), varchar (variable-length strings), binary (for unstructured data blobs), text (for text data), and others. Rounding floating-point numbers to integers uses either symmetric arithmetic rounding or symmetric rounding (fix) depending on arguments: SELECT round (2.5, 0) returns 3.

You can also use Microsoft SQL Server to define and use custom composite types (UDTs). In addition, server statistics are exposed as virtual tables and views (Dynamic Management Views or DMVs). In addition to tables, a database can contain other objects, including views, stored procedures, indexes, and constraints, along with a transaction log. A SQL Server database can hold a maximum of 231 objects and can span multiple OS-level files with a maximum file size of 260 bytes (1 exabyte). The data in the database is stored in primary files with the extension .mdf. Secondary data files marked with the suffix .ndf allow the distribution of data from a single database to multiple files and optionally to multiple file systems. Log files are identified by the extension .ldf.

Memory allocated to a database is divided into sequentially numbered pages of 8 KB size each. One page is the base 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, free 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, assignment map, which contains information about how pages are associated with tables and indexes, changing map containing information about the changes since the last backup or logging have been made on other pages or contain large data types such as image or text. While the page is the basic unit of an I / O operation, the memory space is actually managed to an extent consisting of 8 pages. A database object can either contain all 8 pages in a uniform extent or a scope with up to 7 other objects (mixed extent). A row in a database table can not span more than one page and is therefore limited to 8 KB. However, if the data exceeds 8 KB and the line contains varchar or varbinary data, the data in these columns is moved to a new page (or possibly a sequence of pages, called an allocation unit) and replaced with a pointer to the data.

For physical storage of a table, its rows are divided into a series of partitions (numbered 1 to n). The partition size is user-defined; By default, all rows are in a single partition. A spreadsheet is split into multiple partitions to distribute a database across a computer cluster. Lines in each partition are stored either in the B-tree or in the heap structure. If the table contains an associated clustered index for quickly fetching rows, the rows are stored in order according to their index values, with a B-tree providing the index. The data resides in the leaf node of the leaves and other nodes store the index values ​​for the leaf data reachable from the respective nodes. If the index is not grouped, the rows are not sorted by the index keys. An indexed view has the same storage structure as an indexed table. A table without a clustered index is stored in a disorganized heap structure. However, the table can contain nonclustered indexes to allow for fast fetching of rows. In some situations, the heap structure has performance advantages over the cluster structure. Both heaps and b-trees may include multiple allocation units.

SQL Server buffers pages in RAM to minimize disk I / O. Each 8 KB page can be buffered in memory, and the amount of all currently buffered pages is called a buffer cache. The amount of memory available to SQL Server determines how many pages are cached in memory. The buffer cache is managed by the buffer manager. Either reading or writing to any page copies it to the buffer cache. Subsequent reads or writes are redirected to the in-memory copy, not to the on-disc version. The page is updated by the buffer manager on the disk only if the in-memory cache has not been referenced for some time. Writing pages back to disk uses asynchronous I / O, where the I / O operation is performed in a background thread, so other operations do not have to wait for the I / O operation to complete. Each page is written with its checksum when it is written. As the page is read, its checksum is recalculated and reconciled with the saved version to ensure that the page has not been damaged or tampered with in the meantime.
Concurrency and locks

SQL Server allows multiple clients to share the same database. Therefore, concurrent access to shared data must be controlled to ensure data integrity-when multiple clients update the same data or clients try to read data that is being modified by another client. SQL Server offers two modes of shared access control: pessimistic concurrency and optimistic concurrency. When pessimistic concurrency control is used, SQL Server controls concurrent access by using locks. Locks can be either shared or exclusive. The exclusive lock grants the user exclusive access to the data - no other user can access the data while the lock is held. Shared locks are used when reading data - multiple users can read data that is locked with a shared lock but does not receive an exclusive lock. The latter would have to wait until all released locks were released.

Locks can be applied to different levels of granularity - on whole tables, pages or even per row on tables. Indexes can be either the entire index or index sheets. The granularity to use is set by the database administrator for each database. A finer granular locking system allows more users to use the table or index concurrently, but requires more resources and therefore does not automatically result in higher performance. SQL Server also includes two easier mutually exclusive solutions-latches and spinlocks-that are less robust than locks, but less resource-intensive. SQL Server uses them for DMVs and other resources that are normally underutilized. SQL Server also monitors all worker threads that receive locks to ensure that they do not end in deadlocks. In this case, SQL Server takes remedial action, which in many cases terminates one of the threads involved in a deadlock and resets the transaction that started it. To implement the lock, SQL Server includes the lock manager. The lock manager maintains an in-memory table that manages the database objects and locks them along with other metadata about the lock, if necessary. Access to any shared object is mediated by the lock manager, which either grants access to the resource or blocks it.

SQL Server also provides the optimistic concurrency control mechanism, which is similar to the Multiversions Concurrency control used in other databases. The mechanism allows to generate a new version of a row whenever the row is updated, as opposed to overwriting the row, d. H. A line is additionally identified by the ID of the transaction that created the version of the line. Both the old and new versions of the line are saved and managed, even though the old versions are moved from the database to a system database named Tempdb. When a row is being updated, all other requests are not blocked (as opposed to locking), but executed in the older version of the row. If the other request is an update statement, there are two different versions of the rows. Both are stored by the database and identified by the appropriate transaction IDs.

The main mode for retrieving data from a SQL Server database asks for. The query is expressed using a variant of SQL called T-SQL, a dialect shared by Microsoft SQL Server because of its legacy with Sybase SQL Server. The query declaratively indicates what should be retrieved. It is processed by the query processor, which determines the sequence of steps required to retrieve the requested data. The order of the actions required to run a query is called a query plan. There are several ways to process the same query. For a query that includes a join statement and a select statement, performing joins in both tables and then performing selects on the results results in the same result as selecting from each table, and then executing the join to another execution plans. In this case, SQL Server selects the plan that is expected to deliver 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 attempts to optimize the cost of the resources needed to run the query. During a query, the query optimizer checks the database schema, database statistics, and system load at that time. It then decides which sequence accesses the tables named in the query, which sequence executes the operations, and which access method is used to access the tables. For example, if the table has an associated index, the index should or should not be used: If the index is in a column that is ambiguous for most columns (low selectivity), then the index may not be useful for the index access to the data. Finally, it decides whether the query is executed at the same time or not. While concurrent execution is more expensive in terms of total processor time, because it is actually split among different processors, execution may mean that it executes faster. Once a query plan has been generated for a query, it is temporarily cached. For further calls to the same query, the cached plan is used. Unused plans are discarded after some time.

SQL Server also allows the definition of stored procedures. Stored procedures are parameterized T-SQL queries that are stored in the server itself (rather than being issued by the client application as in general queries). Stored procedures can accept values ​​that the client sends as input parameters and return results as output parameters. You can call defined functions and other stored procedures, including the same stored procedure (up to a specified number of times). You can selectively get access. Unlike other queries, stored procedures have an associated name that resolves to the actual queries at run time. Also, because the code does not need to be sent by the client every time (because it can be called by name), it reduces network traffic and improves performance a bit. Stored procedure execution plans are also cached as needed.

T-SQL (Transact-SQL) is the secondary tool for programming and managing SQL Server. It provides keywords for the operations that can be performed on SQL Server. These include creating and modifying database schemas, entering and editing data in the database, and monitoring and managing the server itself. Client applications that consume data or manage the server use SQL Server functionality by using T-SQL. Send queries and statements, which are then processed by the server and return results (or errors) to the client application. SQL Server enables management with T-SQL. It exposes read-only tables from which server statistics can be read. The management functionality is exposed through system-defined stored procedures that can be called from T-SQL queries to perform the management operation. It is also possible to create connected servers with T-SQL. Linked servers allow a single query to handle operations that run on multiple servers.
Native SQL Server client

SQL Server Native Client is the native client-side data access library for Microsoft SQL Server 2005 or later. It natively implements support for the SQL Server features including the implementation of the tabular data stream, support for mirrored SQL Server databases, full support for all SQL Server supported Data types, asynchronous operations, query notifications, encryption support, and receiving multiple result sets in a single database session. SQL Server Native Client is used by SQL Server plug-ins for other data access technologies such as ADO or OLE DB. The SQL Server Native Client can also be used directly, bypassing the generic data access levels.

 In 2011, a preview of the SQL Server ODBC driver for Linux was released.

Microsoft SQL Server 2005 includes a component called SQL Common Language Runtime (CLR) that integrates with the .NET Framework. Unlike most other applications that use the .NET Framework, SQL Server itself hosts the .NET Framework runtime; H. .NET Framework storage, thread, and resource management requirements are met by SQLOS itself, not the underlying Windows operating system. SQLOS provides .NET code deadlock discovery and resolution services. With SQL CLR, stored procedures and triggers can be written in any .NET managed language, including C # and VB.NET. Managed code can also be used to define UDTs (user-defined types) that can persist in the database. The managed code is compiled in CLI assemblies and registered in the database after checking for type safety. After that they can be called like any other procedure. However, only a subset of the base class library is available when executing code under SQL CLR. Most APIs for UI functionality are not available.

When you write code for SQL CLR, you can access data stored in SQL Server databases by using the ADO.NET APIs just like any other managed application that accesses SQL Server data. However, this creates a new database session that is different from the one in which the code is executed. To avoid this, SQL Server provides the ADO.NET provider with some enhancements that allow the connection to be redirected to the same session that already hosts the running code. Such connections are called context connections and are set to true by setting the context connection parameter in the connection string. SQL Server provides further enhancements to the ADO.NET API, including classes for working with table data or a single row of data, as well as classes for working with internal metadata about the data stored in the database. It also provides access to XML functions in SQL Server, including XQuery support. These improvements are also available in T-SQL procedures as a result of the introduction of the new XML data type (Query, Value, Node Functions).

[Translate to Englisch:]