Troubleshooting rollup replication errors

Don't directly modify rollup database data

Ivanti implements SQL Server replication as a one way transactional replication, from the publisher to the subscriber. If you modify “replicated” data on the subscriber, those changes will not be replicated to the publisher. Furthermore, the publisher will not be aware of those changes. If you want to roll back those changes, you will have to use SQL Server’s Replication Monitor and invalidate the snapshot from that publisher and force a new snapshot.

ProductSnapshot replication error after upgrading Endpoint Manager versions

If you see this error message:

The process could not bulk copy into table '"dbo"."PRODUCTSnapshot"'

The problem is that the ReplProductV view in the upgraded core is out of sync with the older ProductSnapshot table in the rollup database.

To fix this error, the view needs to match the schema. Run sp_help on both the view and the table and make sure the "Nullable" columns match. More than likely, it will be the Version and ProductGUID columns that are out of sync. You can either modify the table in your rollup database (but you would have to alter the views in each non-upgraded core as well):

alter table productsnapshot alter column version nvarchar9255) not null

alter table productsnapshot alter column productguid nvarchar(255) not null

Or you can modify the view in your upgraded core. In order to modify the view, you will have to stop replicating that view within the properties dialog of the local publication.

Field size too large replication error

If you see this error in your replication monitor, the problem is that the view on the core and the table on the rollup have mismatched schemas. While it could be a mismatch in the lengths of the columns, normally this is a mismatch of the NOT NULL attribute of the column. All of the column definitions between Repl<tablename>V on the core and the table name of the rollup must match.

If the error lists a table that ends with Snapshot, that table is used as a temporary table on the rollup. The corresponding view on the core will not contain the word “Snapshot” (e.g. ProductSnapshot = ReplProductV).

Replication errors with the ISA and IDE tables

Problem: When using an older (or upgraded) version of the Ivanti database, you may run into replication errors with the ISA and IDE tables. The identity columns in the ISA and IDE tables were renamed in 2011.

Resolution: Once replication is running, you need to drop the subscriptions and articles for both the ISA and IDE tables. Alter the views and use the old identity column names in the SELECT clause. Create the UNIQUE CLUSTERED indexes for the views and re-add the articles and article filters. Refresh the subscription. Finally, you can manually click on start within the Snapshot Agent Status widow, or wait for the snapshot agent to detect the changes. Consult the Replication Monitor to see that the data is successfully replicated.

Use the script below to fix the problem. Make sure you use the name of your subscriber and database (<your subscriber server name> and <your subscriber database name>) in the sp_dropsubscription procedures.

--Drop the subscription and article for the ISA table

sp_dropsubscription 'LDMS', 'ISACSO', '<your subscriber server name>', '<your subscriber database name>'

go

sp_droparticle @publication = 'LDMS', @article='ISACSO'

go

 

--Alter the view for the ISA table and use the old ISAPorts_Idn column in the select list.

alter VIEW [dbo].[ReplISAV] (Computer_Idn, ISA_IDN, DeviceNum, Description, Designation, Location, Manufacturer, Type, COREGUID)

WITH SCHEMABINDING AS

SELECT isnull((b.Computer_Idn + 2097152), b.Computer_Idn) Computer_Idn, isnull(cast(b.ISAPorts_IDN as int), cast(0 as int)) ISAPorts_IDN, isnull(b.DeviceNum, 0) DeviceNum, b.Description, b.Designation, b.Location, b.Manufacturer, b.Type, isnull(cast(a.SYSTEMGUID as uniqueidentifier), cast(cast(0 as binary) as uniqueidentifier)) COREGUID

FROM dbo.METASYSTEMS a, dbo.ISA b

WHERE a.SYSTEM_IDN = 0

GO

 

--Recreate the clustered index

create unique clustered index PKReplISAV on ReplISAV (CoreGuid, Computer_Idn, ISA_Idn)

go

 

--Add the article and filter back to the publication

sp_addarticle @publication = 'LDMS', @article='ISACSO', @source_object='ReplISAV', @destination_table='ISA', @type='indexed view logbased', @sync_object='ReplISAV', @pre_creation_cmd='delete', @schema_option=0x00, @status=24, @ins_cmd='CALL sp_LDins_ISA', @del_cmd='CALL sp_LDdel_ISA', @upd_cmd='MCALL sp_LDupd_ISA', @fire_triggers_on_snapshot='FALSE'

go

sp_articlefilter @publication = 'LDMS', @article='ISACSO', @filter_name='CoreGuidISA', @filter_clause='CoreGuid = cast(''C192290A-8FAC-4ABF-8183-D6B911ACFE73'' as uniqueidentifier)'

go

 

--Drop the subscription and article for the IDE table

sp_dropsubscription 'LDMS', 'IDECSO', '<your subscriber server name>', '<your subscriber database name>'

go

sp_droparticle @publication = 'LDMS', @article='IDECSO'

go

 

--Alter the view for the IDE table and use the old IDEPorts_Idn column in the select list.

ALTER VIEW [dbo].[ReplIDEV] (Computer_Idn, IDE_IDN, DeviceNum, Description, Designation, Location, Manufacturer, Type, ProdName, COREGUID)

WITH SCHEMABINDING AS

SELECT isnull((b.Computer_Idn + 2097152), b.Computer_Idn) Computer_Idn, isnull(cast(b.IDEPorts_Idn as int), cast(0 as int)) IDEPorts_IDN, isnull(b.DeviceNum, 0) DeviceNum, b.Description, b.Designation, b.Location, b.Manufacturer, b.Type, b.ProdName, isnull(cast(a.SYSTEMGUID as uniqueidentifier), cast(cast(0 as binary) as uniqueidentifier)) COREGUID

FROM dbo.METASYSTEMS a, dbo.IDE b

WHERE a.SYSTEM_IDN = 0

GO

 

--Recreate the clustered index

create unique clustered index PKReplIDEV on ReplIDEV (CoreGuid, Computer_Idn, IDE_Idn)

go

 

--Add the article and filter back to the publication

sp_addarticle @publication = 'LDMS', @article='IDECSO', @source_object='ReplIDEV', @destination_table='IDE', @type='indexed view logbased', @sync_object='ReplIDEV', @pre_creation_cmd='delete', @schema_option=0x00, @status=24, @ins_cmd='CALL sp_LDins_IDE', @del_cmd='CALL sp_LDdel_IDE', @upd_cmd='MCALL sp_LDupd_IDE', @fire_triggers_on_snapshot='FALSE'

go

sp_articlefilter @publication = 'LDMS', @article='IDECSO', @filter_name='CoreGuidIDE', @filter_clause='CoreGuid = cast(''C192290A-8FAC-4ABF-8183-D6B911ACFE73'' as uniqueidentifier)'

go

 

--Refresh the subscription

sp_refreshsubscriptions @publication='LDMS'

go