1

I have transactional replication setup between Server1 database and Server2 database on few tables.

Now the problem is when I start the replication for the first time, it drops the table on target server database and recreates with the records of source server database.

I don't want the Source to drop the Target tables. It should only be effected if I Insert/Delete/Update the source database tables.

The Publisher Database has very few records but the subscriber has millions of records.

Is there any way?

Update:

The reason I am doing this is that we have one big database lets say DATABASE-SUB (subscriber) that has tons of data but recently we have setup another database DATABASE-PUB same as the other one but completely empty that would be used by other customers and should have no data. It's from the scratch but anything they do should be now the part of the DATABASE-SUB.

I am initializing it on the first time and not after that. I might need to initialize when we add/remove some columns but not usually.

Also, the database in DATABASE-PUB would be starting from PrimaryKey ID as 1,2,3,4,5 which already exists in the DATABASE-SUB.

Update 2:

Explanation:

See, the reason to do this is that we have ONE database, a very old but still in use which has all the records till date.

Now, there is one customer who wants an on-premises database. We have put that there and made it CLEAN. NO DATA.

Now, the thing is whatever he insert there shall be replicated from there to the MAIN database, the ONE ^.

Now, the issue is that since the NEW database is truncated and PRIMARY KEYS are starting from 1 again hence the VIOLATION error but we have to copy that records since it's a completely different data.

What to do?

Replication

or

Stored Procedure?

Improve this question
10
  • 1
    Based on this comment, it's not that you want to skip dropping the table on initialization, but you want to skip initialization entirely? Those are separate configuration options, so it would be a good edit to clarify that you do not want to do ANY initialization, just start replicating commands directly.
    – AMtwo
    Nov 24, 2021 at 17:31
  • So is your goal to basically have an empty copy of your database structure that you can use to easily generate copies of to new databases for customers?
    – J.D.
    Nov 28, 2021 at 13:18
  • @J.D. see... I have already 1 database which would act as a subscriber but has millions of records. Now, the publisher DB which would have similar structure but no records. once a record is inserted into it it shall be replicated to the subscriber db. The reason to do this is because we have a lot of customers now and each has on premise database but from the scratch but the subscriber db which is the main db in data center should be containing all the records from all the publishers.
    – Robbie Robertson
    Nov 29, 2021 at 6:35
  • Why is a record that already exists at your Subscriber's database being inserted into your Publisher's database? Or is it a different record but it just happens to have the same primary key value as a different record in your Subscriber's database?
    – J.D.
    Nov 29, 2021 at 12:26
  • @J.D. it just happens to have the same primary key value as a different record in Subscriber's database since its seed value restarts from 1.
    – Robbie Robertson
    Nov 29, 2021 at 12:36

1 Answer 1

Reset to default
1

First thing I'll mention is, while it's allowable, it's not recommended to make changes to the Tables at the Subscription database, including data changes, unless you're using Updatable Subscriptions.

Secondly, the dropping of the tables only happens when you initialize the Subscriber, which should only occur on the initial creation, and whenever you add Articles to the Publication (or for a few other specific changes to the Publication). But generally won't occur during normal day-to-day use of Transactional Replication, when no changes are made to the Publication itself (data changes to the replicated Tables is fine).

But, you can achieve what you want by setting the "Action if name is in use" Article property of your replicated Tables to one of the other non-default options such as "None" instead of "Drop", which should leave your data intact at the Subscriber.

I couldn't find the exact Books Online for the steps on changing the "Action if name is in use" property, but this other Microsoft article details the correct steps as the following too:

In the Article Properties - dialog box in the Destination Object section, select a value of Keep existing object unchanged, Delete data. If article has a row filter, delete only data that matches the filter. or Truncate all data in the existing object for the option Action if name is in use.

Note: Again you probably want to select the "None" option if your goal is to keep your Subscriber database's data intact. But the steps mentioned above are otherwise the correct steps you should follow.

If you're not familiar with how to get to the Publication Articles window, the subsequently linked Books Online called View and Modify Publication Properties walks you through the steps on how to get there.

But in short it's as simple as expanding the "Replication" folder (in the Object Explorer in SSMS), then expanding the "Local Publications" folder, and then right clicking on your Publication and clicking Properties.

Improve this answer
7
  • Hi JD, thanks for so clear and informative answer. I did exactly i.e. Keep existing object unchanged but still upon initialization it is trying to copy the data into the subscriber (push subscription at source) and getting error i.e. violation of primary key i.e. 989 id. Why it's still happening?
    – Robbie Robertson
    Nov 24, 2021 at 14:18
  • any idea why it's still happening?
    – Robbie Robertson
    Nov 24, 2021 at 14:26
  • @RobbieRobertson I haven't used the "None" option before, but it makes sense it's trying to synchronize the data still, despite that it's not dropping the table anymore. And if the data in your Subscriber Tables already exists from the Publisher, then you'll get primary key violation errors as you're seeing. It's not entirely clear what your use case is with why you have data at the Subscriber that's not in the Publisher. If you could put more details in your original post on why that's so, and your end goals, a better answer may be possible. You may have to utilize Pre-Snapshot Scripts etc.
    – J.D.
    Nov 24, 2021 at 14:50
  • Also having a rough idea of how often you anticipate needing to reinitialize the Subscriber would be useful in determining what options are realistic to solve your problem.
    – J.D.
    Nov 24, 2021 at 14:51
  • Are you initializing the subscriber manually? There are some pitfalls if there are updates/deletes at the publisher, and no corresponding rows at thee subscriber.
    – AMtwo
    Nov 24, 2021 at 17:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.