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