ConfigMgr 2007 CI replication issue

The following is a guidline to resolving CI replication issues – If you are unsure then don’t change things without asking for advice!
Summary of steps:

• Backup the site and the DB.
• Uninstall the SUP
• Delete data from CI_configurationitems and CI_sdmpackage tables so you have 0 rows in the first table and 12 schema rows in the CI_Sdmpackages table.
• Clean the objreplmgr.box of old files.
• Rename the objreplmgr.dll to objreplmgr.dll_old (stop and start services to do this).
• Wait for the files to replicate from the Central site (the .SHA file will disappear when this has finished).
• Rename the objreplmgr.dll and wait for the replicated files to be processed.

Detailed steps:

1. Backup the SCCM database! If anything goes wrong with the actions below then we can roll back to the latest backup.

2. Uninstall SUP on the primary site. This would trigger all the updates to get marked as expired in the SCCM DB. The following queries can be checked to see when that’s been done and to track the progress, however this may take some time:

SELECT COUNT (*) AS TotalCIs FROM CI_ConfigurationItems
SELECT COUNT (*) AS Expired FROM CI_ConfigurationItems WHERE IsExpired = 1
SELECT COUNT (*) AS TotalSDMs FROM CI_SDMPackages
SELECT COUNT (*) AS Deleted FROM CI_SDMPackages WHERE IsDeleted = 1

3. After all the CI items have been marked as isexpired then modify the following property in Site Control file, and change the value to 0 instead of 7 days. You will need to stop the smsexec service to do this:

PROPERTY Updates Cleanup Age 0

4. Restart SMS Executive Service. WSUS_SYNC_MANAGER will now start deleting the Expired updates, and log entries similar to below in WSyncMgr.log:

Deleting old expired updates… SMS_WSUS_SYNC_MANAGER 4/23/2010 4:18:52 PM 4104 (0x1008)
Deleted 100 expired updates SMS_WSUS_SYNC_MANAGER 4/23/2010 4:19:22 PM 4104 (0x1008)

Deleted 2995 expired updates total SMS_WSUS_SYNC_MANAGER 4/23/2010 4:28:48 PM 4104 (0x1008)

5. After all the Expired updates have been deleted, check that the vast majority of the CI_SDMPackages are marked as “isdeleted” using the select count (*) queries above. There are 12 rows at the top of this table which must never be deleted. These are usually marked as NULL in the source site column. To delete the “isdeleted” SDM packages run the following Stored Procedure to remove them:

exec sp_DeleteOldSDMPackageData 0

6. After these procedures have been run we should now have two clean tables. Perform a select * from CI_ConfigurationItems / CI_SDMPackages to check the contents and confirm that these tables are clean. There should now only be about 0 rows in the Ci_configurationitems table and 12 in the sdmpackages table.

** You may have to deal with constraints on the tables by disabling and enabling if you cant clean the tables out and get the error about constraints**
http://www.confio.com/logicalread/sql-server-foreign-keys-some-of-the-mystery-explained!-(part-2)/#.UsKiJ2ZFBEY

7. The next step is to clean out the existing files in the objmgr.box. Probably the best thing to do is to archive all the current data to an alternate location and clear out any files from the objmgr.box and the subfolders to make sure there is no old data lurking here.

8. Once we have cleaned the objmgr.box rename the objreplmgr.dll file in Program Files (x86)\Microsoft Configuration Manager\bin\i386 to objreplmgr.dll_old (you will have to stop and start the smsexec to do this).

This will prevent the processing of the files sent from the central site until the replication has completed. This is necessary so that we ensure that some of the files retry period does not go “bad” before the files have had time to arrive.

9. Reinstall the SUP role on the primary site. This should trigger the replication of the CI objects automatically. If this doesn’t replicate immediately then create a .sha file in the objmgr.box on the central site.

10. Wait for the replication process to stop transferring files from the central site. This can sometimes take days and sometimes hours depending on the number of CI objects and WAN link speed. Once you have confirmed that this has stopped then stop the smsexec process on the primary site and rename the objreplmgr.dll_old to objreplmgr.dll. Restart the smsexec service.

11. This processing of the files in the objmgr.box\INCOMING folder should now begin. Hopefully this should complete successfully without any further insertion errors.

12. Change the following property in Site Control file back , from 7 instead of 0 days. You will need to stop/start the smsexec service to do this:

PROPERTY

If whist replicating all the CI objects do not replicate the most likely they have an issue with transaction numbers being out of sync
http://blogs.technet.com/b/configurationmgr/archive/2012/04/17/a-look-at-transaction-based-replication-in-configuration-manager-2007.aspx

———————————————————————————-
Some queries which i used

select COUNT (*) as totalcis from CI_ConfigurationItems where IsExpired= 0

select COUNT (*) from CI_ConfigurationItems

select COUNT (*) as expired from CI_ConfigurationItems where IsExpired= 1

select * from CI_ConfigurationItems where IsExpired= 0

select COUNT (*) from CI_SDMPackages where IsDeleted= 1

exec sp_DeleteOldSDMPackageData 0

select * from CI_ConfigurationItems

select * from CI_SDMPackages

delete from CI_SDMPackages where SDMPackage_ID > 17906

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_NAME= CI_SDMPackageRelations_CI_SDMPackages_FK

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME= CI_SDMPackageRelations
select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_NAME= ‘CI_SDMPackageRelations_CI_SDMPackages_FK’

ALTER TABLE CI_SDMPackageRelations NOCHECK CONSTRAINT ALL
ALTER TABLE CI_SDMPackageRelations WITH CHECK CHECK CONSTRAINT ALL
GO

ALTER TABLE CI_SDMPackages WITH CHECK CHECK CONSTRAINT ALL
———————————-
References
SMS: Replication Manager Cannot Process Transactions If Transaction ID Is Not Synchronized

Understanding Site to Site Communication in SMS/SCCM

Troubleshooting “Failed to Insert Object” error message

http://blogs.technet.com/cfs-filesystemfile.ashx/__key/CommunityServer-Components-PostAttachments/00-03-34-44-65/ConfigMgr2007Site2SiteReplication.doc

Object Replication Manager (objreplmgr) on child site failed to insert .CID and .SDM files

Posted 27th June by Vincent Goh

I was presented with an issue whereby software updates (configuration items) were not replicating from the central parent site down to a particular child primary site. The symptoms of this being that if a certain update list, update package, or update deployment contained one of the missing updates (CI_items) then the effected object(s) would not appear in the console, and therefore I cannot deploy the effected update(s) at the child site

In this scenario I found a backlog of .CID files in the inbox folder below, each .CID file representing an update which couldn’t be processed.

\inboxes\objmgr.box\INCOMING\retry

The retry is attempted 100 times and then the .CID is place in the ‘bad’ folder

\inboxes\objmgr.box\INCOMING\retry\BAD
When SMS_OBJECT_REPLICATION_MANAGER attempts to process the effected CI’s the following is logged to the objreplmgr.log which indicates the failure:
Processing replication file d:\Microsoft Configuration Manager\inboxes\objmgr.box\INCOMING\RetryGL_42586.CID in retry.

Failed to insert Object 6298a02f-0a6a-4f34-b832-08059b682b63 from replication file d:\Microsoft Configuration Manager\inboxes\objmgr.box\INCOMING\RetryGL_48796.CID.

On to the resolution, I found that running the following 6 SQL queries on the effected child sites resolved the issue:

Delete from CI_ConfigurationItems Where CIType_ID in (1, 6, 8);
Update CI_SDMPackages set IsDeleted = 1 where SourceSite = ‘ZZZ’;
Delete from CI_ComplianceHistory where isdetected = 1
Delete from CI_Compliancehistory where isdetected = 0
Delete from CI_SDMPackages where is deleted =1 and sourecesite = ‘ZZZ’
Exec sp_DeleteOldSDMPackageData 0;

* note: replace ZZZ with the site code of your central site (or the active SUP which is the furthest upstream’)

Running the above queries will purge Software Updates data from the effected child site.

You should then wait about 30 minutes and then restart the following services on the effected child site:

SMS_EXECUTIVE
SMS_COMPONENT_MANAGER

The final step is to initiate a full site replication, this can be done using the heirarchy maintenance tool (syncchild option) or by place a file called .SHA * in the objmgr.box on the parent site.

* .SHA – replace with the 3 digit site code of the desired child site you wish to replicate.

Allow up to 24 hours for the site replication to complete, when it finishes you should have a fully matching compliment of updates on the central site and child primary site.

You can monitor the objreplmgr.log for successful insertion of .CID and .SDM items, and you should also find that the folder \inboxes\objmgr.box\INCOMING\retry does not contain any items for retry.

Posted 27th June by Vincent Goh

References
http://eskonr.com/2012/12/sccm-configmgr-failed-to-insert-sms-package-because-sdm-type-content-is-not-present-in-the-ci_contents-table-will-try-later/

http://blogs.msdn.com/b/steverac/archive/2011/04/10/software-updates-internals-mms-2011-session-part-i.aspx

http://blogs.msdn.com/b/steverac/archive/2011/04/16/software-updates-internals-mms-2011-session-part-ii.aspx

DRS Troubleshooting

Articles which are useful for learning and troubleshooting DRS issues

How to Check Inbox Backlog in SCCM ConfigMgr 2012

SCCM ConfigMgr 2012 Site to Site replication and SQL Replication Guide

SQL Replication Troubleshooting Guide for ConfigMgr SCCM 2012

Tips for troubleshooting SC 2012 Configuration Manager Data Replication Service (DRS)

DRS Initialization In Configuration Manager 2012

Custom SQL Replication reports for System Center 2012 Configuration Manager

DRS Unleashed 

ConfigMgr 2012: DRS and SQL service broker certificate issues