Sitecore XP 10.5 Database Upgrade Deep Dive

A database upgrade plan should begin with a list of operations that do not apply. Sitecore’s upgrade documentation covers multiple source releases, and its pages include procedures introduced long before 10.4. Running every available script is not a deeper upgrade. It is an uncontrolled change set.

For a 10.4-to-10.5 project, build a source-specific action matrix for the actual estate. Separate schema scripts, default-item cleanup, connection changes, service-specific upgrades, and functional validation. A database can require no schema action while still needing a restored copy, an updated destination, permission checks, and application acceptance. Those distinctions are the core of this guide.

This is a proposed engineering method for an on-premises XP estate, with official procedures linked beside the relevant decisions. It is not an account of a database migration performed for your project. The examples are planning and inspection artifacts. They are not a replacement for the packaged vendor scripts or your database administrator’s backup, restore, and access procedures.

I prefer an explicit no-action row over an omitted database. The row forces a reviewer to explain why an operation is unnecessary and which validation remains. That adds administrative work, but it makes an upgrade easier to audit and recover. No execution duration, data volume, or successful production outcome is claimed here because no such evidence has been supplied.

1. Build a source-specific database action matrix

Inventory logical roles and physical databases separately

Begin with deployed connection information and the infrastructure inventory. Record every logical database role, physical SQL instance, database name, application consumer, and owner. Keep secrets out of the matrix; use protected references to the credential source. Include custom databases and additional publishing targets. The inventory must describe the actual solution, including exceptions that are not represented by the standard topology diagram.

Do not use the physical database name as the only indication of purpose. Names can survive an old deployment convention or differ across environments. Ask the application owner which role uses the connection, and ask the database owner to confirm the physical resource. Where several logical connections resolve to one database, record that relationship explicitly. It matters when a script’s target depends on whether a role is separated.

Record the exact source XP release and relevant service versions. “10.4” is a useful series name but may not identify all installed packages or module resources. Preserve the current deployment artifact and the resources selected for the target. If an earlier hotfix or manual database change is known, include its provenance and ask the owner how it affects the approved upgrade path rather than silently carrying it forward.

Keep different operation classes in different columns

Use distinct columns for schema action, content cleanup, service upgrade, connection mapping, runtime identity, and validation. A single “database upgraded” checkbox cannot express those states. For example, a schema operation may be complete while the application still points at the old database, or a copied database may be correctly attached while the cleanup decision remains open. The matrix should make those gaps visible.

The official database upgrade overview directs readers to database-specific instructions and notes that sequence and manual actions can matter. Treat that as a requirement to select operations, not permission to infer a universal script order from filenames. Link each action to the exact applicable procedure and preserve the package used for execution.

For every row marked no schema action, include a short reason tied to the source-version matrix. Then list what still must be tested. The reason prevents the row from looking like an omission, while the validation column prevents no action from becoming no responsibility. Assign a reviewer who can check applicability without relying on the operator’s memory of a previous upgrade.

Capture a safe metadata baseline

The following read-only SQL example collects basic database metadata. Replace the example names with the approved inventory and run it using an account authorized for inspection. Microsoft documents these columns in sys.databases. The result records metadata, not Sitecore schema correctness, and visibility depends on the caller’s permissions.

SELECT
    name,
    state_desc,
    compatibility_level,
    collation_name,
    recovery_model_desc
FROM sys.databases
WHERE name IN (
    N'project_Core',
    N'project_Master',
    N'project_Web'
)
ORDER BY name;

Preserve the output with the server identity, observation time, and inspected environment. Do not respond to a missing row by immediately granting a broad role; first ask whether the account’s visibility explains the result. A metadata baseline is useful for detecting an unexpected destination or configuration difference, but it does not prove that the application can read its expected data or perform its intended operations.

Keep metadata observations separate from change instructions. A difference in compatibility level, collation, or recovery model is a question for the approved platform design and database owner. Do not automatically normalize settings because a comparison shows different values. The migration record should state which differences are intentional and which require investigation, with a reason for each decision.

Approve the matrix before the window

Review the completed map with platform, database, application, and integration owners. Ask each owner to identify hidden writers and readers. An external reporting process, scheduled export, or custom service can depend on a database without appearing in the web application’s configuration. Add those consumers to the stop, resume, and validation plan. The matrix should explain the impact of taking a dependency out of service.

Freeze the selected operation set for rehearsal. Changes after rehearsal need a classification and a decision about what evidence to repeat. Replacing a package or changing a source version is materially different from correcting a comment. The release lead should preserve the approved matrix version with the execution record so the operator’s actions can be compared with the actual plan.

This phase passes when the team can trace each database from logical purpose to physical destination, selected procedure, and acceptance test. If that trace is incomplete, stop before running scripts. Database work becomes harder to reason about once a partially understood operation has changed the state the team is trying to investigate.

2. Protect coherent copies and verify their destinations

Identify every writer before choosing the boundary

For each database, identify which applications or services can write and when they run. Include scheduled and asynchronous work rather than looking only at interactive traffic. Define how each writer is stopped, paused, redirected, or otherwise controlled under the approved procedure. Assign an owner who can verify the resulting state. A maintenance page alone does not establish that the whole data estate is quiet.

Follow Sitecore’s safeguarding guidance for database use during scripts. In the project record, preserve the exact method selected and the roles it covers. If the team upgrades isolated copies, prove that they are copies and that the rehearsal cannot accidentally target live databases. If it disables the environment, account for the relevant services as well as web roles.

Define a coherent recovery set with the database and application owners. Record which backups, application artifact, configuration references, and required keys belong together. The required consistency boundary depends on the application’s workflows and external effects. Make that boundary explicit rather than assuming that backups taken at unrelated times automatically describe one usable application state.

Restore before calling the backups usable

Use the backup and restore procedures approved for the actual SQL deployment. Encryption, infrastructure, access controls, and recovery requirements can change the steps. This article intentionally avoids a universal restore command. The operator should have the exact approved procedure, the required access path, and a tested way to locate the selected recovery set during the maintenance window.

Rehearse restoration into isolated destinations and verify them against the mapping document. Test the application through safe accounts and controlled data after connections are applied. Preserve database-level restore evidence and application-level outcomes separately. The first shows that the database operation completed; the second shows that the chosen recovery set can support the selected business workflows.

Measure the complete recovery route, including obtaining access, locating data, restoring, reconnecting roles, and validating service. Do not use a database restore duration as the entire recovery estimate. Handoffs and application acceptance consume time too. Use observed rehearsal results to reserve a recovery window, and identify any part of the route that remains untested because the environments differ.

Copied Collection storage needs more than new filenames

The 10.4 shard-renaming guide, linked from the safeguarding instructions, explains that Collection includes a shard map manager and shard databases. Renaming copies can require reference updates and role restarts. Follow that exact procedure where it applies. Do not assume that changing the top-level application connection updates every stored shard destination.

Keep a before-and-after map for copied shard names and locations. Have the reviewer compare the copied application’s destinations with the intended isolated cluster. Confirm that no old destination remains through the supported inspection procedure. Preserve the map with the restoration evidence so that a later xConnect failure can be investigated without guessing which shard references were modified during copying.

Do not introduce a shard split, merge, encryption redesign, or data-retention change merely because the databases are already being moved. If one of those changes is necessary, treat it as a separate dependency with its own supported procedure and rehearsal evidence. Combining unrelated transformations can make it difficult to distinguish an upgrade failure from a change to the data architecture.

Keep rehearsal side effects under control

Review outbound integrations, publishing targets, email, background tasks, and other effects that a restored environment could trigger. Apply the project’s approved isolation controls before starting consumers. A copied database may contain configuration or queued work that was appropriate in production. The rehearsal should demonstrate the upgrade without producing unintended business actions elsewhere.

Record how test data is selected and protected. Where production data is needed for a valid rehearsal, follow the organization’s established access and handling requirements. Do not create a new public evidence folder containing sensitive records just to make validation easier. Store only the identifiers and observations the reviewer needs, with protected links to any restricted evidence.

The protection gate passes when backups have been restored, destinations verified, writers controlled, and side effects accounted for. Keep the original recovery set intact through the migration and observation period selected by the owners. Successful script execution on a copy is useful evidence, but it does not remove the need to preserve the state required for a return.

A worked review of a copied estate

Consider a proposed rehearsal with a Core database, a separate security database, Master, two publishing targets, and a Collection cluster. The project wants copied destinations whose names are visibly different from production. This is an illustrative topology, not a description of your installation. Begin by writing both the logical purpose and physical destination for every connection before restoring or starting an application.

The reviewer first asks whether the core and security logical connections resolve to different physical resources. If they do, the security script decision must follow that actual separation. If the names in the document differ but the deployed values resolve to one resource, the document is wrong. Correct the inventory before execution. The lesson is to verify resolved destinations rather than making a database decision from a naming convention.

Next, review the two Web targets separately. Ask which site or publishing purpose uses each one, which CM configuration selects it, and which delivery application reads it. A successful publish to one target should not pass the other target’s acceptance row. Select controlled content and record the expected destination for each test. Keep those tests distinct even if both databases have the same no-schema-action classification.

For Collection, compare the restored database list with the copied shard map and the consumers’ configuration. The operation record should identify the supported copy and rename procedure used, not merely the command that restored the files. Have the reviewer inspect the reference mapping through the documented process. If a reference still resolves to production, stop application startup and correct the mapping through the approved procedure before performing a functional test.

Then inspect the application artifact and connection deployment together. A configuration file can be correct in the build output but absent from the deployed role; a secret reference can have the right name but resolve to an old value. The acceptance record should describe the effective destination observed by the running application using the project’s approved diagnostics. Avoid printing passwords as a way to prove that the value was loaded.

Finally, trace a representative workflow through the copied estate. Use a controlled editor change, the intended publishing target, and a safe XP interaction where applicable. The expected outcome should name which destination supplies each observation. If the test succeeds while the destination cannot be established, the result is weaker than it looks. It might show that some reachable environment works rather than that the copied rehearsal estate is coherent.

Record state transitions instead of collecting green screenshots

A useful operation log records precondition confirmed, operation started, operation completed, verification passed, and reviewer accepted as distinct states. These labels are a proposed process, not Sitecore status values. They let the team represent an operation that completed but has not yet been verified. That distinction matters when several operators work through a long matrix and the release lead needs to know what remains uncertain.

For example, a script operator can attach output and mark execution complete while the application owner still has a blocked validation. The release record should preserve both observations. A green console screenshot cannot replace the blocked test, and the blocked test does not automatically mean the script failed. The two owners can investigate the layer where evidence is missing without rewriting the history of the operation.

Use an explicit not-attempted state for operations skipped after an earlier stop condition. Otherwise, a later reader may mistake blank cells for missing documentation or assume that all planned work ran. Record the stop reason, current database state as assessed by the owner, and the chosen recovery or investigation action. This makes the failed rehearsal useful evidence for improving the procedure rather than an unexplained unsuccessful attempt.

Before the production window, have an operator who did not write the plan walk through these records. Ask that person to identify the first target, the source package, the expected result, and the response to a failed verification. Any ambiguity discovered in that review is a concrete opportunity to improve the procedure while the team still has time. Keep the review changes and the final rehearsed version together so that approval refers to a reproducible plan.

3. Upgrade the content and security databases deliberately

Read the XM portion as a source-version matrix

The XM database table applies to the XM part of XP too. For 10.4 sources, it lists CMS_master.sql for Master and no schema action for Web or Experience Forms. CMS_security.sql targets the actual security storage: Core when security is shared, or the separate security database when extracted. Use the guide’s notes and ordering for execution.

Turn that selection into rows for your physical resources. If the estate has several Web databases, list each one and identify its publishing purpose. A no-schema-action classification does not mean the target application should continue using an old or unintended destination. Verify the connection mapping and the content-serving result for each publishing target that remains in scope.

Review security placement before executing the selected security operation. Compare the logical core and security connections, and have the owner confirm the layout. Do not run a script twice against two names that resolve to the same physical database. Conversely, do not update Core while leaving a separate security database unreviewed because the environment name convention made it look optional.

Separate default-item cleanup from schema changes

Sitecore’s content database cleanup procedure has an applicability note: sources at 10.1 or later without modules or connectors skip that section. Where applicable, UpdateApp removes unmodified default items and logs modified items for review. This is distinct from selecting schema scripts. Do not label a cleanup run as proof that every database schema operation completed.

For modified default items, assign a reviewer who understands the customization. The decision is whether the modification remains necessary in the target and how it should be represented there. Keep the old item, intended target behavior, and final disposition linked in the evidence packet. Removing or retaining a modified item without understanding its purpose can change application behavior even when the underlying database is structurally sound.

Select the tool package and module resources that correspond to the actual source. Preserve their provenance and identifiers. The UpdateApp 1.5.2 page provides source-version and topology variants. Do not choose the nearest-looking package name or mix resources from a module version that was never installed. An unexplained mismatch is a reason to resolve the inventory before continuing.

Make the operator record specific

For each applicable operation, record the physical target, approved package, procedure reference, operator, start time, end time, output, and verification. Add a precondition check that compares the actual database with the matrix. This is a proposed operational control. It reduces the chance that a correct script is run against the wrong database because two connection files or query windows look similar.

If an operation fails, preserve the output and assess the resulting state before retrying. Determine whether the procedure documents repeatability or a recovery path. Do not infer idempotence from a script’s name or from the absence of an obvious error during a second run. Ask the database owner and supplier guidance to establish the appropriate next action for the actual failure.

Rehearsal should produce one coherent execution sequence from fresh copies, including any approved remediation. Avoid building a production runbook from disconnected successful fragments across several attempts. A change that appears harmless can depend on state left by an earlier attempt. Replaying the final procedure from the intended starting point is how the team discovers that dependency before production.

Reconnect and review cleanup outcomes

The SQL connection instructions direct each role to the upgraded databases and include follow-up review of modified items from UpdateApp logs. Treat connection deployment and item review as explicit tasks. The database operator should not have to assume that an application deployment later changed the right file or resolved the right secret reference.

Use controlled content to verify Core-related authoring behavior, Master changes, and publishing to each intended Web target. Where the application uses Forms, include a safe representative submission and its expected outcome. The tests should follow the application’s approved APIs and workflows rather than introducing direct modifications to Sitecore tables as a shortcut.

The content database gate passes when applicable scripts and cleanup decisions are complete, connections are reviewed, and representative application behavior is demonstrated. Preserve the no-action explanations beside completed operations. That record makes it possible for a later reviewer to distinguish an intentional skip from an unperformed task.

4. Handle xDB, shards, reporting, and processing by applicability

No schema action is a meaningful result

In the xDB source-version table, the 10.4 column marks processing engine storage and tasks, EXM Master, processing tasks and pools, Reporting, Reference Data, Collection shard map manager and shards, and Marketing Automation as no action. Messaging is the exception discussed next. Do not apply the older-source instructions farther down the page indiscriminately.

Translate those entries into your inventory rather than copying a generic database list. Some roles may not be deployed or used in the solution, while custom services may depend on their data. Record actual applicability and the owner who confirms it. A database that is out of scope should have a reason; a database in scope needs a validation plan even if its schema selection is no action.

Keep schema selection distinct from service upgrade and data-processing readiness. New application binaries can fail to connect, use the wrong endpoint, or exercise custom code paths without a required schema script being missing. Diagnose the observed failure using the connection and consumer map. Do not respond to every xDB problem by running another historical database script.

Review post-upgrade instructions in their own context

The Update xDB chapter organizes work by the release where changes were introduced and tells readers to perform applicable steps. Record which entries apply to your source. A chapter title is not a command to execute all of its children. Preserve the applicability review so the decision is repeatable when another environment is prepared.

Give Reporting a separate decision record. The Reporting rebuild guidance discusses rebuilding for consistency of historical and current data. That concern is separate from a source table marking no schema action. Have the analytics owner review the documented rationale and the actual reporting requirements, then record whether a rebuild is needed and how its outcome will be checked.

Do not promise that a reporting screen is correct merely because it loads. Choose a controlled period, known test interactions where appropriate, and the specific report or downstream process that matters. Record what equivalence or intentional difference is expected. If historical data cannot be fully validated in the rehearsal, state that coverage limit rather than generalizing from a successful current-data check.

Validate Collection through its supported consumers

Use safe test identities and supported application paths to verify representative xConnect reads and writes in the isolated estate. Connect each test to its intended Collection destination and service identity. Preserve correlation evidence so the team can tell whether an operation reached the expected data store. Avoid using direct table edits as a substitute for exercising the application’s actual data contract.

Check the relationship between Collection and its search or processing consumers through the applicable procedures. A successful write does not establish that every downstream consumer has observed it. Choose the meaningful end state for the business workflow, then identify how the team can observe it. Use the application’s agreed timing expectations rather than inventing a universal processing latency.

If a shard was renamed during copying, include the relevant reference and restart checks in this phase. Keep that work separate from schema selection: no schema action does not cancel the copy-configuration requirements. A failure caused by a stale shard destination should be investigated as a mapping problem, with the before-and-after shard record available to the operator.

Keep processing and reference-data tests purposeful

Select a representative processing job or workflow with the application owner. Record how it is submitted, which role should consume it, what successful completion means, and where an error becomes visible. The test should be safe to repeat. A background service running without a startup error is only one observation; it does not demonstrate that the work the application depends on completes.

For Reference Data, identify the application use that must continue to work and verify it through the supported service path. Preserve the source-version no-action rationale rather than introducing older migration steps to make the checklist look more complete. Where custom code accesses data outside the supported path, inventory and test that dependency explicitly instead of assuming platform compatibility covers it.

This phase passes when the xDB applicability record is complete, necessary post-upgrade decisions are documented, and selected consumer workflows have evidence. Keep unresolved downstream work visible to the release owner. A collection of green process-health checks should not conceal a blocked reporting or processing result that is material to the production decision.

5. Review Messaging, Identity, and runtime permissions

Select the Messaging procedure from the transport

For the SQL-backed Messaging path, the xDB guide identifies SXP_Messaging.sql; it says the database upgrade is unnecessary when Azure Service Bus is used. Record the actual transport before selecting the operation. An unused database left from an earlier deployment is not sufficient evidence that the current application uses SQL transport. Verify the running configuration and have the integration owner confirm it.

Preserve the Messaging procedure’s output and perform the specified verification with the database owner. Then test a safe representative message-driven workflow through its normal producer and consumer. The schema operation and the functional result are separate evidence. A script can finish while a consumer still points at another destination or fails under its runtime permissions.

Keep queued or in-flight work in the migration boundary. Decide which producers pause, whether work drains, and how unresolved items are accounted for. Those are project-specific operational decisions. Do not silently discard work to make the target look healthy. If reconciliation or compensation is needed, assign it to the owner of the business transaction rather than treating it as an incidental platform cleanup.

Inspect Identity independently of the XP label

The XP upgrade chapter links to separate Identity upgrade guides by hosting model. Select the actual deployed Identity source and intended target. Do not infer the installed Identity build solely from the XP version or from an older project note. Preserve its package and configuration as an independently versioned dependency in the matrix.

At review time, the linked on-premises preparation page describes Identity 9.0 and its prerequisites. It also identifies security database and configuration backups. This is a reason to read the linked versioned guide, not to assume every existing XP deployment already has that Identity version or should receive an unreviewed package replacement.

The corresponding Identity upgrade procedure identifies CMS_security_IdentityServer.sql and a security-database step before installing its target. That service-specific script is not interchangeable with CMS_security.sql from the XP matrix. Determine applicability through the selected Identity path and record the physical security destination before any execution.

Verify encrypted connections through the runtime path

The Identity guide discusses encrypted SQL communication and certificate trust, including conditions tied to older Identity sources. Follow the applicable instructions and test the intended production trust model. Do not promote a non-production certificate-validation bypass into the release configuration merely because it allowed installation to continue. Preserve the failure evidence and fix the trust path through the approved infrastructure process.

For each database consumer, verify the account and connection deployed to the running role. An administrator’s successful query does not establish that the service identity has the required access. Use a role-to-database matrix with the permitted operation and observed result. Ask the database owner to review unexpected privileges or failures rather than granting a broad role as a permanent workaround.

Separate upgrade-operator privileges from runtime privileges. The person applying scripts may need a different access path from the application. Record how temporary access is granted and removed under the organization’s existing process. The final acceptance should demonstrate the intended runtime state, including the account the application will use after the maintenance window ends.

Test login and background work as separate journeys

Choose safe authentication journeys that reflect the solution: a representative editor sign-in, return to the intended application, and any external identity path in scope. Verify the expected user context and authorization result. A login page rendering successfully is not the same as a completed authentication flow, and a successful administrator session is not sufficient coverage for every user role.

For background services, test the database path and business outcome separately from interactive login. Record which identity the service uses, where it obtains configuration, and how operators observe failure. The matrix should show whether a service can start, connect, and complete its selected work. Those states help identify whether a post-upgrade failure belongs to deployment, permissions, or application processing.

The service and permission gate passes when Messaging applicability is resolved, the Identity path is selected and completed where needed, and runtime connections are demonstrated under the intended accounts. Keep service-specific operations in their own records. That makes the next upgrade easier to plan without confusing a previous XP schema action with an Identity or transport requirement.

6. Prove data behavior and retain a coherent recovery point

Build a validation ladder

Start with destination identity, then connection, then the operation the consumer requires, and finally the business workflow. Each layer answers a different question. A database online state does not show that the application uses it; a successful connection does not show that a publishing or processing transaction produces the expected result. Keep the evidence at each level rather than collapsing it into one pass indicator.

Use a controlled sample for content, security, and XP workflows. Record the identifiers, source snapshot, application artifact, test identity, and expected outcome. Make cleanup repeatable. A useful sample has a reason for each selected case: a language variant, a publishing target, a permission boundary, or an asynchronous operation whose failure would matter to the release decision.

The following proposed acceptance matrix separates schema selection from functional evidence. Replace its examples with the actual project tests. The table is a planning artifact and does not claim that the tests have been executed. A reviewer should be able to identify blocked and untested rows as clearly as passed rows.

Do not use row counts as the whole acceptance test

Counts can support a chosen reconciliation, but define what is counted and why equality is expected. A cleanup operation may intentionally remove default items, while an application workflow may add controlled test data. An unexplained total is difficult to interpret. Preserve the operation’s intended changes and compare the selected evidence against that intent rather than expecting every count to stay identical.

Investigate discrepancies with the database and application owners. Identify whether the difference belongs to the selected snapshot, an intended cleanup, a failed operation, a changed connection, or an application transformation. Avoid repairing the visible symptom with direct updates before the cause is understood. That can make the test pass while leaving the approved procedure unable to reproduce the result.

Keep performance observations separate from correctness. Measure representative database-dependent workflows under comparable conditions, record the workload and environment, and use the project’s acceptance targets. This article does not claim that moving to a new release automatically improves a particular query or processing job. If a regression matters, preserve the evidence and investigate it before cutover.

Recover the application state, not only one database

Define which resources must return together if the release is rejected. A previous application artifact, one restored database, and several newer writable dependencies may not describe the intended recovery state. The recovery plan should identify its consistency boundary and the treatment of writes after that boundary. Have the owners of external effects review the reconciliation decision too.

Rehearse a controlled return before production. Restore the selected set, apply the intended connections, and run the critical acceptance journeys. Record the whole duration and any manual handoff. A database-level restore test is valuable but incomplete if nobody has demonstrated that the previous application can operate on the recovered estate with its intended credentials and configuration.

During a failed attempt, preserve script output and application evidence before restoring. Record which operations completed, which failed, and which resources received writes afterward. That record supports both the immediate recovery and the later cause analysis. Do not erase the only useful evidence by treating a restore as a reason to discard the failed execution log.

Close with an auditable database disposition

At handover, every matrix row should have a final state: applicable operation completed, no operation required with a cited reason, or an accepted exception with an owner. Include the deployed application artifact, connection mapping, test outcomes, and recovery retention decision. Keep restricted evidence in protected storage and provide stable references so the operations team can use it later.

Assign deferred work explicitly. If a report comparison or background observation continues after cutover, name the owner, expected result, and action if it fails. The release record should show that limitation rather than claiming the whole estate was proven immediately. A future support engineer should be able to understand what was tested during the window and what was observed afterward.

The next practical step is to populate the source-specific matrix for the real estate and have a second operator review physical destinations and applicability. That is the point where this general method becomes a project procedure. With that record, a database upgrade is a controlled set of changes with evidence; without it, the team is relying on filenames, memory, and the hope that a successful startup proves more than it actually does.