Assessing a Database Before a Migration

A migration date is easy to announce before anyone looks inside the database. Assessing a database before a migration gives the plan real boundaries. Inventory features, dependencies, data volume, and behavior before promising a cutover window.

Name the Source and Target When Assessing a Database Before a Migration

Record the source product, edition, version, compatibility level, hosting model, and recovery setup. Name the proposed target with the same detail. A move between versions of SQL Server differs from a move between database engines. Do not let the word migration hide which kind of change you are planning. State whether the application code and client drivers will change too.

I ask for the exact target before estimating effort. A database moved to another server on the same engine has one set of risks. A database translated from another engine has more semantic work. Which business process cannot stop during cutover? The answer constrains the transfer path and the schedule before any tool is selected.

List Features Actually in Use

Inspect tables, views, procedures, functions, triggers, full-text indexes, Service Broker, replication, encryption, CLR, linked servers, and scheduled jobs as relevant to the source. A feature being available on the server does not prove this database uses it. Conversely, a server-level dependency can be essential even though it is absent from the database backup. Build a feature inventory with owner and evidence.

The query below counts object types in the current SQL Server database. It is only a first pass. Add checks for features outside sys.objects and review application use. I start with the uncommon features because they drive the migration method.

SELECT
    type_desc,
    COUNT(*) AS ObjectCount
FROM sys.objects
GROUP BY type_desc
ORDER BY type_desc;

Measure Data and Change Rate

Collect database size, table sizes, file growth, log usage, and daily change rate from the source. Use measured values from a representative period. A one-time size tells you transfer volume. Change rate tells you whether a final sync can fit the downtime window. Include large objects and indexes in the estimate. Run a rehearsal to measure backup, copy, restore, and validation on the intended hardware.

I do not turn a brochure throughput number into a cutover promise. Network and storage behavior under your workload decide the real duration. The query below reports current SQL Server database file sizes in pages. Convert and record the result with the query date. Do not paste an old number into every future plan.

SELECT
    DB_NAME(database_id) AS DatabaseName,
    name AS LogicalFileName,
    type_desc,
    size AS SizeInPages
FROM sys.master_files
WHERE database_id > 4
ORDER BY DatabaseName, file_id;

Check Compatibility and Query Behavior

Run the supported assessment tool for the source and target versions. Review deprecated or unsupported features, data types, collations, and query syntax. For SQL Server upgrades, capture important query behavior with Query Store or a representative workload before changing compatibility level. For a cross-engine move, compare transaction semantics and application queries as well as schema.

I put every warning into one of three buckets: must fix before move, can fix after move, or not applicable with evidence. An unread warning list is not an assessment. Test each high-impact behavior on the target with real data shapes. A query compiling is only the first gate. Result and performance still matter.

Trace Outside Dependencies When Assessing a Database Before a Migration

List logins, jobs, credentials, linked servers, external files, application connection strings, reports, ETL jobs, and downstream consumers. Identify which host and identity each dependency uses. A database backup does not carry the entire surrounding estate. Change the database endpoint without updating a job, and the failure can appear hours after the cutover.

I ask owners to mark the dependencies they can validate during a rehearsal. Unknown ownership is a migration risk, not a blank to ignore. Search application configuration and scheduled tasks in addition to SQL metadata. Keep the dependency record tied to a specific environment so test and production are not mixed.

Decide What Must Be Validated

Define success in terms users recognize: critical queries return expected results, writes commit, reports reconcile, jobs run, and external integrations receive data. Add data checks for row coverage, key ranges, and transformed values. State the source cutoff or snapshot used for each comparison. A count from a moving source cannot be compared fairly with a target taken later.

Build the validation queries before migration day. I run them in rehearsal and save both outputs. If they are too slow or require unavailable access, discover that while there is time to improve them. The target should not be declared ready because a single smoke test returns one row.

Assign a Rollback Decision Point

Document the latest moment when you can return to the source without losing new target writes. After the cutover, two writable copies can diverge. Decide whether rollback means switching users back, replaying changes, or restoring a backup, and test the chosen path. Put a named owner on the go or no-go call.

I prefer explicit stop conditions, such as failed login mapping or a critical reconciliation mismatch. Vague confidence is hard to use under pressure. A rollback plan is part of assessing a database before a migration because it changes the schedule, application freeze, and data movement design.

Turn Findings From Assessing a Database Before a Migration Into a Plan

Assessing a database before a migration should produce output that names the target, feature gaps, required application changes, data movement method, rehearsal dates, validation owners, and open decisions. Attach measured estimates with their source and date. Assign each risk a test or a resolution owner. If a key fact is unknown, mark it unknown rather than filling the gap with an optimistic assumption. Identify the check that will resolve it and the person who can run that check.

Only then set a cutover date. The plan will be shorter because it can refer to real inventory and test results. A good assessment does not prevent every surprise, but it makes the important surprises visible while the team still has choices. Review the open decisions with application and operations owners before assigning a final date.

Related reading on this blog: SQL SERVER 2022: Oldest Compatibility Level Supported and Impact of Changing Database Compatibility Level on Cache.

A migration assessment is not a tool report, it is the evidence behind a workable move.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.