Moving a SQL Server workload to Azure is not simply a backup-and-restore exercise.
Before migration, DBAs need to understand the existing environment, identify compatibility issues, measure workload performance, and determine which Azure SQL platform best fits the workload.
A structured assessment helps reduce migration risks and unexpected issues during cutover.
1. Start With Discovery
The first step is to understand what currently exists.
Collect information about:
- SQL Server version
- Edition
- Operating system
- CPU and memory
- Storage configuration
- Database size
- Database growth
- SQL Agent jobs
- Linked servers
- Logins and users
- Database dependencies
- SSIS packages
- Replication
- CLR
- Service Broker
- Database Mail
- Maintenance jobs
- Backup strategy
- HA/DR configuration
A basic SQL Server inventory query can provide an initial baseline:
SELECT
SERVERPROPERTY('ServerName') AS ServerName,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('EngineEdition') AS EngineEdition;
This should be combined with a broader discovery process rather than relying on a single query.
2. Understand Database Size and Growth
Database size is important for migration planning, but growth rate is equally important.
For example:
SELECT
DB_NAME(database_id) AS DatabaseName,
SUM(size) * 8.0 / 1024 AS SizeMB
FROM sys.master_files
GROUP BY database_id
ORDER BY SizeMB DESC;
You should also capture historical growth.
A database that is currently 500 GB but growing by 100 GB every month has very different capacity requirements from a database that has remained stable at 500 GB for several years.
3. Identify SQL Server Features
One of the most important assessment activities is identifying SQL Server features used by the application.
Examples include:
- Linked servers
- SQL Agent
- CLR
- Replication
- Service Broker
- Cross-database queries
- Database Mail
- External scripts
- FileStream
- PolyBase
- SQL Server Integration Services
- SQL Server Reporting Services
The question is not simply:
"Does the database work?"
The question is:
"Does the complete application ecosystem work on the selected Azure platform?"
This distinction is critical.
4. Check Compatibility
Compatibility analysis identifies database objects or features that may require modification.
Potential issues can include:
Unsupported Feature
↓
Application Dependency
↓
Required Code Change
↓
Testing
↓
Migration
For example, an application may depend on a SQL Server feature that is available on SQL Server but behaves differently or is unavailable on the selected Azure SQL service.
Therefore, compatibility should be assessed before selecting the final migration target.
5. Analyze SQL Agent Jobs
SQL Agent jobs are often overlooked during migration.
Inventory:
- Job names
- Schedules
- Owners
- Steps
- Commands
- Operators
- Alerts
- Dependencies
For example:
SELECT
name,
enabled,
description
FROM msdb.dbo.sysjobs
ORDER BY name;
A job may contain references to:
- Local file paths
- Windows commands
- PowerShell
- Network shares
- SQL Server instances
- SSIS packages
These dependencies need to be redesigned or migrated appropriately.
6. Review Linked Servers
Linked servers are another important migration dependency.
Check them with:
SELECT
name,
product,
provider,
data_source
FROM sys.servers
WHERE is_linked = 1;
For every linked server, document:
Source
↓
Linked Server
↓
Target
↓
Application / Job Dependency
A linked server connecting to another SQL Server may require a different architecture after migration.
7. Establish a Performance Baseline
Do not migrate a workload without understanding its current performance.
Capture:
- CPU utilization
- Memory utilization
- IOPS
- Disk latency
- Batch requests
- Connection count
- Wait statistics
- Query duration
- Query CPU
- Query reads
- Query writes
Join the conversation! Your thoughts help the community grow.