Distribution Database Migration

  • WAL

    Mr or Mrs. 500

    Points: 594

    joe.tj (11/28/2014)


    Hi VU,

    I used SSMS to generate scripts of logreader,ditribution and snapshot on the old distributor.

    Then I modified this scritpt content with variables(etc $(Publisher),$(Subscriber)) .And remove the @job_id from scripts. Then I use this script in step 14.

    This afternoon,I found that UPDATE MSdistribution_agents didn't change the job_id in it.I tried to Update this table many times,but its job_id still the same.

    Hi Joe,

    If step14 is done correctly then proceed to step 15. You need to script out and create push subscriber jobs with correct values on the new distributor.

    Cheers,

  • joe.tj

    SSC Rookie

    Points: 26

    Thank you.

    I'll drop current testing replication,then rebuild it and try this solution again.

    Maybe I'll find something useful.:-D

  • stacylaray

    Newbie

    Points: 3

    This is not supported as the code is updating the replication metadata tables. If the distributor server name changes from S1 to S2, the only supported way is to drop and recreate all replication. The distribution server name is hardcoded in the jobs, system tables, agent profiles and many other places. There will be issues if the distribution server changes as part of the update.

    The below code is updating replication metadata tables and it’s not supported:

    10.Update MSpublisher_databases table with correct publisher id:

    update t

    set t.publisher_id =@srvid

    FROM [$(DistributionDB)].[dbo].[MSpublisher_databases] t

    Stacy Gray

    Technical Advisor

    Microsoft

    Stacy Gray (Sql 3T)
    Technical Advisor
    SQL Server Engine
    Microsoft

  • WAL

    Mr or Mrs. 500

    Points: 594

    stacylaray (3/25/2015)


    This is not supported as the code is updating the replication metadata tables. If the distributor server name changes from S1 to S2, the only supported way is to drop and recreate all replication. The distribution server name is hardcoded in the jobs, system tables, agent profiles and many other places. There will be issues if the distribution server changes as part of the update.

    The below code is updating replication metadata tables and it’s not supported:

    10.Update MSpublisher_databases table with correct publisher id:

    update t

    set t.publisher_id =@srvid

    FROM [$(DistributionDB)].[dbo].[MSpublisher_databases] t

    Stacy Gray

    Technical Advisor

    Macrosoft

    The code is valid and experienced SQL Server DBAs will save time, energy and nerves when distribution db needs to be moved to a new sql instance without downtime. Your comment shows limited knowledge of SQL Server replication and it should be removed. I have spoken to Microsoft and they will make it supported.

    Regards,

    V.

  • Hussain_Patel

    Newbie

    Points: 7

    Hi

    Thanks a lot for the distribution database migration article.

    I have few questions to ask.

    1. Is that really required to script out replication jobs including log reader agent and snapshot agent jobs, however by restoration msdb database, we can regain all jobs right.

  • humbleDBA

    Hall of Fame

    Points: 3460

    WAL (3/29/2015)


    stacylaray (3/25/2015)


    This is not supported as the code is updating the replication metadata tables. If the distributor server name changes from S1 to S2, the only supported way is to drop and recreate all replication. The distribution server name is hardcoded in the jobs, system tables, agent profiles and many other places. There will be issues if the distribution server changes as part of the update.

    The below code is updating replication metadata tables and it’s not supported:

    10.Update MSpublisher_databases table with correct publisher id:

    update t

    set t.publisher_id =@srvid

    FROM [$(DistributionDB)].[dbo].[MSpublisher_databases] t

    Stacy Gray

    Technical Advisor

    Macrosoft

    The code is valid and experienced SQL Server DBAs will save time, energy and nerves when distribution db needs to be moved to a new sql instance without downtime. Your comment shows limited knowledge of SQL Server replication and it should be removed. I have spoken to Microsoft and they will make it supported.

    Regards,

    V.

    Hi Val,

    Not if you're responding to this any more, but with regard to your reply to stacylaray, do you have written confirmation (letter/email) from Microsoft that they will, or now do, support your technique - especially when updating the replication metadata tables? I haven't been able to find anything from Microsoft to say that they support this technique, or have incorporated their own solution based on your technique, and unless you can back up your statement "I have spoken to Microsoft and they will make it supported" then it means nothing - as I could also say that I have spoken to Microsoft and they have said they will not support it.

    If you do have it in writing, then please share - redacting sensitive info as necessary. It would give more creedence to your statement.

    Thanks

  • d-i-m-on

    Old Hand

    Points: 321

    Be careful!
    This only works for pull subscription, because you can not change Distribution server for the push!

Viewing 7 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply