Tag Archives: 2017

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

Say Goodbye to Service Packs: SQL Server 2017 Won’t Have Them

Take Your Service Pack and Get Outta Town

Service Packs (SPs) have long been an quick litmus test to determining where you are when assessing needed upgrades. You could almost ignore CUs (Cumulative Updates) and use the SP to define where you are and you needed to go. 2008R2 SP3? You’re pretty much all patched up. 2012 SP1? Got a ways to go. But that’s about to change with SQL Server 2017 as Microsoft is doing away with Service Packs, and just releasing sequential updates as CUs.

In SQL2017 and beyond, every CU will be tested like a Service Pack and contain all the updates, hotfixes and security patches of every CU before it. So we can expect to see versions like SQL Server 2017 CU12.

Well, That’s Nice. But Why?

Microsoft wants to move to a more “agile” method, allowing them to get more updates out faster. Releasing many smaller CUs is faster-to-market and means less patching of odds and ends with hotfixes.

Also, this will just simplify the whole process. Instead of saying 2017 SP3 CU2, it will simply be 2017 CU26 (I just made these up. I am not clairvoyant now, but I will be in the future).

There will be two tracks for updates, the main CU path and a GDR (General Distribution Release) path. GDR path is just security updates (maybe a system-breaking hotfix once in a while). This path will be entirely separate from the normal CU path and you will not be able to jump back and forth between them*.

When Will I Get These CUs?

2012/2014/2016 are all still on the old model. Starting with SQL Server 2017 you’ll see this new servicing model.  After RTM, SQL2017 will get a new CU every month for the first year, but will slow down after that. Microsoft’s reasoning is that most of the major fixes are in the first year, so they want to keep ’em coming during this critical phase. For the remainder of the four years of mainstream support, this pace will slow to one CU every quarter.

If you’re on Linux, it’s the same deal. You’ll be able to pull these CUs from the same repositories that you get SQL Server from. This is kind of a big ‘duh’ but I felt it need mentioning.

Anything Else?

Sure there is! Lots of odds and ends for you to know. Like:

  • CUs will accommodate localized content (they didn’t before)
  • CUs will still be released the same time every month
    • That’s the week of the 3rd Tuesday, but you knew that
  • You don’t have to be on a specific CU to be supported.
  • CUs will not contain any “net new” features.
  • CUs can be uninstalled from Windows
  • In Linux, install and run the container from a previous CU to do a rollback

And that’s it. Happy patching!

-CJ Julius

*You can go from the GDR path to the CU path, but not back again. Once you’re on the CU path, you’re there for good.

Simple Database Inventory Manager 2.3

We got a job.
We got a job.

Always moving forward, here at KnowledgeHunter Corp! (Note: not a real corporation) Just posted the github of the new SDIM 2.3. Changes in just about every corner, but the big overall change is the addition of job information in the data pull.

If you have no idea what SDIMS is or I’m yammering about, here’s the run-down. Otherwise, read on for the 2.3 update news.

Dey took er jerbs
New buttons in 2.3 include 'Jobs' and 'Jobs Extended'
New buttons in 2.3 include ‘Jobs’ and ‘Jobs Extended’

The newest feature is ability to inventory pretty thorough job information. Well, the stuff I’m always looking for anyway. Also included is some extended information, which right now basically includes just a description with a few other identifying columns. Future versions may gather more data and put it here. Powershell handles it well, even with several thousand rows/jobs.

Here is the kind of information that you can expect to see:

  • Server/Instance
  • Job Name
  • Is the Job enabled?
  • Schedule Name
  • Is the Schedule enabled?
  • Schedule Type, Occurrence, Recurrence (if applicable) and Frequency
  • Job Description

This information has been added to the ‘Full Inventory’, so be aware that has a lot of new stuff in it too.

Full disclosure: I used heavily modified version of a few queries from Dattatrey Sindol to pull jobs data.

Always Squashing

Fixed a few bugs:

isProduction column now does not display in Instances list. That was a feature that got removed from everywhere (before 1.0). Finally removed the column.

HIPAA level feature is now completely removed. Never got this one working right and I decided that in the future I’ll go with something a bit more general, like maybe just ‘priority’ or something.

Fixed a few typos. Me spel gud now.

Known Issues:

Too many varchar(max) columns. I know. I know.

Me Want

So, how do you get this? Well…

If you never have used it before, I would suggest going through the walkthrough I put out a while ago. I may in the future build a more simplified version for people who just want to install and don’t care about the behind-the-scenes. (UPDATE: I did just that, here)

Elseif you’re using prior to 2.1 already, then you should probably just drop all the SQL objects and rebuild with the new 2.3 items. This includes the PowerShell datapull and frontend pieces; they’re the only two PowerShell pieces.

Elseif you’re on 2.1 right now you can just replace the items that were changed. I have created a script that will do this for you, because that’s the kind of guy I am. You may need to run the script twice because I didn’t check for object existence. Running it multiple times won’t hurt. It doesn’t touch the Servers table, but it does drop the InstanceList, so if you have that statically assigned, then you’re going to want to back it up and then re-insert the data with the missing columns removed.

And Here’s A Count of All the Bolts

Lastly, here’s the items that were altered in the 2.1 – 2.3 release if you’re curious:

Tables:
dbo.JobList – This holds all the jobs information. Added for new feature (look! No varchar(max)!)
dbo.InstanceList – Removed a column.

Stored Procedures:
dbo.prGetInstances – Altered to fix bug
dbo.prGetInventory – Altered to fix bug
dbo.prGetJobs – New for new feature
dbo.prGetJobsExt – New for new feature
dbo.prGetServersAndInstances – Altered to fix bug
dbo.prInsertJobList – New for new feature
dbo.prUpdateInstanceList – Altered to fix bug

PowerShell:

DB_DataPull – Altered to pull Jobs (new feature) and fix bugs
DB_DataPUll_FrontEnd – Altered to display Jobs/Jobs Extended