This article is a mirror article of machine translation, please click here to jump to the original article.

View: 12586|Reply: 0

[Source] sqlserver transaction replication adds new tables without reinitialization

[Copy link]
Posted on 11/26/2014 3:51:12 PM | | | |
When an online transaction copy subscription is in progress, you now need to add a new table to it.

There are several possible ways


1. The easiest way is of course to reinitialize. Add the table (sp_addarticle or use the copy wizard) and right-click to reinitialize it to generate a new snapshot.
  This method has countless advantages, but one disadvantage is that all tables in the replication subscription chain are reinitialized once, and the tables read during initialization have no data. It may also last longer


2. Create a new release, and then make a copy subscription to the updated table separately
  This method is relatively secure and will certainly not affect the existing replication subscription chain, and newly added tables can be initialized at will. The disadvantage is that you can't always build a release on the last table, a more reliable way is to migrate the project (table) in this new release to the official release subscription chain on a regular basis. Of course, there is also an advantage in this, because the new table can sometimes be dealt with when there is a problem.


3. In three steps:
A. Fill the new table with data on the full read/write database (data consistency is required)
B. Stop reading the log agent
C. Add the new table to the publish subscription
D. Enable the Log Read Agent
  This method does not have a significant impact on online publishing (compared to method 1), but it stops reading and copying data during operation, increasing the read/write replication delay. If you are not harsh on the read and write delay, you can choose it. It is highly recommended to prepare the script in advance. Quick battle and quick decision



These are the three methods that come to mind

Theoretically, the third type is the most reasonable (compromise), but when it comes to the requirements of the replication subscription environment I am currently managing now, method 2 is more often used. Although it may have a slight negative impact on server performance. But it also creates a proper buffer for DBAs to manage this 22-year replication subscription






Previous:SQLServer backups restore databases
Next:Index utilization problem when sqlserver handles like '%keyword%'
Disclaimer:
All software, programming materials or articles published by Code Farmer Network are only for learning and research purposes; The above content shall not be used for commercial or illegal purposes, otherwise, users shall bear all consequences. The information on this site comes from the Internet, and copyright disputes have nothing to do with this site. You must completely delete the above content from your computer within 24 hours of downloading. If you like the program, please support genuine software, purchase registration, and get better genuine services. If there is any infringement, please contact us by email.

Mail To:help@itsvse.com