July 3, 2012 at 8:08 am
We are currently migrating some database servers to new hardware. My current process is to build the new servers with SQL Server 2008 R2 and migrate all SQL Server settings/configuration information. Last week I performed a migration on a group of servers that had Transactional Replication setup. I thought I would be able to script out replication on the legacy server and then apply the script on the new server as a step in the migration. That worked, but I had to initialize the replication after I applied the script before replication would begin. I was thinking since the database instance name was the same and I restored a full backup and then differential that I would be able to apply the script and replication would just pickup where it left off.
It was not that big of a deal last week, as the databases being replicated were small. However, next weeks migration has almost a TB size database with replication and I do not want to have to re-initialize that DB.
Does anyone know the steps I can perform to migrate Transactional Replication from legacy server to new server without having to re-initialize? I would prefer not to have to tinker around with migrating Master and MSDB if at all possible.
Any helpful hints or tricks would be greatly appreciated.
July 3, 2012 at 8:13 am
We do the same procedure without initializing. We sync missing data manually with a merge, SQL Data Compare (redgate), or with a simple insert and join if the data allows for it.
Jared
CE - Microsoft
July 3, 2012 at 8:21 am
I perform regular migrations like this without issue.
How did you script the publication did you make any modifications?
Are they push/pull subscribers?
Did you drop replication prior to backing up/restoring? (this will potentially cause problems)
I logship the publisher databases or subscriber databases to speed up the process.
Publisher is little more complex as cleaner to run sp_removedbreplication and redo replication on the new server.
I always set up the subscriptions using @sync_type='none' but you could use @sync_type='replication support only'. This prevents the need to reinitialise the subscribers.
July 3, 2012 at 8:39 am
My current process is as follows:
1.) "Generate Scripts" for each local publication
2.) Day of migration I delete all location publications (Verify they are removed from distributor)
3.) Perform my differential backups and apply to new server fulls that are in no recovery mode
4.) Apply scripts created in step #1 without making any changes
5.) Verify publication is created with subscriber and that jobs are created on distributor
6.) Check replication monitor and it shows that Log Reader is running and nothing to replicate and snapshot show not running
These are all push transactional replication publications.
July 4, 2012 at 12:29 am
sqlsponge2 (7/3/2012)
4.) Apply scripts created in step #1 without making any changes
That's likely the key.
By default, generate scripts will create a script for subscriptions as they were initially setup which normally has parameters similar to this.
@sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
With 'automatic', the subscription will need initialising as a brand new subscription.
You should change it to this (doesnt generate replication procs at subscriber)
@sync_type = N'none', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
or this (does generate replication procs at subscriber)
@sync_type = N'replication support only', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
July 4, 2012 at 12:39 am
Worth noting that, I'm assuming there are no changes going on in the data at the time of migration. Else you will need to do some data validation/comparisons etc.
July 4, 2012 at 4:43 am
I think I will tinker around with the "replication support only" setting. Thanks for the information.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply