I'm using SQL Server 2014 SP3CU2 Enterprise Edition.
I have a transaction replication on a DB with some very large tables. I needed to change the structure of a column on one of the tables that were replicated (1.8B rows), so I did the following:
- Created a duplicated table with the new structure.
- Removed the table (article) from the publication
EXEC sp_dropsubscription @publication = N'PubName', @article = N'TableName', @subscriber = N'SubscriberName', @destination_db = N'DB';
EXEC sp_droparticle @publication = N'PubName', @ article = N'TableName'
- Switched the tables (old/new)
- Added the table (article) to the publication
EXEC sp_addarticle @publication = N'PubName', > @article = N'TableName', @source_owner = N'SchemaName', @source_object = N'TableName', @type = N'logbased', @description = N'', @creation_script = N'' , @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'TableName', @destination_owner = N'SchameName', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_SchemaNameTableName]', @del_cmd = N'CALL [sp_MSdel_SchemaNameTableName], @upd_cmd = N'SCALL [sp_MSupd_SchemaNameTableName]'
exec sp_addsubscription @publication = N'PubName', @subscriber = N'SubscriberName@destination_db = N'DB', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
- Started the Snapshot agent.
I did it in the ACC environment with no issues (that env is much smaller though).
In prod, the snapshot agent took 2.5h to generate the 1 article (as expected, as it's a large table), but once it was done, the distribution agent just filled the gap - the missing rows on the subscriber and continued the sync - the table WASN'T dropped on the subscriber side.
I'm puzzled and can't understand why was it acting this way.
I'm planning to do this again in a few weeks - to re-initialized the replication (because I need to coordinate it with other people) but I can't explain what happened and/or what I need to do in order to avoid it from happening again.
Any help will be appreciated.
Thanks in advance, Roni.