SQL 2016 brings to the table some very exciting features that will benefit on-premise, Azure, and hybrid customers
In June of 2016, Microsoft announced the general availability of SQL 2016 which they are touting as “the world’s fastest and most price-performant database for HTAP (Hybrid Transactional and Analytical Processing) with updateable, in-memory column stores and advanced analytics through deep integration with R Services”. Basically, it is a fully integrated platform that brings the capabilities of data warehousing, data integration, business intelligence, and analytics into a single platform with a heavy focus on cloud and hybrid architecture. As with every SQL release, Microsoft always introduces new capabilities, however SQL 2016 brings to the table some very exciting features that will benefit on-premise customers, Azure customers and now even a hybrid of both. Here are some of the capabilities that SQL 2016 offers, and should be considered when looking to upgrade.
Hybrid cloud architecture with Stretch Database
Stretch Database is a hybrid cloud approach that combines SQL on-premise database with SQL Azure database in the cloud. The Azure SQL database acts as a repository for data that is not frequently accessed or data that isn’t used but still needs to be stored for business purposes. As enterprise level databases continue to grow in size and single tables can reach millions or billions of rows, managing the data as well as providing proper redundancy can become extremely expensive. SQL’s Stretch Database provides a mechanism to store specific areas of the data that need to be maintained but maybe not frequently accessed. The data in SQL Azure and SQL on-premise is connected together and can still be accessed through the end user applications as a regular SQL database. Since some of the data is in Azure, it is scalable and businesses only end up paying for the space that is needed versus investing in the entire infrastructure upfront.
In-memory OLTP for faster processing
In-memory OLTP (Online Transaction Processing) was initially introduced with SQL 2014, however Microsoft has made vast improvements with SQL 2016. In-memory OLTP can improve the performance of data warehouses by putting high concurrency transactional workloads to in-memory tables versus disk which allows for performing real-time analytics. SQL 2016 has added the ability to store up to 2 TB of data within in-memory optimized tables, added support for column store indexes, foreign keys, check and unique constraints, and indexing as well as other enhancements. SSAS cube partitions can take advantage of these enhancements as well using either Relational Online Analytical Processing mode (ROLAP) or DirectQuery mode for faster query times and optimized performance.
Integration with R
With SQL 2016 the R analytic framework is now embedded natively into SQL Server. This alleviates the need to install a separate instance of the R framework which is beneficial since there is now less data movement between SQL Server and an external R SQL server. R code can be written and executed in native SQL Server tools which are familiar to many SQL users. There are two methods that are provided for embedded R services: R Services In-Database requires a database engine instance of SQL Server 2016 and is available in Standard, Developer and Enterprise editions; while the R Services Standalone edition does not require an SQL Server instance to be installed and provides R connectivity tools but is only available with SQL Server Enterprise edition. However, with either option, the integration of R services within SQL 2016 means that R code, scripts and solutions can be designed, managed and executed all within SQL Server.
Azure SQL Data Warehouse
Azure SQL Data Warehouse is built on SQL 2016 OLTP and is the next generation of Microsoft’s Parallel Data Warehouse (PDW). Azure DW can support petabytes of data and is built for systems that contain data in the terabytes. What is interesting about Azure DW is that it is very cost effective as you only pay for the data that is being used and processed. Azure DW can also be paused, at which point there is no charge but the data still resides in Azure Blob storage. This provides another level of data security as Azure Blob storage provides redundancy with multiple data centers. Azure DW can be scaled up or down quickly depending on workload demands and can accommodate rapid data growth. Another built-in feature is automatic snapshot of the data which is stored for seven days and has multiple restore points.
Polybase – Integration of relational and non-relational big data
SQL Servers Polybase allows for data residing in Hadoop or Azure Blob Storage (non-structured) to be combined with relational data residing in SQL Server (structured). Polybase objects are set up in SQL Server which contains data from Hadoop or Azure Blob Storage. Once the data is in the Polybase object, users can use familiar tools such as SSMS to view the data and familiar query languages such as T-SQL to query the data. Users do not need specific knowledge of Hadoop or Azure Blob Storage in order to analyze the data and/or combine it with relational data.
JSON Integration
Native JSON support in SQL Server allows for relational data to easily interchange with JSON data. JSON data can be stored in relational format and relational data can be stored in SQL formats using T-SQL. The new clause FOR JSON in the SELECT statement will convert SQL datatypes for the appropriate JSON data types. This enhancement will make it much easier to exchange JSON data with SQL Server data and vice versa.
Always Encrypted
Always Encrypted is a new capability that provides more security than Transparent Data Encryption. Always Encrypted ensures that SQL Server itself, or anyone accessing SQL Server, cannot see the data as the data is decrypted at the application level. Applications reading Always Encrypted data need to have the encryption key before the data can be displayed. This option is beneficial in cloud environments where security is a concern as the application is the only layer than can decrypt the data.
SQL Server 2016 has a variety of enhancements that will benefit both on-premise and cloud deployments and these new enhancements should be looked at when upgrading to SQL 2016 to enhance your business. Halo will soon integrate with many of the SQL 2016 features to continue delivering a state-of-the-art supply chain intelligence solution.
