[Resolved] Redundant data in the AirWatch Database leading to increased completion time during upgrades for 9.1.x and above

Symptoms

When upgrading to 9.1.x or higher, the Database installer for 9.1.x may take a long time to complete due to redundant data in the dbo.certificate table. This does not occur on all environments. It is recommended that the following checks are made on the AirWatch Database prior to scheduling an upgrade.

 

How to diagnose prior to upgrade

To verify if your environment could be affected by this problem, please execute the following script:


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
            IF OBJECT_ID('tempdb..#AuthCerts', 'U') IS NOT NULL
            DROP TABLE #AuthCerts;
                       
            CREATE TABLE #AuthCerts ( CertificateID BIGINT,
                                                                                                SubjectName NVARCHAR(500) COLLATE DATABASE_DEFAULT )
            CREATE NONCLUSTERED INDEX nix_SubjectName_AuthCerts ON #AuthCerts (SubjectName)
 
            INSERT INTO #AuthCerts
                         
            SELECT  c.CertificateID
                        , c.SubjectName
            FROM mobilemanagement.AuthenticationToken at
            INNER JOIN dbo.Certificate C
                        ON at.CertificateID = C.CertificateID
            WHERE at.AuthenticationType = 4  --Cert based auth


    IF OBJECT_ID('tempdb..#Certs', 'U') IS NOT NULL
            DROP TABLE #Certs;
                       
            CREATE TABLE #Certs ( CertificateID BIGINT,
                                                                                    SubjectName NVARCHAR(500) COLLATE DATABASE_DEFAULT )
            CREATE NONCLUSTERED INDEX nix_SubjectName_Temp ON #Certs(SubjectName)
 
            INSERT INTO #Certs
 
            SELECT r.CertificateID,r.SubjectName
            FROM
                        (
                        SELECT  c.CertificateID
                                    , c.SubjectName
                                    , RowNumber = ROW_NUMBER () OVER(PARTITION BY da.CertificateID ORDER BY C.SubjectName)
                        FROM interrogator.DeviceApplicationCheckIn da
                        INNER JOIN dbo.Certificate C
                                    ON DA.CertificateID = C.CertificateID
                        WHERE da.bundleID IN( 'com.airwatch.androidagent','com.Airwatch.mac.agent')
                        )r
            WHERE r.RowNumber = 1   
           
            SELECT COUNT(r.CertificateID) AS DuplicateCertCount
            FROM(
                                    SELECT
                                                            c.CertificateID
                                    FROM #AuthCerts t
                                    INNER JOIN dbo.Certificate c
                                                ON t.SubjectName = c.SubjectName
                                    LEFT JOIN #AuthCerts t2
                                                ON c.CertificateID = t2.CertificateID   
                                    WHERE t2.CertificateID IS NULL
 
                                    UNION
 
                                    SELECT c.CertificateID
                                    FROM #Certs t
                                    INNER JOIN dbo.Certificate c
                                                ON t.SubjectName = c.SubjectName
                                    LEFT JOIN interrogator.DeviceApplicationCheckIn da
                                                ON c.CertificateID = da.CertificateID   
                                    WHERE c.CertificateID < t.CertificateID
                                    AND da.DeviceAppCheckInID IS NULL
                        )r

 

If it returns a value greater than 0, follow the steps outlined below.

 

Preparation prior to upgrade

The redundant certificate cleanup script can be found here in the Resources portal. 

Depending on the number of such records, the script may take several hours to complete. It is recommended that the script be executed prior to the upgrade window – preferably a week in advance. The script should only be executed during off-peak hours OR when the AirWatch services have been stopped.

 

Support Contact Information

To open a Support Request, please call your local AirWatch support line or submit a Support Request via myAirWatch.

 

Best Regards,

The AirWatch Team

Have more questions? Submit a request

0 Comments

Article is closed for comments.