move distribution database from local to remote server

Question

move distribution database from local to remote server

KATHLEEN Y ZHANG

Hall of Fame

Points: 3534
More actions
June 26, 2008 at 1:18 pm

#193255

Hi, I am wondering if someone have the experience of migrating distributor to a remote server they would like to share.
Currently I have a server that serves as both publisher and distributor of transactional replication. I am planning to off load the publisher and move the distribution database to a remote server. I have a few questions:
1. Can I configure the remote distributor while keeping replication running?
2. Do I have to re-snapshot everything once new distributor is set up?
3. I have 4 different databases on the publisher that replicate, does it make sense to set up four different distribution databases for performance purpose? Also, for each distribution database, do I need multiple database files and log files for performance also?
Thanks in advance!

Viewing 6 posts - 1 through 5 (of 5 total)

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

noeld SSC Guru Points: 96590 More actions · Answer 1

KATHLEEN Y ZHANG (6/26/2008)
Hi, I am wondering if someone have the experience of migrating distributor to a remote server they would like to share.
Currently I have a server that serves as both publisher and distributor of transactional replication. I am planning to off load the publisher and move the distribution database to a remote server. I have a few questions:
1. Can I configure the remote distributor while keeping replication running?
2. Do I have to re-snapshot everything once new distributor is set up?
3. I have 4 different databases on the publisher that replicate, does it make sense to set up four different distribution databases for performance purpose? Also, for each distribution database, do I need multiple database files and log files for performance also?
Thanks in advance!

Very interesting questions, all of them 🙂

1. No. You will have to reconfigure it from scratch.

2. Because you have to start over you must re-snapshot BUT if you think it would be too expensive you could use initialization with backup

3. IF currently you have all publications registered in one database on the dist/publisher, the separated distributor should be fine. I have seen cases in which some databases overwhelm the rest and in that case you could create different distribution databases to prevent lock escalation on one replication set affect the other. Normally this is just extreme and probably not needed.

* Noel

KATHLEEN Y ZHANG Hall of Fame Points: 3534 More actions · Answer 2

Thanks Noel. Very helpful!

More questions:

1. You are right, I just tried to add the new distributor and I got 'The server 'abcd' is already defined as a Distributor. To reconfigure the server as a Distributor, you must first uninstall the exisiting Distributor. Use the stored procedure sp_dropdistributor, or use the Disable Publishing and Distribution Wizard.'

2. we have some timestamp columns that were used for migration from access to sql server, so I need to drop them in order to do initilization from backup.

3. I am not exactly sure about the lock escalation scenario you mentioned, could you expand on it?

Thank you!

noeld SSC Guru Points: 96590 More actions · Answer 3

KATHLEEN Y ZHANG (6/27/2008)
Thanks Noel. Very helpful!
More questions:
1. You are right, I just tried to add the new distributor and I got 'The server 'abcd' is already defined as a Distributor. To reconfigure the server as a Distributor, you must first uninstall the exisiting Distributor. Use the stored procedure sp_dropdistributor, or use the Disable Publishing and Distribution Wizard.'
2. we have some timestamp columns that were used for migration from access to sql server, so I need to drop them in order to do initilization from backup.
3. I am not exactly sure about the lock escalation scenario you mentioned, could you expand on it?
Thank you!

KATHLEEN,

2. You don't need to drop them but you will have to choose if you want the destination datatype be converted to binary(8) (default) or not. Initialization with backup is very efficient because it uses it to locate the last valid LSN from where repliaction should pickup.

3. There are two tables "shared" by all agents in the distribution database. MS_replTransactions and MS_replcommands. If one of the databases get updated heavily these two tables get very busy and the Agents that "other" publications may use could get blocked (by locks on those tables) due to so much activity on the heavily loaded one.

hope is clear

* Noel