February 10, 2009 at 2:56 am
Hi there,
I have been looking at the possibilities of peer to peer replication for geo-replication purposes and found a very interesting document from Microsoft and how they have done it themselves.
http://msdn.microsoft.com/en-us/library/dd263442.aspx
The information in the whitepaper is really good, and tells you that SQL 2008 and Windows 2008 are really much better products for this sort of thing and please buy it now 😉
The part that interested me the most was that a pull subscription is far faster than a push subscription (explanation is in the appendix of the whitepaper). This is quite important when doing geo-replication across continents as we are planning.
My problem is, when using the new wizard to setup peer-to-peer replication push subscriptions are created and not pull subscriptions.
Does anyone know a way of changing this in a user friendly way, or am I best off creating the replication and then creating pull subscriptions by hand?
Any suggestions or ideas are welcome!
Many thanks in advance
GermanDBA
Regards,
WilliamD
February 17, 2009 at 12:50 am
Hi again,
just in case anyone is interested, I got in touch with the author of the whitepaper and he has this exact topic in an up-coming blog!
He also asked me to propose a change request at microsoft connect to have the wizard extended to offer the option of creating either push or pull subscriptions from the get-go.
Until that happens, you have to script out the entire thing by hand.
I'll post the link to the blog here as soon as it is out.
Regards,
WilliamD
April 1, 2009 at 9:48 am
I'd be VERY interested in knowing when the blog has been posted. I have an identical issue. Thanks.
Sean
April 1, 2009 at 10:36 am
Also, the article seems to suggest the benefits are based on proximity of the distributor to the subscriber so I'm wondering if using Node 2 as a remote distributor for Node 1 and Node 1 as a remote distributor for Node 2 (both push subscriptions) might achieve the same performance gains Microsoft will lead us to believe can be gained from a Pull subscription but without actually having to figure out how to implement a Pull subscription. Does anyone think that makes sense? Has anyone tried this?
Cheers.
Sean
April 2, 2009 at 1:10 am
Hi Sean,
doesn't peer-to-peer do this anyway? IIRC the distribution is set to be performed on the source machine as default. I would suggest that you try it out if possible.
BTW:
The answer to my change request on CONNECT was : http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=415096
"Good idea, but not for SQL 2008".
That is quite a bummer, but what can you do. It is not something that will get lots of pressure like SP3 for 2005 did, so I will have to be patient and use manual config until the wizard is created.
Still no word on the blog though, you can keep an eye out for anything new here: http://blogs.msdn.com/sqlperf/
Regards,
WilliamD
April 2, 2009 at 9:55 am
Thanks for the reply.
GermanDBA (4/2/2009)
Hi Sean,doesn't peer-to-peer do this anyway? IIRC the distribution is set to be performed on the source machine as default. I would suggest that you try it out if possible.
Yes, the default for push is that distribution is done on the source machine, for pull it is performed on the destination machine. It appears to me that the article is saying that the reason Pull is faster is that it is on the destination machine. I'm thinking that I can achieve the same effect by using the destination server as my distributor for my push subscription. I definitely plan to try this but have other major issues I need to resolve first.
GermanDBA (4/2/2009)
BTW:The answer to my change request on CONNECT was : http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=415096
"Good idea, but not for SQL 2008".
That is quite a bummer, but what can you do. It is not something that will get lots of pressure like SP3 for 2005 did, so I will have to be patient and use manual config until the wizard is created.
The more I look at Peer-toPeer replication the more I get the impression that it is half-finished. On the surface it would appear to be ideal for my requirements but once I start to dig deeper I am hitting lots of obstacles such as this one.
GermanDBA (4/2/2009)
Still no word on the blog though, you can keep an eye out for anything new here: http://blogs.msdn.com/sqlperf/
Thanks for the link.
Sean
April 6, 2009 at 4:51 am
Just to say that my idea about configuring the publisher to use the distribution database on the subscriber is fatally flawed when (as in my case) you have more than 2 nodes. I therefore need to try script out a pull subscription. Any help in that would be gratefully received.
Cheers.
Sean
April 6, 2009 at 6:01 am
Graspnext (4/6/2009)
Just to say that my idea about configuring the publisher to use the distribution database on the subscriber is fatally flawed when (as in my case) you have more than 2 nodes. I therefore need to try script out a pull subscription. Any help in that would be gratefully received.Cheers.
Sean
Sean,
I started with this a while back, but stopped due to a lack of time.
One way I looked at was to set up the p2p replication between two nodes and then script out the replication. This spits out an entire list of t-sql that would do the job, with comments on when and where to run each step.
I wanted to create a set of sprocs to do this with variables for the source, target etc. but have not had the chance to do this. I will be doing so in the next few weeks/months as we continue with our plans to geo-replicate.
I will let you know, if and when I get anything up and running.
GermanDBA
Regards,
WilliamD
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply