Distribution Database Migration

Question

Post reply

Distribution Database Migration

WAL

SSChasing Mays

Points: 615
More actions
November 20, 2014 at 12:02 am

#292204

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

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

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

Vj Pidikiti SSC Rookie Points: 26 More actions · Answer 1

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 More actions · Answer 2

Techunaut

SSC Rookie

Points: 49

November 20, 2014 at 6:32 am

#1759813

erase

Divine Flame SSCoach Points: 15943 More actions · Answer 3

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 SSChasing Mays Points: 615 More actions · Answer 4

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 SSChasing Mays Points: 615 More actions · Answer 5

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 SSChasing Mays Points: 615 More actions · Answer 6

WAL

SSChasing Mays

Points: 615

November 20, 2014 at 1:50 pm

#1759968

Hope all works,

Misha_SQL SSCertifiable Points: 5407 More actions · Answer 7

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: 75999 More actions · Answer 8

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.

LinkedIn Profile

WAL SSChasing Mays Points: 615 More actions · Answer 9

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 More actions · Answer 10

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 More actions · Answer 11

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 SSChasing Mays Points: 615 More actions · Answer 12

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 SSChasing Mays Points: 615 More actions · Answer 13

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 More actions · Answer 14

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.