Sqlmigration objects review

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: 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 is_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 (sys.backup_devices — the catalog of named devices created by sp_addumpdevice; note msdb.dbo.backupmediafamily is backup history, not the device list and has no type_desc column) 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