I have to move a 5 TB SQL Server 2008 database that has mirroring setup to a new server that will have SQL Server 2012 and always on AG setup.
I am wondering if detach/attach would be faster or backup/restore. The compressed split backup is about 1.2 TB.
I understand that detach/attach means database will not be available on the source.
Any advise on matters to consider, as this will be a first SQL Server upgrade for me.
Thanks as always to the DBA community.
Best Answer
First you need to run upgrade advisor to be able to confirm that there won't be any errors during the upgrade from 2008 to 2012.
If you want to minimize downtime, then setup mirroring from SQL 2008 to 2012 (yes mirroring is still supported but declared deprecated). Then during the day of cutover (migration), change the mode of mirroring to SYNCHRONOUS and failover. This will require couple of seconds to bring the mirror server DB online.
this way, you can prepare in advance and minimize the downtime.
once the 2012 server db is online, you can get that database in AG. Make sure to remove mirroring once you failover.
I have put in detailed steps for migration at : Upgrade SQL Server 2000 database to 2008 R2 and enable new features
Don't go for detach/attach as it is risky as if something goes wrong, you will end up without backup. Backup restore is the best method.