#SCCM Expert #Private Cloud Consultant#SQL Expert#Corporate Trainer #Automation Consultant

Replication link between the CAS and the primary is broken with SMS Replication Monitor reporting missing tables”The publisher reported 2 tables missing”

Hi Guys, lot of us have faced this issue where replication between the CAS and Primary site breaks. It can happen due to various reasons and today we will discuss one of them. So we will start with the with issue description.

Issue Definition

The replication link between the CAS and the primary is broken.

Data Analysis

  1. Check the console, find DRS is failed on both global data and site data.
  2. Reinitialize the global data replication, find a group of global data keeps failing.Post-snapshot stored procedures for group Asset Intelligence Knowledge Base finished. SMS_REPLICATION_CONFIGURATION_MONITOR 8/4/2016 5:33:58 PM 5088 (0x13E0)STATMSG: ID=7805 SEV=I LEV=M SOURCE=”SMS Server” COMP=”SMS_REPLICATION_CONFIGURATION_MONITOR” SYS=VSGRHOWPSCM01.PRU.intranet.asia SITE=SGP PID=6572 TID=5088 GMTDATE=Fri Apr 08 09:33:58.277 2016 ISTR0=”Asset Intelligence Knowledge Base” ISTR1=”CAS” ISTR2=”” ISTR3=”” ISTR4=”” ISTR5=”” ISTR6=”” ISTR7=”” ISTR8=”” ISTR9=”” NUMATTRS=0 SMS_REPLICATION_CONFIGURATION_MONITOR 8/4/2016 5:33:58 PM 5088 (0x13E0)The publisher reported 2 tables missing. The publisher will send messages when tables are available. SMS_REPLICATION_CONFIGURATION_MONITOR 8/4/2016 5:33:58 PM 5088 (0x13E0)Error: Init package either did not have bcp for 2 tables or failed to apply bcp for 2 tables of replication group Asset Intelligence Knowledge Base. SMS_REPLICATION_CONFIGURATION_MONITOR 8/4/2016 5:33:58 PM 5088 (0x13E0)

    STATMSG: ID=7806 SEV=E LEV=M SOURCE=”SMS Server” COMP=”SMS_REPLICATION_CONFIGURATION_MONITOR” SYS=VSGRHOWPSCM01.PRU.intranet.asia SITE=SGP PID=6572 TID=5088 GMTDATE=Fri Apr 08 09:33:58.305 2016 ISTR0=”Asset Intelligence Knowledge Base” ISTR1=”CAS” ISTR2=”” ISTR3=”” ISTR4=”” ISTR5=”” ISTR6=”” ISTR7=”” ISTR8=”” ISTR9=”” NUMATTRS=0 SMS_REPLICATION_CONFIGURATION_MONITOR 8/4/2016 5:33:58 PM 5088 (0x13E0)

    Error: Replication group “Asset Intelligence Knowledge Base” has failed to initialize for subscribing site SGP, setting link state to Error. SMS_REPLICATION_CONFIGURATION_MONITOR 8/4/2016 5:33:58 PM 5088 (0x13E0)

    Processing replication pattern site. SMS_REPLICATION_CONFIGURATION_MONITOR 8/4/2016 5:33:58 PM 5088 (0x13E0)

  3. Checking the SQL error log, we can see that there are 2 tables missing in the CAS database.
  4. We can recreate the 2 tables in the CAS database using the command line below:
    /****** Object: Table [dbo].[LU_CPU] Script Date: 04/08/2016 18:22:26 ******/
    SET ANSI_NULLS ON
    GO
     SET QUOTED_IDENTIFIER ON
    GO
     CREATE TABLE [dbo].[LU_CPU](
    [CPUHash] [nvarchar](64) NOT NULL,
    [Manufacturer] [nvarchar](255) NULL,
    [BrandID] [int] NULL,
    [PCache] [int] NULL,
    [NormSpeed] [int] NULL,
    [Mobile] [bit] NULL,
    [Name] [nvarchar](255) NULL,
    [CPU_Birth] [datetime] NULL,
    [LastUpdated] [datetime] NOT NULL,
    [IsDeleted] [bit] NOT NULL,
    [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [SourceSite] [nvarchar](3) NULL,
    [rowversion] [timestamp] NOT NULL,
    CONSTRAINT [LU_CPU_PK] PRIMARY KEY CLUSTERED
    (
    [IsDeleted] ASC,
    [CPUHash] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
    ) ON [PRIMARY]
     GO
     SET ANSI_NULLS ON
    GO
     SET QUOTED_IDENTIFIER ON
    GO
     CREATE TABLE [dbo].[LU_CPU_Local](
    [CPUHash] [nvarchar](64) NOT NULL,
    [Manufacturer] [nvarchar](255) NULL,
    [BrandID] [int] NULL,
    [PCache] [int] NULL,
    [NormSpeed] [int] NULL,
    [Mobile] [bit] NULL,
    [Name] [nvarchar](255) NULL,
    [CPU_Birth] [datetime] NULL,
    [LastUpdated] [datetime] NOT NULL,
    [IsDeleted] [bit] NOT NULL,
    [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [SourceSite] [nvarchar](3) NULL,
    CONSTRAINT [LU_CPU_Local_PK] PRIMARY KEY CLUSTERED
    (
    [IsDeleted] ASC,
    [CPUHash] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
    ) ON [PRIMARY]
     GO
  5.  After that, we need reinitialize the data replication again.
  6. All data group will replicate well and the link will become green.

Hope this helps 🙂

Advertisements

Tagged as: , , , , , ,

Categorised in: SCCM, Server 2016

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Translate

%d bloggers like this: