November 9, 2012 at 7:01 am
We have a mission critical SQL2005 instance and we're planning on doing a side-by-side upgrade to SQL2012. I'm pushing for a rolling upgrade using mirroring and my manager is pushing for a dettach/attach type upgrade. But here's the catch: he also wants to be able to revert back to the 2005 instance if a problem should arise with the 2012 instance. I told him that I don't think that there is a way to keep the two instances in sync after the upgrade, should you want to go back to the original 2005 instance. Am I wrong? Is this possible?
November 9, 2012 at 7:16 am
You are right, you cant take the database from 2012 to 2005 as it will not reattach or restore.
Two options I can think of off the top of my head are
Use SSIS to pump the data from 2012 to 2005, might not be an option due to the amount of time it will take to transfer the whole data set of the db between servers.
Use transactional replication from 2012 to 2005 which would keep the 2005 DB upto date.
Others might jump in with other options
November 13, 2012 at 9:35 pm
Transactional Replication is worth exploring. It could work, if your schema supports it. All tables need a Primary Key and I would venture a guess that more databases exist where at least one table does not have a PK than databases where all tables have a PK.
I would also explore the possibility of addressing your manager's expectations. If they are pushing really hard for a backout option that would begin after transactions were applied to the 2012 databases it means they are not allowing for the proper amount of preparation, or that maybe the migration test plan is incomplete, not comprehensive, or not being fully executed.
Essentially, once you allow new transactions to be applied to the 2012 environment, your options to resume operations on 2005 become far more limited.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 13, 2012 at 10:24 pm
There is no downgrade path once your databases have been upgraded. If you want to do this, however you do it, you will need to "copy" the transactions from the 2012 instance to 2005. Replication is a possibility if you have PK's everywhere. SSIS as was mentioned. Another option would be to use Service Broker to deliver the data from 2012 to 2005. Baxically you'd have a FOR INSERT,UPDATE,DELETE trigger on every table that takes the data, serializes it to xml, and sends it via SB. The advantage beintg that it is asynchronous and will have a smaller impact on your source (2012) server.
However you do it you're talking about a lot of work to do what you want to do.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply