Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Quick question, request your input.

I am migrating a SQL Server 2008 R2 instance with database mirroring to SQL Server 2012 with always on.

Should I :

  1. Restore the SQL Server 2008 R2 backup to SQL Server 2012 primary and mirror (with no recovery), setup the mirroring and then do the post upgrade steps like dbcc checkdb, update stats etc only on primary

    OR

  2. Restore the SQL Server 2008 R2 backup to SQL Server 2012 primary. Complete the post upgrade steps like dbcc checkdb, update stats etc on primary. Then take fresh backup of this instance and setup the SQL Server 2012 always on on secondary.

Thank you in advance for your kind recommendations

share|improve this question

1 Answer 1

It really depends on your downtime window.

My preferred steps would be :

  • Create a full backup of the SQL Server 2008 R2 database and restore it on the SQL Server 2012.

  • Perform all of the post-restore steps.

  • Add the database to an Availability Group.

share|improve this answer
    
Hi Kin: one of my databases is 5TB. Hence I am figuring out the right process. So your recommendation is 2? Also, does increasing temp db - no of files and size help? Thank you –  TDPSQL_Newbie Feb 21 at 17:02
    
If the database backup is compressed, it should cutdown the size. Also, when you move the backup across to 2012 server for restore, the network bandwidth will play a huge role (even if you go for option 1. Make sure that on 2012, Instant file initialization is enabled as it will speed up the restore time. Tempdb configuration is a different story, as it will help reduce the contention when you have multiple tempdb data files with Same size and autogrowth settings. Check for number of VLF's on the current database as well. –  Kin Feb 21 at 17:22
    
Thank you, I will checkInstant file initialization and VLF. I prefer method 2 as well. I have reasonable downtime to work with...unto a week for the 5 TB. ofcourse, I prefer to do all activities and in minimal time. Thanks –  TDPSQL_Newbie Feb 21 at 17:58
    
@TDPSQL_Newbie Glad could help. Please upvote/accept answer if you feel that the answer has answered your question. –  Kin Feb 21 at 19:45
    
vote up requires 15 reputations :( –  TDPSQL_Newbie Feb 21 at 20:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.