If you haven’t heard yet, there are several major and exciting updates to SQL Server with the 2017 release. From the ability to run on Linux and Docker containers to rich data graphing functionality, the new features could have a big impact on your database and your data.
In addition to Windows, you can now run SQL Server on Linux and Docker containers. If you’re already managing one of these environments – or you’ve been considering moving an existing SQL Server to one of these environments – then this will come as particularly welcome news.
It is worth nothing that there are some pieces of functionality that are missing on Linux. There are no Reporting Services, Analysis Services, or Machine Learning Services (formerly R services) yet. Only time will tell if customer demand sways Microsoft to offer these functions across all operating systems.
In 2016 and older versions, SQL Server had to rely on index and column statistics to determine key features of query execution. Because these older versions of SQL Server had no knowledge of the correlations among column values in a given query, the process was imperfect and there was always a bit of guesswork involved.
Enter SQL Server 2017. Now, the query engine uses adaptive processing – meaning that it has the ability to adjust queries “on the fly.” So, SQL Server uses results from steps earlier in the query plan execution to intelligently alter steps later in the plan, to improve the processes.
Python is the standard for binding your database to deep learning libraries, including Google’s TensorFlow and Microsoft’s Toolkit. With the 2017 update, a developer can now more easily provide machine learning right inside of SQL Server. Having Python integrated right into the SQL Server will allow you to complete your Python processing without the hassle and time associated with moving your data outside of SQL Server, which was previously required to perform Python processing.
With SQL Server’s database structure, you can store nodes and edges – where nodes are individual entities – such as an employee or a department – and edges are the relationships between those nodes – such as an employee who works in a certain department.
With a traditional, relational database, many-to-many relationship data was difficult to model with the traditional tools available. With the 2017 SQL Server update, you can now more easily store many-to-many relationship between nodes – such as multiple employees who work in a given department.
What’s more, you can now more capture and analyze complex relationships that exist between nodes. With this, you can more efficiently store and query complex relationships between nodes and edges. You can create as many different node tables as you need to capture the entities in your data model. You also have the ability to create a separate edge table for every single one of the relationship types that connect these entities within your data.
Graph data has become of increasing interest in recent years because of its natural support for social media data. However, there are many other useful applications beyond social media mining. Experts forecast that now that SQL Server provides these rich graphing capabilities, many new apps will be quickly developed.
Prior to the 2017 release, graph support was already popular among those using Azure SQL – the cloud version of SQL Server. This popularity indicated to Microsoft that graph support would be a worthwhile feature to include in the 2017 SQL Server update.
With SQL Server 2017, Microsoft began offering the paid editions on a trust basis, so you don’t need a product key or activation to get started.
If you have a limited maintenance window or very large indexes that take quite some time to rebuild, then this new feature in SQL Server 2017 is going to be particularly useful. You now have the option to pause your online index rebuild operations and then continue them later to finish the function. Along with the ability to pause and restart an index rebuild at a later time, you can also restart a failed online index rebuild operation. This can save you time and increase efficiency.
Before SQL Server 2017, if the SQL Server wasn’t cleanly shut down, then the identity cache would automatically clear. Because of this, you’d have gaps in your identity values when your SQL Server wasn’t properly shut down.
To solve for this, Microsoft came up with a new database scoped configuration that enables you to turn off identity caching, on a per-database basis. Therefore, your identity values will no longer have gaps when SQL Server fails or restarts unexpectedly.
If you run the same package across multiple machines, then this new function may save you some time. This new feature for SSIS is called “scale out” – and it allows you to execute a package across several machines. In this way, you will improve the overall performance and efficiency of your SSIS packages.
Thinking that these updates to recent SQL Server may be beneficial to your business and database setup? Contact us for a personalized analysis and consultation.
We are experts at upgrading SQL Server and can make it as painless as possible.