I have recently implemented your scenario for one of my company's critical - ordering & sales system's migration from 2008 to 2014.
You have 2 options for rollback depending on the downtime that you are allowed -
Upgrade :
- Follow the pre-migration steps
- setup DB mirroring from 2008r2 to 2014 in async mode.
- Before cutover, change the mirroring to sync mode and make sure that mirroring queues are caught up before doing failover.
- Perform mirroring failover. This is one way . You cannot failover to lower version.
- disable all the application logins on both the servers (script out disable and enable). This is very important since you want to make sure that both the copies (2008r2 and 2014) are exactly same while you are configuring rollback options.
Rollback plan (Going backwards comes with a price that you need to agree with the business and application stake holders) :
- Option 1 : Use transactional replication if you can accept downtime. The downtime will be the amount of time it takes to take a full backup of your 10TB database.
- You can setup distribution database on 2014 ahead of time.
- During backup, no data changes should be done on either side.
- Once the backup is done, the replication will just work fine without doing snapshot as the LSNs are same on both sides.
- I did not go with this !
-
- Option 2 : Use Peer-to-peer replication if you need very minimal downtime.
- The downtime here is equal to the time it takes to create the peer and related jobs which depending on the size of tables (article) will vary. In my case for a 700GB database, it took 4 mins. I scripted entire replication and used .bat scripts to fire replication scripts on both sides 2014 and 2008.
- P2P needs to have both dbs exactly same. Since you disable all the logins and not activity is happening, this is my preferred (and now a defacto) option wherein I have to provide rollback option.
Once you configure rollback option (either T-rep or P2P), if you run into major issues, you can just point your application to the 2008R2. What we did is we use Failover Partner, this way we just shutdown the 2014 server and application will automatically redirect to 2008 server.
Important Notes:
I used P2P replication as downtime had to be less than 15 mins.
Above both options requires that all the tables have primary keys defined. Tables without PKs e.g. staging tables needs to be discussed with stake holders and excluded.
We kept replication for only 1 day as fallback/rollback plan. If we did not see any issues after migration (done during weekend) and survive 1 business day (monday) we tear down replication and setup DBs into AG in 2014.
We did not change the compatibility level to 2014. We kept it same as 2008. I would highly recommend to do that unless you have thoroughly tested your application on 2014 in downstream environments. Even if you change the compatibility level to 2014 and you see query regressions you can enable TF 9481 to tell optimizer to use legacy CE.
Fair Warning: I have implemented above in production with full success - but I did extensive testing and invested time in automating the entire process and hence I can confidently give the process and timings. Highly suggest you to death test the process & document it before you implement it in PROD.
