Microsoft SQL Server for Linux: a bridge between the worlds of Linux and Windows
The Microsoft SQL Server relational database management system has a long history - the product idea originated in the mid-80s, and the first version appeared in 1988. It was based on the Transact-SQL query language, created jointly by Microsoft and Sybase. The strategy for the further development of Microsoft SQL Server took on a solid and complete look in 2010. Then it was announced that SQL Server would be a single product implemented in desktop systems, data centers and in the cloud (in 32-bit and 64-bit versions).
Priority areas include business intelligence (BI) and the development of appropriate tools, the development of a cloud computing ecosystem with the transfer of business intelligence tools to the cloud, and the enhancement of the capabilities of SQL Server Management Studio with SQL Azure. Considerable attention was paid to DBMS scaling, application virtualization in the database environment, as well as the spatial representation of data.
Evolution of Microsoft SQL Server . In recent versions, developers have focused on in-memory data processing and working with big data.
')
As an advantage of the Microsoft DBMS, customers note the ease of implementation, management, programming, and updating.
2016 was the year of another version change - transition from Microsoft SQL Server 2005 to Microsoft SQL Server 2016. Its new features and improvements provide higher performance, enhanced security, and full-fledged integrated reporting and analytics capabilities. This is not a radical “milestone”, but experts call Microsoft SQL Server 2016 the most significant update in the entire history of the product. It includes advanced analytics tools, machine learning, as well as new opportunities for analyzing and visualizing information on any devices.
The new version of Microsoft SQL Server allows you to create mission-critical transaction processing (OLTP) applications with improved scalability, in-memory performance and high availability. This ensures consistency of the local and cloud environments: SQL Server allows clients to access data on local servers and in the cloud.
Here are some key features of Microsoft SQL Server 2016:
With SQL Server 2016, you can get valuable information from real-time transactional and analytical data. The product is positioned as a secure and scalable database platform that provides everything you need - from advanced analytics to high performance processing of any loads in RAM.
Real-time analytics in real time allows you to make decisions faster.
SQL Server 2016 is much faster than previous versions: on the same hardware, requests are executed about 25% faster, and with some new SQL Server 2016 tools with memory processing, the gain reaches 30 times for OLTP transactions and 100 times for queries (Microsoft data ). However, the hardware requirements in the new version have grown.
Functional differences between different editions of SQL Server 2016.
(red highlights new features missing in SQL Server 2014).
In recent years, Microsoft has expanded the range of its proposals. Along with SQL Server, its arsenal also includes the Azure SQL Database (“DBMS as a service”) and two cloud-based no SQL solutions — Azure DocumentDB and Azure Tables. In 2016, Microsoft made another important announcement - introduced SQL Server for Linux. Its data management and business intelligence platform has become even more versatile, expanding the possibilities for working with data and applications using different tools, languages ​​and systems in a cloud, hybrid or local environment. This is another step towards simplifying SQL Server and increasing its availability. The release of this version is expected in mid-2017.
SQL Server for Linux , based on SQL Server 2016, provides the ability to develop and deploy intelligent applications on a single platform for data management and business intelligence. Microsoft’s announced support for the Linux family of operating systems in the new version of SQL Server is the corporation’s next step in entering the Linux market after a partnership agreement with RedHat and Canonical.
With the release of SQL Server for Linux, developers will have a wide choice of platform for applications, and virtual server users ( VPS ) will be able to deploy SQL Server not only under Windows. SQL Server applications can be run in Docker containers.
In particular, some customers are already using Azure Data Lake services on Ubuntu. Now developers can create new applications with all the features of SQL Server. And collaboration with Red Hat means migrating SQL Server to the Red Hat Enterprise platform: customers will get more advanced OS selection options. The site SQL Server also has information about the new features of the database.
In March 2016, a preliminary trial version of SQL Server for Linux was released, containing the basic functions of a relational database management system. As expected, the Linux version of SQL Server will have the performance level required for mission-critical tasks, a low TCO (Total Cost of Ownership), a high level of security and support for hybrid cloud technologies, including as Stretch Database.
Microsoft approached the adoption of open source in 2008, when the corporation joined the Apache Software Foundation. In 2009, she made one of the largest contributions to the development of Linux through code that provides virtualization based on the Hyper-V hypervisor.
Since 2014, Microsoft has become even more active in supporting the open source software community. The code of the .Net platform, C # and Visual Basic compilers, Edge browser and some other products has been fully or partially open. Microsoft became a platinum member of the Linux Foundation along with Fujitsu, HP, Cisco, Huawei, IBM, Intel, Samsung, NEC, Qualcomm and Oracle. Today, she participates in many Open Source projects run by the Linux Foundation, including CII, node.js Foundation, OpenAPI, and OpenDaylight.
Therefore, support for the Linux kernel did not come as a surprise to the industry. Microsoft has not only demonstrated once again that it plays an important role in the open source movement. The release of SQL Server for Linux is aimed at expanding the market: the corporation does not want to lose the 15% of the global DBMS market, which is occupied by Oracle-products of Oracle and IBM. According to IDC, Microsoft owns more than half of the global market for DBMS for Windows. However, on other platforms, Oracle leads, with its share approaching 50%.
According to analysts, the Microsoft solution can be chosen not only because of its attractive price, but also because it contains a number of built-in features that are not available in competing products or are offered for an additional fee, and are compatible with Microsoft Excel .
Microsoft announced that customers will be able to purchase licenses for SQL Server free of charge, provided that they decide to abandon the Oracle DBMS. “Stop depending on Oracle. Transfer your Oracle databases to SQL Server and get the appropriate temporary SQL Server licenses for free with Software Assurance, ”urges Microsoft on its website. The corporation even developed an online training: "Transforming Business by Moving from Oracle to SQL Server."
Canonical has already made SQL Server for Linux available in Ubuntu OS images that users install in Microsoft Azure. This allows them to flexibly use computing resources and inexpensive disk storage. This option dramatically reduces the overall cost of the platform. In addition, in cloud environments, resources are optimized for increased workloads. Using Docker and Canonical orchestration tools, you can flexibly increase performance in accordance with the load. Azure and container technologies in the Linux environment allow you to implement complex and high-loaded projects without buying additional equipment.
According to a survey of database administrators, Microsoft SQL Server is already used in most organizations.
The release of SQL Server for Linux means that Microsoft is becoming a provider of cross-platform solutions. Earlier, Microsoft has already moved its Office and Office 365 to iOS and Android platforms. Products like Microsoft Intune and Azure AD also support multiple devices.
The Microsoft project, codenamed Helsinki, involves migrating SQL Server to several Linux distributions, including Ubuntu, Red Hat Enterprise Linux, and SUSE Linux Enterprise Server. This logically complements the corporate cloud strategy - IaaS VM support in Microsoft Azure for various Linux distributions (CentOS, openSUSE, Oracle Linux, SUSE Linux Enterprise Server, Red Hat Enterprise Linux and Ubuntu). When purchasing SQL Server licenses (per server or processor core), the customer will be able to use the same license in Windows Server and Linux. Under the Software Assurance program, you can receive future product versions for free.
A competitive analysis of the RDBMS market over the past 30 years shows strong growth in Microsoft SQL Server, especially after the release of SQL Server 2000. With Linux support, this growth could be even greater. Currently, Linux, software virtualization, containerization, orchestration, application and communication environments with open source play an important role in the public cloud. In addition, according to IDC, almost 40% of x86 servers are sold with Linux OS and a third of them are used as database servers. Of course, Microsoft SQL Server will not become an open source product, but by offering it as a component of the Open Source stack, Microsoft will significantly increase the number of installations. This is a serious challenge for Oracle and IBM DB2 - also a proprietary database for open source environments.
The next version of SQL Server, codenamed SQL Server v.Next, will provide the Linux ecosystem with Microsoft DBMS, including SQL Server Agent, Active Directory authentication, high availability and disaster recovery, data security and protection. A preliminary version of SQL Server for Linux is already available for Red Hat Enterprise Linux (RHEL), SUSE Linux Enterprise Server (SLES), Ubuntu, as well as cloud and container platforms such as OpenStack, Docker Swarm, Kubernetes, and Mesosphere D / C OS.
Meet: SQL Server for Linux . All major SQL Server 2016 assets will be included.
SQL Server for Linux also includes SQL Server 2016 tools such as storing columns in memory, which improves performance when running queries up to 10 times, In-Memory OLTP, which increases the speed compared to storing tables on a disk up to 2.5 times. And Row-Level Security and Dynamic Data Masking protect data on the server from unauthorized access without making changes to client applications.
When installing SQL Server 2016, standard Linux methods are used: yum for Fedora distributions and apt-get for Debian. It supports systemd launch, Linux file paths in T-SQL statements and scripts. High-availability clusters can be managed using popular Linux tools like Pacemaker and Corosync.
Microsoft also offers cross-platform tools for SQL Server on Linux or Windows, such as SQL Server Management Studio (SSMS), SQL Server Data Tools (SSDT), PowerShell (sqlps), and the recently announced Visual Studio Code Extension for SQL Server. Microsoft Migration Assistant tools for transferring workloads are also supported. Using cross-platform tools, organizations can now start migrating to SQL Server for Linux, and in 2017 switch to the commercial version of v.Next.
Continuous integration and delivery (CI / CD) - DevOps practice, which accelerates the introduction of corrections and changes, allows you to release products to the market, to improve their quality and reliability.
With support for containers in Windows and Linux, SQL Server will run Docker Swarm, Red Hat Open Shift, Mesosphere DC / OS and Kubernetes orchestrators. With Management Pack for SQL Server for Linux, organizations can use System Center Operations Manager for comprehensive monitoring — from hardware to database instances.
Since SQL Server is now available for containers, you can use some DevOps practices in application development, for example, creating an image of a container for use in different operating systems. Using Docker Swarm or Red Hat Open Shift orchestrators, you can quickly update containers in test and production environments.
With the release of SQL Server for Linux, you can use databases in a heterogeneous environment — both on your own servers and in virtual machines in private / public clouds and with VPS / VDS service providers .
Priority areas include business intelligence (BI) and the development of appropriate tools, the development of a cloud computing ecosystem with the transfer of business intelligence tools to the cloud, and the enhancement of the capabilities of SQL Server Management Studio with SQL Azure. Considerable attention was paid to DBMS scaling, application virtualization in the database environment, as well as the spatial representation of data.
Evolution of Microsoft SQL Server . In recent versions, developers have focused on in-memory data processing and working with big data.
')
As an advantage of the Microsoft DBMS, customers note the ease of implementation, management, programming, and updating.
Microsoft SQL Server 2016
2016 was the year of another version change - transition from Microsoft SQL Server 2005 to Microsoft SQL Server 2016. Its new features and improvements provide higher performance, enhanced security, and full-fledged integrated reporting and analytics capabilities. This is not a radical “milestone”, but experts call Microsoft SQL Server 2016 the most significant update in the entire history of the product. It includes advanced analytics tools, machine learning, as well as new opportunities for analyzing and visualizing information on any devices.
The new version of Microsoft SQL Server allows you to create mission-critical transaction processing (OLTP) applications with improved scalability, in-memory performance and high availability. This ensures consistency of the local and cloud environments: SQL Server allows clients to access data on local servers and in the cloud.
Here are some key features of Microsoft SQL Server 2016:
- The new technology of permanent encryption (Always Encrypted) protects data during storage and movement without sacrificing database performance. Security is especially important when transferring data to the cloud, and constant encryption is designed to solve this problem.
- Stretch Database technology allows you to dynamically transfer “hot” and “cold” data from SQL Server to the Azure cloud. What is it for? In particular, as historical data is accumulated in the database, its complexity and cost of operation increase. Automatic transfer of archived data to the cloud as they become obsolete is a reasonable solution. Such data is moved in the background. This does not affect applications and requests.
- The Row Level Security (RLS) feature allows administrators to control and control user access to certain data. This is especially useful in situations where detailed access control is required, for example, in some government agencies or medical institutions.
- Local and cloud consistency . Regardless of where the data is located — in the corporate data center, in the private cloud or in the Microsoft Azure cloud — there is consistent work for both the server and the database provided as a service. Hybrid cloud support makes it possible to distribute the load between the local and cloud environments. This means savings and flexibility.
- High performance Data Warehousing - # 1, # 2, and # 3 in the TPC-H 10 Terabyte test for non-clustered environments and # 1 in the SAP SD Two-Tier test in Windows.
- Structured and unstructured data support: PolyBase queries for Hadoop data. You can query relational and non-relational data using simple T-SQL using PolyBase. In this case, a separate Microsoft SQL Server 2016 Enterprise Edition is used as the head node, and scaling is performed using several Standard Edition computing nodes.
- Comprehensive mobile business analytics helps transform data into useful information, getting valuable information on any device (both with and without Internet connection) in the form of reports. Over 250 built-in analytic functions are offered.
- Advanced analytics in the database allows you to analyze operational data in real time and with the required scale directly in the SQL Server database. This eliminates the need to move them. The announced Microsoft R Server uses technologies acquired by Revolution Analytics to support Hadoop and Teradata.
With SQL Server 2016, you can get valuable information from real-time transactional and analytical data. The product is positioned as a secure and scalable database platform that provides everything you need - from advanced analytics to high performance processing of any loads in RAM.
Real-time analytics in real time allows you to make decisions faster.
SQL Server 2016 is much faster than previous versions: on the same hardware, requests are executed about 25% faster, and with some new SQL Server 2016 tools with memory processing, the gain reaches 30 times for OLTP transactions and 100 times for queries (Microsoft data ). However, the hardware requirements in the new version have grown.
Functional differences between different editions of SQL Server 2016.
SQL Server 2016 functionality
| Performance
| Perform OLTP in memory
|
| Storing columns in memory
| |
| Real-time operational analytics
| |
| Resource governor
| |
| Query storage
| |
| Availability
| Alwayson
|
| Extended visualization support and live migration
| |
| Security
| Permanent encryption
|
| Transparent data encryption
| |
| Row level security
| |
| Dynamic data masking
| |
| Backup encryption support
| |
| Detailed audit
| |
| Segregation of duties
| |
| Programmability
| JSON support
|
| PolyBase queries for Hadoop data
| |
| Temporal
| |
| Ready for use in the cloud
| Stretch Database
|
| Archiving to Azure
| |
| Azure Disaster Recovery
| |
| Optimized virtual machine images in the Azure collection
| |
| Control
| Distributed playback
|
| Policy based management
| |
| Business analytics
| Advanced Reports
|
| Mobile business analyst
| |
| Integration Services Managed as a Server
| |
| Pin reports to Power BI
| |
| Multidimensional semantic models
| |
| Improved tabular semantic business intelligence models
| |
| Master Data Services
| |
| Data Quality Services
| |
| Advanced Analytics
| Extended database analytics using R Services
|
| Multi-threaded R request processing and in-memory stream processing
|
In recent years, Microsoft has expanded the range of its proposals. Along with SQL Server, its arsenal also includes the Azure SQL Database (“DBMS as a service”) and two cloud-based no SQL solutions — Azure DocumentDB and Azure Tables. In 2016, Microsoft made another important announcement - introduced SQL Server for Linux. Its data management and business intelligence platform has become even more versatile, expanding the possibilities for working with data and applications using different tools, languages ​​and systems in a cloud, hybrid or local environment. This is another step towards simplifying SQL Server and increasing its availability. The release of this version is expected in mid-2017.
Meet SQL Server for Linux
SQL Server for Linux , based on SQL Server 2016, provides the ability to develop and deploy intelligent applications on a single platform for data management and business intelligence. Microsoft’s announced support for the Linux family of operating systems in the new version of SQL Server is the corporation’s next step in entering the Linux market after a partnership agreement with RedHat and Canonical.
With the release of SQL Server for Linux, developers will have a wide choice of platform for applications, and virtual server users ( VPS ) will be able to deploy SQL Server not only under Windows. SQL Server applications can be run in Docker containers.
In particular, some customers are already using Azure Data Lake services on Ubuntu. Now developers can create new applications with all the features of SQL Server. And collaboration with Red Hat means migrating SQL Server to the Red Hat Enterprise platform: customers will get more advanced OS selection options. The site SQL Server also has information about the new features of the database.
In March 2016, a preliminary trial version of SQL Server for Linux was released, containing the basic functions of a relational database management system. As expected, the Linux version of SQL Server will have the performance level required for mission-critical tasks, a low TCO (Total Cost of Ownership), a high level of security and support for hybrid cloud technologies, including as Stretch Database.
Microsoft approached the adoption of open source in 2008, when the corporation joined the Apache Software Foundation. In 2009, she made one of the largest contributions to the development of Linux through code that provides virtualization based on the Hyper-V hypervisor.
Since 2014, Microsoft has become even more active in supporting the open source software community. The code of the .Net platform, C # and Visual Basic compilers, Edge browser and some other products has been fully or partially open. Microsoft became a platinum member of the Linux Foundation along with Fujitsu, HP, Cisco, Huawei, IBM, Intel, Samsung, NEC, Qualcomm and Oracle. Today, she participates in many Open Source projects run by the Linux Foundation, including CII, node.js Foundation, OpenAPI, and OpenDaylight.
Therefore, support for the Linux kernel did not come as a surprise to the industry. Microsoft has not only demonstrated once again that it plays an important role in the open source movement. The release of SQL Server for Linux is aimed at expanding the market: the corporation does not want to lose the 15% of the global DBMS market, which is occupied by Oracle-products of Oracle and IBM. According to IDC, Microsoft owns more than half of the global market for DBMS for Windows. However, on other platforms, Oracle leads, with its share approaching 50%.
According to analysts, the Microsoft solution can be chosen not only because of its attractive price, but also because it contains a number of built-in features that are not available in competing products or are offered for an additional fee, and are compatible with Microsoft Excel .
Microsoft announced that customers will be able to purchase licenses for SQL Server free of charge, provided that they decide to abandon the Oracle DBMS. “Stop depending on Oracle. Transfer your Oracle databases to SQL Server and get the appropriate temporary SQL Server licenses for free with Software Assurance, ”urges Microsoft on its website. The corporation even developed an online training: "Transforming Business by Moving from Oracle to SQL Server."
Canonical has already made SQL Server for Linux available in Ubuntu OS images that users install in Microsoft Azure. This allows them to flexibly use computing resources and inexpensive disk storage. This option dramatically reduces the overall cost of the platform. In addition, in cloud environments, resources are optimized for increased workloads. Using Docker and Canonical orchestration tools, you can flexibly increase performance in accordance with the load. Azure and container technologies in the Linux environment allow you to implement complex and high-loaded projects without buying additional equipment.
According to a survey of database administrators, Microsoft SQL Server is already used in most organizations.
The release of SQL Server for Linux means that Microsoft is becoming a provider of cross-platform solutions. Earlier, Microsoft has already moved its Office and Office 365 to iOS and Android platforms. Products like Microsoft Intune and Azure AD also support multiple devices.
The Microsoft project, codenamed Helsinki, involves migrating SQL Server to several Linux distributions, including Ubuntu, Red Hat Enterprise Linux, and SUSE Linux Enterprise Server. This logically complements the corporate cloud strategy - IaaS VM support in Microsoft Azure for various Linux distributions (CentOS, openSUSE, Oracle Linux, SUSE Linux Enterprise Server, Red Hat Enterprise Linux and Ubuntu). When purchasing SQL Server licenses (per server or processor core), the customer will be able to use the same license in Windows Server and Linux. Under the Software Assurance program, you can receive future product versions for free.
A competitive analysis of the RDBMS market over the past 30 years shows strong growth in Microsoft SQL Server, especially after the release of SQL Server 2000. With Linux support, this growth could be even greater. Currently, Linux, software virtualization, containerization, orchestration, application and communication environments with open source play an important role in the public cloud. In addition, according to IDC, almost 40% of x86 servers are sold with Linux OS and a third of them are used as database servers. Of course, Microsoft SQL Server will not become an open source product, but by offering it as a component of the Open Source stack, Microsoft will significantly increase the number of installations. This is a serious challenge for Oracle and IBM DB2 - also a proprietary database for open source environments.
What will it be?
The next version of SQL Server, codenamed SQL Server v.Next, will provide the Linux ecosystem with Microsoft DBMS, including SQL Server Agent, Active Directory authentication, high availability and disaster recovery, data security and protection. A preliminary version of SQL Server for Linux is already available for Red Hat Enterprise Linux (RHEL), SUSE Linux Enterprise Server (SLES), Ubuntu, as well as cloud and container platforms such as OpenStack, Docker Swarm, Kubernetes, and Mesosphere D / C OS.
Meet: SQL Server for Linux . All major SQL Server 2016 assets will be included.
SQL Server for Linux also includes SQL Server 2016 tools such as storing columns in memory, which improves performance when running queries up to 10 times, In-Memory OLTP, which increases the speed compared to storing tables on a disk up to 2.5 times. And Row-Level Security and Dynamic Data Masking protect data on the server from unauthorized access without making changes to client applications.
When installing SQL Server 2016, standard Linux methods are used: yum for Fedora distributions and apt-get for Debian. It supports systemd launch, Linux file paths in T-SQL statements and scripts. High-availability clusters can be managed using popular Linux tools like Pacemaker and Corosync.
Microsoft also offers cross-platform tools for SQL Server on Linux or Windows, such as SQL Server Management Studio (SSMS), SQL Server Data Tools (SSDT), PowerShell (sqlps), and the recently announced Visual Studio Code Extension for SQL Server. Microsoft Migration Assistant tools for transferring workloads are also supported. Using cross-platform tools, organizations can now start migrating to SQL Server for Linux, and in 2017 switch to the commercial version of v.Next.
Continuous integration and delivery (CI / CD) - DevOps practice, which accelerates the introduction of corrections and changes, allows you to release products to the market, to improve their quality and reliability.
With support for containers in Windows and Linux, SQL Server will run Docker Swarm, Red Hat Open Shift, Mesosphere DC / OS and Kubernetes orchestrators. With Management Pack for SQL Server for Linux, organizations can use System Center Operations Manager for comprehensive monitoring — from hardware to database instances.
Since SQL Server is now available for containers, you can use some DevOps practices in application development, for example, creating an image of a container for use in different operating systems. Using Docker Swarm or Red Hat Open Shift orchestrators, you can quickly update containers in test and production environments.
With the release of SQL Server for Linux, you can use databases in a heterogeneous environment — both on your own servers and in virtual machines in private / public clouds and with VPS / VDS service providers .
Source: https://habr.com/ru/post/315766/
All Articles