DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

SQL Server 2016 – What Are The New Features?

Posted on May 17, 2016 Written by Andy Hayes Leave a Comment

SQL Server 2016 New FeaturesSQL Server 2016 will be made available to purchase by the public on 1st June 2016 and if we compare this schedule with Microsoft’s plans of a major release every 2 years, this one comes 26 months after SQL Server 2014 which arrived on 1st April 2014.

So will the list of new features in SQL Server 2016 persuade businesses to upgrade?

This list is by no means complete and so I think I’ll be updating this list as I discover them.

As SQL Server will come in different versions, typically the more businesses are prepared to pay, the richer the feature set available.

  • Always Encrypted – as it says on the tin, the data is always encrypted. This means that access to encrypted data will only be available to the application using SQL Server. The client application has the encryption key and this is never passed to SQL Server.
  • Query Store – in older versions of SQL Server, query plans can be examined using DMV’s which interrogate the plan cache. Once the plans exit the cache, they are no longer visible for comparison anywhere. The query store provides a way to store older versions of execution plans allowing comparisons between old and new to be made. A nice one for those wanting to check performance of plans over time.
  • Live Query Statistics – allows the execution plan to be viewed live as the query is executing – nice!
  • Dynamic Data Masking – when switched on, this feature controls what data is masked from view to users who are not allowed to view it. For example confidential columns may be obscured from view to a group of unauthorized users but the other columns would be visible. Parts of columns may also be obscured from view.
  • Multiple TempDB Files – adding additional tempdb files is not a new feature as such and has been good practice to implement on multi-core machines. What has been added is the ability to do this upon installation of SQL Server.
  • Polybase – allows the querying of Hadoop or Azure blob storage data sets using T-SQL. It’s possible to write queries which join the relational data in SQL Server with the semi-structured data in Hadoop.
  • Row Level Security – access to row data can be restricted based on specified SQL logins. This will be done by filter predicates defined inside of inline table value functions. Security policies will ensure that the filters are executed.
  • R Support – this feature enables data scientists to run R code directly against the SQL Server. Data will no longer need to be exported separately to facilitate these kind of analytic operations.
  • Stretch Database – allows on premise databases to be extended into the cloud. Frequently used data would be stored on premise with older data stored offsite in an Azure database inside the cloud. A question one might ask is what if the on-premise location’s internet link goes down? Only a feature for organizations with good communications redundancy perhaps?
  • JSON Support – MySQL’s doing it in the latest 5.7 release, Postgres has been doing it for years, Oracle’s doing it and it’s coming soon in MariaDB. Relational database products are adapting to include support for JSON and SQL Server has joined the party. JSON data can be parsed and stored in relational format. Functions are included which allow for JSON to be queried and output results from queries can also be formatted as JSON.
  • Temporal Tables – helps to facilitate automatic row versioning. Every time a row is updated in the base table, a copy of the older version is made inside the temporal table. The temporal table is physical object inside of the database. It is separate to but linked to the base table.
  • Backup to Azure – on premise backups can be made to Azure blob storage.
  • Managed Backup – this is automated managed backup of your on-premise backups to Azure.

Improvements to existing features in SQL Server

“There is always room for improvement” and existing features have been enriched in places too. What I have been able to ascertain so far is is that there are improvements made to Columnstore Indexes, AlwaysOn and In-Memory OLTP. I’m sure I’ll discover more as I delve deeper.

A worthy release?

It certainly would appear that there are more features than what was included in SQL Server 2014 and if businesses were thinking of upgrading then this version would be well worth considering.

As ever, those decisions will be based around cost and requirements. There will still be businesses running on older versions of SQL Server no longer supported (2005 support finished ended as of April 12th 2016) and they are quite happy to do so because those versions do the job just fine.

For those businesses requiring a supported version, increased performance and some or all of the newer features but have been delaying upgrading, this release may be enough to persuade them to now move forward.

Filed Under: All Articles, SQL Server News Tagged With: sql server, sql server 2016

How to Get SQL Server Developer Edition for Free

Posted on May 14, 2016 Written by Andy Hayes Leave a Comment

sql server developer edition is freeSQL Server Developer Edition – whether you are wanting to learn the product at home to advance your career or you need a copy for your enterprise to test and develop on, it’s historically been a good investment for relatively little cost.

Personally for home use, I have always downloaded the evaluation versions of SQL Server that expire after 6 months. That’s kind of annoying as after that time, you need to either activate the installation with a purchased licence key or reinstall the product. I’ve preferred to do this because for personal use, I have still been reluctant to spend the money on the development version. The hassle of backing up anything I have done and re-installing has been better for me than spending the money.

The good news is that this has changed as Microsoft have announced that SQL Server Developer Edition is now free to download. I think this is only a good thing for increasing the adoption of SQL Server.

You need to register to Visual Studio Dev Essentials to get access to this download. It is also free to register. 🙂

In my opinion Microsoft appears to be making a number of good strategic decisions of late (Visual Studio Community, SQL Server on Linux, .NET on Linux etc) and this move is consistent with their apparent direction of making their products more accessible and compatible on systems which are not running a form of the Windows operating system.

For more information on this topic, check out this official post from Microsoft.

Filed Under: All Articles, SQL Server News Tagged With: sql server, sql server 2016

If you want to try SQL Azure, then this is how you can do it

Posted on April 17, 2012 Written by Andy Hayes 3 Comments

sql azure cloudJamie Thomson is a freelance SQL Server developer in London. I have found his blog to be an extremely valuable resource in the past. You can follow him on Twitter @jamiet

He has very kindly made some of the well known “out of the box” SQL Server databases available on SQL Azure for you to try.

SQL Azure is not a free service and so he has funded this himself out of his own pocket.

He started by uploading a copy of the AdventureWorks2012 database to SQL Azure, see the link about it here and has since increased the volume of data in the AdventureWorks2012 database to help you to push SQL Azure’s capabilities as well as uploading a copy of the Northwind database. See this link for more info on that.

This is a great idea and is so deserving of support. If you find the service useful then he has supplied details of how you can make a donation to help keep the service running.

Thank you Jamie – a great idea sir.

Filed Under: All Articles, SQL Server News Tagged With: cloud, sql azure

Categories

  • All Articles (82)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (19)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • How to shrink tempdb
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to move tempdb

Recent Posts

  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial
  • How to Use SQL CASE for Conditional Logic in Your SQL Queries
  • Using ISNULL in SQL Server to Replace NULL Values

Search

Connect

  • Twitter
  • Facebook
  • Google+
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright © ‘2021’ DBA Diaries built on the Genesis Framework

This site uses cookies. We assume you are happy with cookies but click the link if you are not. Close