Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 7 down vote favorite

Has anyone had success with mirroring the distribution database? We have a dedicated server as a Distributor. It handles all our push replication from prod to reporting. We'd like to build an identical server,close by, in case the distributor crashes. Has anyone had success building something like this?

Thanks!

share|improve this question
For HA/DR the distribution database can be deployed on a clustered instance, but not in mirrored DB. – Remus Rusanu Oct 5 '11 at 22:00
1  
+1 Interesting idea. A dedicated distributor should be OK to mirror but I've never tried. – gbn Oct 5 '11 at 22:13

2 Answers

up vote 6 down vote

Sadly mirroring of the distributor isn't supported. I'm not sure if it would work as the server names wouldn't be correct when the agents would log in, not to mention you'd have to manually setup all the agents on the backup box. Currently your only HA option for the distributor is Windows Clustering.

share|improve this answer
I find this to be an unfortunate limitation of the Distribution database. I think it was a major oversight to not allow a "Secondary Distribution" database. It provides a single point of failure in an otherwise fairly robust suite of replication features. – Matt M Oct 6 '11 at 14:45
@mrdenny Would you happen to know of an article which lists the steps to cluster the distributor. – sharadov Oct 6 '11 at 17:08
I've written a number of articles on clustering SQL Server that are available on sqlmag.com. Basically you setup an Active/Passive cluster using Microsoft Cluster Services then use that instance as the distributor. You can't take your existing instance that is the distributor and cluster it. Also you'll need shared storage of some sort. – mrdenny♦ Oct 6 '11 at 21:57
@MattM according to the article the problem is that a number of the replication components count on the host name of the distributor not changing, hence the problem. That's where clustering comes into play. Now it'll be interesting to see how AlwaysOn changes things (if it does) as availability groups can use a different network name than the actual instance. But that would probably require that you use Enterprise edition for your distributor which would be pretty expensive. – mrdenny♦ Oct 6 '11 at 22:07
@mrdenny Thanks! I found you're articles, they're pretty detailed, going through them now. – sharadov Oct 6 '11 at 23:28
show 4 more comments
up vote 1 down vote

No. See this MSDN article for why.

share|improve this answer
Ok is there any other way to make a replica of the current distributor,since mirroring of the distributor is not supported – sharadov Oct 5 '11 at 22:05
Check msdn.microsoft.com/en-us/library/ms151224.aspx for discussion about Log Shipping and Replication. Frankly though, I wouldn't do it. Any sort of fail over event that you could have could result in data loss. That in and of itself isn't a problem. Replication is very sensitive to all of the data being in sync. If you lose data, that goes out the window. If you want to protect yourself, get another server and just install SQL on it. Wait for a disaster and then re-configure replication with the new server as the distributor. – Ben Thul Oct 6 '11 at 11:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.