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)

Generate a self-signed certificate using openssl.exe 使用openssl.exe產生自簽憑證

Generate a self-signed certificate using openssl.exe


1.產生RSA Private Key

設定環境變數(Optional)
set OPENSSL_CONF=c:\Program Files\Tableau\Tableau Server\packages\apache.\conf\openssl.cnf

cd "c:\Program Files\Tableau\Tableau Server\packages\apache.\bin"

PS.依照安裝的版本openssl.exe在不同路徑
cd C:\Apache2.2\bin\
or
cd C:\Program Files\Git\usr\bin\

產生RSA Private Key
openssl.exe genrsa -out MyDomain.com.key 4096
未設定環境變數OPENSSL_CONF
openssl.exe genrsa -out MyDomain.com.key 4096 -config ..\conf\openssl.cnf

-out後面指定的是產生的檔案名稱，如果域名是MyDomain.com，則，可以用MyDomain.com.key
4096是Private key的金鑰長度(單位bit)，建議最少2048 bit，不指定預設為512 bit

若 private key 想要加密-des or -des3，例如
openssl.exe genrsa -des3 -out MyDomain.com.key 4096

pass phrase for key: 輸入一組密碼


2.產生certificate signing request (CSR) file
openssl.exe req -new -key MyDomain.com.key -out MyDomain.com.csr
or
openssl.exe req -new -key MyDomain.com.key -out MyDomain.com.csr -config ..\conf\openssl.cnf

Country Name (2 letter code) [AU]:
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:
Email Address []:

Common Name 請輸入the URL that will be used to reach the Server
if you reach Server by typing webserver.MyDomain.com in the address bar of your browser, then webserver.MyDomain.com is the common name


3.產生CRT檔
有些服務需要PEM-encoded X509 certificates副檔名為crt的憑證檔 (例如Tableau Server)
openssl.exe x509 -in MyDomain.com.csr -out MyDomain.com.crt -req -signkey MyDomain.com.key -days 365

Configure SSL for External HTTP Traffic to and from Tableau Server
https://help.tableau.com/current/server/en-us/ssl_config.htm

Tableau Server Self signed certificate process for both web page and Tableau Desktop
https://greatsingapore.wordpress.com/2018/05/25/tableau-server-self-signed-certificate-process-for-both-web-page-and-tableau-desktop/


4.用戶端需安裝CRT憑證
MyDomain.com.crt
將憑證安裝到[受信任的根憑證授權單位] Trusted Root Certification Authorities

SSIS Custom Component (SSIS 客製化元件)

SSIS Custom Component
SSIS 客製化元件

Steps to Build and Deploy Custom SSIS Components
https://bennyaustin.com/2009/06/30/steps-to-build-and-deploy-custom-ssis-components/
Data Flow - Source

Developing a Custom SSIS Source Component
https://www.red-gate.com/simple-talk/sql/ssis/developing-a-custom-ssis-source-component/
Data Flow - Transformations