Add Partitioned Table in Replication

Add Partitioned Table in Replication.

Step1 : Create Partition Table.

--Create Partition Function
CREATE PARTITION FUNCTION FN_PARTITION_TBLPARTITION (INT) AS RANGE RIGHT FOR
VALUES(10)

--Create Parition Schema
CREATE PARTITION SCHEME SCH_PARTITION_TBLPARTITION AS PARTITION
FN_PARTITION_TBLPARTITION TO([PRIMARY], [SECONDARY])

CREATE TABLE Tblpartition
  (
     Seq         INT IDENTITY(1, 1) NOT NULL,
     Fisrt_name  VARCHAR(20),
     Last_name   VARCHAR(20),
     Split_value INT NOT NULL
  )
ON SCH_PARTITION_TBLPARTITION(SPLIT_VALUE)

ALTER TABLE Tblpartition
  ADD CONSTRAINT PK_TBLPARTITION PRIMARY KEY (Seq, Split_value)

INSERT INTO Tblpartition
SELECT 'Nirav', 'Gajjar', 1
UNION ALL
SELECT 'Smith', 'Mack', 2
UNION ALL
SELECT 'Jack', 'Samson', 3
UNION ALL
SELECT 'Smith', 'Raka', 10
UNION ALL
SELECT 'Hen', 'Vaith', 11

Step2 : Script to add Partition Table in existing Replication.

--Script to add table in replication.
EXEC Sp_addarticle
  @publication = N'PublicationName',
  @article = N'TBLPartition',
  @source_owner = N'DBO',
  @source_object = N'TBLPartition',
  @type = N'logbased',
  @description = N'',
  @creation_script = N'',
  @pre_creation_cmd = N'drop',
  @schema_option = 0x00000000081B50DB, --Partitioning Options, This will enables Partition Function and Partition Scheme to replicate.
  @identityrangemanagementoption = N'manual',
  @destination_table = N'TBLPartition',
  @destination_owner = N'DBO',
  @status = 24,
  @vertical_partition = N'false',
  @ins_cmd = N'CALL [sp_MSins_DBOTBLPartition]',
  @del_cmd = N'CALL [sp_MSdel_DBOTBLPartition]',
  @upd_cmd = N'SCALL [sp_MSupd_DBOTBLPartition]',
  @force_invalidate_snapshot = 1
GO

Step3 : Refresh Subscription.

exec sp_refreshsubscriptions @publication = 'PublicationName'

Step4 : Start snapshot sgent in replication to apply changes in snapshot.
–> Replication Monitor.
–> Expand Publisher and select Subscription.
–> Goto Agent Tab.
–> Right click on Snaphot and click on Start.

Regards,

Nirav Gajjar