SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Replication- distribution server crashed


Replication- distribution server crashed

Author
Message
deep_kkumar
deep_kkumar
Mr or Mrs. 500
Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)

Group: General Forum Members
Points: 593 Visits: 756
Jimbo,

so in this case even if we have a backups of distribution server we will be not able to use them when a server crashes. Is there any high availability solution for distribution server we can work on in order to eliminate these kind of situations( mirroring, clustering, log shipping etc).

Thanks,
David Benoit
David Benoit
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7558 Visits: 3650
The difficulty you face is that the LSN for the replicated transactions in the distribution database are out of sync with what you have coming from the publisher. The error "could not execute sp_repldone" is indicative of that. So, if you restore the distribution database you are going to see this. The only way to avoid that would be to restore distribution database and the published databases to the exact point in time. Not an easy feat.

You can restore the distribution database and then force a sp_repldone which will invalidate all transactions in the published database transaction log that are marked for replication. Basically log reader will no longer pick those up. That also means that you have to manually sync all the tables in the publications as they will no longer be in sync with the subscriber. It will allow replication to start working again though. So, if tables are reasonably sized, and a initialize is not an option, this will work.

Hope this helps.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
deep_kkumar
deep_kkumar
Mr or Mrs. 500
Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)

Group: General Forum Members
Points: 593 Visits: 756
Thanks David. I am able to resolve that issue.

But here the problem is if we have a backup of distributor around 4 A.M in the morning and the server crashed at 3 A.M next morning we will be loosing lot of transactions.Would you suggest to take backups more often ? If we take the case of high OLTP environment this will create more issues. Again with table sizes of 30 GB, it's very difficult to run the commands and sync manually.

I am working to setup synchronous mirroring for the distribution server. For this i have to change the recovery model for the distribution database. Any suggestions ?
David Benoit
David Benoit
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7558 Visits: 3650
Yes, agreed. That is a bit troublesome to sync, though not impossible. Having a synchronous mirror for the distribution server would be helpful. I definitely recommend having a HA solution in place for the distribution server and that includes the proper RAID configuration at the storage layer so that you are not suffering from a catastrophic failure that can't be recovered from locally. DR is another situation and what you spend on that will depend on your requirements. I think that most companies would be willing to suffer the downtime required to reinitialize a publication, even if it has a 30 GB table, to save on some of that from a DR perspective.

Another note here, in most cases the distribution server is small enough that they will work well on a VM so you could move it there and partake of the VM environment HA solution if you have that in place.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
deep_kkumar
deep_kkumar
Mr or Mrs. 500
Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)

Group: General Forum Members
Points: 593 Visits: 756
David,

I was testing the synchronous mirror method. Automatic failover works fine as soon as the principal goes down but the tricky part is how to make the mirror act as distributor ? Tried to rename the DNS name, but we have to go for drop server and add server back. I restored master and msdb databases from principal distributor server to mirror server. I am kind of lost here. Any help would be appreciated.

Thanks,
David Benoit
David Benoit
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7558 Visits: 3650
Sorry, I don't. I haven't really used mirroring at all and so am very weak on this subject. Just so that I understand better though, what is your goal that you are hoping to achieve with mirroring the distributor? Is this for HA or DR?

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
deep_kkumar
deep_kkumar
Mr or Mrs. 500
Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)

Group: General Forum Members
Points: 593 Visits: 756
for DR purpose.
David Benoit
David Benoit
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7558 Visits: 3650
In that case you should be able to rename the server following bringing it back online. I have done this and it does work, even for the distributor.

The big caveat with having the distributor set up for DR is that you have to have the LSN's from the publisher transaction log in sync with what the distributor has. Unless you have both the distributor and the publisher mirror at the exact same point you will end up in an out of sync condition and will end up either reintializing OR ignoring the commands that have not been sent from the transaction log to the distributor to get replication flowing again, which results in an out of sync condition.

If you use storage level replication, and you can snapshot both the publisher and the distributor volumes at the same time you should be able to eliminate that. Not a cheap solution of course.

Anyway, my point in bringing that up is that for DR of the distributor your cheapest alternative may just be to have a server set up (but powered down to save licensing costs) to the same exact version and patches, and then just re-establish replication through scripts in the event you actually had a DR scenario. The coverage provided isn't any less than what you will likely get with the mirror. You could also rebuild the replication without initializing and do a manual sync as well.

There is a good bit to this so I'll leave it here. If you have specific questions about what I stated above, let me know.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search