In general, upgrading of SQL Server in any latest version is quite complex. However, in my opinion it is NOT at all a rocket science, it is completely based on your planning and understanding about the complexity of your database(s) and its related applications. If you plan and understand it properly, there is no way you cannot make it smooth and up to the mark. In addition, before upgrading it directly on your production database server, you must test it on your test database server like any other application’s deployment and upgrading. But how will I know that is there any problem in my upgrading or NOT ? The solution is one of the best FREE tools that SQL Server provides namely SQL Server Upgrade Advisor. This tool can be downloaded from Microsoft site or it comes with SQL Server 2012 media (Setup files) . The installation of this tool is self explanatory.
Please note that the database you are planning to upgrade to SQL Server 2012 must have a compatibility of 90 or above. In addition, as much as the gap is between the versions, so is the complexity and problems you can expect. In order to avoid this, it is highly recommended that you should upgrade your SQL Server in a timely manner.
In this article, I will demonstrate how SQL Server Upgrade Advisor works step by step.
Step 1 :
Once you install SQL Server 2012 Upgrade Advisor, you will find it in the Start >> Program files as shown below.
Step 2 :
Now, it is time to execute the SQL Server Upgrade advisor, it will take you to the first screen, from where you can launch the upgrade Advisor Analysis Wizard. Here you will find the summary about the SQL Server Upgrade Advisor. I strongly recommend that you go through it. Once you review the summary, you need to launch the Upgrade Advsior Analysis Wizard as shown below.
Step 3 :
This is a welcome screen. It will give you the summary of how upgrade Advisor works step by step as shown below. However, you can check mark the Do not show this starting page again in order to avoid this screen recurring, as it is for information ONLY.
Step 4 :
Once you crossed the welcome screen, immediately after that you need to select the components you want to analyse for upgrading. Please make sure that you do NOT select Notification Services and Data Transformation Services because these two services have been discontinued in the SQL Server 2012. Reporting services you can select if you have installed in that particular server. In my case reporting service was not applicable.
Step 5 :
The next screen will ask you to select the instance and provide the valid credential of that particular instance as shown below.
Step 6 :
The next step is the selection of all or any one databases of the above selected instance that you want to upgrade to SQL Server 2012. Kindly select the appropriate database and press NEXT button.
Step 7 :
Here you need to provide the instance of Analysis services. In addition, you need to provide its credential as well.
Step 8 :
In this step, you need to select the location and the packages of your integration services. In my case, my SSIS packages are available in the same server so I selected the first option. However, you can select as per your requirement as shown below.
Step 9 :
In this step, SQL upgrade advisor will show you the path of Upgrade advisor report & its log files for your reference.
Step 10 :
Once you press NEXT button, it will start analyzing your selected components on selected databases. It takes 2-30 minutes, depending upon the database size, number of component and performance of your server.
Step 11 :
Once Analysis is done, it will show you a summary report along with the status and a Launch Report button. Here you need to launch the report, in order to see the results of the SQL Server upgrade analysis for your selected components.
Step 12 :
Once you launch the report, SQL Server Uprade Advisor will show you all the critical issues that you must fix it before or after upgrading. In order to do a smooth migration make sure that you resolve all the given below issues. In my case it is displaying only one issue related to the keyword. In fact I used one word that became a keyword in SQL Server 2012. So I need to replace this keyword.
Step 13 :
Once you select the issue as shown above, it will show where the conflict exactly is. In addition, it will provide all necessary details that you may require at the time of fixing.
Let me know about your experience relating to SQL Server upgrade advisor.