JDBC Connections: Briefing On and Preparing for SQL Server 2016
As Microsoft prepares to retire SQL Server 2005 in April of 2016, there is a lot of excitement surrounding the updated Microsoft data platform that will also be released next year. The question for CTOs and DBAs planning their upgrade strategy is should we skip all other SQL Server versions and implement the new SQL Server 2016?
Microsoft’s upcoming release has numerous enhancements to offer and we have outlined briefly what improvements you will find in SQL Server 2016. The SQL Server 2016 Community Technology Preview is also now available for download, and the evaluation period is for 180 days.
Whichever version of SQL server selected, JNetDirect will provide you with compatible drivers and tools that allow you to take full advantage of the new features. For 15 years JNetDirect has worked closely with the SQL Server Community and we will be ready to support your plans and strategy when Microsoft releases SQL Server 2016.
What is new in SQL Server 2016?
When Microsoft’s CEO, Satya Nadella, announced the upcoming release of SQL Server 2016, he stated that it continues the Company’s move towards a mobile first / cloud first strategy. This next major release focuses on data security, improving analytics and the ability to easily build, deploy and manage solutions that span on-premises and cloud.
Here is a quick briefing on the improvements available in SQL Server 2016:
- Query Store
The Query Store has been one of the most popular features offered, and it is no different this time. Improvements to the Query Store include the following:
- Bug fixes and UI enhancements
- Once the defined max size limit has been reached, it will automatically switch to READ_ONLY
- No force plan recompiles
- Stretch Database
The new Stretch Database technology allows the ability for administrators to move historical data by stretching warm and cold OLTP data to Microsoft Azure, or in simpler terms, store portions of a database in the cloud. This can be done without any changes in the applications. There are also additional improvements which include:
- Stretch Database Advisor is now available so that all existing database tables, as well as to assist in discovering and evaluating candidates for stretch
- Row Level Security (RLS) is now enabled
The main benefits of this new feature is the ease of storing transactional data for a long period of time, as well as saving money for storing this information.
PolyBase Technology has now been enhanced to simplify the overall management of both relational and non-relational data by being able to query both.
- In-Memory Enhancements
The In-memory now includes operational analytics and enhanced performance. In fact, it now provides up to 30x faster transactions and more than 100x faster queries. As shown in the diagram below, one of the benefits includes having in-memory for more applications that are being used.
- Core Engine Scalability
The enhancements within the Core Engine Scalability will allow for much higher scalability of high concurrency workloads which are running on NUMA hardware. This is enhanced without any application changes.
- Temporal Tables
Temporal tables are a completely new feature which enables the handling and analyzing of database records that change over time, and the features include:
- COLUMNPROPERTY exposes the ‘ishidden’ property
- Full column support with ROWVERSION
- Enhancements within the SQL Server Management Studio which will include syntax highlighting of temporal keywords and Transact-SQL client side validations
- Master Data Services (MDS)
The MDS was first released in 2008, and it has now been significantly improved upon, which will add extra ease when managing data. Some of the enhancements include:
- Easily set up a sync relationship to sync entity from another model
- SCD type-2 subscription view for member transaction logging
- The Compound Keys index support will include attributes which are customized to improve performance or to enforce constraints.
- DBCC CHECKBD
Within the DBCC CHECKBD function, the following improvements have been made:
- Various improvements to the persisted computed columns and filtered indexes validation function.
- Performance enhancements when validating tables with thousands of partitions
- Reporting Services
The new release now allows for the creation of two additional chart types: Treemap and Subburst.
- Query Execution
The Query Execution function now provides an enhanced diagnostic testing for memory grant usage. There were new XEvents which were added to allow for better diagnostics.
SQL Server 2016 has also ramped up its security measures. For example, the platform includes enhancements to the Always Encrypted technology that will ensure that your data is protected at all times, both at rest and in motion. In addition, it includes safety features which also protect your information both on-premises and in the cloud. These enhancements are all without any application settings. In addition, routine backups are now faster so you can be rest assured that your information is safely updated as often as you require. This is a very exciting addition as the Always Encrypted feature allows for the complete control over both the encryption and decryption.
Improving on the Tried and True Methods
Microsoft has always delivered some of the most groundbreaking database technology, and that fact continues to be true with the release of SQL Server 2016. The diagram below shows the Microsoft data platform:
The updated data platform provides users the ability to maintain a complete platform for hybrid cloud. In addition, it is also simple to deploy to all users within your group and business insights will be available on all mobile devices (Windows, iOS and Android) on apps which can easily be downloaded.
The updated and enhanced SQL Server 2016 will continue to expand Microsoft’s feature rich database platform pushing the combination of on-premise and cloud capabilities. However, one “bottleneck” may be the driver you use in your applications (or the applications you license). Many open source or “free” drivers have failed to stay current with new feature sets, the latest specifications, licensing requirements and/or the ability to handle your growing data throughput requirements. “Free” also does not include that critical, and very important technical support services when you need it.