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 depends on your downtime window.

My preferred steps would be :

-- take full backup from sql 2008 R2 and restore it on 2012 server.

-- do all the post restore steps.

-- Add the database to Availability Group. Possibly (with modifications as per your environment), you can use automatically add databases using T-SQL script.

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.