Exciting SQL Server 2016 Improvements

SQL Server 2016 is the third major product release by Microsoft in four years with an anticipated launch in spring 2016. Microsoft’s rapid deployment is keeping application vendors on their toes as they work to stay current and support the latest and greatest features. The new features in SQL Server 2012 focused on high availability and disaster recovery with the debut of AlwaysOn Availability Groups and Failover Cluster Instances. The 2014 release focused on performance improvements in In-Memory OLTP (codenamed Hekaton), In-Memory ColumnStore for data warehouses, In-Memory business intelligence with PowerPivot, and it doubled the number of replicas that Availability Groups support! Security was also improved in 2014 with transparent data encryption, improved compliance via separation of duties, and support for Windows Server Core. As if that wasn’t enough to keep up with, Microsoft has some very intriguing enhancements coming in 2016, and Microsoft’s SQL Server 2016 datasheet can be found here.

Feature Enhancements

I was especially impressed by the In-Memory OLTP, query data store, Native JSON, and temporal database enhancements and by the major update to AlwaysOn replicas in the SQL Server 2016 Community Technology Preview 3.0 feature set.

In-Memory OLTP (codename Hekaton) received a major face-lift, increasing the maximum size of a table to an incredible 2 TB, up from 256 GB. The types of data, structures, indexes, and operations are all updated making these tables less cumbersome to use and more easily adapted. This feature is also making its way into Azure SQL Database very soon! In-Memory OLTP now supports TDE, ALTER TABLE, and allows some parallel query plans to be used.

In the new query data store, SQL Server will log information about each query such as the number of executions, execution time, memory, reads/writes, etc. It will keep track of the performance history for all query plans. With this, we can see any degradations as new plans are created by the DB engine and force SQL to use an older plan if needed. The answer to “why is this query suddenly slow” is not just “go ask the developer,” but now the DBA can review recent query plan changes and identify potential issues there.

Native JSON support makes it easy to parse and store JSON and export relational data to JSON services. It is not an exact copy of native XML support that SQL has had since 2005, but Microsoft’s goal is to “create a simpler but still useful framework for processing JSON documents.” This will not be the same as “native JSON type” as JSON will be represented as NVARCHAR data. That is fantastic since NVARCHAR is supported in all SQL Server components. If you want to put JSON into an in-memory table, go for it!

The temporal database feature gives us the answer to “what did this database/table look like at 3:03 p.m. yesterday” with a system-versioned table. All records are versioned in the database, so there is a current version as well as previous versions. This helps us track data changes over time, find trends, provide an audit trail, and recover from accidental changes.

SQL 2014 doubled the number of AlwaysOn readable secondary replica databases to eight, and SQL Server 2016 adds a third synchronous replica. Secondary AlwaysOn replicas now automatically load balance READ workloads using round-robin. The AlwaysOn Availability Groups feature is also now available in SQL Standard Edition as “Basic Availability Group,” but there are some limitations compared to the Enterprise edition.

Security

SQL Server 2016 also promises some important security improvements. It implements a method of encrypting databases with the new Always Encrypted feature set. Previously, data could be encrypted at rest, in backups, in transit, and on individual columns, but set up was exhausting, nothing was shared in configuration, and mistakes were easy to make. Always Encrypted promises to simplify end-to-end encryption, which will hopefully make it more frequently used!

Row-level security enables the security team (or another DBA role) to better control access to data based on the user’s rights. These changes to security are implemented inside the database and require no modifications to the application. However, this means more oversight by the DBA team and more communication with the business/customers to vary what data should be viewable.

Dynamic data masking is a new feature that SQL Server can use to obfuscate data in real time so users do not get access to unauthorized data like personally identifiable information that is unencrypted in the table. For example, to a restricted access user a masked social security number would look like 111-11-1111, where as an encrypted social security number would look like ¤ß/ç)÷Ðu7Z.m5È.

Cloud Integration

SQL Server 2016 also takes cloud integrations a step further without making query changes in the application. Stretch Database is a new hybrid model for database tables, possibly offering local server performance for active/recent data and Azure cloud storage for archive data without any change to the application. Frequently, archive data is partitioned out of the primary file group onto slower disks or moved into an entirely different table. Stretch Database improves this by storing those archive portions of a table in less expensive Azure Blob storage.

SQL can now backup straight to Azure block blob storage as opposed to SQL Server 2014’s limitation to backup online to page blob storage. Block storage is cheaper than page storage, and now the total backup size limit is increased to more than 12 TB.

SQL Server 2016 running on Azure VMs using databases stored on Azure Blobs for primary storage can now use Snapshot Backups. When a log backup is complete, a new snapshot is created that holds all the previous database files, which enables point-in-time restores. SQL Managed Backup also now supports all recovery models (full, simple, and bulk-logged) and includes the system databases. There is now no excuse to not be backing up your SQL databases! You can store them on local disk, shared storage, network storage, and Azure blobs.

SQL Server 2016 Community Technology Previews keep sharing new features at a blistering pace—it almost makes it hard to keep up! I’m very excited about what features we have yet to see that will make it into the final release. Be sure to follow Credera’s blog for more updates!

Need help getting the ball rolling on a SQL Server 2016 evaluation? Are you aware of SQL 2005 going end-of-life soon? Contact Credera at info@credera.com or call 972.692.0010. For more information on Microsoft infrastructure topics, please visit our blog or follow us at @CrederaIT.

• Microsoft
• SQL
• SQL Server 2016
• Native JSON