Assessing your environment

To assess your SQL Server environment and find out whether your Enterprise SQL Server instance is using Enterprise feature, you can take one of the following approaches:

• Using a T-SQL script

• Using the AWS SCT tool

Using a T-SQL script to assess a SQL Server database

To determine whether your Microsoft SQL Server database uses Enterprise edition-specific features, you can run the following T-SQL statement in SQL Server Management Studio (SSMS).

IF (SELECT SERVERPROPERTY('EngineEdition')) = 2
    SELECT 'This is already a Standard SQL Server Edition instance!' AS Status
ELSE
    BEGIN
        --Checking Enterprise features
        CREATE TABLE ##EntFeatures ( dbid int, db_name SYSNAME, feature_name VARCHAR(100))
        EXEC sp_msforeachdb
        N' USE [?]
        IF (SELECT COUNT(*) FROM sys.dm_db_persisted_sku_features) >0
        BEGIN
           INSERT INTO ##EntFeatures
            SELECT db_id(),dbname=DB_NAME(),feature_name FROM sys.dm_db_persisted_sku_features
        END '
        IF (SELECT COUNT(1) FROM ##EntFeatures WHERE dbid>4) >0
            SELECT db_name,feature_name FROM   ##EntFeatures;
        ELSE
            SELECT CONCAT('Ready to move! You are running a(n) ', CONVERT(VARCHAR,SERVERPROPERTY('Edition')),' and are not using any Enterprise features') AS Status
    END

IF OBJECT_ID('tempdb.dbo.##EntFeatures') IS NOT NULL
DROP TABLE ##EntFeatures

If the script returns a single row with the message Ready to move! You are running a(n) <your SQL Server edition>, as shown in the following screen illustration, your SQL Server database can be a candidate for downgrading from Enterprise edition to Standard edition.

If the script returns a list of databases and Enterprise edition features, you must evaluate the possible consequences of removing Enterprise features from those databases.

Using AWS SCT to assess a SQL Server database

When you create a database migration assessment report in AWS SCT, the License Evaluation and Cloud Support section of the report lists the SQL Server features that your source database is using. You can use this list to make an informed decision while migrating to Amazon RDS for SQL Server.

Scaling the SQL Server assessment

AWS SCT now supports batch assessment using the multiserver assessor to evaluate multiple servers based on input that you provide for each schema definition that you want to assess. Your schema definition contains database server connection parameters and the full name of each schema. After assessing each schema, the assessor produces a summary aggregated report that shows the estimated complexity for each possible migration target. It also produces a server-level report that includes the License Evaluation and Cloud Support section.