November 7, 2023 at 8:05 am
Hi,
This is Syed JB, I have an existing Transactional Replication. Unfortunately, for some reasons Replication has been removed for some time. But, now, we have to rebuild the Transactional Replication. Here is the problem, Subscriber has the historical data and for this I can’t push the snapshot and without the initialization, subscriber subscription status set to PENDING. I have tried to setup by changing the parameter in create replication scripts, but NO luck. My replication only delivers INSERT and UPDATE statements only(Not the Full replication)
<li class="text--left" style="text-align: left;">@immediate_sync = N'true'/'False'
<li class="text--left" style="text-align: left;">@independent_agent = N'true'
So, please suggest me.
November 7, 2023 at 2:26 pm
My first step would be to check the MS documentation - https://learn.microsoft.com/en-us/sql/relational-databases/replication/troubleshoot-tran-repl-errors?view=sql-server-ver16
But in your specific use case, I think you are stuck between a rock and a hard place. If I understand right, you can't get the subscriber to pull the data because the subscriber isn't synced with the primary and you need it synced to primary without losing any existing data in the subscriber. The only solution I can think of is to make a new table on the subscriber side to pull the data into and then create a view that unions the current table and the new table and have anyone who needs that historical data use the view.
My understanding is that replication needs to replicate to a NEW table when you set it up.
Now, my next question is "is replication the right tool for what you are trying to do?". What I mean is, it sounds like you are trying to create a table that has the history of another table, correct? If so, replication is going to hit a snag since you are only capturing INSERT and UPDATE operations. INSERT will have historical data, but the UPDATES are going to overwrite the values. What I mean is if you INSERT the value 'cat' then update that to be 'dog', the replication table will ONLY contain 'dog'. If you are trying to create a logging or audit table, then a trigger or a modification to the stored procedure would be a better approach. If storing only the last value for things is what you want, then replication will work, but again modifying the stored procedure or a trigger may be easier to maintain long term. Or service broker is another option; it allows for eventual consistency between databases.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 27, 2023 at 12:39 am
I understand you are trying to preserve some data at the subscription, and save time. Perhaps, try this steps:
I am not sure if this could help. But if you have time and resources, it is one way to approach it.
There are other approaches. I would let the SQL replication do the work for you.
DBASupport
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply