Microsoft announced the first version of SQL Server 2017 to run on Windows Server, Linux and Docker on Ignite, yesterday, calling it one of the heroes of ‘
our new choice paradigm’. It will be available to customers from October 2, 2017, onwards.
SQL Server 2017 is a milestone that was in pipeline since 2016 when Microsoft first announced about planning to make SQL Server available on Linux. The new Server adds features from the data science and NoSQL, and offers cross-platform capabilities and Docker container compatibility for ease of index maintenance, high availability, and data warehouse performance.
Credit/Source: Microsoft
The new Linux Server 2017 comes with some cool features, as described on the
official blog.
• ‘AI built-in, with R and Python analytics plus native scoring in T-SQL – SQL Server was the first commercial database to have AI built-in. SQL Server 2017 enables you to build intelligent apps using scalable, parallelized and highly performant analytics using both R and now Python. New native scoring in T-SQL lets you score in near real-time.
“Beyond the addition of Python as a language that can used for the scoring process, T-SQL code itself can now perform "native scoring" as well, using the language's new PREDICT function. As long as the models being scored against have been trained and are stored in the database in the prescribed RevoScaleR or RevoScalePy format, T-SQL can be used to score against them, whether or not R or Python is even installed on the server. This allows for a very important separation of concerns, whereby Data Scientists can build and train ML models in their own environment, then store them in the database, where any developer can write T-SQL queries to score against them. And if the SQL Server host has Graphical Processing Units (GPUs) on board, data scientists can see even bigger gains, beyond elimination of data movement, by training their models there.
• SQL Server 2017 will enable customers to use graph data management and analysis in order to discover new kinds of relationships. You can track and analyze highly interconnected data, for uses like detecting financial fraud or understanding pharmaceutical drug interactions.
• Adaptive Query Processing is a new family of features in SQL Server that bring intelligence to database performance. Adaptive Memory Grants in SQL Server track and learn from how much memory is used by a given query over time to avoid over- or under-provisioning memory and spilling to disk, a situation that can negatively affect performance. The more you run your application, the smarter this feature gets. Automatic Plan Correction ensures continuous performance by finding and fixing performance regressions.
• The newly renamed Machine Learning Server for Hadoop, formerly R Server, brings R and Python based scalable analytics to Hadoop and Spark environments, and it is now available to all SQL Server Enterprise Edition customers as a Software Assurance benefit.
Source: Microsoft
• SQL Server Enterprise edition software assurance benefits also enable you to run Power BI Report Server. Power BI Report Server enables a truly hybrid reporting and dashboard experience by allowing you to manage your SQL Server Reporting Services (SSRS) reports alongside your Power BI reports.
• Easy lift and shift to the cloud with Azure SQL Database: This feature is designed to provide an automated path to the cloud with zero downtime.
• SQL Server Integration Services (SSIS) customers can easily lift SSIS packages into the cloud using new managed SSIS hosting capabilities in Azure Data Factory’
Microsoft’s support for running machine learning workloads inside SQL Server using Python and R allows data scientists to store and process information inside SQL Server rather than importing them from or to another location.
SQL server 2017 retains its focus in core database engine performance and its features makes substantial progress toward bringing Data Science forward into mainstream enterprise computing for example:
1. SQL Server's vector processing-based batch execution mode is now available to the entire execution of R or Python code.
2. the non-clustered variety of Columnstore indexes (NCCIs) can now be built without taking the tables they index offline.
3. BLOBs (binary large objects, such as whole files, stored in a table) in Columnstore indexes can now be compressed.
Moving beyond the core features we discover that other components of the SQL Server have been improved as well like -
1. SSAS (SQL Server Analysis Services) Tabular will be able to provide same data acquisition dialogs and much of the data source connectivity that's available in the Excel 2016 for Windows and Power BI Desktop.
2. The SSDT tooling for SSAS also now includes an editor for the Tabular engine's DAX (Data Analysis Expressions) language.
3. No price hike for now and on top of it customers can avail an attractive discount of 30% when they purchase Red Hat Enterprise Linux (RHEL) to support their SQL Server.
By introducing Linux and Docker containers compatibility, SQL Server can now run not only on Windows servers, Linux-based servers but also on a Mac-something that users struggled with earlier. However, SQL Server Analysis Services and SQL Server Reporting Services, are not available in Linux versions yet.