Migration Planner

Overview

The Migration Planner answers what a new server needs before this instance can be moved onto it: how much storage on which volumes, what has to be carried across by hand, and how long the cutover will take.

The Migration Planner
Four views over one checklist. Each view filters the same list of findings rather than replacing it, so there are four ways of looking at one answer rather than four separate answers.

What this replaces

The old page was called Upgrade Recommendations and was a row in ReportsXmlConfig.xml with type="Html". A T-SQL batch concatenated a string of markup, handed it back in one column, and it was dropped into a browser control. It printed four rows: data, log, tempdb and fourteen days of backups, each with a current size and a proposed size.

Six things were wrong with it:

Fault What it meant
Nothing could be clicked A picture of a table inside a browser control. No sort, no copy, no drill in, no right-click. The stylesheet set no font, so it rendered in the control’s default serif while every other page was in Segoe UI.
Drive letters were hardcoded M, L and T were three DECLARE statements in the XML and nobody could change them without editing the file.
So was the growth factor @FudgeFactor = 1.33 sat in a DECLARE with a comment saying it assumed 75 percent capacity, which made the single most arguable number on the page the one nobody could see or argue with.
It never looked at the disks It totalled sys.master_files and stopped, while the Disk Space report in the same XML file was already reading sys.dm_os_volume_stats. It proposed a layout without saying what the current one was, which is the comparison the whole exercise turns on.
It recommended nothing about the upgrade Not the version, not the edition, not the support dates, and none of the things that have to be carried by hand: certificates, logins, linked servers, jobs, replication.
It could not fail visibly The whole batch sat in a BEGIN TRY with an empty CATCH, so a login without permission on msdb got a heading with an empty table under it.

The drive letters and the growth factor are now toolbar settings, which is the point: they were always assumptions, and an assumption you cannot see is one you cannot argue with.

Where to find it

Route How
Server tree Right-click the server → Instance Reports → Migration Planner

Requirements

  • SQL Server 2008 or newer. The instance and file sizing rest on columns that do not exist before it.
  • Nothing else is required. Everything except the instance identity and the file sizes is optional.

That last point is deliberate. A migration is planned by whoever has the credentials they have, and a page that refuses to draw because it could not read msdb is no use to them. Each optional read fails quietly and its findings are simply absent, with the footer saying what could not be checked, which is different from saying it passed.

The four views

All four filter one list: the migration checklist. Switching view changes which findings you are looking at, not what the report concluded.

Plan

Proposed volume cards, and the direct successor of the old table. One card per volume the new server needs:

Card What it sizes
Data files Every data file on the instance.
Log files Every transaction log.
TempDB TempDB, separately, because it belongs on its own volume.
Backup target Enough for the retention you are keeping.

Each card carries the current size and the provisioned size, plus how many files are involved, what is holding them today, and a sentence where the arrangement deserves one. The old report printed the first two numbers and none of the rest.

Move

The Move view: today's volumes, through content type, onto the new server's
Three columns: where things are now, what kind of content they are, and where they land.

Today’s volumes, through content type, onto the new server’s volumes. This is the comparison the old page could not make, because it never read what the current layout was.

Readiness

Findings by area, split into blocking, review and passing.

The gap between blocking and review is the whole point of this view. Blocking means the migration does not work until somebody acts. Review means it will work and somebody should still look. Collapsing those two into one list is what makes a migration checklist unusable.

Areas include versions and support dates, editions and their caps, compatibility levels, configuration, and trace flags that have since become default behaviour.

Cutover

Estimated backup, copy and restore per database, which is the number that decides whether the window you have booked is long enough. The copy leg uses the link speed from the toolbar, because it is the one input the server cannot tell you.

Reading the grid

Column What it carries
Severity Blocking, Review, OK or Info.
Area Versions, Editions, Configuration, Trace flags and so on.
Finding What was found.
On this server The current value.
On the new server What it needs to be, or what it will become.
What to do The action, where there is one.
The checklist grid
Three columns that the old page had no room for: what you have, what you need, and what to do about the difference.

It is one grid. Every view filters it, so a finding you saw in Readiness is the same row you can sort, copy and act on here.

The toolbar

Control What it sets
Sizing / Readiness and the view buttons Which slice of the checklist is shown.
Layout… The proposed drive letters for data, log, tempdb and the backup target.
Growth factor The headroom multiplier applied to current sizes.
Refresh Re-reads.

Every assumption on this page is on the toolbar. The old report’s 1.33 growth factor and its M, L, T drive letters were the two things most worth arguing about and the two things nobody could change.

How to read the report

  1. Set the layout and the growth factor first. Everything numeric on the page moves when they do, and the defaults are the report’s opinion rather than yours.
  2. Read Readiness before Plan. A blocking finding means the sizing is academic until it is resolved.
  3. Work the blocking list to empty. Then read the review list, which is where the surprises usually are: certificates, logins, linked servers, jobs, replication.
  4. Check Move against reality. If the current layout is not what you thought, the plan is built on it.
  5. Take Cutover to whoever booked the window. Backup plus copy plus restore, per database, is the number that decides whether the plan fits.
  6. Read the footer. It names what could not be checked, which is not the same as passing.

Common patterns

A blocking finding on version or edition. The target cannot host this instance as configured. Everything else on the page is provisional until that changes.

The proposed volumes much larger than today’s. Usually the growth factor rather than the data. Check what it is set to before ordering storage.

A long review list and no blocking findings. The normal healthy case. The migration will work; the review list is what makes it uneventful.

A cutover estimate longer than the window. Either the link speed on the toolbar is wrong, or the window is. Both are worth knowing before the weekend rather than during it.

Findings absent and a footer saying why. A permission the login does not have. Absent is not passing, and the footer is the only place that distinction appears.

Where the data comes from

  • SERVERPROPERTY and the instance catalog for version, edition and configuration.
  • sys.master_files for file sizes.
  • sys.dm_os_volume_stats for what the current volumes actually are.
  • msdb for jobs and backup history, optionally.
  • The security, linked server and replication catalogs, optionally.

The reference tables are not on the server. Support dates, compatibility floors, edition caps and the trace flags that became default behaviour live in the application, because none of them exist on the instance and all of them go stale. They are updated with the product.

Nothing is stored and nothing is written.

Settings

Setting What it holds
MigrationPlannerView Which view the page opens on
MigrationPlannerTarget The target version being planned for
MigrationPlannerDataDrive Proposed data volume
MigrationPlannerLogDrive Proposed log volume
MigrationPlannerTempDrive Proposed tempdb volume
MigrationPlannerBackupTarget Proposed backup target
MigrationPlannerGrowth The growth factor
MigrationPlannerLinkMbps Link speed for the cutover copy estimate

Per-user rather than per-instance.

Messages you may see

Version too old:

This report needs SQL Server 2008 or newer. The instance and file sizing it rests on read columns that do not exist on older builds.

No version reported:

This instance did not report its version. The page cannot plan a migration without knowing what is being migrated.

Timed out:

The instance lookup did not finish in time. Try the Refresh button, or check the status of the SQL Server.

Report Why you would go there
Databases By Size Where the storage total on this page comes from.
Disk Space What the current volumes actually have on them.
Backup Status Whether the backups the cutover plan assumes are real.
Linked Servers One of the things that has to be recreated by hand.
Replication Another, and the one most often forgotten.

Frequently asked questions

Why is the growth factor a setting rather than a number? Because it is an assumption. The old report hardcoded 1.33 with a comment explaining it, which made the most arguable number on the page the one nobody could change.

A whole area is missing from the checklist. Did it pass? No. Check the footer. An optional read that failed leaves its findings absent, and the footer names what could not be checked. Absent is not passing.

What is the difference between Blocking and Review? Blocking means the migration does not work until somebody acts. Review means it will work and somebody should still look. Keeping them apart is what the Readiness view is for.

Where do the support dates come from? The application, not the server. They do not exist on an instance and they go stale, so they ship with the product and are updated with it.

Why does the cutover estimate need a link speed? Because the copy leg is the only part of backup, copy, restore that the server cannot tell you anything about.

Does this change anything on the server? No. It reads, and every proposal is a number on a page.