April 26, 2012 at 9:44 am
I was wondering how to implement HA for a merge replication subscriber.
Given that:
* mirroring is not an option (by desing)
* clustering still has the storage as a single point of failure
... what else could be a resonable option?
Thanks for your help and opinions.
-- Gianluca Sartori
April 26, 2012 at 11:28 am
If you won't mind novice’s advise... http://msdn.microsoft.com/en-us/library/ms151224.aspx
April 26, 2012 at 11:35 am
Thanks for the link, Dev.
Unfortunately that page describes how to set up log shipping on the publisher, but I'm looking for HA on the subscriber.
I haven't tried log shipping, but I don't expect it to be any different from mirroring as far as replication is concerned.
-- Gianluca Sartori
April 26, 2012 at 11:47 am
Please correct me if I am wrong, isn’t merge replication publishers & subscribers interchangeable and both servers can play both roles (primary & secondary).
May I know why you are not considering Mirroring at first place?
April 27, 2012 at 2:49 am
Gianluca Sartori (4/26/2012)
* mirroring is not an option (by desing)
Why? You can have two servers using mirroring and have the publisher use a DNS alias for the subscriber name. Change the IP on the alias in the event of failure. I know names are a bit finicky for the publisher but I dont believe this is necessarily a problem for the subscribers
Gianluca Sartori (4/26/2012)
* clustering still has the storage as a single point of failure
Only in the traditional sense. You can install a SQL 2008 cluster without a SAN using a thirdy party disk mirroring tool or SAN replication similar to deploying a geo-cluster. So this could work.
http://clusteringformeremortals.com/2009/10/07/step-by-step-configuring-a-2-node-multi-site-cluster-on-windows-server-2008-r2-%E2%80%93-part-3/[/url]
Alternatives? Why not have two merge subscribers with the same data on each. Other than logshipping you've already mentioned all HA options.
April 27, 2012 at 10:34 am
Gianluca Sartori (4/26/2012)
I was wondering how to implement HA for a merge replication subscriber.Given that:
* mirroring is not an option (by desing)
* clustering still has the storage as a single point of failure
... what else could be a resonable option?
Thanks for your help and opinions.
I am not sure if I understood your requirement correctly. Please elaborate if you don't mind.
TA.
Regards,
SQLisAwe5oMe.
April 28, 2012 at 7:09 am
Dev (4/26/2012)
Please correct me if I am wrong, isn’t merge replication publishers & subscribers interchangeable and both servers can play both roles (primary & secondary).May I know why you are not considering Mirroring at first place?
Yes, but replication is not HA. I could replicate the subscriber to another subscriber, but that would break the propagation of data if the first subscriber has issues.
-- Gianluca Sartori
April 28, 2012 at 7:14 am
MysteryJimbo (4/27/2012)
Gianluca Sartori (4/26/2012)
* mirroring is not an option (by desing)Why? You can have two servers using mirroring and have the publisher use a DNS alias for the subscriber name. Change the IP on the alias in the event of failure. I know names are a bit finicky for the publisher but I dont believe this is necessarily a problem for the subscribers
Good idea. I will give it a try.
Gianluca Sartori (4/26/2012)
* clustering still has the storage as a single point of failureOnly in the traditional sense.
Agreed. But that doesn't convince my customer. I will have to find something else.
Why not have two merge subscribers with the same data on each.
That was my first idea, but bandwidth is very low and I have to preserve it.
Other than logshipping you've already mentioned all HA options.
:hehe::hehe: Quite true!
AlwaysOn is the only option I have not tried, but that would also mean upgrading to 2012.
Thanks for your suggestions!
-- Gianluca Sartori
April 28, 2012 at 7:15 am
SQLCrazyCertified (4/27/2012)
Gianluca Sartori (4/26/2012)
I was wondering how to implement HA for a merge replication subscriber.Given that:
* mirroring is not an option (by desing)
* clustering still has the storage as a single point of failure
... what else could be a resonable option?
Thanks for your help and opinions.
I am not sure if I understood your requirement correctly. Please elaborate if you don't mind.
TA.
Sure! What exactly is unclear to you?
-- Gianluca Sartori
May 1, 2012 at 11:12 pm
Several comments here:
1. You cannot use DNS names with replication. Replication requires you use the actual server name.
2. Merge replication subscribers and publishers are not interchangeable. Data is merged bi-directional, but the publisher is the master in the relationship.
3. The SQLCAT (SQL Customer Advisory Team) team wrote a good white paper on mirroring a replication subscriber. Failover is not supported for subscribers, but the paper details how you can manually recover the mirror and quickly set it up as a new replication subscriber using intialize from LSN. You could also do it using initialize from backup. Go to http://www.sqlcat.com and search their scalability tag for the white paper.
4. Clustering is the best option for a subscriber. You can get over the pitfall of having a single point of failure by using SAN replication to create a geo-cluster.
5. You're correct. Log shippign would have the same limitations as mirroring for a replication subscriber.
May 2, 2012 at 7:27 am
Thank you Robert,
I guess I will stick to clustering, then.
I found that whitepaper and I'm in the process of reading it. It's a bit of manual work, but could be worth a try.
Thanks again,
Gianluca
-- Gianluca Sartori
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply