Distribution Database Migration

  • WAL

    Mr or Mrs. 500

    Points: 594

    Comments posted to this topic are about the item Distribution Database Migration

  • Vj Pidikiti

    SSC Rookie

    Points: 26

    Hi Val,

    This approach is interesting. I would like to ask a quick question.

    How can you backup and restore the distribution database over to a different server? It would get restored as an user database but not as a system database, right?

    Regards,

    VJ

    Vijay Pidikiti

  • Techunaut

    SSC Rookie

    Points: 49

    erase

  • Divine Flame

    SSCoach

    Points: 15941

    I have not read the article yet, however, I would like to know what is so wrong with this article that it is rated so poorly?

    Anybody who has rated could you please raise your concern so that I can save my time & not read this article?


    Sujeet Singh

  • WAL

    Mr or Mrs. 500

    Points: 594

    ninety3cents (11/20/2014)


    Hey Val,

    I find this a little more complicated then it needs to be. Why not just initialized from a back up? That's how i've done it and its been rather easy.

    Hija,

    yes, it looks complicated, but it works. There are MS limitations and restrictions for manual subscription initialization using a db backup: http://msdn.microsoft.com/en-GB/library/ms151246.aspx.

    http://msdn.microsoft.com/en-us/library/ms147834.aspx

    Also, it can be impractical to restore publisher db on all subscribers causing downtime and loss of data.

    V.U.

  • WAL

    Mr or Mrs. 500

    Points: 594

    Hi Vj,

    If you try provided scripts in the article with correct server names then it should work for you.

    KEEP_REPLICATION should do the trick.

    V.U.

  • WAL

    Mr or Mrs. 500

    Points: 594

    Hope all works,

  • Misha_SQL

    SSCertifiable

    Points: 5386

    I liked this article! This method could come in handy sometimes. One question regarding the part, which deals with linked servers. What exactly are you doing when you " get one missing linked server on the new distributor server;"? Which linked server from the CURRENT_DISTRIBUTOR are we trying to re-create on the new distributor? What if there are more linked servers defined on the CURRENT_DISTRIBUTOR for some reason? Getting TOP 1 seems kind of arbitrary.

    select top 1 @server_name=srvname from [$(CURRENT_DISTRIBUTOR)].master..sysservers

    except

    select srvname from master..sysservers

    Thank you!

  • David.Poole

    SSC Guru

    Points: 75200

    Nice to see you writing Val. Shame the rest of the guys at Moneysupermarket haven't lost their writing cherry. You all have a huge amount of knowledge to share.

  • WAL

    Mr or Mrs. 500

    Points: 594

    Misha_SQL (11/21/2014)


    I liked this article! This method could come in handy sometimes. One question regarding the part, which deals with linked servers. What exactly are you doing when you " get one missing linked server on the new distributor server;"? Which linked server from the CURRENT_DISTRIBUTOR are we trying to re-create on the new distributor? What if there are more linked servers defined on the CURRENT_DISTRIBUTOR for some reason? Getting TOP 1 seems kind of arbitrary.

    select top 1 @server_name=srvname from [$(CURRENT_DISTRIBUTOR)].master..sysservers

    except

    select srvname from master..sysservers

    Thank you!

    Hija,

    yes, the provided script will create only one linked server. if your current distributor has more than one subscriber then you will need to run it few times to make sure all linked servers are created on the new distributor instance. You can also script linked servers out of current distributor and create them on the new one.

    regards,

    V.U.

  • joe.tj

    SSC Rookie

    Points: 26

    Hi,

    I make a test on a push replication following you steps.but it doesn't work.

    1.the distribute job occurs error msg:Agent message code 20044. The subscription to publication 'PubTest' has expired or does not exist.

    2.the repl monitor can't find the subscriber

    3.when I right click the subscription to view sync status, it appears error msg:To synchronize, you must first create an agent job for this subscription.

    Do you have any ideas about these?

  • Anders_ss

    Newbie

    Points: 8

    I'm been using this article as reference when I tested a migration of the Distribution database the last week. It is very good and informative, but it has some limitations. It probably works well with transactional replication, but lack some scripts for merge replication.

    In order to make it working on my test enviroment using only merge replication with pull subscription from SQLexpress clients I had to update the publisher_id in the following tables:

    [distribution].[dbo].[MSsnapshot_agents]

    [distribution].[dbo].[MSmerge_subscriptions]

    [distribution].[dbo].[MSmerge_agents]

    [distribution].[dbo].[MSmerge_identity_range_allocations]

    I can now sync my clients again, and they did not have to download the snapshot again in order to make it work.

    Anders

  • WAL

    Mr or Mrs. 500

    Points: 594

    joe.tj (11/27/2014)


    Hi,

    I make a test on a push replication following you steps.but it doesn't work.

    1.the distribute job occurs error msg:Agent message code 20044. The subscription to publication 'PubTest' has expired or does not exist.

    2.the repl monitor can't find the subscriber

    3.when I right click the subscription to view sync status, it appears error msg:To synchronize, you must first create an agent job for this subscription.

    Do you have any ideas about these?

    Hi,

    This solution should work with push/pull subscribers.

    make sure all linked servers have been created.

    what have you done in step14?

    V.

  • WAL

    Mr or Mrs. 500

    Points: 594

    Anders_ss (11/27/2014)


    I'm been using this article as reference when I tested a migration of the Distribution database the last week. It is very good and informative, but it has some limitations. It probably works well with transactional replication, but lack some scripts for merge replication.

    In order to make it working on my test enviroment using only merge replication with pull subscription from SQLexpress clients I had to update the publisher_id in the following tables:

    [distribution].[dbo].[MSsnapshot_agents]

    [distribution].[dbo].[MSmerge_subscriptions]

    [distribution].[dbo].[MSmerge_agents]

    [distribution].[dbo].[MSmerge_identity_range_allocations]

    I can now sync my clients again, and they did not have to download the snapshot again in order to make it work.

    Anders

    Cool...good point. I shall add scripts and more info for merge,snapshot and peer-to-peer replication in future.

  • joe.tj

    SSC Rookie

    Points: 26

    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.

Viewing 15 posts - 1 through 15 (of 22 total)

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