Microsoft has added a lot of new features and functions to SQL Server 2017. Here, we will examine 10 of those features and functions. It will also give clients a good idea of what these features can do for them in a real-time environment.
1. Operating System Expansion
Included in 2017 were language and platform expansion. All platforms (like Windows, Red Hat Enterprise Linux 7.3, SUSE Enterprise Linux, Ubuntu, and Docker) SQL Server is able to run on – as of RC2. The expansion into other operating systems paves the way for those who had wanted to utilize SQL Server but weren’t able to due to the OS restrictions. For those who have been working in these other operating systems, this is a big positive in their work. See the Linux updates Here.
2. Graph Databases
This capability is positioned to open up a whole new world of big data for SQL Server 2017. This type of database uses nodes and edges to show relationships within the collected data. This would allow for a greater representation of information found within and because of those relationships in data values. One of the most important aspects of large data sets is extracting meaningful value from the information included. For more information see this.
3. Python Coding Integration
The added value of supporting this language is found in its general purpose usage. In SQL Server 2017 a Data Scientist can initiate machine learning more efficiently and directly within in the SQL Server. This is an important feature, due to the nature of Python, being able to examine data without having to compile the data outside the SQL Server, then return it, once Python has examined it. Now with integrated Python support, those examinations can be performed right inside SQL Server. There was a concern that the Python engine might be “stealing” resources from other SQL Server services. That is not the case. Inside the Python engine is Resource Generator where the Python processes can be throttled, to allow for more or less resource consumption. Here is Python through Machine Learning.
4. Tempdb Size Improvements
Back in SQL Server 2016, tempdb was changed a great deal, affecting setup, visibility in error logs, etc. For SQL Server 2017 there have been changes to the maximum initial data file size from 1GB to 256GB. Check out setup additions and enhancements right here.
5. In Memory Enhancements
There are a number of enhancements to this feature, including Computed columns and their indexes are supported, 8 index limit on memory-optimized tables has been lifted, filegroups can now be stored in Azure. All the updates for In-memory and Azure are here.
6. CLR Updated Security
As seen from a security standpoint there could be an “unsafe’ code marked as SAFE allowed through the Code Access Security boundary in .NET Framework. The updated security in CLR protects vulnerable assemblies. This new configuration is called CLR strict Security and it closes the gap in all processes. More information on the New CLR strict Security is here.
7. SQL Machine Learning Services
With the addition of Python support and machine learning algorithms, the new scope was named to reflect the type of service – Machine Learning Services.
There are also R components added to the Machine Learning Services, in SQL Server 2017, with updates to several R-based packages. The new R functions are in package management and cover the following areas: The Database roles are there to help Admins control packages, and assign permissions, during the installation process. The command CREATE EXTERNAL LIBRARY helping DBAs by using a familiar T-SQL language. RevoScaleR process can be helpful in installation, removal, and listing user packages. For more information on Machine learning in SQL Server 2017 go here.
8. Automatic Database Tuning
This feature helps with identifying problems in the performance of queries, it then offers a solution, and can even fix the problem itself. In SQL Server 2017 this feature can be set to just report a potential error, and then the correction is made manually if warrantied. See more here.
9. DMV’s (Dynamic Management Views) added
Sys.dm_db_log_stats: A string to call information on transactions and reveals summary level attributes. Used to keep tabs on transaction log health.
Sys.dm_tran_version_store_space_usage: This shows the impact on version store usage grouped by the database. The resulting output can be utilized to profile workload, before and after a change, in a test environment.
Sys.dm_db_log_info: Virtual log file (VLF) monitors virtual log files to help catch and prevent transaction log problems.
Sys.dm_d_stats_histogram: A new management view to enable the examination of statistics. Displayed in a table format.
Sys.dm_os_host_info: Retrieves information about the SQL Server, such as platform, distribution, Service Pack, and language.
sys.dm._os_sys_info: An expansion of the original string to include more information on the CPU running SQL Server, such as socket count, core count, and more.
More on DMV’s can be found here.
10. Intelligent Transaction Log Backup
Similar to differential backup, the Dynamic Management Function (DMF) is a new column in the database, allowing DBA and all SQL utilizers, to compile smart T-log backups. These backups can be performed when the backup frequency is low, or not as complete as it should be. Is especially useful when there is a large influx of new transactions in a short period of time. Can avert an issue when too many T-logs are created during a routine backup, which can happen even when there are only a few transactions being initiated. To find out more about Log Backups go here.
These are just some of the new features in SQL Server 2017, the best of them, to be sure, but there is more.