none
Upgrading MDS from 2014 to 2019 - Error with Constraints RRS feed

  • Question

  • When upgrading our Master Data database from 2014 to 2019 we are receiving errors in regards to foreign key and primary key constraints. How does one get around this? Are we supposed to drop all the constraints, upgrade, and then put them back? I have also tried to upgrade from 2014 to 2016 and get the same results. (see Below)

       Microsoft.MasterDataServices.Configuration.ConfigurationException: The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk_tblBRBusinessRule_tblEntity_Entity_ID". The conflict occurred in database "******", table "mdm.tblEntity", column 'ID'.
    ALTER TABLE [mdm].[tblAttribute] ADD [Description] NVARCHAR (500) NULL
    ALTER TABLE [mdm].[tblAttribute] ADD [DomainEntity_Version] INT NULL
    ALTER TABLE [mdm].[tblAttribute]  ADD CONSTRAINT [fk_tblAttribute_tblModelVersion_DomainEntity_Version] FOREIGN KEY(DomainEntity_Version) REFERENCES mdm.tblModelVersion(ID) ON UPDATE NO ACTION ON DELETE NO ACTION
    ALTER TABLE [mdm].[tblAttribute] DROP CONSTRAINT [fk_tblAttribute_tblAttributeValidation_AttributeValidation_ID]
    ALTER TABLE [mdm].[tblAttribute] DROP COLUMN [AttributeValidation_ID]
    ALTER TABLE [mdm].[tblAttributeGroupDetail] DROP COLUMN [DomainBinding]
    ALTER TABLE [mdm].[tblAttributeGroupDetail] DROP COLUMN [TransformGroup_ID]
    DROP TABLE mdm.[tblAttributeValidation]
    ALTER TABLE [mdm].[tblBRBusinessRule] DROP COLUMN [RuleConditionSQL]
    EXEC sp_rename '[mdm].[tblBRBusinessRule].[Foreign_ID]', 'Entity_ID', 'COLUMN'
    Caution: Changing any part of an object name could break scripts and stored procedures.
    ALTER TABLE [mdm].[tblBRBusinessRule]  ADD CONSTRAINT [fk_tblBRBusinessRule_tblEntity_Entity_ID] FOREIGN KEY(Entity_ID) REFERENCES mdm.tblEntity(ID) ON DELETE CASCADE ---> System.Data.SqlClient.SqlException: The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk_tblBRBusinessRule_tblEntity_Entity_ID". The conflict occurred in database "******", table "mdm.tblEntity", column 'ID'.
    ALTER TABLE [mdm].[tblAttribute] ADD [Description] NVARCHAR (500) NULL
    ALTER TABLE [mdm].[tblAttribute] ADD [DomainEntity_Version] INT NULL
    ALTER TABLE [mdm].[tblAttribute]  ADD CONSTRAINT [fk_tblAttribute_tblModelVersion_DomainEntity_Version] FOREIGN KEY(DomainEntity_Version) REFERENCES mdm.tblModelVersion(ID) ON UPDATE NO ACTION ON DELETE NO ACTION
    ALTER TABLE [mdm].[tblAttribute] DROP CONSTRAINT [fk_tblAttribute_tblAttributeValidation_AttributeValidation_ID]
    ALTER TABLE [mdm].[tblAttribute] DROP COLUMN [AttributeValidation_ID]
    ALTER TABLE [mdm].[tblAttributeGroupDetail] DROP COLUMN [DomainBinding]
    ALTER TABLE [mdm].[tblAttributeGroupDetail] DROP COLUMN [TransformGroup_ID]
    DROP TABLE mdm.[tblAttributeValidation]
    ALTER TABLE [mdm].[tblBRBusinessRule] DROP COLUMN [RuleConditionSQL]
    EXEC sp_rename '[mdm].[tblBRBusinessRule].[Foreign_ID]', 'Entity_ID', 'COLUMN'
    Caution: Changing any part of an object name could break scripts and stored procedures.
    ALTER TABLE [mdm].[tblBRBusinessRule]  ADD CONSTRAINT [fk_tblBRBusinessRule_tblEntity_Entity_ID] FOREIGN KEY(Entity_ID) REFERENCES mdm.tblEntity(ID) ON DELETE CASCADE
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
       at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
       at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at Microsoft.MasterDataServices.Configuration.DatabaseUtilities.ProcessSqlStringWithTransaction(String commandSql, SqlConnection connection, SqlTransaction upgradeTransaction)
       at Microsoft.MasterDataServices.Configuration.DatabaseUtilities.UpgradeMasterDataServicesDatabase(SqlConnection connection, String databaseName, SkuTypes skuType, Collection`1& businessRuleExclusionWarnings)
       at Microsoft.MasterDataServices.Configuration.Commands.UpdateMasterDataServicesDatabase.InternalProcess()
       at Microsoft.MasterDataServices.Configuration.Commands.SqlCmd`1.Execute()
       at Microsoft.MasterDataServices.Configuration.InstanceManager.UpgradeMasterDataServicesDatabase(DatabaseServerInformation serverInformation, DatabaseInformation databaseInformation)
       --- End of inner exception stack trace ---
       at Microsoft.MasterDataServices.Configuration.InstanceManager.UpgradeMasterDataServicesDatabase(DatabaseServerInformation serverInformation, DatabaseInformation databaseInformation)
       at Microsoft.MasterDataServices.Configuration.ConfigurationAdministration.UpgradeDatabase(DatabaseInformation databaseInformation)
       at Microsoft.MasterDataServices.Configuration.UI.MdsDatabase.UpgradeDatabase()


    Meachel Carnahan BSc. Computer Science Database Administrator

    Thursday, October 3, 2019 5:47 PM

All replies

  • I have been into the same position, recently. Eventually, there was no quick fix.
    The model it self is somehow corrupted, I guess a bug in version prior 2016, which happens after cleaning procedures in MDS have ran.
    You can verify this for ex. if you try to add an new column in existing Entity, it will fail with the same error. 

    Solution:
    Use the tools from the MDS [installation directory]\Microsoft SQL Server\1?0\Master Data Services\Configuration

    1. Export the model with the data by using MDSModelDeploy

    2. Then in cycle manually

    Edit the file with the ModelPackageEditor omitting some of the entities,
    Save it as new file, import it with MDSModelDeploy and try to use it for upgrade.
    If it fails, try again with omitting different set of Entities

    End of the cycle When the upgrade will pass, so you can identify the exact 'problematic Entity' that was omitted.

    3. Preserve the structure and the data for the 'problematic Entity'

    4. Run step 2 without the 'problematic Entity'

    5. Create the 'problematic Entity' manually

    I hope this will help you

    Wednesday, October 16, 2019 8:57 AM
  • Hi Vasko,

    These steps, are they done with a restored copy of the database?  We took a copy of prod over to the new test server then restored the MDS database onto the new instance. When going through the MDS Config Manager under the "Database Configuration" tab we select the MDS database on the instance and hit UPGRADE. This is where we are receiving the error.  Your suggestion - do we deploy the model against this database? or create a new one? 

    Thank,
    Meach


    Meachel Carnahan BSc. Computer Science Database Administrator

    Monday, October 21, 2019 6:31 PM
  • Hi Meach, 

    Create a new database, back it up
    and then deploy the Model, exported and edited without some of the entities.

    Each time the step 2 fails, just restore the empty database to try again,
    Until you locate the Entity causing the error, which you'll need to recreate manually after. 

    Best, 
    Vasko

    Tuesday, October 22, 2019 1:39 PM
  • Good Morning Vasko,

    Thanks for this suggestion. I created a new empty database and did as you suggested - remove entities one by one and import until the problem was found. I was able to identify 1 problem in one model so far. I do have a question. By "fixing" these problems in the model in production will this allow us to bring the database over to 2019. We need the MDS database that currently sits in prod to be upgraded with all it's data. And when we do the upgrade option through MDS Configuration manager that's when we get the error. 


    Meachel Carnahan BSc. Computer Science Database Administrator

    Wednesday, October 23, 2019 3:40 PM
  • Hi Meach,

    After fixing the problematic entity you should be able to upgrade the MDS.

    What I did was, created a new MDS db in production, imported the cleaned model, created the problematic entity, populated the entity with data only, recreated the security and then switched the new MDS db to point in the MDS configuration , delete the old MDS db, then upgrade the new MDS db.
    Afterward I even renamed the new MDS db to the the of the old MDS db, so everything is consistent. 

    Vasko

    Monday, October 28, 2019 8:52 AM