Skip to content

Instantly share code, notes, and snippets.

@MasayukiOzawa

MasayukiOzawa/AzureSQLDbFeature.md Secret

Last active May 19, 2021
Embed
What would you like to do?
Database Migration Assistant Rule List
Title ChangeCategory FeatureParityRecommendationCategory RuleSeverity Impact Recommendation AdditionalInformation
BufferPoolTitle Information UnsupportedFeature Low BufferPoolImpact BufferPoolRecommendation BufferPoolAdditionalInfo
FailoverClusteringTitle MigrationBlocker UnsupportedFeature Medium FailoverClusteringImpact FailoverClusteringRecommendation FailoverClusteringAdditionalInfo
DatabaseMailTitle MigrationBlocker UnsupportedFeature Medium DatabaseMailImpact DatabaseMailRecommendation
ServerScopedCredentialsTitle MigrationBlocker PartiallySupportedFeature Medium ServerScopedCredentialsImpact ServerScopedCredentialsRecommendation ServerScopedCredentialsAdditionalInfo
ServerAuditsTitle MigrationBlocker PartiallySupportedFeature Medium ServerAuditsImpact ServerAuditsRecommendation ServerAuditsAdditionalInfo
MaintenancePlansTitle MigrationBlocker UnsupportedFeature Medium MaintenancePlansImpact MaintenancePlansRecommendation MaintenancePlansAdditionalInfo
SSISTitle MigrationBlocker UnsupportedFeature Medium SSISImpact SSISRecommendation SSISAdditionalInfo
SSASTitle MigrationBlocker UnsupportedFeature Medium SSASImpact SSASRecommendation SSASAdditionalInfo
SSRSTitle MigrationBlocker UnsupportedFeature Medium SSRSImpact SSRSRecommendation SSRSAdditionalInfo
TraceFlagsTitle MigrationBlocker UnsupportedFeature Medium TraceFlagsImpact TraceFlagsRecommendation
ServerScopedTriggersTitle MigrationBlocker UnsupportedFeature Medium ServerScopedTriggersImpact ServerScopedTriggersRecommendation
PolicyBasedManagementTitle MigrationBlocker UnsupportedFeature Medium PolicyBasedManagementImpact PolicyBasedManagementRecommendation PolicyBasedManagementAdditionalInfo
UserDefinedErrorMessagesTitle MigrationBlocker UnsupportedFeature Medium UserDefinedErrorMessagesImpact UserDefinedErrorMessagesRecommendation UserDefinedErrorMessagesAdditionalInfo
DataCollectionTitle MigrationBlocker UnsupportedFeature Medium DataCollectionImpact DataCollectionRecommendation DataCollectionAdditionalInfo
WindowsAuthenticationTitle MigrationBlocker UnsupportedFeature Medium WindowsAuthenticationImpact WindowsAuthenticationRecommendation WindowsAuthenticationAdditionalInfo
EncryptionTitle MigrationBlocker UnsupportedFeature Medium EncryptionImpact EncryptionRecommendation
AgentJobsTitle MigrationBlocker UnsupportedFeature Medium AgentJobsImpact AgentJobsRecommendation AgentJobsAdditionalInfo
AlwaysOnAvailabilityGroupsTitle MigrationBlocker UnsupportedFeature Medium AlwaysOnAvailabilityGroupsImpact AlwaysOnAvailabilityGroupsRecommendation AlwaysOnAvailabilityGroupsAdditionalInfo
FileStreamTitle MigrationBlocker UnsupportedFeature Medium FileStreamImpact FileStreamRecommendation FileStreamAdditionalInfo
FullTextSearchTitle MigrationBlocker PartiallySupportedFeature Medium FullTextSearchImpact FullTextSearchRecommendation FullTextSearchAdditionalInfo
CDCTitle MigrationBlocker UnsupportedFeature Medium CDCImpact CDCRecommendation CDCAdditionalInfo
LogShippingTitle MigrationBlocker UnsupportedFeature Medium LogShippingImpact LogShippingRecommendation LogShippingAdditionalInfo
DatabaseMirroringTitle MigrationBlocker UnsupportedFeature Medium DatabaseMirroringImpact DatabaseMirroringRecommendation DatabaseMirroringAdditionalInfo
TransactionalReplicationTitle BehaviorChange PartiallySupportedFeature Medium TransactionalReplicationImpact TransactionalReplicationRecommendation TransactionalReplicationAdditionalInfo
ServiceBrokerTitle MigrationBlocker UnsupportedFeature Medium ServiceBrokerImpact ServiceBrokerRecommendation ServiceBrokerAdditionalInfo
CrossDataseReferencesTitle BreakingChange PartiallySupportedFeature Medium CrossDataseReferencesImpact CrossDataseReferencesRecommendation CrossDataseReferencesAdditionalInfo
LinkedServerTitle MigrationBlocker UnsupportedFeature Medium LinkedServerImpact LinkedServerRecommendation LinkedServerAdditionalInfo
UnsupportedObjectTypesTitle MigrationBlocker UnsupportedFeature Medium UnsupportedObjectTypesImpact UnsupportedObjectTypesRecommendation UnsupportedObjectTypesAdditionalInfo
FixedCatalogCollationTitle MigrationBlocker UnsupportedFeature Medium FixedCatalogCollationImpact FixedCatalogCollationRecommendation FixedCatalogCollationAdditionalInfo
TrustWorthyTitle MigrationBlocker UnsupportedFeature Medium TrustWorthyImpact TrustWorthyRecommendation
DbChainingTitle MigrationBlocker UnsupportedFeature Medium DbChainingImpact DbChainingRecommendation
InMemoryTableTitle BehaviorChange PartiallySupportedFeature Medium InMemoryTableImpact InMemoryTableRecommendation
TablePartitioningTitle MigrationBlocker PartiallySupportedFeature Medium TablePartitioningImpact TablePartitioningRecommendation
FileGroupsTitle MigrationBlocker UnsupportedFeature Medium FileGroupsImpact FileGroupsRecommendation
EncryptionTitle MigrationBlocker UnsupportedFeature Medium EncryptionImpact EncryptionRecommendation
RuleId Title ChangeCategory RuleSeverity Impact Recommendation AdditionalInformation
46010 One or more objects contain statements that are not supported in Azure SQL Database [46010] MigrationBlocker High While assessing the schema on the source database, one or more syntax issues were found. Syntax issues on the source database indicate that some objects contain syntax that is unsupported in Azure SQL Database. Note that some of these syntax issues may be reported in more detail as separate issues in this assessment. Review the list of objects and issues reported, fix the syntax errors, and re-run assessment before migrating this database.
46022 [46022] FASTFIRSTROW is not a recognized table or a view hint BreakingChange High The FASTFIRSTROW tells the optimizer that it is important to have the first row returned as fast as possible, allowing the application to return results to the users faster This hint is deprecated. The OPTION (FAST 1) is preferred as the FASTFIRSTROW syntax is deprecated.
70504 [70504] References to only one-part or two-part objects are supported in Azure SQL Database MigrationBlocker High

Queries or references using three- or four-part names are not supported in Azure SQL Database. Three-part name format, [database_name].[schema_name].[object_name], is supported only when the database_name is the current database or the database_name is tempdb and the object_name starts with #.

  • Move the dependent datasets from other databases into the database that is being migrated.
  • Migrate the dependent database(s) to Azure and use 'Elastic Database Query' functionality to query across Azure SQL databases.
  • Azure SQL Database elastic database query overview
    70527 [70527] One or more users are pointing to wrong Windows logins MigrationBlocker High The assessment detected users with user names that do not match their login names. Ensure that the user and login names match for the users reported in the "Impacted objects" section.
    70557 [70557] Failed to load the assembly MigrationBlocker High The assembly is either corrupt or not valid, which may block you from migrating to Azure SQL Database. The assembly is either corrupt or not valid.
    70590 [70590] Undeclared variables or parameters found MigrationBlocker High Objects were found that have statements referencing undeclared variables or parameters. These objects may block you from migrating to Azure SQL Database. The "Impacted objects" and "Object details" sections provide the specific object names and references where the undeclared variables or parameters are used. Declare those variables and parameters and re-execute the assessment for any further issues.
    70593 [70593] REVOKE object permissions statement is not supported in Azure SQL Database MigrationBlocker High The REVOKE statement helps to revoke permissions on a table, view, table-valued function, stored procedure, extended stored procedure, scalar function, aggregate function, service queue, or synonym. Revoking some of these object permissions may not supported in Azure SQL Database. The specific object names and associated REVOKE statements are provided in the "Impacted objects" and "Object details" sections. Please review and fix those objects before migrating to Azure SQL Database.
    71501 [71501] Unresolved references found MigrationBlocker High One or more objects were found that contain unresolved references, which may block migration to Azure SQL Database. Address the unresolved references reported in "Object details" section.
    71502 [71502] Unresolved references found MigrationBlocker High One or more objects were found that contain unresolved references, which may block migration to Azure SQL Database. Address the unresolved references reported in "Object details" section.
    71501 [71501] One or more database options set on this database are not supported in Azure SQL Database MigrationBlocker High Database options that have unresolved references may block database migration to Azure SQL Database. The following Database options are not supported in Azure SQL Database:
    • change_tracking_option
    • database_mirroring_option
    • date_correlation_optimization_option
    • db_state_option
    • db_user_access_option
    • delayed_durability_option
    • external_access_option
    • FILESTREAM_options
    • HADR_options
    • recovery_option
    • service_broker_option

    Disable these from the database before migrating to Azure SQL Database.

    Azure SQL Database Options
    71501 [71501] Logins mapped to either certificate or asymmetric key are not supported in Azure SQL Database MigrationBlocker High

    Azure SQL Database supports two types of authentication:

    SQL Authentication, which uses a username and password

    Azure Active Directory Authentication, which uses identities managed by Azure Active Directory and is supported for managed and integrated domains

    Windows authentication (integrated security) is not supported in Azure SQL Database. Database users mapped to Windows logins not supported.

    Remove the reported unsupported users before migration and start using either SQL Authentication or Azure Active Directory Authentication after migrating to Azure SQL Database.

    Logins mapped to either certificate or asymmetric key are also not supported.

    71561 [71561] Objects found containining references to unresolved objects, which are not supported in Azure SQL Database MigrationBlocker High

    Queries or references using three- or four-part names not supported in Azure SQL Database. Three-part name format, [database_name].[schema_name].[object_name], is supported only when the database_name is the current database or the database_name is tempdb and the object_name starts with #.

  • Move the dependent datasets from other databases into the database that is being migrated.
  • Migrate the dependent database(s) to Azure and use 'Elastic Database Query' functionality to query across Azure SQL databases.
  • Azure SQL Database elastic database query overview
    71562 [71562] Cross database queries using three- or four-part names not supported in Azure SQL Database MigrationBlocker High

    Queries or references using three- or four-part names not supported in Azure SQL Database. Three-part name format, [database_name].[schema_name].[object_name], is supported only when the database_name is the current database or the database_name is tempdb and the object_name starts with #.

  • Move the dependent datasets from other databases into the database that is being migrated.
  • Migrate the dependent database(s) to Azure and use 'Elastic Database Query' functionality to query across Azure SQL databases.
  • Azure SQL Database elastic database query overview
    71624 [71624] Granting CONNECT permission to the guest user in Azure SQL Database is not permitted MigrationBlocker High In SQL Server, a special user, guest, exists to permit access to a database for logins that are not mapped to a specific database user. When guest user is enabled and connect permissions granted, any login can use the database through the guest user.

    Granting CONNECT permission to the guest user in Azure SQL Database is not permitted.

    Revoke CONNECT permission from GUEST user by executing "REVOKE CONNECT FROM GUEST" before migrating to Azure SQL Database.

    71626 [71626] One or more SQL Server or database features are not supported in Azure SQL Database MigrationBlocker High

    These unsupported features may block migration to Azure SQL Database.

    These unsupported features may block migration to Azure SQL Database platform. Review the "Impacted Objects" and "Object Details" sections for the specific object type, object and error details, fix the object and re-execute the assessment.

    71626 [71626] Service Broker feature(Queues) is not supported in Azure SQL Database MigrationBlocker High SQL Server Service Broker provides native support for messaging and queuing applications in the SQL Server Database Engine.

    Service Broker feature is not supported in Azure SQL Database. You need to disable the Service Broker feature using the following command before migrating this database to Azure:

    ALTER DATABASE [database_name] SET DISABLE_BROKER;

    In addition, you may also need to remove or stop the Service Broker endpoint in order to prevent messages from arriving in the SQL instance.

    Once the database has been migrated to Azure, you can look into Azure Service Bus functionality to implement a generic, cloud-based messaging system instead of Service Broker.

    Service Bus
    71626 [71626] Service Broker feature(Services) is not supported in Azure SQL Database MigrationBlocker High SQL Server Service Broker provides native support for messaging and queuing applications in the SQL Server Database Engine.

    Service Broker feature is not supported in Azure SQL Database. You need to disable the Service Broker feature using the following command before migrating this database to Azure:

    ALTER DATABASE [database_name] SET DISABLE_BROKER;

    In addition, you may also need to remove or stop the Service Broker endpoint in order to prevent messages from arriving in the SQL instance.

    Once the database has been migrated to Azure, you can look into Azure Service Bus functionality to implement a generic, cloud-based messaging system instead of Service Broker.

    Service Bus
    71627 [71627] Detected one or more features unsupported or partially-supported by Azure SQL Database MigrationBlocker High

    These unsupported features may block migration to Azure SQL Database.

    Review the "Impact Object" and "Object Details" sections for the specific object type, object and error details, fix the object and re-execute the assessment.

    71627 [71627] CLR Assemblies not supported in Azure SQL Database MigrationBlocker High CLR integration lets you create a user-defined function in SQL Server. A user-defined function is a Transact-SQL or common language runtime (CLR) routine that accepts parameters, performs an action, such as a complex calculation, and returns the result of that action as a value. Azure SQL Database does not allow creation of a managed application module that contains class metadata and managed code as an object in an instance of SQL Server. If you are relying on this feature, you will need to bring the functional logic used in CLR either to application layer or into stored procedures which will require re-engineering. Securing your SQL Database
    71627 [71627] Database users mapped with Windows authentication (integrated security) not supported in Azure SQL Database MigrationBlocker High

    Azure SQL Database supports two types of authentication:

    SQL Authentication, which uses a username and password.

    Azure Active Directory Authentication, which uses identities managed by Azure Active Directory and is supported for managed and integrated domains.

    Windows authentication (integrated security) is not supported in Azure SQL Database. Database users mapped to Windows logins not supported.

    Remove the reported unsupported users before migration and start using either SQL Authentication or Azure Active Directory Authentication after migrating to Azure SQL Database.

    Logins mapped to either certificate or asymmetric key are also not supported.

    Securing your SQL Database
    71627 [71627] Windows users can be converted to external users in Azure SQL Database BehaviorChange High

    Azure SQL Database supports two types of authentication:

    SQL Authentication, which uses a username and password.

    Azure Active Directory Authentication, which uses identities managed by Azure Active Directory and is supported for managed and integrated domains.

    Although it does not support Windows users or Windows authentication, Azure SQL Database supports external users, which can be used similarly through Active Directory.

    Either perform a schema migration through DMA to convert these Windows users to external users for use with Azure Active Directory, or do not migrate your Windows users to Azure SQL Database.

    Securing your SQL Database
    71630 [71630] Filestream not supported in Azure SQL Database MigrationBlocker High The Filestream feature, which allows you to store unstructured data such as text documents, images, and videos in NTFS file system, is not supported in Azure SQL Database.

    Upload the unstructured files to Azure Blob storage and store metadata related to these files (name, type, URL location, storage key etc.) in Azure SQL Database.

    You may have re-engineer your application to enable streaming blobs to and from Azure SQL Database.

    Streaming Blobs To and From SQL Azure
    71627 [71627] Table with FILSTREAM column not supported in Azure SQL Database MigrationBlocker High The FILSTREAM column, which allows you to store unstructured data such as text documents, images, and videos in NTFS file system, is not supported in Azure SQL Database.

    Upload the unstructured files to Azure Blob storage and store metadata related to these files (name, type, URL location, storage key etc.) in Azure SQL Database.

    You may have re-engineer your application to enable streaming blobs to and from Azure SQL Database.

    Streaming Blobs To and From SQL Azure
    71626 [71626] FileTable not supported in Azure SQL Database MigrationBlocker High The FileTable feature, which builds on top of SQL Server FILESTREAM technology and allows you to store unstructured data such as text documents, images, and videos in NTFS file system, is not supported in Azure SQL Database Managed Instance.

    Upload the unstructured files to Azure Blob storage and store metadata related to these files (name, type, URL location, storage key etc.) in Azure SQL Database.

    You may have re-engineer your application to enable streaming blobs to and from Azure SQL Database.

    Streaming Blobs To and From SQL Azure
    $ErrorActionPreference = "Stop"
    [xml]$ruleAzureSQLDb = Get-Content -Path "C:\Program Files\Microsoft Data Migration Assistant\RuleMetadataStore.xml"
    [xml]$ruleAzureSQLDbFeature = Get-Content -Path "C:\Program Files\Microsoft Data Migration Assistant\AzureSQLDatabase\AzureSQLDbFeatureRuleMetadataStore.xml"
    function CreateHeader($propertyList){
    $sbL1 = New-Object System.Text.StringBuilder
    $sbL2= New-Object System.Text.StringBuilder
    foreach($property in $propertyList.Name){
    [void]$sbL1.Append("| {0} " -f $property)
    [void]$sbL2.Append("| --- ")
    }
    [void]$sbL1.Append("|" + "`n")
    [void]$sbL2.Append("|" + "`n")
    return $sbL1.ToString() + $sbL2.ToString()
    }
    Function CreateBody($propertyList, $MetaData){
    $sb = New-Object System.Text.StringBuilder
    foreach ($rule in $Metadata){
    foreach($propertyName in $propertyList.Name){
    [void]$sb.Append("| {0} " -f ($rule.$propertyname -replace "`n"," "))
    }
    [void]$sb.Append("|`n")
    }
    return $sb.ToString()
    }
    #region Format RuleMetadataStore.xml
    $propertyList = @("RuleId","Title", "ChangeCategory", "RuleSeverity", "Impact", "Recommendation", "AdditionalInformation") | % {[PSCustomObject]@{Name = $_}}
    $sqldbRule = $ruleAzureSQLDb.RuleMetadataStore.RuleMetadataCollection | where advisorType -eq "DacFxExportAzureSQLDb"
    $md = New-Object System.Text.StringBuilder
    [void]$md.Append((CreateHeader $propertyList))
    [void]$md.Append((CreateBody $propertyList $sqldbRule.RuleMetadata))
    $md.ToString() | Set-Content -Path "DacFxExportAzureSQLDb.md" -Encoding UTF8 -Force
    [void]$md.Clear()
    #endregion
    #region Format AzureSQLDbFeatureRuleMetadataStore.xml
    $propertyList = @("Title", "ChangeCategory","FeatureParityRecommendationCategory", "RuleSeverity", "Impact", "Recommendation", "AdditionalInformation") | % {[PSCustomObject]@{Name = $_}}
    $sqldbFeatureRule = $ruleAzureSQLDbFeature.RuleMetadataStore.RuleMetadataCollection.RuleMapping
    [void]$md.Append((CreateHeader $propertyList))
    [void]$md.Append((CreateBody $propertyList $sqldbFeatureRule.RuleMetadata))
    $md.ToString() | Set-Content -Path "AzureSQLDbFeature.md" -Encoding UTF8 -Force
    #endregion
    Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment