Solved

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

Posted on 2014-10-07
7
493 Views
Last Modified: 2016-02-13
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 ...
0
Comment
Question by:LGroup1
  • 4
  • 3
7 Comments
 
LVL 48

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40366324
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
 

Author Comment

by:LGroup1
ID: 40366408
Okay, thanks.  This process seems pretty easy, should this be a pretty straightforward upgrade ?  Do these things usually go pretty smoothly ?
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40366427
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Closing Comment

by:LGroup1
ID: 40366435
Thanks !
0
 

Author Comment

by:LGroup1
ID: 40369059
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
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40370087
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
 

Author Comment

by:LGroup1
ID: 40370513
Great, thanks again,
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Email is way too noisy, prone to hiding the important stuff, and really becoming unreliable for critical/timely communications. There are better ways to communicate.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question