You may have heard a lot of talk about some new features and enhancements coming with SQL Server 2017 and for good reason. From database administrators to developers and report writers, there’s something new and exciting for just about everyone. While the spotlight may be focused on SQL on Linux, several new features have been introduced including new DMOs and wait stats in Query Store to help make your life that much easier. Integration Services and Reporting Services were not left out and have some fantastic features as well including high availability and user provided comments. For the developers out there, Python support has been included in the new Machine Learning Services add-on. We here at XTIVIA are thrilled to help you set up and explore any of these new and exciting features as well as those not highlighted in this article.
SQL on Linux: sudo yum install sandwich
One of the more highly anticipated features is finally being able to run SQL Server on Linux. It’s the same database engine you’ve come to know and love, just on a different operating system. For many shops, Linux provides an economical and stable platform for their environment. Combining SQL Server’s robust and extensive feature set with Linux is a win-win!
SQL Server, like most of us, doesn’t like to wait long. With SQL Server 2017 CTP 2.0 and Azure SQL Database, Query Store now supports wait statistics per query over time.
Why is this so exciting? Looking up wait stats usually involves querying sys.dm_os_wait_stats and sys.dm_exec_session_wait_stats to review instance and session level wait stats, respectively. Neither of them can easily be tied to a query, making troubleshooting a bit complicated at times. Now you can find out which query waited for resources and why possibly turning you into a genius superhero.
New Dynamic Management Objects (DMOs) For the Win!
sys.dm_db_log_info replaces the DBCC LOGINFO command and basically shows you information on virtual log files (VLFs). This is important because a large number of VLFs can cause many issues with your database including longer backup and recovery times.
sys.dm_db_log_stats helps you check and keep an eye on the health of your transaction logs. It’s a one-stop DMV that shows you all kinds of good information including the recovery model for your databases, the min, and max log sequence numbers (LSNs), VLF counts, what is holding up my log truncation (i.e., the log_reuse_wait_desc from sys.databases), log backup information such as “ack! when was the last time all of my transaction logs were backed up?”, and so on. You can use it for one database or in a CROSS APPLY with sys.databases to see log information for all of your databases.
If you work with hierarchical data, the Graph Database makes it that much easier to work with and hopefully with fewer headaches. A graph database is basically a logical construct within a user database. It’s used to simplify complex data modeling when you have hierarchical and many-to-many relationships.
Integration Services is Moving on Up
Integration Services (SSIS) has some cool new features as well. If you’re not familiar with it, SSIS is a tool for extracting, transforming, and loading (ETL) data mainly designed for data warehousing. What’s great is that you can now scale out SSIS. This means you can distribute package execution across multiple servers and run them in parallel to increase performance.
High availability is also now supported. This means you can enable Always On and setup failover clustering for the server that runs the SSIS Scale Out Master service which basically manages the Scale-Out deployment. That’s cool, but is it I-can-run-packages-on-Linux cool? Why, yes, it is! You can run packages on Linux that have connections to Windows on-premise or in the cloud, Azure, and many other different data sources. Granted, you still have to create and manage the packages in Windows, but now you can leverage the benefits of Linux systems more.
Reporting Services (SSRS) is now a separate download and is no longer installed with the main SQL Server installation media. However, a hyperlink to the download is included in the SQL Server installation screen and on the features screen which is nice.
An interesting new feature of SSRS is the ability to now add comments to reports. With the comments, you can also include attachments. Yes, you read that right. Users can now give feedback, suggestions, additional information, and so on directly to reports. All this fun magic happens in the SSRS web portal. This works for traditional paginated reports as well as Power BI reports. The comments are stored in a new SSRS table called Comments. However, you can disable this feature through permissions.
You can also now create native DAX queries against SSAS tabular data models using Report Builder or SQL Server Data Tools (SSDT). What’s even better is you can drag and drop the fields letting it create the query for you! No more manually writing DAX queries! The drag and drop feature is limited to tabular data models created in SQL Server 2016+ only. If you’re using SQL 2012 or 2014 as a data source, you can still use the text editor to write the queries manually.
SSRS provides a new REST API for accessing report metadata that can be used as an option for integrating reports into applications. This replaces the SOAP API. It’s also OData compliant. So, you can use it in Power BI to build your own reports.
Fun with Machine Learning Services
Machine Learning Services is the new name for R Services and now includes Python support. Python is actually integrated with the database engine. However, you can also install the standalone version if you’re using models that don’t require SQL Server. Python code is called using the sp_execute_external_script system stored procedure for those wondering just how that works.
SQL Server 2017 gives us many great new features, enhancements, and tools to help us solve problems. From running on Linux to using new DMOs and Query Store wait stats to utilizing Machine Learning Services for analytics, there are plenty of fun new features for just about everyone. We here at XTIVIA are excited to help you set up and explore these fantastic new features and tools.