Best Migration Path from MS SQL Server 2005 to MS SQL Server 2012

Can somebody provide the best plan, path, tools/utilities, and processes to use in order to migrate an existing MS SQL Server 2005 instance running on Windows Server 2003 to MS SQL Server 2012 running on Windows Server 2012 ?  For example, are there built-in migration wizards that work well, and if so does one first have to migrate from MS SQL Server 2005 to MS SQL Server 2008 (and then from MS SQL Server 2008 to MS SQL Server 2012) to use those migration wizards ?  Or can one effectively use migration wizards built-in to a new installation of MS SQL Server 2012 to migrate from the existing MS SQL Server 2005 server ?  Do the built-in MS SQL wizards work pretty well, or are there some relatively inexpensive but effective third party tools (from Idera or others perhaps) that are well worth the money for such a project ?  And finally, are the DBs and Stored Procedures the main things that need to be migrated ?  Or are there any other processes or rules or alerts that need to be researched or migrated as well ?  TIA ...
LGroup1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You can migrate directly from SQL Server 2005 to 2012. Best method should be side-by-side and using backup/restore or detach/attach.
First thing to do is to  run the SQL Server 2012 Upgrade Advisor on the SQL Server 2005 instance before you start with the upgrade. That tool will search for deprecated features and the compatibility of the code and data and will give you a list of warnings and errors at the end of the report.
If you have reports (SSRS), packages (SSIS) and cubes (SSAS) you might consider migrate them also. But usually you won't need third-party software.
After migrating the databases don't forget to set their compatibility to SQL Server 2012, rebuild the indexes and run a full backup.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LGroup1Author Commented:
Okay, thanks.  This process seems pretty easy, should this be a pretty straightforward upgrade ?  Do these things usually go pretty smoothly ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Depends on the report from the SQL Server 2012 Upgrade Advisor. If nothing has to been reported than usually is that simple as I wrote. Otherwise you need to fix the warning and errors reported before migration.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

LGroup1Author Commented:
Thanks !
0
LGroup1Author Commented:
Also, does one have to migrate the settings from the MS SQL Server 2005 System DBs, and if so how is that done ?  Or is that basically just including the SSRS, SSIS, and SSAS packages ?  In addition, would it be recommended to migrate from MS SQL Server 2005 to MS SQL Server 2008 first and then to MS SQL Server 2012, or is it okay to jump right from MS SQL Svr 2005 straight to MS SQL Svr 2012 ?  TIA ...
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You don't migrate the settings. It's a new server and a new SQL Server version so you should configure the new instance according your resources.
There's no need to migrate to SQL Server 2008 first. SQL Server 2012 recognizes databases from SQL Server 2005.
0
LGroup1Author Commented:
Great, thanks again,
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
iPhone

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.