Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Replication- distribution server crashed Expand / Collapse
Author
Message
Posted Monday, December 16, 2013 9:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 8:13 PM
Points: 124, Visits: 750
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,
Post #1523321
Posted Monday, December 16, 2013 10:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:07 AM
Points: 2,107, Visits: 3,583
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
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #1523333
Posted Monday, December 16, 2013 10:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 8:13 PM
Points: 124, Visits: 750
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 ?
Post #1523347
Posted Monday, December 16, 2013 10:59 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:07 AM
Points: 2,107, Visits: 3,583
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
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #1523355
Posted Wednesday, December 18, 2013 11:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 8:13 PM
Points: 124, Visits: 750
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,
Post #1524259
Posted Wednesday, December 18, 2013 11:28 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:07 AM
Points: 2,107, Visits: 3,583
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
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #1524263
Posted Wednesday, December 18, 2013 11:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 8:13 PM
Points: 124, Visits: 750
for DR purpose.
Post #1524269
Posted Wednesday, December 18, 2013 11:51 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:07 AM
Points: 2,107, Visits: 3,583
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
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #1524277
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse