Microsoft has a new release for the Analytics Platform System (APS).  This appliance update is called APS 2016 and has been released to manufacturing and is now generally available.  APS is Microsoft’s scale-out Massively Parallel Processing fully integrated system for data warehouse specific workloads.

This release is built on the latest SQL Server 2016 release, offers additional language surface coverage to aid in migrations from SQL Server and other platforms, adds PolyBase connectivity to the current versions of Hadoop from Hortonworks, additional PolyBase security with Kerberos support and credential support for Azure Storage Blobs, greater indexing and collation support and improvements to the setup and upgrade experience with FQDN support.

The majority of these capabilities have shipped in the monthly releases of Azure SQL Data Warehouse service and/or SQL Server 2016 following the cloud first principle of shipping, getting feedback, and improving rapidly across all of our products.

What’s New in the Release:

SQL Server 2016 re-platform and TSQL compatibility improvements to reduce migration friction from SQL SMP

• Default database compatibility level 130
• Column-level SQL collations – users can choose SQL collations in addition to previously supported Windows collations
• Secondary index support for CCI tables – enables better performance of lookup queries in CCI
• Wide row support – ability to have large objects in a row (>32k) and a large number of rows (>32k) in a table (Note: bulk insert/bcp is the only way to load large objects currently)
• VARCHAR(MAX), NVARCHAR (MAX) and VARBINARY(MAX) – maximum storage size of 2GB

Data Types:

• UNIQUEINDENTIFIER – a 16-byte GUID
• NUMERIC – numeric data types that have fixed precision and scale

Windowing Functions:

• Full window frame syntax support for ROWS or RANGE based windows including UNBOUNDED PRECEEDING, CURRENT ROW, UNBOUNDED FOLLOWING and BETWEEN
• Additional windowing functions support including FIRST_VALUE, LAST_VALUE, CUME_DIST, PERCENT_RANK

Security Functions:

• CHECKSUM() and BINARY_CHECKSUM() – used for building hash indexes
• IS_MEMBER() – indicates whether the current user has a specified database role
• HAS_PERMS_BY_NAME() – evaluates the effective permission of the current user on a securable

Additional Functions:

• NEWID() – creates a UNIQUEIDENTIFIER
• RAND() – returns a pseudo-random float value from 0-1
• DBCC SHRINKDATABASE() – shrinks the size of data and log files in a database
• sp_spaceused() – displays the disk space used or reserved in a table or database

Polybase/Hadoop Enhancements

• Support for the latest distributions from Hortonworks (HDP 2.4 and HDP 2.5)
• Kerberos support via database scoped credentials
• Credential support with Azure Storage Blobs

Install/Upgrade Enhancements

• WSUS updated images
• Security updates taken in updated firmware and drivers
• Fully Qualified Domain Name support
• APS installer includes PAV and hotfixes
• The latest generation processor support (Broadwell), DDR4 DIMMs, and improved DIMM throughput

The latest APS update is an addition to already existing data warehouse portfolio from Microsoft, covering a range of technology and deployment options that help customers get to insights faster.  Customers exploring data warehouse products can also consider SQL Server with Fast Track for Data Warehouse or Azure SQL Data Warehouse, a cloud based fully managed service.

For more details about these features, please visit our online documentation or download the client tools.