sqlmigration-objects-review git:20260621.d1ce43dA

Immutable. This exact content is served forever at /api/v1/blob/a1038896b56ae8d2.

---
name: sqlmigration-objects-review
description: Audits the operational-object migration plan for a SQL Server move — 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):

```sql
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, collation_compatible
FROM sys.servers WHERE is_linked = 1;

SELECT name, description FROM msdb.dbo.sysmail_profile;

SELECT physical_device_name, type_desc FROM msdb.dbo.backupmediafamily;

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:** Sequence credential migration before proxy migration — see
`/sqlmigration-security-review` J10/J11 for the credential-side detail; this check flags the job
step's dependency on that sequencing being correct.

### M6 — Job Schedule Assumes Source Server's Time Zone
**Trigger:** A job schedule's `active_start_time` was tuned for the source server's local time
zone, and the target server is configured in a different time zone (common when migrating across
geographic regions or to Azure, which defaults new resources to UTC).
**Severity:** Warning
**Fix:** Recompute `active_start_time` for the target server's time zone before migrating
schedules, or confirm both source and target run in the same time zone if the schedule's
real-world timing (e.g., "after business close") must be preserved exactly.

## Category 2 — Linked Servers

### M7 — Linked Server Provider Not Available on Target
**Trigger:** A linked server (`sys.servers` with `is_linked = 1`) uses a provider (e.g.,
`Microsoft.ACE.OLEDB`, a legacy OLE DB provider) that is not installed or not supported on the
target instance's OS/SQL Server version.
**Severity:** Critical
**Fix:** Confirm the target has the required provider installed and registered before migrating
the linked server definition; for deprecated/discontinued providers, redesign the integration
(e.g., move file-based imports to `BULK INSERT`/`OPENROWSET` with a supported format, or to SSIS).

### M8 — Linked Server Data Source Unreachable From Target Network Path
**Trigger:** A linked server's `data_source` resolves to a hostname/IP that is reachable from the
source's network segment but not confirmed reachable from the target's (different VLAN,
firewall rules scoped to the old instance's IP, or the target is in Azure with no VNet peering to
the remote source).
**Severity:** Warning
**Fix:** Confirm network connectivity (firewall rules, DNS resolution, VNet peering for Azure
targets) from the target to each linked server's data source before relying on it post-cutover;
update firewall rules to include the target's IP/subnet.

### M9 — Linked Server Collation-Compatible Setting Mismatched
**Trigger:** A linked server's `collation_compatible` setting does not match the actual collation
relationship between the local and remote instance after migration (target's collation differs
from what the source had, per `/sqlmigration-review` Y4).
**Severity:** Info
**Fix:** `EXEC sp_serveroption '<linked_server>', 'collation compatible', 'false';` if collations
no longer match after migration — leaving it set to `true` against a mismatched collation causes
queries to silently return wrong comparison results rather than erroring.

## Category 3 — Database Mail

### M10 — Database Mail Profile/Account Not Migrated
**Trigger:** Source has an active Database Mail profile (`msdb.dbo.sysmail_profile`) used by
Agent operators or application code, and the migration plan does not include
recreating the profile/account on the target.
**Severity:** Warning
**Fix:** Script the profile and account definitions from
`msdb.dbo.sysmail_profile`/`msdb.dbo.sysmail_account` and recreate via
`msdb.dbo.sysmail_add_profile_sp`/`sysmail_add_account_sp`; re-enter the SMTP account password
manually, since it is not exposed by any system view.

### M11 — SMTP Relay Allow-List Missing the New Instance
**Trigger:** Database Mail is migrated and configured correctly, but the organization's SMTP
relay only allow-lists the source server's IP/hostname for relay permission.
**Severity:** Warning
**Fix:** Request the target instance's IP/hostname be added to the SMTP relay's allow-list before
relying on Database Mail for production alerting after cutover; test with
`EXEC msdb.dbo.sp_send_dbmail` before declaring the migration complete.

## Category 4 — Backup Infrastructure

### M12 — Backup Device Path Unreachable From Target
**Trigger:** A logical backup device (`msdb.dbo.backupmediafamily`/`sys.backup_devices`) points
to a UNC path or local path that is not reachable, or does not exist, from the target instance's
service account context.
**Severity:** Warning
**Fix:** Recreate the backup device pointing at a path reachable from the target, granting the
target's SQL Server service account write access to that path; test with a trivial backup before
relying on it for production backup jobs.

## Category 5 — Custom Errors

### M13 — Custom Error Message Definition Not Migrated
**Trigger:** Source has custom error messages (`sys.messages` with `message_id >= 50000`) raised
by application code or Agent alerts (see M4), and the migration plan does not include
`sp_addmessage` calls for the target.
**Severity:** Warning
**Fix:** Script `sp_addmessage @msgnum, @severity, @msgtext, @lang, @with_log, @replace` for each
custom message from `sys.messages` and run on the target before any dependent application code
or alerts go live.

## Category 6 — Server Triggers

### M14 — Server-Level DDL/Logon Trigger Not Migrated
**Trigger:** Source has a server-scoped trigger (`sys.server_triggers`) — most commonly a logon
trigger restricting connections, or a DDL trigger auditing schema changes — and the migration
plan only addresses database-level objects.
**Severity:** Warning
**Fix:** Script each server trigger's definition (`OBJECT_DEFINITION(object_id)` from
`sys.server_triggers`) and recreate with `CREATE TRIGGER ... ON ALL SERVER` on the target. Test
logon triggers carefully in a non-production window — a broken logon trigger can lock out all
connections including `sysadmin`, requiring the dedicated administrator connection (DAC) to
recover.

## Category 7 — XE Sessions

### M15 — Extended Events Session Definition Not Migrated
**Trigger:** Source has one or more user-defined XE sessions (`sys.server_event_sessions`
excluding system sessions like `system_health`/`AlwaysOn_health`) used for ongoing diagnostics or
auditing, and the migration plan does not recreate them.
**Severity:** Info
**Fix:** Script each session's definition via SSMS "Script Session as CREATE" or by reading
`sys.server_event_sessions`/`sys.server_event_session_events`/`sys.server_event_session_fields`,
and run the `CREATE EVENT SESSION` statement on the target, including `STATE = START` if the
session should begin running immediately.

## Category 8 — Endpoints

### M16 — Non-AG Endpoint Not Migrated
**Trigger:** Source has a non-Database-Mirroring endpoint (Service Broker `TSQL` endpoint, or a
legacy SOAP/HTTP endpoint) in active use, and the migration plan only addresses Always On AG
mirroring endpoints (covered by `/sqlag-review`).
**Severity:** Warning
**Fix:** Script the endpoint definition from `sys.endpoints`/`sys.service_broker_endpoints` and
recreate with `CREATE ENDPOINT` on the target, including the correct `STATE`, port, and
authentication/encryption options; legacy SOAP/HTTP (native XML Web Services) endpoints have
been deprecated since SQL Server 2008 R2 and remain present-but-unsupported-for-new-development
through current versions — Microsoft has not published a removal date, but treat any still in
use as a migration-blocking redesign item rather than a like-for-like `CREATE ENDPOINT` port,
since the feature has no investment and no guaranteed availability on newer target versions.

## Output Format

```
## sqlmigration-objects-review

### Summary
[2-3 sentences: object inventory size, headline risk]

### Critical Issues
[M-checks with Critical severity — each as: **M# — Name**, evidence, impact, fix]

### Warnings
[M-checks with Warning severity]

### Info
[M-checks with Info severity, if any]

### Passed Checks
[M-checks evaluated and not fired]

### Not Assessed
[M-checks that could not be evaluated because the required input was not provided]

### Object Migration Script Checklist
[Ordered list of native-tool scripts to run: credentials/logins prerequisite note, Agent jobs,
linked servers, Database Mail, backup devices, custom errors, server triggers, XE sessions,
endpoints, in dependency order]

---
Analyzed by: [model name] · [date/time]
```

## Output Filters

- `--brief` — Summary + Critical Issues only
- `--critical-only` — Critical Issues only

## Verbose Output (--verbose)

When `--verbose` is passed, also write
`output/sqlmigration-objects-review/<timestamp>-M/analysis.md` (full report) and `trace.md`
(which facts were available, which M-checks were skipped and why).

## Notes

- This skill is strictly offline — it never connects to SQL Server; all checks evaluate pasted
  facts or capture-script output.
- None of these objects travel with a database-level backup/restore — they are instance-scoped
  (`msdb`/`master`) and must always be migrated as a separate, explicit step regardless of which
  database migration mechanism is used.
- Dispatched here from `/sqlmigration-review` for the operational-object family — not invoked
  standalone for non-migration operational review.

## Companion Skills

- **`sqlmigration-review`** — parent skill; version/edition/platform compatibility (Y1–Y15)
- **`sqlmigration-security-review`** — login/credential prerequisites for M2 (job owner) and M5
  (proxy credential)
- **`sqlag-review`** — Always On AG mirroring endpoints, distinct from the non-AG endpoints M16
  covers