certified-oracle-mysql-dba.jpg

Reinitializing a Single Article in a Publication

No Comments »

Transactional replication can sometimes be a tricky high availability option to work with. Many companies are now using it more and more to offload reporting to a different server. But as we know, making schema changes to the tables in a publication is something that must be done when the application changes or different business needs must be met. The situation I have been running into more often is when one or more columns in a table need to be changed and the article needs to be updated. If it is a small publication, the change is usually done by deleting the subscription, removing the article from the publication, updating the article, re-adding the article, re-adding the subscription and reinitializing the subscription. But what happens when reintialization takes a long time to complete? Sometimes we do not have the time to follow the previous method.

For example, when a publication is 500 GB in size and takes 20 hours to apply the snapshot to the subscriber it may not be viable to reinitialize even with a backup when we want to change a schema of a single article. Whenever the publication is very large, we need to find a different way of making a snapshot of a single article. We can do this by following the process below.

1. First, we turn off @allow_anonymous and @immediate_sync on the publication by doing the following:

EXEC sp_changepublication

@publication = ‘testpublication’,

@property = N’allow_anonymous’,

@value = ‘false’

GO

EXEC sp_changepublication

@publication = ‘testpublication’,

@property = N’immediate_sync’,

@value = ‘false’

GO

2. Then, we drop the article from the subscription.

EXEC sp_dropsubscription

@publication = ‘testpublication’,

@subscriber = ‘subscriber_name’,

@article = ‘article_we_want_to_change’

3. Next, we want to force an invalidate of the snapshot.

EXEC sp_droparticle

@publication = ‘testpublication’,

@article = ‘article_we_want_to_change’,

@force_invalidate_snapshot = 1

4. Now we can change the schema of the article we just removed from the subscription.

5. Then, we add the article we want to change back to the publication.

EXEC sp_addarticle

@publication = ‘testpublication’,

@article = ‘article_we_want_to_change’,

@source_object = ‘article_we_want_to_change’,

@force_invalidate_snapshot = 1

6. We will then want to refresh the subscription.

EXEC sp_refreshsubscriptions @publication = ‘testpublication’

7. Next we can start our snapshot agent which will snapshot only the article that we made changes to.

8. Next re-add the @immediate_sync and @allow_anonymous.

EXEC sp_changepublication

@publication = ‘testpublication’,

@property = N’immediate_sync’,

@value = ‘true’

GO

EXEC sp_changepublication

@publication = ‘testpublication’,

@property = N’allow_anonymous’,

@value = ‘true’

GO

Voilà! We have successfully made a snapshot of a single article, eliminating the need to reinitialize the entire publication. All the report users will be thanking you.

Tim Foley, Sr. SQL Server Database Administrator

RDBAELOGO

DBA Tips

​Be sure to Choose
the Right Provider
Download our free guide

Important
Questions to Ask

when choosing a
Remote DBA Provider
This simple .pdf will help ensure you efficiently evaluate remote DBA companies
Download NOW!