Tag Archives: walkthrough

5 (more) New Features in SQL Server 2017

FETCH NEXT 5

About a week ago I posted about the Top 5 Things I’m most excited to see in SQL Server 2017. As you may have noticed, I just focused on the Engine/Agent and not on anything else. To be fair (and I’m always fair to myself), most of the big exciting changes were there.

However, I did want to give the rest of the SQL Server features their due. To rectify that oversight, here’s 5 more new things in SQL Server 2017 that I’m excited about.

1. SSIS On Linux
A Linux Cluster.
Image Source: Visual Hunt

This is a bit of cheat, because I already went over SQL Server on Linux, but this I thought deserved special notice. Did you know you can run your SSIS packages from your Linux box with SQL Server now? You can.

Just pop in this little one-liner and you’re off to the races*.

$ dtexec /F \<package name \> /DE <protection password>

If push came to shove you could probably put that on a cron job should you want. You still need a Windows server to create and maintain the packages, but you can run them locally from the box if you’re trying to keep the family together for the kids.

* Certain Terms and Conditions may apply. See your dealer for details.

2. Machine Learning Services
Python!

This is a feature that kinda existed previously, but it was just called “R” Services. The big thing of note is that it now supports Python and the associated libraries. See previous post in this series to catch my sarcasm about Python not being included in the first place.

Thing to note about Machine Learning Services is that it’s not supported in-database on Linux. You can still do things like native scoring (PREDICT), but that’s just about the long and the short of it. Microsoft is making noises like they’re going to address this in the somewhat-near future.

3. SSIS Scale-out

This is a pretty neat feature that I hadn’t thought about before. What if you had several servers that potentially COULD handle an SSIS workload (in an HA scenario or something), but you didn’t want to always target the same instance. You know, spread the love around.

SQL Server 2017 allows you to set up a master on your main instance and then workers on the servers you want to be able to scale-out to. After a bit of setup on your worker machines you can then either target machines with specific packages or let SSIS decide. Check out this walkthrough for more.

You turn this feature on (assuming you’ve set it up properly) in the SSIS Catalog Properties.
Image Source: Microsoft

 

4. New SSRS Web portal

This is less a new feature, and more of a major revamp to something that already existed. The new Reporting Services default Web Portal is a lot snazzier and has some new things. You can customize branding the instance and even develop KPIs that are contextual to the folder you are currently viewing.

Purdy.
Image Source: Microsoft

 

5. MDS Performance Improvements

Master Data Services has had a rough life. Beginning life as far back as SQL Server 2008R2, this has been the red-headed stepchild of the SQL Server offerings. It started out, in my humble opinion, barely usable, unnecessarily complex and just feature-poor. I’m not alone in this opinion.

Subsequent releases have helped it, but even after several versions it still was pretty weak and only useful for very specific cases. MDS only really came into its own in 2016, but with some performance limitations.

Pictured: Master Data Services circa 2008R2.
Image Source: Visual Hunt

Edging ever closer to a more perfect product, SQL Server 2017 features some much needed performance optimization allowing it to stage millions of rows in a reasonable amount of time. It was painfully slow previously with only a few hundred thousand records.

Lastly, they fixed the slow UI movement when doing things like expanding folders on certain pages.

Honorable Mentions

Not any this time, unless you want to talk about SSAS object-level Security or DAX finally getting an IN operator. Those seem pretty useful.

The End?

That’s it for 2017. There are, of course, many many more changes and new features in SQL Server 2017, but I think 10 or so is good enough to give you a taste. There are changes all across the product and I encourage you to look them over yourself.

-CJ Julius