none
MSmerge_Index_xxxxx missing on subscriber RRS feed

  • Question

  • I have 2 sites that have been replicating successfully for many years using a Merge Replication.  We needed to add several new tables to the replication.  I have added the tables in the Articles of the publication and recreated the snapshot for the publication.  On the subscriber side, I stopped the synchronization and re-started synchronization to read the new snapshot.  Everything appears to be replicating fine but what I have found, only on the subscriber side, the MSmerge_index_xxxxx index is not created on any of the newly added tables.  The publisher database looks fine.  All table have the index added correctly but the subscriber does not have them created.  Is this going to be an issue with my database replication?
    Wednesday, August 7, 2019 2:20 PM

Answers

  • Hi jeff0411,

     

    >>only on the subscriber side, the MSmerge_index_xxxxx index is not created on any of the newly added tables.  The publisher database looks fine.  All table have the index added correctly but the subscriber does not have them created.

     

    Existing indexes in article before generating snapshot would be replicated to subscriber  while applying  snapshot on the subscriber. (We can turn off this in article properties):

     

     

     

     

    After that, all operations on the index on the publication will not be replicated to subscriber. For more details, please refer to https://www.sqlshack.com/sql-server-replication-merge-what-gets-replicated-and-what-doesnt/

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by jeff0411 Friday, August 9, 2019 1:56 PM
    Thursday, August 8, 2019 8:24 AM

All replies

  • This could be normal. Is this article/table part of a row filtering?
    Wednesday, August 7, 2019 4:23 PM
    Moderator
  • no row filtering on the table
    Wednesday, August 7, 2019 4:26 PM
  • can you script out the table and post it here? What version of sql is this?
    Wednesday, August 7, 2019 4:31 PM
    Moderator
  • SQL Server 2014

    USE [TESTDB]
    GO
    /****** Object:  Table [dbo].[DBW_EC]    Script Date: 8/7/2019 12:36:32 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[DBW_EC](
     [CREATED_BY] [varchar](128) NULL,
     [CREATION_DATE] [datetime] NULL,
     [DEPARTMENT1_APPROVAL_DATE] [datetime] NULL,
     [DEPARTMENT1_APPROVED_BY] [varchar](128) NULL,
     [DEPARTMENT2_APPROVAL_DATE] [datetime] NULL,
     [DEPARTMENT2_APPROVED_BY] [varchar](128) NULL,
     [DEPARTMENT3_APPROVAL_DATE] [datetime] NULL,
     [DEPARTMENT3_APPROVED_BY] [varchar](128) NULL,
     [DEPARTMENT4_APPROVAL_DATE] [datetime] NULL,
     [DEPARTMENT4_APPROVED_BY] [varchar](128) NULL,
     [DESCRIPTION] [varchar](255) NULL,
     [EC_UNIQUE_ID] [int] IDENTITY(100000000,1) NOT FOR REPLICATION NOT NULL,
     [FILE_DIRECTORY] [varchar](255) NULL,
     [FILE_NAME] [varchar](128) NULL,
     [FLOOR_DISPOSITION] [varchar](150) NULL,
     [ID] [varchar](50) NULL,
     [IMPLEMENTATION_DATE] [datetime] NULL,
     [INVENTORY_DISPOSITION] [varchar](50) NULL,
     [PRIORITY_STATUS] [int] NULL,
     [PRIORITY_STATUS_2] [varchar](50) NULL,
     [REASON_FOR_CHANGE] [varchar](255) NULL,
     [REASON_REJECTED] [varchar](255) NULL,
     [WF_SUBMITTED_BY] [varchar](128) NULL,
     [WF_SUBMITTED_DATE] [datetime] NULL,
     [WF_REJECTED_BY] [varchar](128) NULL,
     [WF_REJECTION_DATE] [datetime] NULL,
     [ACCEPTED_BY] [varchar](128) NULL,
     [ACCEPTED_DATE] [datetime] NULL,
     [ADDED_TO_ECN_BY] [varchar](128) NULL,
     [ADDED_TO_ECN_DATE] [datetime] NULL,
     [CONFIRMED_COMPLETED_BY] [varchar](128) NULL,
     [CONFIRMED_COMPLETED_DATE] [datetime] NULL,
     [REQUIRE_REVISION] [bit] NULL,
     [EC_STATE] [varchar](30) NULL,
     [EC_TYPE] [varchar](10) NOT NULL,
     [USER_ASSIGNED] [varchar](128) NULL,
     [NOTES] [varchar](255) NULL,
     [INCORPORATING_ECN] [varchar](50) NULL,
     [FAST_TRACKED] [bit] NULL,
     [DOCUMENT_UNIQUE_ID] [int] NULL,
     [IN_WORKFLOW] [bit] NULL,
     [TERMINATED_BY] [varchar](128) NULL,
     [TERMINATION_DATE] [datetime] NULL,
     [WF_ON_HOLD] [bit] NULL,
     [WF_ON_HOLD_NOTES] [varchar](255) NULL,
     [WF_ON_HOLD_USER] [varchar](128) NULL,
     [JOB_NUMBER] [varchar](128) NULL,
     [PROJECT_MANAGER] [varchar](128) NULL,
     [FORM_NAME] [varchar](128) NULL,
     [EC_TYPE_ALIAS] [varchar](128) NULL,
     [ECR_ID] [varchar](50) NULL,
     [REOPENED_BY] [varchar](128) NULL,
     [REOPENED_DATE] [datetime] NULL,
     [WF_ASSIGNED_USER] [varchar](128) NULL,
     [DESIGN_NAME] [varchar](150) NULL,
     [REQUIRE_MAJOR_REVISION] [bit] NULL,
     [WF_REJECTION_NOTES] [varchar](500) NULL,
     [WF_APPROVAL_NOTES] [varchar](500) NULL,
     [ASSIGNED_PSE] [varchar](75) NULL,
     [ASSIGNED_PMM] [varchar](75) NULL,
     [BACKUP_PMM] [varchar](75) NULL,
     [BACKUP_PSE] [varchar](75) NULL,
     [CURRENT_CHECKER] [varchar](75) NULL,
     [CURRENT_BACKUP] [varchar](75) NULL,
     [WF_PROCESS] [varchar](128) NULL,
     [WF_STATE_INDEX] [int] NULL,
     [WF_STATE_NAME] [varchar](128) NULL,
     [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [MSmerge_df_rowguid_543C2DE27EA84C8C9ECD1C491B4A9EF5]  DEFAULT (newsequentialid()),
     CONSTRAINT [PK__DBW_EC__700E29E35D8E259E] PRIMARY KEY CLUSTERED
    (
     [EC_UNIQUE_ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO

    Wednesday, August 7, 2019 4:38 PM
  • Hi jeff0411,

     

    >>only on the subscriber side, the MSmerge_index_xxxxx index is not created on any of the newly added tables.  The publisher database looks fine.  All table have the index added correctly but the subscriber does not have them created.

     

    Existing indexes in article before generating snapshot would be replicated to subscriber  while applying  snapshot on the subscriber. (We can turn off this in article properties):

     

     

     

     

    After that, all operations on the index on the publication will not be replicated to subscriber. For more details, please refer to https://www.sqlshack.com/sql-server-replication-merge-what-gets-replicated-and-what-doesnt/

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by jeff0411 Friday, August 9, 2019 1:56 PM
    Thursday, August 8, 2019 8:24 AM
  • So it appears that the MSmerge_index_xxxx is being created on the publisher database at the time the snapshot is created.  There is a .dri file in the snapshot folder location that contains the constraint scripts and the MSmerge_index_xxxx is in that file.  I assume I am not seeing the index being created on the subscriber database because I am unchecking the Initialize box when creating the subscription.  Based on that, I would also then assume that the MSmerge_index_xxxx should be manually created on the subscriber database.  
    Thursday, August 8, 2019 6:15 PM