1

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.

marc_s
  • 9,052
  • 6
  • 46
  • 52
TDPSQL_Newbie
  • 123
  • 1
  • 2
  • 12

2 Answers2

1

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.

Community
  • 1
Kin Shah
  • 62,545
  • 6
  • 124
  • 245
0

Hi best option is to to use backup restore.

  1. It will keep minimum down time
  2. This is safe as your database at 2008 will be available till your 2012 is ready
  3. you can do comparison testing for few days
  4. you can make sure if there is any issues in 2012 you can take your business back to 2008 and avoid worst situation till all check from app is done
Saurabh Sinha
  • 229
  • 3
  • 6