How to manage existing transactional replication database table using SQL Server Management Studio

Question

Today, I created a transactional replication database using SQL Server Management Studio and now the data is synchronizing correctly between the publisher and subscriber databases. Now, my situation and doubts are follow.

1. How do I add a new column field to an existing table in the replication database? First, I need to alter the column in the publisher database, then how do I add it to the subscriber database?

2. How can we add a new table to both the publisher (where we can directly create a new table) and the subscriber in the existing replication? Do I need to stop running the log agent reader and distribution agent? After adding them to the publisher, start again and it will synchronize automatically?

3. My current replication database is only for table objects, not using any SP, view, or user-defined function.

4. Do I need to stop the log reader agent and distribution agent if any changes are made to existing tables (such as adding new fields or indexes)?

Answer 1

How do I add a new column field to an existing table in the replica database? First, I need to alter the column in the publisher database, then how do I add it to the subscriber database?

It depends on the schema change you're making, but in most cases your change should replicate automatically - see this article.

It is possible to turn of schema replication, but it is turned on by default. To confirm your settings, connect to your published database and execute sp_helppublication. Check the column replicate_ddl - if the value is 1 then your DDL changes (schema changes) will replicate by default. This means all you have to do is make the schema change (add column, drop column etc) on the published database and it will propagate to the subscriber(s).

If schema changes are not replicating in your publication, then you have to drop the article, make the schema change on your published database, recreate the article and generate a new snapshot. The next time the Distribution Agent runs it will apply the snapshot and begin replicating your updated table.

Do I need to stop the log reader agent and distribution agent if any changes are made to existing tables (such as adding new fields or indexes)?

Adding new columns or removing columns should replicate automatically. The next time the distribution agent runs it will propagate the change to all subscribers. If you distribution agent is running continuously, it will simply pick up the changes on its next poll, you don't need to stop/start it.

You shouldn't need to stop/start either the Log Reader or Distribution Agent to apply a schema change, unless you start encountering blocking (where these agents are blocking your schema change session) while trying to apply the change.

New indexes will also propagate IF the Copy nonclustered indexes option is enabled for the article. This can be checked in the Article properties in SSMS.

If Copy nonclustered indexes is disabled, you simply create the index on the publisher and then create it on the subscriber manually - NOTE: In this scenario, if you reinitialise the subscription in the future, it will remove these nonclustered indexes so you will need to recreate them on the subscriber. You can automate this using post-snapshot scripts to recreate these indexes. Its also possible to maintain totally different indexes (except the Primary Key index) on the subscriber to support different querying workloads on the subscription database.

How can we add a new table to both the publisher (where we can directly create a new table) and the subscriber in the existing replica? Do I need to stop running the log agent reader and distribution agent? After adding them to the publisher, start again and it will reflect automatically.

You can simply create the new table on the publisher, then add a new Article to the publication for that table. You then generate a new snapshot using the Snapshot Agent and it will create a 'mini-snapshot' which contains just this single article. This article then gets applied to all subscribers when the Distribution Agent next runs and data will begin replicating.

This process is detailed in this article.

Answer 2

How do I add a new column field to an existing table in the replica database? First, I need to alter the column in the publisher database, then how do I add it to the subscriber database?

You usually dont modify objects directly in the subscriber db. instead, modifications should first be made in the publisher db which will then flow the changes to the subscriber db. You can read Kin Shah's answer on altering columns when the table is replicated.

How can we add a new table to both the publisher (where we can directly create a new table) and the subscriber in the existing replica? Do I need to stop running the log agent reader and distribution agent? After adding them to the publisher, start again and it will reflect automatically.

I usually use the GUI to add tables in the publication. If set up correctly, you don’t need to stop the log reader agent or distribution Agent. Make sure that the @immediate_sync and @allow_anonymous parameters are set to 0. See answer from Brandon William

Do I need to stop the log reader agent and distribution agent if any changes are made to existing tables (such as adding new fields or indexes)?

NO. It should automatically flow if you setup correctly. check my answer above.

For indexes, it depends:

• for clustered index, it will be copied to the subscriber db by default.
• for nonclustered indexes, it will not be copied to the subscriber db by default. If you need to copy a nonclustered index, you must enable it manually in the article properties via the GUI.
• for unique key constraints, it will flow to the subscriber db by default.

Answer 3

How do I add a new column field to an existing table in the replica database?

Do you mean alter structure of a table involved in replication or just add a field only to replica?

I think the easiest way will be to drop the table article from publication, modify it, re add it and let it synchronize.

How can we add a new table to both the publisher (where we can directly create a new table) and the subscriber in the existing replica?

The same as above

Do I need to stop the log reader agent and distribution agent if any changes are made to existing tables (such as adding new fields or indexes)?

No you don't have to stop log reader but you'd rather remove the article from subscription.

Creation of indexes on subscriber or publisher is not affecting replication.