Exciting Features of SQL Server 2016

The new SQL Server 2016 release is just around the corner (June 1^st) and we’re really looking forward to it at Pragmatic Works! It’s also a busy time for the Software team, as we have to make sure all of our products play well with the new SQL Server 2016 features. We have just released the 2016.2 build of all of our products, which is compatible with SQL Server 2016 RC3 and will also work with the 2016 RTM release when it is available.

In every new release of SQL Server, there are features that I find really valuable immediately. Then there are those that I’m interested in, but will likely wait and see how they fare in real-world scenarios before adopting. Here’s a few of those:

Features I’m Excited About

One big feature that I like is the integration of R into SQL Server. This opens up some great possibilities for data analysis and brings a different audience into the SQL Server fold. Of course, it also increases the number of things we need to know as SQL Server professionals.

I’m also looking forward to finally being able to use one version of Visual Studio to work with multiple versions of SSAS and SSIS. In Visual Studio 2015 with the latest SSDT installed, we have the ability to create both tabular and multi-dimensional SSAS models, and the ability to work with SSIS packages back to the 2012 version. This is huge for anyone who needs to work with multiple versions of the SQL Server BI stack easily. This did entail a number of updates for our products as well, but it’s well worth it – it simplifies the setup of a BI development environment considerably.

Temporal tables in the database engine is another feature that provides some great benefits. Tracing history on tables is something that I run into pretty regularly as a requirement. I like that fact that SQL Server handles the bulk of the complexity of managing historical copies of the data.

Support for JSON in the database engine is also big. JSON is very common when interacting with web APIs, and is rapidly approaching (or surpassing, depending on who you talk to) the popularity of XML as means of exchanging information between systems. Building it into SQL Server will definitely help simplify system architecture where JSON is in the picture.

The new SSAS Tabular programming model is also a big plus for me. I do a lot of coding with the object models for SQL Server, and the new tabular objects are much better for working with tabular databases, since you don’t have to interact with it like it’s a multidimensional database. This also led to a fair amount of work for our products to make sure we took full advantage of the new model, but again, it was well worth it.

Features I’m Watching

I was excited to hear about the reusable control flow feature in SSIS 2016, but after experimenting with it, I’m not sure how well it will fit with my typical SSIS development process. It’s a design time only feature, and in some ways it reminds me of the Data Source feature back in SSIS 2005 / 2008. That feature proved to be pretty difficult to use in the real world. So on control flow templates, I’m going to wait a bit to see how well people are making use of them before I dive headlong into adoption.

Incremental package deployment has been a requested feature since SSIS 2012 introduced the Project Deployment model. Initially, I was one of those clamoring for it. However, over the intervening years, I’ve become more convinced that SSIS projects, much like applications, should be deployed as a unit. It makes release management much easier, and reduces the chance of something getting deployed to a server that is out of sync with the rest of the packages. So, I’m probably not going to be using this feature a lot myself. I can see benefits in shops that do make individual updates, but if you really find yourself using this feature a lot, you might want to consider if you have a process problem.

Those are some of the more interesting features to me in the 2016 release. It’s been interesting watching the release shape up over the last couple of years and it’s been a lot of work making sure our software is compatible with the new version. Now I’m looking forward to the release, and seeing how people make use of the new functionality.