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?
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.
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?