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?
-
For HA/DR the distribution database can be deployed on a clustered instance, but not in mirrored DB.– Remus RusanuOct 5, 2011 at 22:00
-
1+1 Interesting idea. A dedicated distributor should be OK to mirror but I've never tried.– gbnOct 5, 2011 at 22:13
Add a comment
|
2 Answers
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.
-
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 MOct 6, 2011 at 14:45
-
@mrdenny Would you happen to know of an article which lists the steps to cluster the distributor.– sharadovOct 6, 2011 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.– mrdennyOct 6, 2011 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.– mrdennyOct 6, 2011 at 22:07
-
@mrdenny Thanks! I found you're articles, they're pretty detailed, going through them now.– sharadovOct 6, 2011 at 23:28
No. See this MSDN article for why.
-
Ok is there any other way to make a replica of the current distributor,since mirroring of the distributor is not supported– sharadovOct 5, 2011 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 ThulOct 6, 2011 at 11:50