Make SCCM Database Page Compression Check Enabled Part of Pre-req check
Just upgraded from 1910 to 2002 and the upgrade failed and had to open a support case. Bottom line our SQL DBA made unauthorized changes to SCCM database and enabled PAGE compression on 370 tables in the SCCM database.
Please add to the pre-req check the running of this SQL query if any tables are set to PAGE data compression fail the pre-req.
SQL to Check: SELECT t.name AS tablename,
i.name AS indexname,
FROM sys.tables AS t
INNER JOIN sys.indexes AS i
ON t.objectid = i.objectid
INNER JOIN sys.partitions AS p
ON i.objectid = p.objectid
AND i.indexid = p.indexid
Where p.datacompressiondesc = 'PAGE'
Report recommendation to turn off PAGE compression using this SQL:
EXEC spMSforeachtable 'SET QUOTEDIDENTIFIER on;alter TABLE ? REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE)'
That will save you from failed ConfigMgr Upgrade nightmare.