locked
Transactional Replication RRS feed

  • Question

  • We are planning for migration project from sql 2005 to sql 2012, For moving this huge data we planned for using Transactional replication From server A (sql 2005) to Server B(Sql 2012). On the Go -live during the cut off time i am thinking to script out the replication and then resume it and take a tail log backup and want to apply this on to the Server B(SQL 2012). But we have another replication which is going from Server B to Server C both on sql 2012. What happens when i restore the tail log backup from server A and apply it on the server B. Does the replication still work, Do i have to follow any steps

    Please help

    Thanks 

    Vamshi

    Tuesday, May 17, 2016 3:15 PM

Answers

  • Log shipping should be the best fit for this, but with server b being published you are not goign to be able to do any sort of a restore.

    On cutover you need to run your distribution agents on Server A until they display an idle message. You know now that all the transactions on Server A have made their way to server B.  Then you can decommission Server A and Server B will continue to server as a publisher to Server C.

    The tail of the log could only be used if you were log shipping and wanted to shut down your database on the source - Server A and then replay the last commands in the log on Server B.

    But to replicate you can't have a database in read only or standby mode so this is not an option for you.

    The best way to do it would be to replicate from Server A to Server C. Log ship from Server A to Server B. Then on cutover get the tail log from server A apply it on server B, bring it only, and then configure Server C as a no-sync subscriber of Server B.


    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

    Tuesday, May 17, 2016 6:37 PM
    Answerer

All replies

  • Hi Vamshi,

    Instead of replication why don't you choose Log shipping from 2005 to 2012 for migration purpose.

    one more question , are the replication already being setup between server B and Server C?

    Regards

    ChetanV

    Tuesday, May 17, 2016 3:28 PM
  • We can do log shipping also, but the size of the database is over 800GB and to copy the backup, i assume it takes more than one day and in my current environment we are more familiar with replication and even we dont have enough time to go with a different plan

    Yes we do have replcation setup already  from server b and Server c.


    vamshi 


    • Edited by Reddy435 Tuesday, May 17, 2016 4:18 PM
    Tuesday, May 17, 2016 4:18 PM
  • We are planning for migration project from sql 2005 to sql 2012, For moving this huge data we planned for using Transactional replication From server A (sql 2005) to Server B(Sql 2012). On the Go -live during the cut off time i am thinking to script out the replication and then resume it and take a tail log backup and want to apply this on to the Server B(SQL 2012). But we have another replication which is going from Server B to Server C both on sql 2012. What happens when i restore the tail log backup from server A and apply it on the server B. Does the replication still work, Do i have to follow any steps

    Please help

    Thanks 

    Vamshi

    I think I am kind of confused here... how can you apply tail log on a database that is online state. the fact you said, there is replication from server B to C - makes me think that the database  is online on server B and is replicated to server C.

    Hope it Helps!!

    Tuesday, May 17, 2016 4:22 PM
  • the second replication from server b to server C can be paused when we restore the tail log backup.After it is done we can unpause the replication. So in this case i heard sometimes it requires to re-initialize the whole database.Does it?

    vamshi 


    • Edited by Reddy435 Tuesday, May 17, 2016 4:47 PM
    Tuesday, May 17, 2016 4:37 PM
  • ok. 

    so, you have Database DB1

    Server A - is SQL 2005 and your current main database

    Server B - SQL 2012 and you want to make this  the main database

    Server C - SQL 2012 - secondary server.

    Replication is set up for DB1 database from Server A - Server B and from Server B to Server C.

    DB1 is in online state on all three servers.

    so, i am not sure how you can apply the tail log of the DB1 database from Server A to Server B , because the database is on ONLINE state.  

    It it my understanding that you cannot restore log backups after the database is in online state. 


    Hope it Helps!!


    • Edited by Stan210 Tuesday, May 17, 2016 5:06 PM
    Tuesday, May 17, 2016 5:05 PM
  • Log shipping should be the best fit for this, but with server b being published you are not goign to be able to do any sort of a restore.

    On cutover you need to run your distribution agents on Server A until they display an idle message. You know now that all the transactions on Server A have made their way to server B.  Then you can decommission Server A and Server B will continue to server as a publisher to Server C.

    The tail of the log could only be used if you were log shipping and wanted to shut down your database on the source - Server A and then replay the last commands in the log on Server B.

    But to replicate you can't have a database in read only or standby mode so this is not an option for you.

    The best way to do it would be to replicate from Server A to Server C. Log ship from Server A to Server B. Then on cutover get the tail log from server A apply it on server B, bring it only, and then configure Server C as a no-sync subscriber of Server B.


    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

    Tuesday, May 17, 2016 6:37 PM
    Answerer