Inventory SQL Server Host Dependencies Before Choosing RDS

Produce an owned SQL Server and Windows dependency register, separate managed support from external responsibilities, and specify isolated tests before accepting an...

Before choosing a managed database target, inventory the work performed around the database, not just its user tables. A Windows task that creates tomorrow's import file, a SQL Agent step that launches a script, and an assembly that reads a local directory can remain essential even when a restored database passes application queries. Produce one dependency register connecting each business obligation to its trigger, execution identity, host resources, target disposition and validation evidence. An unknown required dependency holds the target decision.

This playbook assesses SQL Server 2019 Standard on a self-managed Windows host against standard Amazon RDS for SQL Server 2019 Standard. It is not an engine-conversion procedure, RDS Custom assessment or production migration runbook. Exact source build, target RDS engine version, Region, topology and availability must be collected for the real proposal. No database, Windows host or AWS account was accessed here. All worked records and times below are fictional; proposed tests remain NOT EXECUTED.

1. Define the service obligation and collection boundary

The migration lead starts with the business outputs rather than a server object count. Ask which files must arrive, which reconciliations must finish, which reports must be published, and who notices a missed run. For each obligation record deadline, timezone, tolerated delay, retry behavior and downstream acknowledgement. A daily job can have a month-end branch that is invisible in an ordinary day of logs. Choose a review window that includes the relevant exceptional schedules, or retain those branches as unobserved.

The SQL owner records instance identity, edition, complete build, database population and Agent access. The Windows owner records the actual OS and scheduler/module versions, local service/task coverage and any separate automation controller. The target owner records standard RDS engine string, edition, Region, topology and option assumptions from current evidence. A documentation page listing SQL Server 2019 does not prove an exact version is currently offered for that proposal. Do not silently replace an unavailable build with a later major.

Separate collection from testing. The collection approval names authorized metadata reads, host definitions, history window, workload-load limit and evidence retention. It does not permit starting a job, opening customer payloads, changing a role or installing an agent. If an approved read is denied, preserve the denial and its scope. Ask the owner for an authorized export rather than escalating privileges or interpreting zero rows as zero dependencies.

2. Establish which responsibilities RDS will not inherit

AWS's SQL Server overview excludes direct host access and importing msdb. A user-database restore therefore cannot stand in for host or Agent reconstruction. Inventory server identities, job definitions and supporting files separately. A familiar connection endpoint is not evidence that the old Windows service account or filesystem exists on the managed host.

The managed feature restrictions exclude xp_cmdshell, FILESTREAM/file tables and maintenance plans. CLR is not supported for SQL Server 2017 and later in standard RDS, which includes this 2019 target. A required user assembly cannot be waved through because it uses SAFE permissions. Linked servers have limited support and need their own exact provider/path review; do not label every linked server unsupported or supported from its name.

Keep these as feature conflicts, not immediate instructions to delete functionality. The application owner might replace an assembly with a service call, the host owner might externalize a file exporter, or the business owner might retire an unused output. Each changes responsibilities and potentially failure behavior. If the required behavior cannot change, compare an appropriately reviewed self-managed target or defer. Same-engine hosting preserves neither automatic suitability nor a license entitlement.

3. Collect SQL Agent steps, branches and visibility evidence

The SQL owner performs approved reads using the actual source's supported tools. Microsoft's job-step reference identifies subsystem, command, output file, database context, proxy and branch/retry fields. Retain those fields for every step, together with job ownership, enablement, schedules and history. Inspect commands in the controlled evidence store: they can contain credentials, addresses and private business logic. Publish only opaque references and classifications in the shared register.

Do not classify a job from step one alone. An initial T-SQL query may branch into a command-shell export on failure, or declare success after skipping an unavailable downstream action. Record success/failure step destinations and final outcome meaning. Inspect called procedures and referenced scripts through their owners. A text search for drive letters can identify candidates but cannot discover dynamically constructed paths or prove the absence of external effects.

Include Agent alerts, operators, email-notification settings and token references in the authorized inventory. RDS Agent restrictions exclude those mechanisms. A retained job can therefore lose its failure notification or rely on an unavailable token. Record the recipient/response obligation and an owned replacement or HOLD, not just the job's steps.

Record the collector's visibility separately. SQL Agent roles differ in scope; an account that sees only its owned jobs cannot certify all instance jobs. Have the instance owner reconcile the collected population against an authorized complete source view. Do not grant broad roles as part of this playbook. Evidence of incomplete visibility is a coverage gap, not a failed migration and not a clean inventory.

4. Join Windows tasks and services to the same obligations

The Windows owner collects local scheduled-task definitions, service configuration and approved execution history. Get-ScheduledTask is one documented definition read, not proof that the particular source OS/module supports the same command contract. Verify that environment before proposing a collection command. Preserve task path, trigger, principal alias, action reference, working directory, arguments classification and output obligations without copying secret arguments into tickets.

Ask integration owners about remote schedulers, backup tools, enterprise automation, manual month-end scripts and tasks running on another machine. A local scheduler inventory cannot discover all remote callers. Join these records by business obligation and durable operation identity, not merely by similar job names. Two schedulers may deliberately participate in one pipeline, or accidentally create duplicate work. Record which process is authoritative for each trigger.

For every path, identify whether it is input, output, scratch space, installed executable, certificate store or retained recovery artifact. A database connection string can move while a file share still points to the old host. Verify who produces the file, who may read it, how completion is signalled, and what prevents a partial file from being consumed. Do not assume object storage is a drop-in replacement for a local append/rename/locking contract.

5. Inventory in-database calls that cross the host boundary

The SQL owner and application owner inspect user-defined assemblies, procedures and integrations within the approved visibility boundary. Microsoft's assembly catalog exposes assembly identity and permission information, but metadata visibility is restricted. A zero-row result without the visibility record cannot establish absence. Retain object definitions and their hashes in restricted evidence, not binary payloads in a public worksheet.

Trace each known external call to its caller, identity, destination and durable result. Include file access, shell invocations, linked-server lookups and dependencies on server-level configuration. Ask the owner whether the call is on the critical request path, a scheduled path, or an optional diagnostic. Removing an apparently administrative assembly may change a user-facing calculation. Validate the observed caller relationship before selecting a replacement.

The inventory is finite and declared. Static inspection cannot reliably resolve every dynamic SQL string or externally assembled script. Reconcile object definitions with representative approved histories and owner interviews, then label remaining dynamic or rarely executed branches. A signed coverage statement should explain the inspected population and exclusions. It must not claim discovery of every possible dependency in arbitrary code.

6. Assign a disposition to each dependency, not to the server

Use five dispositions. Keep means the exact behavior is a documented target candidate, not yet an observed success. Externalize means a separately owned worker or scheduler will perform the host responsibility outside the database. Replace means the application contract changes through a reviewed implementation. Retire means the accountable business owner confirms the obligation is no longer required. HOLD means evidence or an acceptable implementation is missing. None authorizes production change.

RDS Agent guidance allows T-SQL jobs for the supported editions but excludes command-line and PowerShell script execution and Agent-based backups. For a Multi-AZ proposal, job replication is separately enabled and has eligibility exclusions. Do not turn “T-SQL step” into automatic failover acceptance. Pin the exact job and check the current replication rules and observed synchronization before a later authorized exercise.

The same guidance warns that maintenance and backup windows can interrupt or cancel jobs. Record the proposed windows alongside job duration, deadline, retry owner and downstream acknowledgement. Matching source and target schedule metadata does not establish deadline completion. The later isolated validation must address interruption, safe retry and missed-output detection; unknown recovery behavior remains HOLD.

An externalized export must own connections, credentials, retries, deduplication, scheduling, monitoring and recovery. Its owner must be funded and available after migration. Conversely, a plain supported aggregation job might be simpler to keep inside Agent than to rebuild as a distributed service. Select from the required behavior and evidence. Product names or architecture fashions do not settle that trade-off.

7. Work through a fictional overnight settlement pipeline

Assume the service contract requires an inert settlement extract by 02:00 UTC. Job J1 aggregates at 01:00. J2 starts at 01:10 and launches a command-shell script that writes a local file. Task W1 starts at 01:20 and transfers that file to a partner. Assembly A1 computes a required identifier. All names, outputs and times are stipulated teaching inputs, not observed workloads. The actual deadline, duration and failure budgets must come from the service owner.

J1 is a keep candidate only after its procedure, permissions, scheduling and actual target behavior are reviewed. J2 cannot be retained unchanged as a command-shell Agent step. W1 does not migrate with a user database. A1 conflicts with this standard RDS2019 CLR boundary. The initial target disposition is HOLD even if every copied table reconciles. No average score can cancel the required identifier dependency.

The proposed redesign externalizes J2/W1 to one owned worker, retaining a stable settlement operation ID. It writes an inert output in the rehearsal and records a separate downstream acknowledgement. A1 is either replaced and validated against independently expected identifiers, or the target choice changes. The proposal does not claim that moving scripts outside SQL is trivial or that a different hosting option already supports them. Its work estimate and operating responsibility belong in the decision.

J1 aggregation

Initial disposition: Keep candidate

Evidence that changes it: Exact procedure/role, schedule and isolated target results

J2 command-shell export

Initial disposition: Externalize; not accepted

Evidence that changes it: Reviewed worker revision, stable operation IDs and failure evidence

W1 file transfer

Initial disposition: Externalize; not accepted

Evidence that changes it: Explicit destination, acknowledgement and replay ownership

A1 required CLR identifier

Initial disposition: HOLD

Evidence that changes it: Accepted replacement observations or reviewed alternative target

8. Follow the obligation through the responsibility split

Fictional obligation-to-responsibility map, not a deployed topology. Lines show required contributions, not SQL replication or network routes. One unresolved required contribution holds the decision; a copied database is only one input.

Use the view to challenge the proposed operating split. Who owns the export when the database remains healthy but the worker stops? Who can settle a transfer timeout without creating a second delivery? Who validates the identifier replacement? The inventory reviewer should be able to follow each answer to one row and its retained evidence. A boundary labelled external does not establish implementation, availability or permission.

9. Specify isolated validation before implementing the redesign

The application owner declares expected outputs independently from the candidate implementation. Use synthetic records and an inert downstream receiver. The security owner approves the test identities and ensures production credentials, destinations and automatic schedules are unavailable. Pin source/target/configuration, worker revisions, test population and expected deadline treatment. Test creation and workload execution need their own approval; this playbook's inventory authority is insufficient.

For the settlement example, require one successful operation, duplicate trigger, interrupted export, unavailable destination, timeout after receiver acceptance and late arrival after the deadline. A repeated operation ID must not produce a second accepted settlement. A partial file must not count as complete. After an uncertain delivery, the recovery procedure must consult durable acknowledgement before replay. These are stipulated expectations, not claims about an existing worker's behavior.

For J1, validate the actual database role and procedure output, then test the relevant scheduling and interruption behavior under a separately approved plan. For A1, compare independently expected identifier cases, including rejected or boundary inputs. The observer retains failures, input identity and exact implementation revision. A successful job status with a missing receiver acknowledgement fails the business output gate. Do not replace that criterion with an infrastructure metric after seeing results.

10. Reusable dependency and coverage record

Copy the companion worksheet into the controlled review record. Keep source exports and secret-bearing definitions elsewhere. Give each row a durable ID, immutable revision, owner and invalidating changes. If evidence contents change, issue a new reference; a manually reused alias cannot prove historical integrity. The companion is an editable artifact, not an automated admission checker or a simulated AWS test.

Download the blank dependency worksheet (Markdown). No email is required. The artifact contains no acquired database, Windows or AWS metadata.

Filled example

Record and accountability

dep-j2-r1; migration-lead-a; export-owner-a; fictional roles

Source and target profile

Windows SQL Server2019 Standard; standard RDS2019 Standard proposed; actual builds/Region UNKNOWN

Business obligation

Settlement extract acknowledged by 02:00 UTC; deadline is synthetic

Trigger and branch

J2 01:10; command-shell step after J1; failure/retry branches require retained definitions

Identity and host resources

worker-principal-a; local-output-alias; destination-alias; no credentials supplied

Collection coverage

Agent, Windows, application and remote scheduler exports required; actual visibility unobserved

Support and disposition

Unchanged command-shell step unsupported; externalize proposal NOT ACCEPTED

Replacement and operating owner

worker-revision-a proposed; export-owner-a owns monitoring, replay and acknowledgement

Validation evidence

Duplicate, interruption and uncertain-delivery expectations stipulated; all NOT EXECUTED

Recovery and invalidators

Hold replay pending acknowledgement; changed script, identity, destination or build reopens review

Blank record

Record and accountability

Dependency ID/revision; migration lead; obligation and execution owners

Source and target profile

Exact builds, edition, OS/module, Region, topology and dated availability references

Business obligation

Output, consumer, deadline/timezone, accepted delay and durable completion meaning

Trigger and branch

Scheduler, step IDs, prerequisites, branches, retries and exceptional schedules

Identity and host resources

Principal and privilege references; paths, binaries, shares, network and secret ownership

Collection coverage

Approved collectors, visibility evidence, population, history window and unresolved exclusions

Support and disposition

Current source-backed capability or conflict; keep/externalize/replace/retire/HOLD rationale

Replacement and operating owner

Exact candidate artifact; recurring ownership; rejected alternative and funding dependency

Validation evidence

Independent expected cases, actual observations, failures, observer and unexecuted gates

Recovery and invalidators

Stop/replay rules, uncertain effects, recovery owner and changes requiring recollection

11. Abort, recover or change the target when evidence fails

Stop collection if an approved read causes unexpected load, exposes material outside the approved scope or requires unapproved privileges. Preserve the partial coverage record and have the source owner decide the next permitted collection. Do not run a job to discover what it does. If a script's destination cannot be safely identified, its required output remains held. A denied read is not permission to probe through another account.

During a separately authorized rehearsal, contain unintended schedules and preserve evidence under the test plan. Do not replay an uncertain external action merely because its local process failed. Before target writes, a later migration may retain a source-based abort, but once target writes or external effects occur the source may be stale and cannot simply be resumed. The existing migration recovery paper owns that broader authority contract.

Choose the alternative that resolves the actual conflict. Keep standard RDS with an owned replacement when behavior can be preserved and tested. Consider a reviewed self-managed target when required host control cannot be removed, explicitly retaining patching, backup, security and operational responsibilities. Defer when neither candidate has acceptable evidence or an accountable owner. A claim that the business no longer needs an output requires its business owner's decision, not a migration engineer's empty history query.

12. Acceptance checklist and next owned action

The inventory task is done when the declared source and host populations are reconciled, visibility exclusions are explicit, every required obligation maps to its contributing dependencies, and every row has an accountable disposition with current support evidence. Unsupported required behavior either has an accepted redesign specification or excludes that target. UNKNOWN coverage remains a visible hold. Closing this task can select a candidate for separately authorized validation; it cannot declare a migration accepted.

The reviewer checks exact source/target identity fields, all job steps and branches, external schedules, file consumers, assemblies and privileged calls. They challenge at least one missed schedule and one uncertain downstream effect. They verify that each proposed external worker has real ownership rather than being a blank box, and that a supported T-SQL job has not been mistaken for proof of target scheduling or failover. Retain rejection reasons so a later change can reopen the right row.

Before any target admission, require actual isolated results for the selected redesign, accepted remaining risks, current recovery evidence and separate execution authority. Reopen the register after a new job, script revision, path/identity change, assembly, schedule, engine build or target topology. Reading this playbook does not authorize collection, resources, spend or traffic movement. Bring the filled register and its largest unresolved dependency to the SQL, Windows and application owners; assign that row's next permitted evidence action before booking a migration date.