September 17, 2008 at 3:34 pm
We are using transactional push replication w/o snapshot between SQL Server 2005 databases (4 servers). Replicating data to one of the servers fails. Deleting the subscription and re-creating it does not help. Deleting the whole replication and reinstalling it from script does not help either. The exact same replication takes place on all computers. From A and C to D works. From C to B works but from A to B does not work. 'The subscription(s) marked inactive and must be reinitialized. NoSync transcriptions will need to be dropped and recreated.' message appears. How to fix that? The same replication works fine between two SQL Server 2000 though the authentication is different. We are using SQL Server authentication for 2005 servers and windows for 2000 servers.
September 22, 2008 at 11:49 am
How did you initialize replication to set it up? Did you restore a backup to this subscriber or did you create and apply a snapshot?
I've had to fix this in the past. To resolve, I dropped the subscription, restored the subscription database from the most recent backup of the publisher (assuming there have been no changes to the publisher) and re-added the subscription.
I could not find any way to mark an inactive subscription as active.
Hope that helps.
Chris.
Chris.
September 22, 2008 at 11:59 am
Thank you for your help. This is a transactional NoSync push replication, so we do not use snapshot agent. We also do not create backups. I deleted the subscription and than recreated it. I also tried to stop the Distributor Agent job ans Start Synchronizing from the Replication Monitor.
Peter Redei
September 22, 2008 at 12:04 pm
How did you get the data to the subscriber when you set up replication the first time around?
Chris.
September 22, 2008 at 1:53 pm
I am not sure I understood your question correctly. I guess what you are asking is that how I synchronized the data between servers since I use NoSync. I did not. We do not really care about having identical data in the databases including past data. What we care about is to replicate inserted data from server A to server B, so we can report it.
Thanks,
Peter Redei
September 23, 2008 at 6:14 am
Hi Peter,
Sorry, its really difficult to troubleshoot more complex/detailed problems on here.
When you recreated the subscription, did you start with a new subscriber db?
I have not dealt with this issue on merge replication but I have with transactional replication. In my case, dropping and recreating the subscription wasn't enough, we had to start with a new subscriber database.
The other thing I found with replication is that sometimes entries stick around in some of the system tables. If you get desperate you may need to drop the publication, disable publishing & replication, and set it back up from scratch.
Hope that helps.
Chris.
Chris.
September 23, 2008 at 7:52 am
I've had a similar issue in the past. I had to :
-truncate subscribe table
-import data from publisher
-restart replication
Good luck!
September 23, 2008 at 8:21 am
Thank you very much Chris for your help.
No, the subscriber db is the same. We cannot start with a new one.
In my case, dropping and recreating the subscription wasn't enough either.
I did get dropped the whole publication and recreated it from script. To my surprise this did not solve the problem either. There is one simple thing I have not tried yet, that is rebooting both servers (these are production servers, so I cannot just reboot them). You are right in that entries stick around, so I wonder if a reboot might helps. There is one other weird thing I noticed. When I drop the subscription that does not drop the associated job. I think normally it should. Deleting the job does not help. When I recreate the subscription the job is created as well, but with the prior Id.
Peter Redei
September 23, 2008 at 8:40 am
We tried that too. Unfortunately, for us we had to get rid of the subscription db, since you can't, probably because it would affect your reporting. Can you try setting up the subscription with nosync to a different/new db as an experiment? Then, if it works you can figure out how to put all of the data together and restart replication, or draw a line in the sand and eventually get rid of the db that didn't work or even use synonyms to allow your existing reports to function.
I've seen the jobs get left behind a lot. I usually clean them up myself before recreating the subscription or publication.
Chris.
Chris.
September 23, 2008 at 8:44 am
AFAIK, the data in the publication HAS to be the same to start replication. You need to either do that with a snapshot, db backup/restore, or manually ensuring the data is there. otherwise some transactions that try to replicate might fail.
September 23, 2008 at 9:00 am
Steve,
I thought so too, and I've never tried without restoring a backup or applying a snapshot but Peter mentioned the NOSYNC option which I looked up in BOL.
Constant Value Description
AUTOMATIC
1
Initial synchronization is to be provided to the Subscriber.
NOSYNC
2
No initial synchronization is needed by the Subscriber.
Which I guess could mean that you can start without a snapshot but when I read the "Initializing a Merge Subscription Without a Snapshot " section this isn't discussed.
Peter,
When you created the other subscriptions, did you start with a snapshot?
Chris.
September 23, 2008 at 10:03 am
No. We do not use snapshot at all.
September 23, 2008 at 10:15 am
Steve,
This is not true for a NoSync replication. We simply replicate the new transactions (insert only). As 'aging' records are deleted the databases will be in sync over time. Actually this is more complex here. We have four servers and from them two gets new data at a time. The new data is replicated to the other three servers.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply