Symptoms

The Database has drifted from its registered data-tier application error message appears when the end-point of the Microsoft 365 application is upgraded.

Cause

The current structure of the application end-point database differs from the database structure the upgrade script is intended for. The structure of the application end-point database was manually modified before the upgrade.

Resolution

Important: It is recommended to back up the current database of the application end-point before performing the actions below.

Notes:

  • SQL Server Express LocalDB is used in the KB article as an example. You can use the same approach to resolve the problem for SQL Server Express/Web/Standard/Enterprise.
  • If you receive the Could not generate deployment report or An error occurred when attaching the database(s)" error when attaching to a SQL Server database, this means that you are using an old version of SQL Server Management Studio. Try using the latest version of SQL Server Management Studio.

To resolve this problem, you need to perform the following actions:

  1. Stop the application end-point. This can be done in the following way:

    1. Open Internet Information Services (IIS) Manager and stop the corresponding Microsoft 365 gateway site and the corresponding application pool.
    2. Open Windows Task Manager and wait until the processes of the corresponding application pool user disappear (sqlserv.exe and w3wp.exe).
  2. Obtain the list of changes. This can be done in the following way:

    1. Open SQL Server Management Studio.
    2. In the Connect to Server window, set Server name to (localdb)\MSSQLlocalDB.
    3. In the Connect to Server window, set Authentication to Windows Authentication.
    4. In the Connect to Server window, click Connect.
    5. In the navigation tree, right-click Databases and select Attach.
    6. In the Attach Databases window, click Add.
    7. In the Locate Database Files window, specify the path to the .mdf database file of the application end-point. This file is placed in the MICROSOFT_365_GATEWAY_APPLICATION_DIRECTORY\App_Data directory.
    8. In the Attach Databases window, click OK.
    9. In the navigation tree, right-click the attached database and select Tasks > Upgrade Data-tier Application.
    10. In the Upgrade Data-tier Application window, click Next.
    11. In the Upgrade Data-tier Application window, specify the path to the .dacpac file of the target version of the application package. This file is placed in the DIRECTORY_OF_EXTRACTED_APPLICATION_PACKAGE\DIRECTORY_OF_EXTRACTED_O365-WEB.ZIP\Deployment directory.
    12. In the Upgrade Data-tier Application window, click Next.
    13. In the Upgrade Data-tier Application window, click Save Report and save the file to a directory. This file contains the list of changes. It will be used to analyze and roll back the manual changes.
    14. In the Upgrade Data-tier Application window, click Cancel.
    15. Open the report file in a text editor.
  3. Prepare a temporary copy of the original database of the application end-point. This can be done in the following way:

    1. In the navigation tree of SQL Server Management Studio, right-click Databases and select Deploy Data-tier Application.
    2. In the Deploy Data-tier Application window, click Next.
    3. In the Deploy Data-tier Application window, specify the path to the .dacpac file of the current version of the application package. This file is placed in the DIRECTORY_OF_EXTRACTED_APPLICATION_PACKAGE\DIRECTORY_OF_EXTRACTED_O365-WEB.ZIP\Deployment directory.
    4. In the Deploy Data-tier Application window, click Next three times.
    5. In the Deploy Data-tier Application window, click Finish.
  4. Analyze and roll back each change. Use the prepared report file, the current database of the application end-point, and the temporary copy of the original database of the application end-point.

  5. Validate that the current database of the application end-point is ready to be upgraded. This can be done in the following way:

    1. In the navigation tree of SQL Server Management Studio, right-click the current database of the application end-point and select Tasks > Upgrade Data-tier Application.
    2. In the Upgrade Data-tier Application window, click Next.
    3. In the Upgrade Data-tier Application window, specify the path to the .dacpac file of the target version of the application package. This file is placed in the DIRECTORY_OF_EXTRACTED_APPLICATION_PACKAGE\DIRECTORY_OF_EXTRACTED_O365-WEB.ZIP\Deployment directory.
    4. In the Upgrade Data-tier Application window, click Next.
    5. In the Upgrade Data-tier Application window, make sure there are no detected changes.
    6. In the Upgrade Data-tier Application window, click Cancel.
  6. Detach the current database of the application end-point and start the application end-point.
  7. Run the upgrade script again.


Internal

Internal link