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 support line or submit a Support Request via My Workspace ONE.
Best Regards,
The VMware Workspace ONE Team
0 Comments