locked
Peer to Peer replication migration RRS feed

  • Question

  • Hello guys,

    We have a two MSSQL instances SQL 2008 R2 Enterprise with established peer to peer transactional replication between them.

    The solution is built as a scale-out for better performance. One of the servers is a read server and the second one is a write server. Backend app server uses the write server to load data and another frontend application & SharePoint server use the read server.

    There are tables that are enabled for replication and others that are not which results in different database size on each peer.

    We are planning to migrate the solution to two SQL 2008 R2 database consolidation instances built on a HA cluster active/active.

    Our plan is to proceed in the following way:

    1. Make sure there are no replicated transactions in progress and databases are in sync
    2. Script replication with all publications/subscriptions on both instances and make the necessary changes in advance to reflect the new instance names. Transfer logins.
    3. Disable all replication jobs on both peers
    4. Detach databases from both reader & writer instances and attach them to the two new SQL 2008 R2 instances. Fix any login/user mappings
    5. Execute the replication creation scripts that will create the distribution database, jobs, publications & subscriptions
    6. Re-sync databases

    I did not find many articles describing how an entire replication solution is supposed to be migrated. I would like to to check with you if the plan is correct or there is a better way to do this as well as any additional factors that must be taken into account.

    Thank you in advance.

    Wednesday, August 28, 2013 8:19 AM

Answers

  • If you're doing a full server migration then you might be able to do this another way...

    Basically if your new server has the same drives (letters and folders) as the existing then it's VERY easy...

    You simply install your clustered instance, make a note of the Master database location...  then you stop the SQL service on your both your existing and clustered instance, copy ALL data and log files (incl. system databases) to their same respective location on the new cluster.

    Once done, get the existing Master database and place it where the Clustered instance is looking for it (basically rename the clustered Master database files and replace them with the master files from your existing server).

    Then, when you start the SQL Server Service on the clustered instance it will pick up the Master database from your current server and, as the Master holds all information with regard to database file locations, security etc. your server should just carry on from where it left off...  as if nothing has changed.

    Hopefully that makes sense?

    That's how I've migrated numerous servers...  it's a lot quicker and more reliable than scripting everything and having to reconfigure all your logins, linked servers, replication sets etc.

    Even if you don't have the same drive letters it's possible...  you would just need to take the databases Offline on the current server, then change their location within the master database (examples of how to do this for system databases, although it's the same for normal databases, can be read here: http://www.theboreddba.com/Categories/system%20databases/How-to-Move-the-Model-and-MSDB-Databases-in-SQL-Server-2008-R2.aspx).  THEN stop the SQL Server Service...  move the Master, move all the data and log files of your databases to their new locations (which you told the Master database before shutting down)...  then it will use those new locations when you switch back on again.

    Hopefully this makes sense?

    It is a method I use a lot for server migration as it involves the least effort and configuration as you're effectively changing nothing.

    • Marked as answer by Stefan Boychev Thursday, August 29, 2013 11:33 AM
    Wednesday, August 28, 2013 11:32 AM

All replies

  • I just wondered if you could clarify a couple of points:Do your current instances contain more than the replicated database?  Ie. Are you migrating JUST a replicated database to the new cluster...  or is this effectively the entire instance you're migrating?

    That could make a difference as to how you could migrate and could potentially offer an easier route.

    Also, just for reference, but you will not be able to detach a replicated database...  SQL will not let you.  However, what you can do is the following:

    Take the database Offline.  Once offline you can then move the underlying data and log files as if you had detached it.  Then you can re-attach these to your new clustered instance.

    Once they have been attached again you can drop the original (and now offline) database.

    Wednesday, August 28, 2013 9:02 AM
  • Hello Kevin,

    Thanks for clarifying. Yes, there are other databases besides the ones that are replicated on the source instances that will be migrated as well and yes - we are moving the entire two instances, nothing is supposed to remain. So all settings, logins, linked servers, etc will be recreated on the target consolidation instances.


    Wednesday, August 28, 2013 10:33 AM
  • If you're doing a full server migration then you might be able to do this another way...

    Basically if your new server has the same drives (letters and folders) as the existing then it's VERY easy...

    You simply install your clustered instance, make a note of the Master database location...  then you stop the SQL service on your both your existing and clustered instance, copy ALL data and log files (incl. system databases) to their same respective location on the new cluster.

    Once done, get the existing Master database and place it where the Clustered instance is looking for it (basically rename the clustered Master database files and replace them with the master files from your existing server).

    Then, when you start the SQL Server Service on the clustered instance it will pick up the Master database from your current server and, as the Master holds all information with regard to database file locations, security etc. your server should just carry on from where it left off...  as if nothing has changed.

    Hopefully that makes sense?

    That's how I've migrated numerous servers...  it's a lot quicker and more reliable than scripting everything and having to reconfigure all your logins, linked servers, replication sets etc.

    Even if you don't have the same drive letters it's possible...  you would just need to take the databases Offline on the current server, then change their location within the master database (examples of how to do this for system databases, although it's the same for normal databases, can be read here: http://www.theboreddba.com/Categories/system%20databases/How-to-Move-the-Model-and-MSDB-Databases-in-SQL-Server-2008-R2.aspx).  THEN stop the SQL Server Service...  move the Master, move all the data and log files of your databases to their new locations (which you told the Master database before shutting down)...  then it will use those new locations when you switch back on again.

    Hopefully this makes sense?

    It is a method I use a lot for server migration as it involves the least effort and configuration as you're effectively changing nothing.

    • Marked as answer by Stefan Boychev Thursday, August 29, 2013 11:33 AM
    Wednesday, August 28, 2013 11:32 AM
  • Hello Kevin,

    Yes, the suggested approach makes perfect sense. However the target instances where we will migrate are already established and have settings applied which we would like to keep. The target instances are not brand new, they have one database on them and a few jobs created along with some logins and instance specific settings.

    When we migrate, we think to keep the target instance settings and add any settings and objects from the source instances whenever possible, hence my thoughts to move databases, then re-create any logins, linked servers, replication settings - distribution database, jobs, publications, subscriptions, etc.

    Considering the above, is a copy of the databases to the new servers + recreating the replication the easiest option?
    Thursday, August 29, 2013 6:07 AM
  • That's a shame...  it's much less fiddly if it's just a move not a script.  But in which case yes...  your approach would be the best.

    The only alterations I would make are my comment about taking the databases offline rather than detaching (as the latter won't work on a replicated database).

    But yes...  aside from that your list is fine.

    Thursday, August 29, 2013 6:17 AM
  • Thank you Kevin
    Thursday, August 29, 2013 11:33 AM
  • Stefan - I would urge you not to follow Kevin's advice.  The problem is that the master database contains metadata which is specific for that instance, primarily the server name, and linked server names as well as the password.

    Doing the fork lift approach will not work on a new instance. Granted you can drop the server and add the server (sp_dropserver, sp_addserver) to fix the server name issue, but then you need to deal with the linked server issues. The linked server that replication uses has its own password.

    Detaching database's are not the best way to move them either. The best way is a backup and restore. Detaching database may not result in a clean shutdown of  the database and if there is any corruption in the database it may show up on the restore. If you do detach - copy the database files to the new server, don't move them.

    With replication, especially with p2p you need to start with your subscriber via a backup. Granted there are ways to do this by manually cobbling together a subscriber, but a backup is the best way.

    I would move the replicated and other user database via a backup and restore, then recreate the publication and then do another backup to recreate the p2p subscription.

    Script out the logins and jobs (non replication jobs), and linked servers.

    Whatever you do, leave the existing publications and subscriptions intact. Don't disable or detach anything.

    This will make failback simple, if there are any problems.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Thursday, August 29, 2013 4:39 PM
    Answerer
  • Hello Hilary,

    Thank you. In our case both peers are in the same time publisher & subscriber, also databases are 20-30 per instance so backup and restore would take some time, hence my thoughts to go with offline/copy to target servers/attach, then re-create logins, jobs & replication.

    You are absolutely right about failing back - I will just offline the databases and disable jobs on source servers, in case anything goes wrong we will just turn all databases online and enable the jobs back.

    Tuesday, September 3, 2013 7:41 AM