SQL with Manoj

Are you planning to upgrade your SQL Servers to 2012? YES!

How will you make sure that you are ready for Upgrade? ???
How will you make sure that the Upgrade will be seamless? 😦

SQL Server 2012 “Upgrade Advisor” is for you to check and analyze instances of all previous SQL Server versions i.e. 2008 R2, 2008, 2005 and even 2000 in preparation for upgrading to SQL Server 2012.

“Upgrade Advisor” identifies all features and configuration changes that might affect your upgrade. It provides links to documentation that describes each identified issue and how to resolve it and also generates a report that captures identified issues to fix either before or after you upgrade.

This tool has some prerequisites to install, and if you don’t install them you might see following error while installation:

Setup is missing prerequisites:

-Microsoft SQL Server 2012 Transact-SQL Script DOM, 
which is not installed by Upgrade Advisor Setup.
To continue, install SQL Server 2012 Transact-SQL Script DOM from below 
hyperlink and then run the Upgrade Advisor Setup operation again :

Go to http://go.microsoft.com/fwlink/?LinkID=216742

The above error mentions to install “MS SQL Server 2012 Transact-SQL ScriptDom” component. Install it from here: http://www.microsoft.com/en-us/download/details.aspx?id=29065&ocid=aff-n-we-loc–ITPRO40886&WT.mc_id=aff-n-we-loc–ITPRO40886

But for this also “MS DOT NET 4.0” is required on your system. To Install it check this link: http://www.microsoft.com/en-us/download/details.aspx?id=17851

After installing Upgrade Advisor, launch the tool, it gives you 2 options:
1. Launch Upgrade Advisor Analysis Wizard
2. Report Upgrade Advisor Viewer

–> Analysis Wizard lets you run alanysis on following SQL components shown in image below:

–> Report Viewer tell you about the changes that needs attention or needs change/fix before or after upgrading to 2012. After Analysis Wizard finishes it creates repots which is in the form of an XML file.

The Report Viewer tool uses this XML file generated and give you details of components that may be affected. The report provides Importance, When to fix (Before/After), Description and links to information that will help you fix or reduce the effect of the known issues, image below:

After you are done with this Analysis and checking Reports, now the time is to work and fix on issues listed in the Reports.

Other than this you also need to check Microsoft BOL and/or MSDN articles to check for Discontinued and Deprecated features, Breaking and Behavior Changes.
Check following important links:
1. SQL Server Backward Compatibility: http://msdn.microsoft.com/en-us/library/cc707787
2. Database Engine Backward Compatibility: http://msdn.microsoft.com/en-us/library/ms143532
3. Analysis Services Backward Compatibility: http://msdn.microsoft.com/en-us/library/ms143479
4. Integration Services Backward Compatibility: http://msdn.microsoft.com/en-us/library/ms143706
5. Reporting Services Backward Compatibility: http://msdn.microsoft.com/en-us/library/ms143251
6. Other Backward Compatibility: http://msdn.microsoft.com/en-us/library/cc280407