question

DM-0933 avatar image
0 Votes"
DM-0933 asked DM-0933 commented

NEW TABLE CREATION AND REINITIALIZATION - REPLICATION

There is a business need that a new table needs to be created at all servers and this table should be part of replication. We already have replication set between head quarter server and location servers;'replicate schema changes' is also set to true. We will need to fire the creation script at head quarter. After say creation of this new table at head quarter server; it has to be ticked for replication. Is it necessary to reinitialize at head quarter after ticking for replication; so that it is taken care in replication from head quarter to location. Is there any less processor and hard disk intensive way out to achieve it in replication (as I will need to do it in serial at head quarter first and then one by one at all other location servers).

Also what does SQL Server engine do when new table is created and reinitialized. Thanks in advance.

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image AmeliaGu-msft ·

Hi DM-0933,

How are things going? Were the answers helpful?
Please feel free to let us know if you have any other question. If the post in the thread is helpful, you could kindly accept it as answer.

Best Regards,
Amelia

0 Votes 0 ·

3 Answers

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered DM-0933 commented

Hi DM-0933,

In addition, you can set Publication properties immediate_sync and allow_anonymous to false in order to only generate a snapshot for the newly added article(s) if you are using transactional and snapshot replication.
Please check Reinitializing a Single Article In Transaction Replication and SQL Server Replication (avoiding reinitialization) which might be helpful.

Best Regards,
Amelia

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


· 2
5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DM-0933 avatar image DM-0933 ·

Hi AmeliaGu-Msft,

Firstly thanks for your guidance; shall check and revert.

0 Votes 0 ·
DM-0933 avatar image DM-0933 ·

Hi AmeliaGu-msft,

Thanks for your revert and guidance. It worked fine in test environment.

Thanks to TomPhillips-1744 and Erland Sir too.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered TomPhillips-1744 commented

Are you creating a new empty table? Or are you renaming the existing table and creating a new table with the same name, and copy data over to the new table?

I don't work a whole lot with replication, so take my word with a grain of salt. But in case of a new table, I would expect the schema of the table to be replicated to the other servers. Really what happens in the second case I am not entirely sure of.

I would recommend that you set up some test servers to try out what happens.

In case of an empty new table, I don't see any point of taking shortcuts to make it more efficient. If you are going to copy data, you could exempt the tables from replication, and send out a script to copy the table and make sure to run it on all servers. This saves you from sending the log records across the network. Then again, this requires more manual work and cause problems if some site is forgotten for some reason.

· 3
5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DM-0933 avatar image DM-0933 ·

Hi Erland Sir,

Firstly, thanks for your revert. It will be table which will have the data and has to be copied across to other subscriber servers. Shall check your advice on test machine.

0 Votes 0 ·
DM-0933 avatar image DM-0933 DM-0933 ·

Hi Erland Sir,

Checked in test environment. Actually; it didn't replicate a new table to subscriber by 'replicate schema changes' set to true.

Am checking further. Thanks once again.

0 Votes 0 ·
TomPhillips-1744 avatar image TomPhillips-1744 DM-0933 ·

You must create a new snapshot for the initial table creation.

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered DM-0933 commented

You do not need to "reinitialize" the subscription when adding a new table to the subscription. Add the table to the database.
Add the table to the subscription. Then run the snapshot agent job, which will move the empty table to all subscribers. Then it will start being populated by the distribution job.

· 3
5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DM-0933 avatar image DM-0933 ·

Hi TomPhillips-1744,

Firstly; thanks for your revert.

Just a query; say I add a new table to publisher; do I have reinitialize the publisher; after it as as table does not reached subscriber by replication; do I have to manually run script of adding table at subscriber and reinitialize. Can you please give steps that I have to do at publisher and subscriber servers. Thanks in advance.

0 Votes 0 ·
TomPhillips-1744 avatar image TomPhillips-1744 DM-0933 ·

I gave the steps above to add tables to a subscription.

  • Add the table to the database

  • Add the table to the articles in the publication

  • Run the snapshot agent job


That is all you need to do. You do not need to reinitialize the subscription(s).

https://docs.microsoft.com/en-us/sql/relational-databases/replication/publish/add-articles-to-and-drop-articles-from-existing-publications?view=sql-server-ver15


0 Votes 0 ·
DM-0933 avatar image DM-0933 TomPhillips-1744 ·

Hi TomPhillips-1744,

Thanks for your revert. Checked. Even after running the snapshot agent job; the new table didn't got created at Subscriber database. Steps that I did were as below.

  1. Made new table on publication db.

  2. Add it to articles in publication. Clicked ok.

  3. Ran the snapshot agent from agents tab in replication monitor on publication db. It gave message that 100% A snapshot of 200 articles generated.

On checking at subscriber database; the table isn't existing; have I missed something. Please guide.

0 Votes 0 ·