Assessing your environment - AWS Prescriptive Guidance

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.

                Status message indicating that there are no dependencies.

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.

                Script results listing databases that have dependencies on Enterprise edition.

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.

                List of dependencies that need to be removed before downgrading and features that cannot be supported after downgrading.

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.