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
- Check the console, find DRS is failed on both global data and site data.
- 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)
- Checking the SQL error log, we can see that there are 2 tables missing in the CAS database.
- 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
- After that, we need reinitialize the data replication again.
- All data group will replicate well and the link will become green.
Hope this helps 🙂
SukhdeepBlogs 
Leave a Reply