ReplTip – Technique to exceed column limits for Merge Replication

Chris Skorlinski
Microsoft SQL Server Escalation Services

While working with a customer on a Merge Replication project, we encounter a potential road block. They had a published table which exceeded the number of columns allowed for Merge Replication.

From this article https://technet.microsoft.com/en-us/library/ms151749(v=sql.105).aspx:

If your application does not require column-level tracking, it is recommended that you use row-level tracking (the default) because it typically results in better synchronization performance. If row tracking is used, the base table can include a maximum of 1,024 columns, but columns must be filtered from the article so that a maximum of 246 columns is published. If column tracking is used, the base table can include a maximum of 246 columns.

We explored alternative to Merge Replication including Peer-2-Peer and Change Data Capture, however business requirements for disconnected subscribers with subsets of data drifted towards Merge Replication. I brainstormed with my colleague Taiyeb Zakir, then we reached out to our product team. Together we came up with 4 different solution. This document covers one solution.

To work around Merge Replication max column restriction, we created “shadow” tables and triggers to move the data between Publisher and Subscriber. Let’s look at how this works.

To keep simple, we’ll have table called People with 3 columns: ID, First Name, and Last Name. In reality, there were about 1,500 columns. To move data to Subscriber using Merge Replication we’ll create 2 Shadow table, one with First Name, other Last Name, then publish these table. Application change occur on the main table with triggers moving data into these shadow tables then Merge transferring to Subscriber’s Shadow tables where again triggers will move data back into the single table.

I used uniqueidentifier as data type for my People ID column. This simplifies my example as Merge Replication requires a ROWGUIDCOL
column in the table. If not already part of the schema, Merge Replication would add the column when creating the Publication. Adding it before I publish, as a PrimaryKey, or Non-Clustered Index simplifies trigger rewrites. Here are how the tables look.

— =============================================

— Author:        Chris Skorlinski

— =============================================

CREATE
TABLE [dbo].[People](

    [ID] [uniqueidentifier] ROWGUIDCOL
NOT
NULL,

    [FirstName] [nchar](10)
NULL,

    [LastName] [nchar](10)
NULL,

CONSTRAINT [PK_People] PRIMARY
KEY
CLUSTERED

(

    [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

CREATE
TABLE [dbo].[People_Shadow_A](

    [ID] [uniqueidentifier] ROWGUIDCOL
NOT
NULL,

    [FirstName] [nchar](10)
NULL,

CONSTRAINT [PK_People_Shadow_A] PRIMARY
KEY
CLUSTERED

(

    [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

CREATE
TABLE [dbo].[People_Shadow_B](

    [ID] [uniqueidentifier] ROWGUIDCOL
NOT
NULL,

    [LastName] [nchar](10)
NULL,

CONSTRAINT [PK_People_Shadow_B] PRIMARY
KEY
CLUSTERED

(

    [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

We’ll need an INSERT, UPDATE, and DELETE triggers to glue the People table to the “Shadow” tables. Triggers on People table split the data into 2 paths. Triggers on the Shadow tables combine it back into the single table.

Let’s follow the update logic first. Based on this solution, changes to First Name column need to UPDATE People_Shadow_A, while changes to Last Name go to People_Shadow_B. For this we’ll need an UPDATE TRIGGER on the People table. During Merge Synchronization those updates are moved to Subscribers Shadow table where triggers move the UPDATE into the combined People table. This will require two UPDATE TRIGGERs, one on each of the Shadow tables. I’m keeping the logic in the triggers simple, no error handling, that can be added later.

Notice the People table has “NOT
FOR
REPLICATION” enabled. This prevents Merge Replication from executing this trigger. Merge should only execute the Shadow table triggers. The triggers we created on the People trigger will only be executed when application\user INS\UPD\DEL occur. For this demo we need to prevent the triggers from looping back around, for this we’ll add logic to each SHADOW trigger basically says If you are being updated from Merge Agent, go ahead and modify the People table. If you are being called from People Trigger, don’t go back and modify the People Table again. This code check stops People table “RECURSIVE_TRIGGERS“.

–Code on Shadow tables to stop “RECURSIVE_TRIGGERS“.

    IF ((SELECT
TRIGGER_NESTLEVEL())
> 1)
RETURN

— Trigger on People to split the UPDATE

— =====================================

CREATE
TRIGGER dbo.tr_UPD_People

ON dbo.People

AFTER
UPDATE

NOT
FOR
REPLICATION

AS

BEGIN

    SET
NOCOUNT
ON;

    IF
UPDATE(FirstName)

    BEGIN

        UPDATE dbo.People_Shadow_A

        SET FirstName =
(Select FirstName FROM inserted)

        WHERE dbo.People_Shadow_A.ID =
(Select ID FROM inserted)

    END

    IF
UPDATE(LastName)

    BEGIN

        UPDATE dbo.People_Shadow_B

        SET LastName =
(Select LastName FROM inserted)

        WHERE dbo.People_Shadow_B.ID =
(Select ID FROM inserted)

    END

END

GO

— Trigger on People_Shadow_A to update People FirstName

— =======================================================

CREATE
TRIGGER dbo.tr_UPD_People_A

ON dbo.People_Shadow_A

AFTER
UPDATE

AS

BEGIN

    SET
NOCOUNT
ON;

    IF ((SELECT
TRIGGER_NESTLEVEL())
> 1)
RETURN

    UPDATE dbo.People

    SET FirstName =
(Select FirstName FROM inserted)

    WHERE dbo.People.ID =
(Select ID FROM inserted)

END

GO

— Trigger on People_Shadow_B to update People LastName

— =====================================================

CREATE
TRIGGER dbo.tr_UPD_People_B

ON dbo.People_Shadow_B

AFTER
UPDATE

AS

BEGIN

    SET
NOCOUNT
ON;

    IF ((SELECT
TRIGGER_NESTLEVEL())
> 1)
RETURN

    UPDATE dbo.People

    SET LastName =
(Select LastName FROM inserted)

    WHERE dbo.People.ID =
(Select ID FROM inserted)

END

GO

Before we get too far along, remember we just have one database at this point. We’re creating all data handing triggers in our Publisher. Later when we setup Merge Replication, we’ll backup and restore the Publisher to the Subscriber, setting up Subscriber using “No Initialize”. This automatically preload Subscriber with all required schema and triggers. In full on production environment you may want to apply these as scripts when deploying Snapshot as part of Merge Subscriber initialization.

Let’s talk about INSERT, this is a bit more complicated. When INSERT happen on Publisher, People INSERT TRIGGER inserts two rows, one into Shadow_A and one in Shadow_B. Merge Replicates these to the Subscriber where we need to combine into 1 final INSERT.

Option 1, specify the Merge article processing order as documented HERE. Write the Shadow_A trigger to always INSERT, then Shadow_B to always UPDATE. Option 2, add error handling on the INSERT, if EXISTS, then UPDATE. Whichever approach you select, you’ll need the same for DELETE. I’m going for “article order” option, easier for quick demo.

— Trigger on People to split the INSERT

— =====================================

CREATE
TRIGGER [dbo].[tr_INS_People]

ON [dbo].[People]

AFTER
INSERT

NOT
FOR
REPLICATION

AS

BEGIN

    SET
NOCOUNT
ON;

    IF ((SELECT
TRIGGER_NESTLEVEL())
> 1)
RETURN

    INSERT
INTO dbo.People_Shadow_A(ID, FirstName)
(Select ID, FirstName FROM inserted)

    INSERT
INTO dbo.People_Shadow_B(ID, LastName)
(Select ID, LastName FROM inserted)

END

GO

— Trigger on People_Shadow_A to INSERT People.(ID, FirstName)

— ===========================================================

CREATE
TRIGGER dbo.tr_INS_People_A

ON dbo.People_Shadow_A

AFTER
INSERT

AS

BEGIN

    SET
NOCOUNT
ON;

    IF ((SELECT
TRIGGER_NESTLEVEL())
> 1)
RETURN

    INSERT
INTO dbo.People(ID, FirstName)
(Select ID, FirstName FROM inserted)

END

GO

— Trigger on People_Shadow_B to UPDATE (insert) People.LastName

— ===============================================================

CREATE
TRIGGER dbo.tr_INS_People_B

ON dbo.People_Shadow_B

AFTER
INSERT

AS

BEGIN

    SET
NOCOUNT
ON;

    IF ((SELECT
TRIGGER_NESTLEVEL())
> 1)
RETURN

    — Row inserted from tr_INS_People_A, just update

    UPDATE dbo.People

    SET LastName =
(Select LastName FROM inserted)

    WHERE dbo.People.ID =
(Select ID FROM inserted)

END

GO

During a DELETE on Publisher, People table DELETE trigger will trigger delete in both People_Shadow_A and People_Shadow_B. Merge synchronization sends down DELETE where DELETE trigger on People_Shadow_A pushes final DELETE to the combined People table where rows is removed. No DELETE trigger needed for Shadow_B as only one DELETE is required. You can’t double-deleted!

— Trigger on People to split the DELETE

— =====================================

CREATE
TRIGGER [dbo].[tr_DEL_People]

ON [dbo].[People]

AFTER
DELETE

NOT
FOR
REPLICATION

AS

BEGIN

    SET
NOCOUNT
ON;

    DELETE
FROM dbo.People_Shadow_A WHERE ID IN
(Select ID FROM deleted)

    DELETE
FROM dbo.People_Shadow_B WHERE ID IN
(Select ID FROM deleted)

END

GO

— Trigger on People_Shadow_A to DELETE People

— ===========================================

CREATE
TRIGGER dbo.tr_DEL_People_A

ON dbo.People_Shadow_A

AFTER
DELETE

AS

BEGIN

    SET
NOCOUNT
ON;

    IF ((SELECT
TRIGGER_NESTLEVEL())
> 1)
RETURN

    DELETE
FROM dbo.People WHERE ID IN
(Select ID FROM deleted)

END

GO

Once in the Shadow tables and trigger logic is written, create the Publication, then Backup\Restore to Subscriber using the “Keep Replication” option. Adding Subscriber via SQL Server Management Studio, clear the “initialize” option. This Merge “no sync”, like Transactional Replication “Replication Support Only” feature, will pushed only required Replication metadata to the subscriber leaving the user create tables, including our preloaded Shadow and People tables untouched.

Create the Publication for the Shadow Table.

Backup and Restore the Published tables to Subscriber. When you RESTORE on Subscriber, select “Keep_Replication” option.

Once restored, create the Subscription using SQL Management Studio. Clear the “Initialize” check box. We already have data from the Backup\Restore, we do not need Merge Agent to push down user tables. We only want Merge Replication to push down required Merge objects sitting in the Snapshot folder.

Start the Snapshot Agent and generated the Merge Replication management objects, then run the Merge Agent to push those down to the Subscriber.

Publication created and Subscriber has been initialized, time to insert some data.

— Show row INSERTED into People

SELECT
*
FROM [Peoples].[dbo].[People]

SELECT
*
FROM [Peoples].[dbo].[People_Shadow_A]

SELECT
*
FROM [Peoples].[dbo].[People_Shadow_B]

As expected, 1 rows on the Publisher, split into the 2 Shadow tables results in 2 Shadow Table INSERTS being sent to the Subscriber. Triggers on Subscriber move data into the combined People table.

— Show Trigger Insert\Update rows on Subscriber.

SELECT
*
FROM [Peoples_Subscriber].[dbo].[People]

SELECT
*
FROM [Peoples_Subscriber].[dbo].[People_Shadow_A]

SELECT
*
FROM [Peoples_Subscriber].[dbo].[People_Shadow_B]

ID FirstName LastName

———————————— ———- ———-

85341E22-22AB-403B-8CC0-FDFE42D3C970 Chris Skorlinsi

ID FirstName

———————————— ———-

85341E22-22AB-403B-8CC0-FDFE42D3C970 Chris

ID LastName

———————————— ———-

85341E22-22AB-403B-8CC0-FDFE42D3C970 Skorlinsi

OOPS, I just noticed I spelled my last name wrong, I forgot the “k”. I’ll update the Publisher and push down change through Shadow_B table at the next Merge synchronization.

ID FirstName LastName

———————————— ———- ———-

85341E22-22AB-403B-8CC0-FDFE42D3C970 Chris Skorlinski

ID LastName

———————————— ———-

85341E22-22AB-403B-8CC0-FDFE42D3C970 Skorlinski

I know you’re wondering, he’s made all the data changes on the Publisher, what about data changes that originate on the Subscriber? Ahh, we’ve got that covered automatically when we create the Subscriber using a Backup\Restore from the Publisher after all triggers were created. This automatically restore the required triggers on the Subscriber. Same logic, just going in the other direction. We could have script the triggers then applied after the Subscribe created using Snapshot Push, that also works.

One last step, we’ll DELETE “Chris”, we’re expecting 2 DELETE from Merge, as you member Merge only knows of Shadow tables. It will synchronize the 2 Shadow table DELETEs, one of which has a DELETE Trigger to clean up the People table.

Your environment may unique requirement were this approach will not work. You may even come up with a better solution. Remember, we had 3 others we explored including breaking up the large table logically into smaller ones keeping under the Merge column constrains or passing the all the column data to subscriber as single combined NTEXT column with XML tags then parsing at Subscriber. Select the one that best fits your needs.