Feature consideration when SQL Server migrate or upgrade

某些功能受限於特定版本(例如Enterprise)，啟用這些功能的資料庫無法移至(Restore)不支援它們的版本(例如Standard)。
版本上不支援之功能必須先移除，資料庫才可以移轉到所有可用的版本。

Partition Table

SQL 2005/SQL 2008/SQL 2008 R2/SQL 2012/SQL 2014，只有Enterprise支援
SQL 2016 SP1之後，全版本(Enterprise,Standard,Web,Express)皆支援

Backup compression

SQL 2008 只有Enterprise支援
SQL 2008 R2/SQL 2012/SQL 2014/SQL 2016/SQL 2017/SQL 2019，則Enterprise,Business Intelligence,Standard支援

Data Compression, Change data capture

SQL 2008/SQL 2008 R2/SQL 2012/SQL 2014，只有Enterprise支援
SQL 2016 SP1之後，全版本(Enterprise,Standard,Web,Express)皆支援

Transparent database encryption

直至目前最新版SQL 2019也只有Enterprise支援

列出目前資料庫中已啟用的版本特定功能

SQL Server 2005

select * from
   (-- VarDecimal Storage Format
    select case
             when max(objectproperty(object_id, N'TableHasVarDecimalStorageFormat')) = 0
               then ''
             else 'VarDecimal Storage'
           end as Enterprise_feature_name
      from sys.objects
    UNION ALL
    -- Partitioning
    select case
             when max(partition_number) > 1
               then 'Partitioning'
             else ''
           end
      from sys.partitions
) t
where Enterprise_feature_name <> ''


SQL Server 2008之後
select * from sys.dm_db_persisted_sku_features

啟用或停用功能

Create Partitioned Tables and Indexes
https://docs.microsoft.com/en-us/sql/relational-databases/partitions/create-partitioned-tables-and-indexes?view=sql-server-ver15

Transparent Data Encryption (TDE)
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver15
建立資料庫憑證，ALTER DATABASE啟用加密

sys.sp_cdc_disable_db (Transact-SQL)
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-disable-db-transact-sql?view=sql-server-ver15

Columnstore indexes: Overview
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver15

若要移除 Vardecimal 儲存格式，請使用 sp_tableoption 陳述式

Data Compression
https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression?view=sql-server-ver15

FILESTREAM (SQL Server)
https://docs.microsoft.com/en-us/sql/relational-databases/blob/filestream-sql-server?view=sql-server-ver15

The Memory Optimized Filegroup
https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/the-memory-optimized-filegroup?view=sql-server-ver15



Reference:
Editions and supported features of SQL Server 2016
https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2016?view=sql-server-ver15

Features Supported by the Editions of SQL Server 2014
https://docs.microsoft.com/en-us/sql/getting-started/features-supported-by-the-editions-of-sql-server-2014?view=sql-server-2014

Features Supported by the Editions of SQL Server 2012
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/cc645993(v=sql.110)?redirectedfrom=MSDN

Features Supported by the Editions of SQL Server 2008 R2
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/cc645993(v=sql.105)?redirectedfrom=MSDN

Features Supported by the Editions of SQL Server 2008
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/cc645993(v=sql.100)?redirectedfrom=MSDN

Editions and Components of SQL Server 2005
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms144275(v=sql.90)