Repair Database Constraint Violations

After past upgrades, Flexera Support has identified cases where database constraints have been relaxed, and unexpected data inserted; and these changes have subsequently caused issues with license compliance outcomes. For reasons like this, the database migration scripts have been made more rigorous, ensuring that all database constraints are (re)activated, and stopping (failing the upgrade process) if unexpected data is found in tables of the following FlexNet databases:

Compliance database (suggested name FNMSCompliance)
Inventory database (suggested name FNMSInventory)
Data warehouse database (suggested name FNMSDataWarehouse).

You can avoid having your upgrade fail because of any such database issues by running the following process against each of those databases in turn, before you start the upgrade process. To run this process, you need to be familiar with Microsoft SQL Server Management Studio, and your login must be mapped to users with (at least) Select permissions on all of the above databases.

Note: This process does not make any changes to the data in your databases, because any such changes (if required) need inspection and assessment. However, it does reactivate any database constraints that have been disabled. For this reason, it is strongly recommended that, now that your system is isolated (see Isolate the System), you start with a full backup of all your FlexNet databases.

To identify issues with database constraint violations:

Log into Microsoft SQL Server Management Studio using an account with adequate mappings to database users.
Make full backups of all your existing FlexNet databases:
- Compliance database (suggested name FNMSCompliance)
- Inventory database (suggested name FNMSInventory)
- Data warehouse database (suggested name FNMSDataWarehouse)
- Reporting snapshot database (suggested: FNMSSnapshot).

Select the first database to validate (such as FNMSCompliance), and copy the following script and paste it into the execution window, select it, and run it.

Note: Long lines in this script have been wrapped for publication. You may choose to correct the wrapping before execution.

This script generates SQL statements to test data in tables that database migration will also check. A query is output for each database constraint that appears as 'untrusted' to Microsoft SQL Server.

SELECT
    N'IF EXISTS (SELECT * FROM ' + QUOTENAME(SCHEMA_NAME(c_o.schema_id)) 
                                 + N'.' + QUOTENAME(c_o.name) + N' AS a' 
                                 + N' WHERE ' +
    (
        SELECT CASE WHEN fkc.constraint_column_id = 1 
                                 THEN N'' 
                                 ELSE N' AND ' END + N'a.' 
                                 + QUOTENAME(c.name) + N' IS NOT NULL'
        FROM sys.foreign_key_columns AS fkc
        INNER JOIN sys.columns AS c
            ON c.object_id = fkc.parent_object_id
            AND c.column_id = fkc.parent_column_id
        WHERE fkc.constraint_object_id = fk.object_id
        ORDER BY fkc.constraint_column_id
        FOR XML PATH ('')
    ) +
    N' AND NOT EXISTS (SELECT * FROM ' + QUOTENAME(SCHEMA_NAME(p_o.schema_id)) 
                                 + N'.' + QUOTENAME(p_o.name) + ' AS b WHERE ' +
    (
        SELECT CASE WHEN fkc.constraint_column_id = 1 
                                 THEN N'' 
                                 ELSE N' AND ' END + N'a.' + QUOTENAME(c.name) 
                                 + N' = b.' + QUOTENAME(cr.name)
        FROM sys.foreign_key_columns AS fkc
        INNER JOIN sys.columns AS c
            ON c.object_id = fkc.parent_object_id
            AND c.column_id = fkc.parent_column_id
        INNER JOIN sys.columns AS cr
            ON cr.object_id = fkc.referenced_object_id
            AND cr.column_id = fkc.referenced_column_id
        WHERE fkc.constraint_object_id = fk.object_id
        ORDER BY fkc.constraint_column_id
        FOR XML PATH ('')
    ) + 
    N')) RAISERROR (''' + REPLACE(N'Data exists in ' + SCHEMA_NAME(c_o.schema_id) 
                                 + N'.' + c_o.name + N' which will break foreign key ' 
                                 + fk.name, '''', '''''') + N''', 18, -1)' AS TestQueries
FROM sys.foreign_keys AS fk
INNER JOIN sys.objects AS p_o
    ON p_o.object_id = fk.referenced_object_id
INNER JOIN sys.objects AS c_o
    ON c_o.object_id = fk.parent_object_id
WHERE fk.is_not_trusted = 1
ORDER BY p_o.name

If there are no SQL statements printed when you run this script, the database constraints are all trusted by SQL Server, and you may loop back and repeat this step for the remaining databases in turn. If no statements are created for any of the three databases under test, this process is complete, and you may skip forward to the next topic.

An untrusted database constraint produces an output SQL statement such as the following example (here wrapped for publication, and of course the table names and column names vary according to each case):

IF EXISTS (SELECT * FROM [dbo].[PaymentScheduleDetail_MT] AS a 
         WHERE a.[ActualAmountRateID] IS NOT NULL AND NOT EXISTS 
         (SELECT * FROM [dbo].[CurrencyRate_MT] AS b 
         WHERE a.[ActualAmountRateID] = b.[CurrencyRateID])) 
         RAISERROR ('Data exists in dbo.PaymentScheduleDetail_MT which will 
         break foreign key FK_PaymentScheduleDetail_ActualAmountRate', 18, -1)

Select any (or all) of the output SQL statements, and run them.
Each statement either:
- Produces no output, in which case the data is clean, and the previously untrusted constraint has now been reactivated
- Produces a two-line output of this form:
```
Msg 50000, Level 18, State 1, Line nn
Data exists in dbo.tableName which will break foreign key FK_Name
```
You may choose to check details of the tableName and FK_Name in your pre-upgrade version of the FlexNet Manager Suite Schema Reference. However, identifying and cleaning up the problematic data is likely to require specialized insight and expertise, so that the steps below are recommended.
Copy all such output, and paste into a document for safekeeping. Identify this set with the database name.
Loop back to step 3, repeating the process for all three databases in turn until all are completed.
Ask your registered support contact (a designated person within your enterprise who has access rights and login details) to open a new support case at https://community.flexera.com/t5/forums/postpage/board-id/@support, including a clear description of the issue. Be sure to attach the document listing affected tables and foreign keys.
A useful title might be something like "Upgrade stalled by DB constraint violations".

In due course, Flexera personnel will be in touch to assist with your data clean-up. Clearly, access to your data will be required, so you may plan for that (for example, you may support a conference call with a system allowing shared screens so that you can collaborate on the data clean-up).

Tip: Since you cannot proceed with the upgrade until these data issues are repaired, you may consider resuming normal operations with FlexNet Manager Suite until after the data cleanups are resolved.