Transactional Replication

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

Answer 1

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

Answer 2

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 

Answer 3

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!!

Answer 4

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 

Answer 5

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!!

Answer 6

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