Microsoft SQL Server

Microsoft SQL Server is a relational database management system (RDBMS) developed by Microsoft Corporation. The main query language used is Transact-SQL, developed jointly by Microsoft and Sybase. Transact-SQL is an implementation of the ANSI/ISO standard for structured query language (SQL) with extensions. It is used to work with small and medium-sized databases up to large enterprise scale databases; it competes with other DBMS in this market segment.

SQL is a common interface to databases. “All industrial databases – Oracle, Microsoft SQL Server, PostgreSQL, MySQL – work on SQL.

History MS SQL Server

MS SQL Server source code (before version 7.0) was based on Sybase SQL Server code, and this allowed Microsoft to enter the database market for enterprises, where Oracle, IBM, and later Sybase itself competed.

Microsoft, Sybase and Ashton-Tate initially joined forces to create and market the first version of the program called SQL Server 1.0 for OS/2 (around 1989), which was actually the equivalent of Sybase SQL Server 3.0 for Unix, VMS and others. Microsoft SQL Server 4.2 was released in 1992 and was part of Microsoft OS/2 operating system version 1.3.

The official release of Microsoft SQL Server version 4.21 for Windows NT took place simultaneously with the release of Windows NT itself (version 3.1). Microsoft SQL Server 6.0 was the first version of SQL Server created exclusively for NT architecture and without participation in Sybase development process.

By the time Windows NT was released, Sybase and Microsoft had separated and followed their own software product models and marketing schemes. Microsoft was seeking exclusive rights to all versions of SQL Server for Windows. Later, Sybase changed its product name to Adaptive Server Enterprise to avoid confusion with Microsoft SQL Server. Until 1994, Microsoft received three copyright notices from Sybase as a hint of Microsoft SQL Server origin.

After the split, the company made several independent software releases. SQL Server 7.0 was the first database server with a true graphical user interface for administration. To eliminate claims of copyright infringement by Sybase, all inherited code in the seventh version was rewritten.

SQL Server 2005 version – was introduced in November 2005. The version was launched in parallel with the launch of Visual Studio 2005. There is also a “reduced” version of Microsoft SQL Server – Microsoft SQL Server Express; it is available for download and can be distributed for free along with the software that uses it.

Since the release of the previous version of SQL Server (SQL Server 2000), the integrated development environment and a number of additional subsystems included in SQL Server 2005 were developed.

Changes have affected the implementation of ETL (Extract, Convert and Download Data) technology, which is part of the SQL Server Integration Services (SSIS) component, warning server, tools for the analytical processing of multidimensional data models (OLAP) and collection of relevant information (both services are part of Microsoft Analysis Services), as well as several message services, namely Service Broker and Notification Services. In addition, performance improvements have been made.

Microsoft SQL Server functionality

Microsoft SQL Server uses a query language version of SQL called Transact-SQL (short for T-SQL), which is an implementation of SQL-92 (ISO standard for SQL) with multiple extensions. T-SQL allows to use additional syntax for stored procedures and provides transaction support (database interaction with control application).

Microsoft SQL Server and Sybase ASE use an application level protocol called Tabular Data Stream (TDS, Tabular Data Transfer Protocol) for interaction with the network. TDS protocol was also implemented in FreeTDS project in order to provide various applications with the possibility of interaction with Microsoft SQL Server and Sybase databases.

Microsoft SQL Server also supports Open Database Connectivity (ODBC) – an interface for interaction of applications with DBMS. The SQL Server 2005 version provides the ability to connect users through web services that use the SOAP protocol. This allows client programs that are not designed for Windows to connect cross-platform to SQL Server. Microsoft has also released a certified JDBC driver that allows Java-based applications (such as BEA and IBM WebSphere) to connect to Microsoft SQL Server 2000 and 2005.

SQL Server supports database mirroring and clustering. SQL Server Cluster is a set of identically configured servers; this scheme helps to distribute the workload between several servers. All servers have the same virtual name, and data is distributed to the IP addresses of the cluster machines during the work cycle. Also, in the event of a failure or failure on one of the cluster servers, automatic load transfer to another server is available.

SQL Server supports redundant data replication in three scenarios:

Snapshot: A “snapshot” of the database is produced, which the server sends to recipients.
Change history: All database changes are continuously transferred to users.

Synchronization with other servers: Databases of multiple servers are synchronized with each other. Changes to all databases are made independently of each other on each server, and during synchronization data are reconciled. This type of duplication provides the possibility of resolving contradictions between databases.
SQL Server 2005 has built-in support for the .NET Framework.

Due to this, stored database procedures can be written in any .NET platform language, using the full set of libraries available for the .NET Framework, including Common Type System (data type handling system in Microsoft .NET Framework). However, unlike other processes, the .NET Framework, as the base system for SQL Server 2005, allocates additional memory and builds SQL Server management tools instead of using the built-in Windows tools.

This improves performance compared to the general Windows algorithms, as the resource allocation algorithms are specifically configured for use in SQL Server structures.

Microsoft has developed a competitor for Oracle Exadata and SAP HANA

Microsoft has developed a new technology in-memory, which will soon be added to SQL Server. The technology is called Hekaton. This was announced by ComputerWorld on November 7, 2012.

Microsoft, in an effort to speed up transaction processing (OLTP), has added to SQL Server the ability to use relational database management systems.

It is planned that the next version of SQL Server will include the ability to place part of the database tables or even all databases in the server memory. Also, additional tools will be added to simplify the technology launch.

According to Microsoft General Manager Doug Leland, Hekaton technology is currently being tested by some customers. But no more precise timing has been announced yet.

Microsoft says the server will perform operations faster if the necessary tables and databases are stored in memory rather than written to the disk to which they need to access. The giant is confident that the technology will increase the speed of data processing up to 50 times compared to similar systems for SQL Server.

The main directions for using Hekaton are online banking systems, ERP, and other transaction systems that need to communicate and use databases quickly. The technology can be installed on one server and further scaled to other servers, as it has no strict limitations on the memory used.

Hekaton’s exit could be a serious headache for companies like Oracle with its Oracle Exadata product and for SAP, in particular SAP HANA. This is because the technology greatly simplifies the IT architecture and eliminates the need to purchase data processing components as competitors do.

Doug Leland says that Hekaton is not Microsoft’s first experience with in-memory technology. For example, the Microsoft Excel office application uses PowerPivot and Power View technologies to quickly manipulate large amounts of data.

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

Application development

Microsoft and other companies produce a large number of software development tools to develop business applications using Microsoft SQL Server databases. Microsoft SQL Server 2005 also includes Microsoft .NET Common Language Runtime (CLR), which allows implementing stored procedures and various functions for applications developed in the .NET platform languages (e.g. VB.NET or C#). Previous versions of Microsoft development tools used only the API to gain functional access to Microsoft SQL Server.

SQL Server Express Edition

Microsoft SQL Server Express is a free version of SQL Server, a development of MSDE system. This version has some technical limitations. These limitations make it unsuitable for the deployment of large databases, but it is quite suitable for the maintenance of software systems on a small company scale.

Contains full support for new data types, including XML-specifications. Actually, it is full MS SQL Server, including all its programming components, support of national alphabets and Unicode. Therefore it is used in applications, at designing or for independent studying.

There are no obstacles for the further deployment of the saved up database on MS SQL Server of the non-express version. In 2007, Microsoft released a separate utility with a graphical interface for administration of this version, which is also available for free download from the corporate website.