Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL slideshows

Posted October 2, 2017

9 New Features with SQL Server 2017

By Greg Larsen

  • Previous
    9 New Features with SQL Server 2017
    Next

    9 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.
    See all articles by Greg Larsen
  • Previous
    SQL Server Runs on Windows, Linux and Docker Containers
    Next

    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:
    • Windows
    • Red Hat Enterprise Linux 7.3
    • SUSE Enterprise Linux Server v12 SP2
    • Ubuntu 16.04LTS.
    • Docker Engine 1.8+
    You will now have options as to what operating systems you will be using to run your instances of SQL Server 2017. This is great news for those of you that are already managing one or more of these environments, or are looking to move SQL Server to one of these non-windows environments. 
    See all articles by Greg Larsen
  • Previous
    Graph Database Capabilities
    Next

    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.
    See all articles by Greg Larsen
  • Previous
    Python Support
    Next

    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.
    See all articles by Greg Larsen
  • Previous
    Resumable Online Index Rebuild
    Next

    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.
    See all articles by Greg Larsen
  • Previous
    New CLR Security Model
    Next

    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)
  • Previous
    Identity Cache
    Next

    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.
    See all articles by Greg Larsen
  • Previous
    Adaptive Query Processing
    Next

    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. 
    See all articles by Greg Larsen
  • Previous
    Simplify Your Code with New T-SQL Functions
    Next

    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. 
    See all articles by Greg Larsen
  • Previous
    Scaling Out Integration Services
    Next

    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.
    See all articles by Greg Larsen

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.



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.