Translated by

Microsoft SQL Server

Product
Developers: Microsoft
Last Release Date: 2010/04/21
Branches: Information technologies
Technology: DBMS

Content

Microsoft SQL Server — a relational database management system (DBMS) developed by Microsoft corporation. The main used language of requests — Transact-SQL, is created jointly by Microsoft and Sybase. Transact-SQL is implementation of the ANSI/ISO standard on the structured language of requests (SQL) with expansions. It is used for work with small and average databases by the size to large databases of an enterprise scale; competes with other DBMS in this segment of the market.

SQL is the commonly accepted interface to databases. "All industrial bases — Oracle, the Microsoft SQL Server, PostgreSQL, MySQL — work for SQL.

History

The source code MS SQL Server (to version 7.0) was based on the Sybase SQL Server code, and it allowed Microsoft to enter the market of databases for the enterprises where Oracle, IBM, and, later, Sybase competed. Microsoft, Sybase and Ashton-Tate originally integrated for creation and release on the market of the first version of the program which received name SQL Server 1.0 for OS/2 (about 1989) which actually was an equivalent of Sybase SQL Server 3.0 for Unix of VMS, etc. by the Microsoft SQL Server 4.2 it was released in 1992 and was a part of the Microsoft OS/2 operating system of version 1.3. The official release of the Microsoft SQL Server of version 4.21 for Windows NT OS took place along with release of the Windows NT (version 3.1). A Microsoft SQL Server 6.0 was the first version of SQL Server created only for architecture of NT and without participation in Sybase development process.

By then, as entered the market of Windows NT, Sybase and Microsoft OS dispersed and followed own models of the software product and marketing schemes. Microsoft tried to obtain the exclusive rights to all versions of SQL Server for Windows. Later Sybase changed the name of the product to Adaptive Server Enterprise in order to avoid confusion with the Microsoft SQL Server. Till 1994 Microsoft received from Sybase three copyright notices as a hint on origin of the Microsoft SQL Server.

After separation of the company made several independent releases of programs. SQL Server 7.0 was the first database server with this user graphical interface of administration. For elimination of claims from Sybase in copyright violation, all inherited code in the seventh version was rewritten.

The version of SQL Server 2005 — was submitted in November, 2005. Start of the version happened parallel to start of the Visual Studio of 2005. There is also "cut down" version of the Microsoft SQL Server — Microsoft SQL Server Express; it is available to downloading and can be distributed for free together with the software using it.

From the moment of release of the previous version of SQL Server (SQL Server of 2000) development of integrated development environment and number of the additional subsystems which are a part of SQL Server of 2005 was performed. Changes concerned implementation of the ETL technology (extraction, conversion and data loading) which is a part of the SQL Server Integration Services (SSIS) component, the server of the notification, means of analytical processing of multidimensional data models (OLAP) and collecting of relevant information (both services are a part of Microsoft Analysis Services) and also several services of messages, namely Service Broker and Notification Services. In addition, improvements in performance were made.

Functionality

The Microsoft SQL Server as language of requests uses the version of SQL which received the name Transact-SQL (in abbreviated form T-SQL), SQL-92 which is implementation (ISO for SQL) with multiple expansions. T-SQL allows to use additional syntax for stored procedures and provides support of transactions (interaction of the database with the operating application). A Microsoft SQL Server and Sybase ASE for interaction with network use the protocol of level of the application under the name Tabular Data Stream (TDS, the transfer protocol of tabular data). The TDS protocol was also implemented in the FreeTDS project with the purpose to provide to different applications a possibility of interaction with the Microsoft SQL Server and Sybase databases.

The Microsoft SQL Server also supports Open Database Connectivity (ODBC) — an interaction interface of applications with DBMS. The version of SQL Server 2005 provides a connectivity of users through the web services using the SOAP protocol. It allows the client software programs which are not intended for Windows to connect to SQL Server krossplatformenno. Microsoft also released the certified JDBC driver allowing applications to connect under control of Java (such as BEA and IBM WebSphere) to the Microsoft SQL Server of 2000 and 2005.

SQL Server supports mirroring and a clustering of databases. The cluster of the SQL server is a set of equally configured servers; such scheme helps to distribute workload between several servers. All servers have one virtual name, and data are distributed to the IP addresses of machines of a cluster during an operating cycle. Also in case of refusal or failure on one of servers of a cluster automatic transfer of load of other server is available.

SQL Server supports excess duplication of data on three scenarios:

  • Picture: "Picture" of the database which the server sends to receivers is made.
  • Change history: All changes of the database are continuously transferred to users.
  • Synchronization with other servers: Databases of several servers are synchronized among themselves. Changes of all databases happen independently of each other on each server, and at synchronization there is a reconciliation of data. This type of duplication provides a possibility of resolution of conflicts between a DB.

Support of a. net Framework is built in SQL Server of 2005. Thanks to it, stored procedures of a DB can be written in any language of the .NET platform, using a full range of the libraries available to a. net Framework, including Common Type System (the system of the address with data types to Microsoft. net Framework). However, unlike other processes, a. net Framework, being a basic system for SQL Server 2005, selects an add-in memory and builds SQL Server management tools instead of using the built-in means of Windows. It increases performance in comparison with the general algorithms Windows as algorithms of resource allocation are specially configured for use in structures of SQL Server.

Microsoft developed the competitor of Oracle Exadata and SAP HANA

Microsoft developed new in-memory technology which will be shortly added to SQL Server. The technology received the name Hekaton. On November 7, 2012 ComputerWorld reported about it.

Microsoft in the aspiration to accelerate processes of online transaction processing (OLTP) added a feature to use relational database management systems in SQL Server.

It is planned that already in the next version of SQL Server the possibility of placement of a part of tables of databases or even all databases will be included in memories of the server. Also additional tools for the simplified start of technology will be added.

According to the general manager of Microsoft Doug Leland, the Hekaton technology is now tested by some customers. But does not announce more exact terms of its start yet.

Microsoft claims that the server will quicker execute transactions if necessary tables and databases are in memory, but are not written on a disk which it will be necessary to address. The giant is sure that the technology will allow to increase data processing rate to 50 times in comparison with the similar systems for SQL Server.

The main directions for use of Hekaton are bank online of a system, ERP, and other transaction systems which need to communicate and use databases quickly. The technology can be set on one server and further is scaled on other servers since it has no tight restrictions on the used memory.

Exit of Hekaton can become a serious headache for such companies as Oracle with its product Oracle Exadata and for SAP, in particular SAP HANA. It is caused by the fact that the technology considerably simplifies IT architecture and removes need to buy in addition components for data processing as it is implemented at competitors.

Doug Leland claims that Hekaton is not the first experience of Microsoft in work with in-memory technologies. So in the office application of Microsoft Excel the PowerPivot and Power View technologies allowing to manipulate quickly the large volume of data are used.

Microsoft also announced a fast exit of the next version of Data Warehouse Appliance, SQL Server of 2012 Parallel Data Warehouse (PDW). And for SQL Server of 2012 the service pack which in particular includes an opportunity to users of Exel 2013 to work with the data which are stored on SQL Server is released.

Application development

Microsoft and other companies make a large number of the software tools of development allowing to develop business applications using the Microsoft SQL Server databases. The Microsoft SQL Server of 2005 includes also Common Language Runtime (CLR) Microsoft .NET allowing to implement stored procedures and different functions to the applications developed in .NET platform languages (for example, VB.NET or C#). The previous versions of development tools of Microsoft used only API for receiving functional access to the Microsoft SQL Server.

SQL Server Express Edition

Microsoft SQL Server Express is free of charge distributed version of SQL Server, development of the MSDE system. This version has some technical restrictions. Such restrictions do it unsuitable for deployment of big databases, but it quite suits for maintaining software packages in scales of the small company. Contains full support of new data types, including XML-specifications. Actually, it is a full-fledged MS SQL Server, including all its components of programming, support of national alphabets and Unicode. Therefore it is used in applications, at design or for independent studying. There are no obstacles for further deployment of the saved-up database in the MS SQL Server of not express version. In 2007 Microsoft released the separate utility with the graphical interface for administration of this version which is also available to free downloading from the website of corporation.

Links

Microsoft SQL Server Home