Replication errors in System Center 2012 R2 Configuration Manager

Issue

Several customers reported that child primary sites were entering maintenance mode after upgrading the central administration site from System Center 2012 Configuration Manager Service Pack 1 (SP1) with CU2 or CU3 to System Center 2012 R2 Configuration Manager. Entries similar to the following were recorded in the RcmCtrl.log file at child primary sites:

ERROR: Exception message: [A .NET Framework error occurred during execution of user-defined routine or aggregate "spModifyGlobalTable": ~~System.Data.SqlClient.SqlException: ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'MessageTime' cannot be added to non-empty table 'PullDPResponse' because it does not satisfy these conditions.~~System.Data.SqlClient.SqlException: ~~   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)~~   at System.Data.SqlClient.SqlDataReaderSmi.InternalNextResult(Boolean ignoreNonFatalMessages)~~   at System.Data.SqlClient.SqlDataReaderSmi.NextResult()~~   at System.Data.SqlClient.SqlCommand.RunExecuteReaderSmi(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)~~   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)~~   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)~~   at System.Data.SqlClient.SqlCommand.ExecuteScalar()~~   at Microsoft.ConfigurationManager.DataReplicationService.DatabaseOperation.ExecuteScalar(String commandText, List`1 commandParameters)~~   at Microsoft.ConfigurationManager.DataReplicationService.GlobalSchemaChanges.ApplyChangesToTable(DrsLogging drsLogging, String tableName, String tableOwner, List`1 columns, DatabaseOperation databaseOperation, String procedureName)~~   at Microsoft.ConfigurationManager.DataReplicationService.GlobalSchemaChanges.CreateOrUpdateGlobalTable(DrsLogging drsLogging, String tableName, List`1 columns, List`1 triggers, Int32 replicationId, DatabaseOperation databaseOperation, String procedureName)~~   at Microsoft.ConfigurationManager.DataRe...]                SMS_REPLICATION_CONFIGURATION_MONITOR

This error occurs because the PullDPResponse table, which was added with Configuration Manager SP1 with CU2 or CU3, does not contain the MessageTime column that was later introduced with System Center 2012 R2 Configuration Manager. This column did not allow for NULL entries, and therefore, replication would fail if there was data in the table at the Configuration Manager 2012 SP1 child site prior to the upgrade. When an update was started on the child site, the Prerequisite Checker failed because replication between the central administration site and child primary site was not active.

Resolution

This issue has been resolved for all new System Center 2012 R2 Configuration Manager upgrades as of November 14, 2013. New installations or upgrades will download updated setup prerequisite files.

Customers that are working with site servers not connected directly to the Internet should use the Configuration Manager Setup Downloader. More information is available in the Setup Download section of Install Sites and Create a Hierarchy for Configuration Manager topic on TechNet.

Existing hierarchies that have already upgraded the central administration site but cannot upgrade child primary site(s) can be repaired by running the following SQL script against the site database at the child primary site(s):

IF NOT EXISTS(select * from sys.columns where [name] = N''MessageTime'' and [object_id] = OBJECT_ID(N''PullDPResponse''))

    BEGIN

        ALTER TABLE PullDPResponse ADD MessageTime datetime NOT NULL

        CONSTRAINT PullDPResponse_MessageTime DEFAULT (GetUTCDate())

    END

IF NOT EXISTS (SELECT * FROM sys.default_constraints where name = ''PullDPResponse_MessageTime'')

    BEGIN

        ALTER TABLE PullDPResponse ADD CONSTRAINT PullDPResponse_MessageTime DEFAULT (GetUTCDate()) FOR MessageTime

    END