Results 1 to 3 of 3
  1. #1
    Join Date
    May 2008
    Location
    Cambridge
    Posts
    26

    Unanswered: Convert APL table to DOL in replicated environment

    Hi folks,

    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?

    Any insight appreciated.

    Bob
    Bob Holmes
    Sybase ASE/Replication Server Administrator
    Digital Data Safe - managed database services
    Email: bob.holmes@ddsafe.co.uk

  2. #2
    Join Date
    May 2008
    Location
    Cambridge
    Posts
    26
    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.

    BR
    Bob
    Bob Holmes
    Sybase ASE/Replication Server Administrator
    Digital Data Safe - managed database services
    Email: bob.holmes@ddsafe.co.uk

  3. #3
    Join Date
    Jun 2009
    Location
    South Africa
    Posts
    33
    Harq,

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •