Applies toSQL Server 2008–2022; Azure Migrate, SQL Server enabled by Azure Arc, Azure SQL migration tooling; AWS Migration Evaluator, AWS DMS Schema Conversion
1. What you need to know about every database
| Area | Questions |
|---|---|
| Identity | Instance, version, edition, CU, collation, size, owner app |
| Usage | Active connections, last access, CPU, IO, memory (see the sizing post) |
| Features | Agent jobs, linked servers, CLR, FILESTREAM, Service Broker, replication, cross-DB queries, DB Mail, SSIS/SSRS/SSAS |
| Security | Windows logins, SQL logins, TDE, certificates, contained users |
| Dependencies | Which apps and servers connect; linked servers in and out; ETL feeds |
| Continuity | HA/DR in use, RPO/RTO, backup tool |
2. Tools
| Tool | What it gives |
|---|---|
| Azure Migrate (SQL discovery and assessment) | Agentless inventory, readiness and recommended Azure SQL targets with sizes |
| SQL Server enabled by Azure Arc | Inventory and migration assessment from Arc-connected instances |
| Azure SQL migration tooling (Azure Database Migration Service) | Database-level compatibility issues for SQL DB, MI and VM |
| AWS Migration Evaluator | Inventory and a cost case for AWS, including licensing |
| AWS DMS Schema Conversion / SCT | Compatibility and conversion report when the target is PostgreSQL or Babelfish |
| Babelfish Compass | T-SQL feature report for Babelfish |
3. Fill the gaps with T-SQL
-- databases, size, recovery model, compatibility level
SELECT d.name, d.compatibility_level, d.recovery_model_desc, d.is_encrypted,
CAST(SUM(mf.size) * 8 / 1024.0 / 1024 AS decimal(10,1)) AS size_gb
FROM sys.databases d JOIN sys.master_files mf ON d.database_id = mf.database_id
GROUP BY d.name, d.compatibility_level, d.recovery_model_desc, d.is_encrypted;
-- linked servers, agent jobs, CLR assemblies
SELECT name, product, provider, data_source FROM sys.servers WHERE is_linked = 1;
SELECT name, enabled FROM msdb.dbo.sysjobs;
SELECT name, permission_set_desc FROM sys.assemblies WHERE is_user_defined = 1;
-- who connects (sample several times over a week)
SELECT DB_NAME(database_id) AS db, host_name, program_name, login_name, COUNT(*) AS sessions
FROM sys.dm_exec_sessions WHERE is_user_process = 1
GROUP BY database_id, host_name, program_name, login_name;
Cross-database references in code:
SELECT OBJECT_NAME(referencing_id) AS obj, referenced_database_name, referenced_entity_name
FROM sys.sql_expression_dependencies WHERE referenced_database_name IS NOT NULL;
4. Score each database
| Score | Meaning | Usual target |
|---|---|---|
| Green | No blockers for PaaS | Azure SQL DB / MI / RDS |
| Amber | Fixable blockers (cross-DB queries, Agent jobs, Windows auth) | MI / RDS after small changes |
| Red | Hard blockers (OS access, unsupported features, very old app) | VM / EC2 / RDS Custom |
5. Group into move groups
Draw the dependency map: app → databases → linked servers → other databases. Everything that is connected moves in one wave, or you plan a temporary hybrid link (VPN or ExpressRoute/Direct Connect latency matters).
6. Output
A spreadsheet or CMDB view with one row per database: target, blockers, size, tier, owner, wave and planned date. This becomes your migration tracker.
Tip
Collect connection data for at least a full business cycle (a week, plus month-end if possible). The forgotten month-end report connecting through a linked server is a classic cutover-day surprise.