May 29, 2012 at 8:10 am
SQL Server 2008R2 Transactional Replication - Move SubscriberDB - Push Subscription
I have a requirement to move a subscriber database to a new server outside of a system outage i.e. I cannot stop new transaction from loading into the publisher database.
So far I have attempted to stop the distribution agent and let all un-replicated commands replicate to the subscriber database at Server1. Then backup and restore the subscriber database to Server2. I have then created a new subscription from Server2 to the existing publication.
This works but only transactions created from that point forward are replicated to the Server2 subscriber database. I also require all the old transactions built up at the distributor database destined to only go to Server1.
Is there a replication command available to update the destination of existing transactions at the distributer to a new subscriber. subscriber_DB? I’ve looked at both sp_changesubscription and sp_changesubscriber but I don't think these do what I need
There is 1 publication with multiple articles. The publication is currently only subscribed to by one database at Server1.
May 30, 2012 at 4:22 am
why not create a new subscriber to Server 2 then remove it from Server 1?
June 1, 2012 at 1:57 pm
If the table is not too large I would reinitialize the subscription on Server2 with a new snapshot. This will allow it to be synced together.
The distributor will not recognize the new subscription as needing old transactions as it needs to be initialized when the new subscription is created either manually or by a snapshot from sql server. There is no easy way to fool the distributor. This is mainly because it is a different server regardless of the database.
One thing we have done is give the server a C-Name on the network or the new server the same name and rename the old. This allows replication to continue with no other changes. You could try to manipulate the server name for the linked server that the distributor uses or create an alias on the distribution server pointing it to Server2
If it is a very large table and snapshot might take too long what we have done is bcp the data out and then bcp the data in to the new table via cmd line (this case happen to be sql to oracle) then created the subscription as already initialized. This should also only be done in a down time where no transactions would occur as if you miss some then things can get really broke.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply