What's new in Analytics Platform System, a scale-out MPP data warehouse
See what’s new in the latest Appliance Updates for Microsoft® Analytics Platform System (APS). APS is a scale-out on-premises appliance that hosts MPP SQL Server Parallel Data Warehouse.
APS2016 is a prerequisite to upgrade to AU7. Following are the new features for APS AU7:
Auto-create and auto-update statistics
APS AU7 creates and updates statistics automatically, by default. To update statistics settings, administrators can use a new feature switch menu item in the Configuration Manager. The feature switch controls the auto-create, auto-update, and asynchronous update behavior of statistics. You can also update statistics settings with the ALTER DATABASE (Parallel Data Warehouse) statement.
Select @var is now supported. For more information, see select local variable
Query hints HASH and ORDER GROUP are now supported. For more information, see Hints(Transact-SQL) - Query
APS AU7 introduces Feature Switch in Configuration Manager. AutoStatsEnabled and DmsProcessStopMessageTimeoutInSeconds are now configurable options that can be changed by Administrators.
With APS AU7 software, we are packaging and providing the Intel BIOS update that fixes “speculative execution side-channel attacks” (aka. Spectre and Meltdown vulnerabilities). Though packaged together, the BIOS update is installed manually and not part of the APS AU7 software install. Microsoft advises all customers to install the BIOS updated. Microsoft has measured the effect of Kernel Virtual Address Shadowing (KVAS), Kernel Page Table Indirection (KPTI) and Indirect Branch Prediction mitigation (IBP) on various SQL workloads in various environments and found significant degradation on some workloads. We recommend that you test the performance effect of enabling BIOS update before you deploy them in a production environment. If the performance effect of enabling these features is too high for an existing application, you can consider whether isolating your APS Appliance from untrusted code running is a better mitigation for your application. See SQL Server guidance here.
These are the new features for APS 2016:
SQL Server 2016
APS 2016 runs on the latest SQL Server 2016 release and uses the default database compatibility level 130. SQL Server 2016 makes it possible to support some of the new features such as secondary indexes for clustered columnstore indexes and Kerberos for PolyBase.
APS 2016 supports these T-SQL compatibility improvements. These additional language elements make it easier to migrate from SQL Server and other data sources.
- Column-level SQL collations are now supported in addition to Windows collations.
- Nonclustered indexes on clustered columnstore indexes improve performance of queries that search for specific values in the clustered columnstore index.
- sp_spaceused() displays the disk space used or reserved in a table or database.
- Wide tables support is the same as SQL Server 2016. The previous limit of 32K for the row size no longer exists.
- VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX). These LOB data types have a maximum size of 2 GB. To load these objects use bcp Utility. Polybase and dwloader do not currently support these data types.
- NUMERIC and DECIMAL data types.
- ROWS or RANGE in the OVER clause of the SELECT statement.
- Compatibility with Hortonworks HDP 2.4 and HDP 2.5
- Kerberos support via database scoped credentials
- Credential support with Azure Storage Blobs
Install and upgrade enhancements
Enterprise architecture updates Upgrading your existing appliance to APS 2016 installs the latest firmware and driver updates, which include security fixes.
A new appliance from HPE or DELL includes all the latest updates plus:
- Latest generation processor support (Broadwell)
- Update to DDR4 DIMMs
- Improved DIMM throughput
- Fully Qualified Domain Name (FQDN) support makes it possible to setup a Domain trust to the appliance.
- To use FQDN, you need to do a full upgrade and opt-in during the upgrade.
Reduced downtime Installing or upgrading to APS 2016 is faster and requires less downtime than previous releases. To reduce downtime, the install or upgrade:
- Streamlines applying WSUS updates by using an image that contains all the updates through June 2016
- Applies security updates with the driver and firmware updates
- Places the latest hotfixes and the appliance verification utility (PAV) on your appliance so they are ready to install with no need to download them.