Distribution Database Migration

Question

Distribution Database Migration

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

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

WAL SSChasing Mays Points: 615 More actions · Answer 1

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

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

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

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: 3540 More actions · Answer 6

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

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

WAL SSChasing Mays Points: 615 More actions · Answer 8

it works for the transactional replication. This solution has not been tested for the snapshot, merge, and P2P replications.

This reply was modified 3 years, 10 months ago by WAL.
This reply was modified 3 years, 10 months ago by WAL.
This reply was modified 3 years, 10 months ago by WAL.