/sqlmigration-objects-review

name: sqlmigration-objects-review
description: Audits the operational-object migration plan for a SQL Server move with 16 checks (M1-M16) — SQL Agent jobs/operators/alerts/proxies, linked servers, Database Mail, backup devices, custom error messages, server-level triggers, Extended Events sessions, and endpoints. Use this skill when a user is migrating a SQL Server instance and needs to carry over scheduled jobs, linked servers, mail profiles, or other instance-level objects, or asks "what instance objects do I need to recreate after migration." Trigger whenever operational-object portability during a migration is the topic; dispatched here from /sqlmigration-review for the operational-object family.
triggers:
  - /sqlmigration-objects-review
  - /migration-objects-review
  - /sql-migration-instance-objects

sqlmigration-objects-review

Purpose

Reviews the operational-object family of a SQL Server migration — the slice `sqlmigration-review` dispatches here rather than checking itself. This skill owns 16 checks (M1–M16) covering whether instance-level operational objects survive a backup/restore or log shipping/Always On AG seeding migration, since none of these objects travel with a database backup/restore — they live in `msdb`/`master` at the instance level and must be migrated separately.

• **SQL Agent (M1–M6)** — jobs referencing the wrong database, job owner login missing, operators

with stale notification addresses, alerts tied to error numbers not raised on target, proxy account credential mapping, job schedule timezone drift

• **Linked Servers (M7–M9)** — provider availability on target, data source connectivity,

collation-compatible setting

• **Database Mail (M10–M11)** — mail profile/account not migrated, SMTP relay allow-list missing

the new instance

• **Backup Infrastructure (M12)** — backup device path unreachable from target
• **Custom Errors (M13)** — `sys.messages` custom error definitions not migrated
• **Server Triggers (M14)** — server-level DDL/logon triggers not migrated
• **XE Sessions (M15)** — Extended Events session definitions not migrated
• **Endpoints (M16)** — non-AG endpoints (Service Broker, SOAP legacy) not migrated

All fix recipes use native T-SQL system objects/scripts, the in-box `SqlServer` PowerShell module, and `sqlcmd`/`bcp` — no third-party module is referenced or required.

Input

Accepts any of the following:

1. **Source and target server facts pasted as text** — job definitions, linked server lists, mail profile names, target platform 2. **Capture script output** (`.txt`/`.csv`) — see `scripts/capture-objects-facts.sql` 3. **Natural-language description** — "migrating 15 Agent jobs and 3 linked servers to a new instance" 4. **File path to a directory of exported artifacts** — SSMS Generate Scripts output for Agent jobs/linked servers, `sp_helplinkedsrvlogin` dumps

Recommended capture (run on the **source** instance):

SELECT j.name, j.enabled, c.name AS category_name, j.owner_sid
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id;

SELECT name, product, provider, data_source, is_linked, is_collation_compatible
FROM sys.servers WHERE is_linked = 1;

SELECT name, description FROM msdb.dbo.sysmail_profile;

SELECT name, physical_name, type_desc FROM sys.backup_devices;

SELECT message_id, language_id, severity, text FROM sys.messages
WHERE message_id >= 50000;

SELECT name, type_desc, is_disabled FROM sys.server_triggers;

SELECT name, is_running FROM sys.dm_xe_sessions;
SELECT name FROM sys.server_event_sessions;

Category 1 — SQL Agent

M1 — Agent Job References a Database Not in Migration Scope

**Trigger:** A job step's `database_name` (`msdb.dbo.sysjobsteps`) references a database that is not part of the migration's database scope, or has a different name on the target. **Severity:** Critical **Fix:** Update `database_name` on each affected job step via `sp_update_jobstep` after confirming the actual target database name, or exclude the job from migration if its dependent database is being decommissioned.

M2 — Job Owner Login Does Not Exist on Target

**Trigger:** A job's `owner_sid` does not resolve to a login on the target instance (the owning login was not migrated, or login migration ran after job migration). **Severity:** Warning **Fix:** Re-run `sp_update_job @job_name = '<name>', @owner_login_name = '<login>';` once the owning login exists, or reassign ownership to `sa`/a service account if the original owner is intentionally not migrating — see `/sqlmigration-security-review` J1/J2 for the login migration sequencing this depends on.

M3 — Operator Notification Address Is Stale or Unreachable From Target

**Trigger:** An Agent operator's `email_address`/`pager_address` is unchanged from source, and the target instance's outbound mail path (Database Mail profile, SMTP relay) has not been verified to reach that address. **Severity:** Warning **Fix:** Test notification delivery explicitly post-migration with `EXEC msdb.dbo.sp_notify_operator` (test message) rather than assuming the address is reachable just because the operator definition migrated correctly.

M4 — Alert Tied to an Error Number Not Raised on Target Configuration

**Trigger:** An Agent alert (`msdb.dbo.sysalerts`) fires on a specific `message_id`/severity combination tied to a feature or configuration (e.g., a custom error message via `sys.messages`, or an AG-specific error) that does not exist or is not enabled on the target. **Severity:** Warning **Fix:** Cross-reference each alert's `message_id` against `sys.messages` on the target before migrating the alert; migrate the underlying custom error message first if it's user-defined (see M13).

M5 — Proxy Account Migrated Without Matching Credential

**Trigger:** A job step uses a proxy account (`msdb.dbo.sysproxies`) whose backing credential was not migrated or was migrated after the proxy. **Severity:** Critical **Fix