"EoRaptor013" <rchrismon@gmail.com> wrote in message
news:1156953271.878995.8500@p79g2000cwp.googlegroups.com...
> Does anybody know of an example script to quiesce a peer-to-peer
> replication, alter a view, make sure the alterations are propagated and
> re-activate the replication?
>
> I gather I have to execute sp_requestpeerresponse and
> sp_helppeerresponse, then alter the table. Trying to build up to the
> correct solution, I started by just executing the two stored
> procedures:
>
> declare
> @rq_id int
>
> exec sp_requestpeerresponse
> @publication = 'EnduranceProd20060821',
> @request_id = @rq_id
>
> exec sp_helppeerresponses
> @request_id = @rq_id
>
> The result set from sp_helppeerresponses, however, is consistently
> empty. As I read it, this means that I didn't get any responses from
> the other servers in the peer-to-peer replication. Yet, replication is
> working just fine, I have less than 5 seconds latency, and I can insert
> trace tokens from SQL Management Studio with 5 second response or less.
> So, what am I doing wrong?
>
> Also, this is a very nasty table; there's a case statement with nearly
> 200 clauses in it to capture a set of business rules. A SQL query
> created from the view definition runs in 10 or 15 seconds but the last
> time I tried to alter this table, while it was included in replication,
> timed out after 8 hours! Makes me think something is not right.
>
> Any thoughts on either issues would be greatly appreciated.
>
> Randy
>

Hilary Cotter wrote:
> I've fought with this on and off for the past couple of days. I think you
> need to replicate schema objects using snapshot replication as this type of
> replication will replicate schema changes to schema objects each time the
> agent runs.
>

"EoRaptor013" <rchrismon@gmail.com> wrote in message
news:1157218446.037606.51090@74g2000cwt.googlegroups.com...
> Hilary Cotter wrote:
>> I've fought with this on and off for the past couple of days. I think you
>> need to replicate schema objects using snapshot replication as this type
>> of
>> replication will replicate schema changes to schema objects each time the
>> agent runs.
>>
>
> Thank you for looking into it.
>
> Does peer-to-peer replication even use snapshots? Seems like I read an
> MS white paper that says peer-to-peer needs more info than available in
> a snapshot and, because of the peer-to-peer topology, a snapshot isn't
> useful. If I can track the paper down, I'll post it.
>
> In any event. I was able to manage it through Management Studio but it
> was waaay ugly. In the replication monitor, I stopped distribution for
> each publisher. Then I opened the properties for each publisher and
> dropped the view. I then re-enabled distribution and let the system run
> until there were no more transactions listed in the monitor. Then I
> altered the view on all three publishers -- manaully. I again stopped
> distribution on all servers, added the view back into the publication,
> and restarted the distributors. IMHO, this just isn't the way to do it!
>
>
> I've been wrong in my interpretation of the BOL plenty of times before
> but I'm convinced that it says you can directly make schema changes:
> http://msdn2.microsoft.com/en-us/library/ms146867.aspx
>
> Again, thanks.
>

"EoRaptor013" <rchrismon@gmail.com> wrote in message
news:1157218446.037606.51090@74g2000cwt.googlegroups.com...
> Hilary Cotter wrote:
>> I've fought with this on and off for the past couple of days. I think you
>> need to replicate schema objects using snapshot replication as this type
>> of
>> replication will replicate schema changes to schema objects each time the
>> agent runs.
>>
>
> Thank you for looking into it.
>
> Does peer-to-peer replication even use snapshots? Seems like I read an
> MS white paper that says peer-to-peer needs more info than available in
> a snapshot and, because of the peer-to-peer topology, a snapshot isn't
> useful. If I can track the paper down, I'll post it.
>
> In any event. I was able to manage it through Management Studio but it
> was waaay ugly. In the replication monitor, I stopped distribution for
> each publisher. Then I opened the properties for each publisher and
> dropped the view. I then re-enabled distribution and let the system run
> until there were no more transactions listed in the monitor. Then I
> altered the view on all three publishers -- manaully. I again stopped
> distribution on all servers, added the view back into the publication,
> and restarted the distributors. IMHO, this just isn't the way to do it!
>
>
> I've been wrong in my interpretation of the BOL plenty of times before
> but I'm convinced that it says you can directly make schema changes:
> http://msdn2.microsoft.com/en-us/library/ms146867.aspx
>
> Again, thanks.
>