September 25, 2008 at 7:57 am
I have upgraded our replicated database from SQL 2000 to SQL 2005. I tried PUSH subscription initially and it went fine. When I tried PULL subscription, the data is supposed to be retained from old SQL 2000 instance. But it doesn't. Actually, while doing PULL subscription, table should be updated with new data in the subscriber. Along with it, old data should be retained.
Can anyone help me on this?. How to retain old data in the table, as well as updating the table with new one?.
September 25, 2008 at 1:23 pm
What kind of replication are you performing? If not MERGE, then your subscriber will have to match publisher 100%, without regard for what was in subscriber before. There are additional limitations, I believe, when sharing between 2005 and 2000 versions of SQL Server.
September 25, 2008 at 1:26 pm
Replication does not delete anything. It copies transactions from the publisher to the subscribers.
Unless you have merge setup, as mentioned above, it should not move data from the subscriber to the publisher.
September 25, 2008 at 10:37 pm
Thanks for the reply Steve.
I am using transactional replication.let me explain the scenarios clearly.
I am using 3 databases namely A1,A2 and A3.
A1 and A2 reside in one server named S1 and A3 in server S2.A1 has 4 tables.Now I do push replication from database A1(Publisher) in server S1 to database A3(Subscriber) in server S2. A3 also contains the same 4 table structure. Now, I do pull replication from database A3(Publisher) in server S2 to database A2(Subscriber) in server S1.For pull, i use 7 tables which are diferent from those 4 tables.This was the setup which i used in sql 2000.
Now I need to upgrade from 2000 to 2005 server.So i took backup of all those 3 DB and restored in Sql 2005 server,with same server setups.Now, after setting replication in 2005 server push replication works. But in case of pull, the old data in DB A2(Subscriber) is lost and it contains only the data of DB A3(Publisher).I need the old content to be retained in DB A2.
Let me know if you are clear with the scenario..
October 2, 2008 at 9:47 am
Yes, thank you, the scenario is clear. The challenge you are experiencing is, I believe, in how you set up the pull replication drawing from A3 to A2. When you take the initialization snapshot, you overwrite all your old data. You need to perform transactional replication without the initial snapshot. There are multiple posts on this forum as well as information in BOL - I apologize for not having the time to research the links for you. They should give you correct guidance.
When you do the initial snapshot, you overwrite the content in A2. Doesn't matter whether you pull or push - the initial snapshot still overwrites. By setting up the transactional replication WITHOUT the initial snapshot, you'll preserve your prior content (except for updates which are transactionally driven, of course).
Hope this helps.
October 3, 2008 at 6:06 am
Yes..Thanks Steve. I got the solution. I did transactional replication without initialization while creating subscriptions. I unchecked 'Initialize' check box in Initialize Subscriptions in the wizard. This solved the problem. Thanks again 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply