9 Exciting New Features with SQL Server 2017
Microsoft has added a ton of new features in SQL Server 2017, including expanded operating system support, Graph database capability, Python support, and more. Read on to learn about nine of these new features
1 SQL Server Runs on Windows, Linux and Docker Containers
With SQL Server 2017, Microsoft has expanded the operating systems that SQL Server now supports. As of RC2 of SQL Server 2017 it can now be installed on:
2 Graph Database Capabilities
Graph database capability was included in SQL Server 2017. A Graph database is a database structure that allows you to store nodes and edges, where nodes are entities, like an Employee, or a Department, and edges are the relationship between nodes like an employee works in a department. With graph database, you can now more easily store many to many relationships between nodes. Storing data that have complex relationships can be stored in a traditional relational structure, but with Graph Database capabilities you can now more easily store those complex relationships between nodes. By having graph database functionality in SQL Server 2017 you can now more efficiently store and query complex relationships between nodes and entities edges.
3 Python Support
With the introduction of Python support in SQL Server 2017, a Data Scientist can now more easily perform machine learning right inside of SQL Server. Having Python integrated in the SQL Server database engine is a big deal and will allow those Data Scientists to perform Python processing without having to move their data outside of SQL Server. The SQL Server team has made it simple to integrate python code into your applications. They did this by allowing you to execute external python scripts via the “sp_execute_external_script” system stored procedure. Note this system stored procedure was introduced in SQL Server 2016 to support R processing, but now has been expanded with SQL Server 2017 to support Python. With Python integrated into the database engine, DBAs might be concerned about Python stealing valuable resources away from other SQL Server processing. Fret no more, Python resources can be throttled using Resource Governor. To throttle a Python process using Resource Governor, the DBA will need to create a resource pool that throttles external script execution resources and then assign Python processing workloads to the newly create python resource pool. Additionally, there are some logs and views that allow DBAs to see exactly what resources are being consumed by Python.
4 Resumable Online Index Rebuild
If you have a limited maintenance window, or really large indexes that take a long time to rebuild then you will be excited about this new feature. With the introduction of SQL Server 2017, you can now pause your online index rebuild operations, and then restart them later to complete the index rebuild operation. With resumable Online Index rebuilds, DBAs will also be able to restart a failed online index rebuild operation. To minimize the amount of Transaction Log space needed for an online rebuild operation, SQL Server 2017 allows the transaction log to be truncated, while an online index rebuild operation is running. Just like with other new features, Microsoft has provided a new system view named sys.index_resumable_operations that allows you to monitor online index rebuild operations.
5 New CLR Security Model
With the introduction of SQL Server 2017, Microsoft has changed the security model for CLRs. They did this because the Code Access Security (CAS) in the .NET Framework is no longer supported as a security boundary, which means an assembly marked as SAFE may be able to run code that is unsafe, or accesses external system resources. To shore up possible SQL Server security holes around CLRs, SQL Server 2017 has implemented a new configuration option named CLR strict Security and a new system stored procedure named sys.sp_add_trusted_assembly. The new configuration option, named “CLR strict Security,” when enabled causes all SAFE and EXTERNAL_ACCESS assemblies to be treated as if they are UNSAFE. Note this option is enabled by default. In order to execute SAFE and EXTERNAL_ACCESS CLRs with this new option enabled, you will need to sign all SAFE and EXTERNAL_ACCESS assemblies, as well as mark your CLR databases as trustworthy. I know this sounds like a pain, especially if you have lots of CLRs that are marked as SAFE or EXTERNAL_ACCESS. But there is a work-around. The work-around that was implemented with SQL Server 2017 is to use the new stored procedure named sys.sp_add_trusted_assembly. This stored procedure allows you to add a CLR to the list of trusted assemblies. This stored procedure allows you to whiteliste a CLR. By whitelisting a CLR, SQL Server will execute UNSAFE and EXTERNAL_ACCESS CLRs without you having to sign them or set their databases to trustworthy. For more information about the two new CLR security features listed above use these links:
CLR strict security
sys.sp_add_trusted_assembly (Transact-SQL)
CLR strict security
sys.sp_add_trusted_assembly (Transact-SQL)
6 Identity Cache
In the old versions of SQL Server, by default SQL Server would clear the identity cache if SQL Server wasn’t shutdown cleanly. Because of this you would end up with gaps in your identity values when SQL Server wasn’t shutdown normally. To resolve that issue Microsoft came up with a new database scoped configuration that allows you to turn off identity caching by database. By turning off identity caching for a database your identity values will no longer have gaps when SQL Server should fail or restart unexpectedly. Since this setting is now database specific, you can have one database that turns off identity caching, while other databases can use the old behavior of managing the identity cache values.
7 Adaptive Query Processing
Executing plans for a given query might change over time as the underline date associated with the query changes. These changes can make the cardinality and row/or count estimates incorrect causing a poor plan to be selected. Adaptive Query processing, in SQL Server 2017, can improve execution time of similar queries over time. You can specify that a database use Adaptive Query Processing by issuing an ALTER DATABASE statement. There are three different types of query optimizations that Adaptive Query Process will consider: Memory Grants, Adaptive Joins, and Interleaving Executions. By adjusting the memory grant value over time Adaptive Query Processing can reduce the amount of memory that spills to disk to drastically improve query performance. The Adaptive Query Processing will adjust a query plan to use either hash join or a nested loop join based on row counts and a threshold.
8 Simplify Your Code with New T-SQL Functions
Your coding of T-SQL just got easier with SQL Server 2017. With this new version, Microsoft introduces some new string functions, like TRIM, CONCAT_WS, TRANSLATE, and STRING_AGG to name a few. For example, to remove white space from the beginning and the end of a string column or variable you had to using LTRIM and RTRIM to accomplish this in SQL Server 2016 and below. But now in SQL Server 2017 you can removed white space from the beginning and end of a string with a single execution of the TRIM function call.
9 Scaling Out Integration Services
With SQL Server 2017, Microsoft implemented a new feature for SSIS called “Scale Out”. With “Scale Out” it allows you to distribute the execution of a package across multiple machines. By having your package run in parallel on one or more machines you will improve the overall perform of your SSIS package. A scaled out SSIS environment can be run On-Premise, as well as on Azure VM. In order to run a package in parallel the “Scale Out” feature needs to be configured. When configuring the “Scale Out” feature you install one Scale Out Master service and one or more Scale Out Worker services. The Scale Out Master service manages communication with Scale Out Worker services to coordinate the parallel execution for an SSIS package. The Scale Out Worker services perform the actual package execution tasks.
Thanks
Ref:
https://www.databasejournal.com/features/mssql/slideshows/9-new-features-with-sql-server-2017.html
No comments:
Post a Comment