DMA Is Gone: How to Assess SQL Server Compatibility with Azure Using SSMS 22
For many years, anyone working on SQL Server migration projects to Azure relied on one almost mandatory tool: the Data Migration Assistant, better known as DMA.
DMA allowed DBAs and architects to analyze a SQL Server instance or database before migration and identify issues such as incompatibilities, unsupported features, and potential blockers.
As Microsoft’s migration tooling evolved, that scenario changed.
Today, one of the main ways to perform a Migration Readiness Assessment is directly integrated into SQL Server Management Studio 22 or later.
Microsoft documents that the Migrate SQL Server feature available in SSMS can assess SQL Server instances and verify their compatibility with different Azure targets.
What is the Migration Readiness Assessment?
Before migrating a database to Azure, there is one fundamental question:
Is my current SQL Server environment compatible with the Azure target I want to migrate to?
For example, imagine that you currently have:
SQL Server On-Premises
|
|-- Database A
|-- Database B
|-- SQL Server Agent Jobs
|-- Linked Servers
|-- Database Mail
|-- CLR
|-- Stored Procedures
|-- Functions
|-- Triggers
And you want to migrate to:
Azure SQL Database
Not everything that exists in a traditional SQL Server environment is necessarily available or works in the same way in Azure SQL Database.
For this reason, simply restoring a backup or copying the data is not enough to properly plan a migration.
You first need to perform a compatibility assessment.
That is exactly what the Migration Readiness Assessment in SSMS is designed to help with.
According to Microsoft documentation, the feature checks for potential migration blockers for:
- Azure SQL Database
- Azure SQL Managed Instance
- SQL Server on Azure Virtual Machines
The assessment is now available inside SSMS
One of the biggest changes for DBAs is that it is no longer necessary to depend on a separate application just to perform this analysis.
The feature is available in SQL Server Management Studio 22 or later.
The workflow is straightforward:
SQL Server
|
v
SSMS 22+
|
v
Migrate SQL Server
|
v
Run Assessment
|
v
Compatibility / Readiness Report
After connecting to SQL Server through Object Explorer:
- Right-click the SQL Server instance.
- Select Migrate SQL Server.
- The migration page opens.
- In the Database Assessment section, select Run Assessment.
- SSMS analyzes the environment and generates the results.
Microsoft also documents that the assessment results can be exported as a detailed HTML report.
Prerequisites
To use this functionality, Microsoft currently lists the following prerequisite:
SQL Server Management Studio 22 or later.
In addition, the migration-related components must be installed.
Microsoft recommends installing or modifying SSMS through the Visual Studio Installer and selecting the:
Hybrid and Migration workload.
If the Migrate SQL Server option does not appear in Object Explorer, this is one of the first things to verify.
What exactly is analyzed?
The main objective of the assessment is to identify problems before they appear during the migration.
SSMS analyzes metadata from the SQL Server instance and databases, looking for incompatibilities related to the selected Azure targets.
Microsoft refers to this process as:
Azure Migration Readiness
The assessment provides information related to both instance readiness and database readiness.
A SQL Server environment may contain, for example:
Database Mail
SQL Server Agent Jobs
Linked Servers
CLR
Cross-database dependencies
Stored Procedures
Functions
Triggers
SQL Server-specific features
Depending on the chosen target, some of these components may work directly, others may require changes, and some may not be supported.
This is exactly the type of information that should be identified before the migration.
Ready, Ready with warnings, and Not ready
Another useful aspect of the assessment is the classification of the results.
Microsoft uses three main categories.
Ready
This means the database can be migrated to that target without changes related to the issues identified by the assessment.
Ready with warnings
Issues have been identified, but they are not considered migration blockers.
The migration can still proceed, although the warnings should be reviewed.
Not ready
Blocking issues have been identified.
In this case, the report shows the problems that must be addressed before proceeding with the migration.
In practice, the result may look something like this:
Database: ERP_PRODUCTION
Azure SQL Database
Status: NOT READY
Azure SQL Managed Instance
Status: READY WITH WARNINGS
SQL Server on Azure VM
Status: READY
This information can be extremely valuable when selecting the target architecture.
Azure SQL Database, Managed Instance, or Azure VM?
One of the most important decisions in a SQL Server migration project is choosing the correct Azure service.
Three common options are:
Current SQL Server
|
+-------------+-------------+
| | |
v v v
Azure SQL Azure SQL SQL Server
Database Managed on Azure VM
Instance
Azure SQL Database is a highly managed PaaS service, but it has important architectural differences compared with a traditional SQL Server instance.
Azure SQL Managed Instance was designed to provide a higher level of compatibility with traditional SQL Server features.
SQL Server on Azure Virtual Machines maintains an architecture much closer to a conventional SQL Server installation on Windows or Linux.
For this reason, the assessment should not simply be viewed as:
“Is my database compatible with Azure?”
The better question is:
“Which Azure SQL service is my database compatible with?”
That distinction is fundamental.
The assessment does not require Azure Arc
Another important point is that the assessment can also be performed on environments that are not connected to Azure Arc.
When the SQL Server instance is not Azure Arc-enabled, SSMS can perform a local metadata-based assessment.
The workflow is approximately:
SQL Server On-Premises
|
v
SSMS 22
|
v
Local Metadata Assessment
|
v
Compatibility Findings
|
+--> Azure SQL Database
|
+--> Azure SQL Managed Instance
|
+--> SQL Server on Azure VM
This is especially useful for DBAs who want to perform an initial assessment without first deploying a full Azure discovery infrastructure.
What if I only want to verify compatibility?
This is an important distinction.
For a DBA who simply wants to answer:
“Do my databases have compatibility issues that could affect a migration to Azure?”
there is no need to start immediately with a full Azure Migrate project, CPU sizing, memory sizing, IOPS analysis, or cost estimation.
You can start directly with:
SSMS 22
↓
Migrate SQL Server
↓
Run Assessment
The result helps identify the main blockers and warnings associated with the migration.
If the project moves forward, other tools can then be used for sizing, performance analysis, cost estimation, and migration execution.
Required permissions
Another interesting detail in Microsoft documentation is that sysadmin is not required when the goal is only to run the assessment.
For assessment-only scenarios, Microsoft documents a smaller set of permissions, including permissions such as:
CONNECT ANY DATABASE
CONNECT SQL
VIEW ANY DATABASE
VIEW ANY DEFINITION
VIEW SERVER STATE
Additional read access may also be required for specific objects in msdb, including information related to SQL Server Agent and Database Mail.
This is particularly important in enterprise environments where the team responsible for the assessment may not have full administrative privileges on the SQL Server instances.
SSMS also includes an Upgrade Assessment
There is another related, but different, feature available in the same SSMS migration area.
In addition to Azure migration assessment, SSMS also provides an Upgrade Assessment.
In this case, the objective is to analyze a database before upgrading its SQL Server version or compatibility level.
According to Microsoft, this assessment can identify:
- breaking changes
- behavioral changes
- deprecated features
- issues related to compatibility level upgrades
Therefore, it is important to distinguish between:
MIGRATION READINESS ASSESSMENT
SQL Server → Azure
versus
UPGRADE ASSESSMENT
SQL Server → newer version / compatibility level
They solve different problems, although both are part of the broader SQL Server modernization process.
How I would use this in a real migration project
In a real project, I would start by creating an inventory of the existing SQL Server instances and databases.
Then I would run the Migration Readiness Assessment.
1. Inventory
↓
2. Compatibility Assessment
↓
3. Identify blockers
↓
4. Remediation
↓
5. Select the Azure target
↓
6. Migration test
↓
7. Application validation
↓
8. Production migration
For example:
SQLPRD01
ERP
CRM
DW
Finance
Reporting
After the assessment, the results might look like:
ERP
Azure SQL Database
NOT READY
5 blockers identified
CRM
Azure SQL Managed Instance
READY WITH WARNINGS
2 warnings
DW
SQL Server on Azure VM
READY
Finance
Azure SQL Database
READY WITH WARNINGS
Reporting
Azure SQL Managed Instance
READY
This transforms the assessment into an architecture decision tool rather than just an isolated technical check.
Do not migrate first and discover the problems later
A common mistake in cloud migration projects is to start discussing:
- VM size
- number of vCores
- pricing
- storage
- Azure region
- licensing model
before determining whether the application and databases are compatible with the selected Azure service.
Compatibility should be one of the first analyses performed in a migration project.
A safer process is:
DISCOVER
↓
ASSESS
↓
REMEDIATE
↓
MIGRATE
↓
VALIDATE
The Migration Readiness Assessment available in SSMS 22+ makes this first stage much easier for professionals who work with SQL Server every day.
For DBAs in particular, it is useful because the analysis is available inside a tool that is already part of our daily routine: SQL Server Management Studio.
Conclusion
Microsoft’s SQL Server migration tooling continues to evolve, and capabilities that previously required separate tools are increasingly being integrated into SSMS.
For anyone who primarily needs to answer:
“Is this SQL Server instance ready to migrate to Azure?”
the current workflow is straightforward:
SSMS 22+
↓
Migrate SQL Server
↓
Run Assessment
↓
Readiness Report
From the resulting report, DBAs can identify incompatibilities, warnings, and blockers and evaluate which target is more appropriate among Azure SQL Database, Azure SQL Managed Instance, and SQL Server on Azure Virtual Machines.
More important than simply executing a migration is understanding beforehand what changes that migration will require.
And that remains one of the DBA’s most important responsibilities in any SQL Server modernization project.
www.academiadba.com
Share this content:
Post Comment