Lessons learned from a Partner when upgrading Connector for Microsoft Dynamics MSDI database
The MSDI database that Connector for Microsoft Dynamics uses for storing information about integrations is intended to be used ONLY for Connector for Microsoft Dynamics. Recently I was working with a partner who was experiencing some issues when upgrading to the latest version of Connector for Microsoft Dynamics. The partner was receiving error messages similar to the following in during the upgrade from the Connector for Microsoft Dynamics installer:
And in the event viewer the following was recorded:
Warning SQL72015: The table . is being dropped, data loss could occur.
Error SQL72014: .Net SqlClient Data Provider: Msg 50000, Level 16, State 127, Line 6 Rows were detected. The schema update is terminating because data loss might occur.
at Microsoft.SqlServer.Dac.DeployOperation.<CreatePlanExecutionOperation>b__b(Object operation, CancellationToken token)
at Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
at Microsoft.SqlServer.Dac.ReportMessageOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
at Microsoft.SqlServer.Dac.DeployOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
at Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, Action`2 reportStatus, CancellationToken cancellationToken)
at Microsoft.SqlServer.Dac.DacServices.InternalDeploy(IPackageSource packageSource, Boolean isDacpac, String targetDatabaseName, DacDeployOptions options, CancellationToken cancellationToken)
at Microsoft.SqlServer.Dac.DacServices.Deploy(DacPackage package, String targetDatabaseName, Boolean upgradeExisting, DacDeployOptions options, Nullable`1 cancellationToken)
at Microsoft.Dynamics.Integration.Install.CustomActions.Utility.TryCustomAction(Session session, String customActionName, Func`1 action)
If you run into a similar situation, here is what might be going on. In this customer's case they had installed other tables into the MSDI database for another application. The .dpac utilities that are used for upgrading the MSDI database only have knowledge of the tables within a standard MSDI database and in this case, when the other tables were encountered the upgrade utilities failed because they did not know how to upgrade those tables. This is similar to the situation that occurs when "extra" items are added to the tables in MSDI that the upgrade utilities do know about. For example, if you have added a trigger to one of the tables in MSDI, then the upgrade utilities will drop that trigger.