Unanswered: Convert APL table to DOL in replicated environment
I'm looking to convert what is currently an allpages table to datarows. The procedure itself for doing this is fairly straight forward, however, we're working in a warm-standby replicated environment. I need to set the db option "select into/trunc log" on for the change which sets alarm bells ringing in my head.
Is this going to break replication? Or is repserver going to attempt a 6000 deletes followed by 6000 inserts?
I guess the safest option would be to 'set replication off' during the change and then repeat it at the replicate?
Ah, this might be a void question.... I guess since I have to set 'select into' on then ASE is actually going to perform a 'select into' behind the scenes which is definitely going to break replication. So, I guess I need to 'set replication off' and repeat the process at the replicate.
I think I've answered my own question, but if anyone can see that I've overlooked something then I'd appreciate the input.
There should be no problem doing the "alter table ... lock datarows / datapages" since the 'select into' operation will not be replicated. You can use 'set replication off' if you want to.
You will have to do it on both servers, as you suspect. You might want to keep the repagent suspended or Repserver down until you have completed the step on both servers just to ensure you miss no new data changes that might occur on the primary.
If you use semi-automatic warm standby, or if you have other targets that uses your normal warm standby's logical connection as a source, they would require the source table to be explicitly marked for replication. Check the following if that's the case :
After you do the select into, the resulting table's object id will be different from the original, and so will it's replication status. If that's the case, ensure you do the normal sp_setreptable ( remember owner_on if required ) and possibly sp_setrepcol for text/image before you resume data changes on the primary.
For automatic warm standby, there should be no issues.