SQL Server 2016 SP1: Things you should know
With the release of SQL Server 2016 SP1, a number of programmability features which were only available in Enterprise Edition are now enabled in Standard, Web, Express and LocalDB editions of SQL Server 2016. These features will allow developers and ISVs with consistent programming experience to build applications which scale across all edition of SQL Server. For more information on what’s new in SQL Server 2016 SP1, please read our SQL Releases blog. In this blog post, I will try to cover few FAQs and information which you need to be aware of related to SQL Server 2016 SP1 if you are running lower editions (Standard, Web, Express and LocalDB).
Uninstalling SQL Server 2016 SP1 (Not recommended)
(Why? No, Seriously, Why? :) ) If for any reason you choose to uninstall SQL Server 2016 SP1, the uninstallation of SQL Server 2016 SP1 is not blocked and you will be able to uninstall SQL Server 2016 SP1 like any other service pack. However, if you are running Standard, Web, Express edition of SQL Server and leveraging some of the new features which are unlocked only starting SQL Server 2016 SP1, you might see some unforeseen errors or databases might even be left in suspect state after uninstallation of SQL Server 2016 SP1. Even worse would be if the system databases are using new features for example, partitioned table in master database, it can lead to SQL Server instance unable to start after uninstalling SQL Server 2016 SP1 . Hence it is recommended to validate all the new features are disabled or dropped before you choose to uninstall SQL Server 2016 SP1 on editions other than Enterprise Edition. It is not possible to drop memory_optimized_data filegroup . Hence if you have setup memory_optimized_data filegroup on your database with SP1, you should not uninstall SQL Server 2016 SP1 in that case else the database will get in suspect mode with following error message logged in Errorlog
2016-11-24 04:45:39.74 spid15s Error: 41381, Severity: 21, State: 1.
2016-11-24 04:45:39.74 spid15s The database cannot be started in this edition of SQL Server because it contains a MEMORY_OPTIMIZED_DATA filegroup. See Books Online for more details on feature support in different SQL Server editions.
Note: If you are running Enterprise edition of SQL Server 2016 SP1, none of this applies and you can uninstall SP1 without any issues or additional checks.
To assist DBAs in performing checks in an instance to see if new features are in use before you plan uninstall SP1, I have created below T-SQL script which checks all the databases of the instance to see if database is using any new features which needs to be disabled or dropped before you uninstall SQL Server 2016 SP1 on lower editions.
The script is also uploaded in Tigertools github repository to revise it if there are bugs or issues found or for the SQL Community to further enhance it.
SET NOCOUNT ON
DECLARE @cnt int;
DECLARE @edition int;
SELECT @edition= CONVERT(int,SERVERPROPERTY('EngineEdition'))
IF @edition <> 3
BEGIN
DROP TABLE IF EXISTS tempdb.dbo.tbl;
CREATE TABLE tempdb.dbo.tbl(db sysname, feature_name nvarchar(4000), features_in_use bit)
insert INTO tempdb.dbo.tbl select 'server','IsPolybaseInstalled',CAST(SERVERPROPERTY ('IsPolybaseInstalled') as int);
EXEC master.sys.sp_MSforeachdb 'USE [?];
DECLARE @features_in_use int;
SELECT @features_in_use=count(1) from sys.dm_db_persisted_sku_features;
IF (@features_in_use > 0)
INSERT INTO tempdb.dbo.tbl SELECT DB_name(),feature_name,1 from sys.dm_db_persisted_sku_features;
SELECT @features_in_use=count(1) from sys.column_master_keys;
IF (@features_in_use > 0)
INSERT INTO tempdb.dbo.tbl VALUES(DB_NAME(),''Always Encrypted'',1);
SELECT @features_in_use=count(1) from sys.security_policies;
IF @features_in_use > 0
INSERT INTO tempdb.dbo.tbl VALUES(DB_NAME(),''Row-level security'',1);
SELECT @features_in_use=count(1) from sys.masked_columns;
IF @features_in_use > 0
INSERT INTO tempdb.dbo.tbl VALUES(DB_NAME(),''Dynamic Data Masking'',1);
SELECT @features_in_use=count(1) from sys.database_audit_specifications;
IF @features_in_use > 0
INSERT INTO tempdb.dbo.tbl VALUES(DB_NAME(),''Database Auditing'',1);'
SELECT @cnt=count(1) FROM tempdb.dbo.tbl WHERE features_in_use=1
IF @cnt>0
BEGIN
SELECT * from tempdb.dbo.tbl where features_in_use = 1;
THROW 60000, 'The instance cannot be downgraded from SP1 as it contains atleast 1 database mentioned above with SKU features not available in SQL Server 2016 RTM. If downgrade is attempted, it can leave the database in suspect mode. DROP or DISABLE the feature and rerun the script to confirm before you downgrade',0
END
ELSE
THROW 60000,'The instance can be downgraded as it doesnt contain any database leveraging new features enabled in SP1 on lower editions',0
DROP TABLE tempdb.dbo.tbl
END
ELSE
PRINT 'The instance can be downgraded as Enterprise Edition is not impacted in SP1'
If any of the databases contains new features, the above script displays the database names, features enabled and an error message warning the users to drop or disable the feature before you choose to uninstall SP1.
Restoring SQL Server 2016 SP1 databases to lower version of SQL Server 2016 RTM (Not recommended)
(Once again why?) This is not a recommended scenario and our recommendation will be to install SP1 and restore the database on SQL Server 2016 SP1. If for any reason you choose to restore the database to lower version of SQL Server 2016, the restore should work unless you are using newer features enabled in lower edition which can lead to unforeseen issue or recovery blocked after restore as discussed earlier.
sys.dm_db_persisted_sku_features is not updated in SQL Server 2016 SP1
In SQL Server 2016 SP1, we haven’t made any changes to sys.dm_db_persisted_sku_features to support backward compatibility and avoid any breaking changes. Although sys.dm_db_persisted_sku_features was useful for identifying persisted features in Enterprise Edition, it is not Enterprise edition only. In fact, in the above script, we have leveraged sys.dm_db_persisted_sku_features on lower editions to identify the new features in use. The DMV definitely has some of the new features missing in there but the above script shows possible alternative or workaround for this.
In-place downgrade of Enterprise Edition to lower editions
This scenario isn’t supported.
Restoring Enterprise Edition databases on Standard Edition with SQL Server 2016 SP1
This scenario is allowed and if you are using any of the Enterprise edition features like Partitioned tables, In-Memory OLTP tables, Compression which are now enabled with SQL Server 2016 SP1 in a database created in Enterprise edition, you should be able to restore the database on Standard edition of SQL Server 2016 SP1 and it should work. If you are using TDE which is still Enterprise edition only, the restore will be (obviously) blocked in Standard edition as TDE is still not supported in lower editions.
Parikshit Savjani
Senior Program Manager (@talktosavjani)
Comments
- Anonymous
October 24, 2017
Why? Because ... DPM Server 2016 installer crashes if SQL 2016 SP1 is installed, but works fine if SP1 is installed after DPM Server installation.- Anonymous
October 24, 2017
I haven't got chance to see or test DPM Server 2016 installer so I am not sure why it crashes or they are possibly hitting any known issue. Have you tried SQL 2016 SP1 + latest CU ?. You might want to check or post that in DPM forums as well.
- Anonymous