Distribution Database Migration

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

  • 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

  • erase

  • 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

  • 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.

  • 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.

  • Hope all works,

  • 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!

  • 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.

  • 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.

  • 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?

  • 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

  • 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.

  • 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.

  • 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