Upgrading the SQL Server version can have a major impact the applications using the databses. For example, from SQL Server 2005 onwards you are required to use col IS NULL instead of Col = NULL and so any applications using the later will break. Using Upgrade Advisor for SQL Server is a good start for any SQL Server however we still need to address the entire process of upgrading SQL Server to a new version. So What is the process we should follow while upgrading SQL Server? They are:
1. Upgrade Method: This is the first and most important thing to be decided before we start upgrading SQL Server. There are two types of upgrade methods:
A. Side-by-Side: In this method we keep our legacy database server intact. This will make it easy in case we need to roll back to the old system if a failure occurs or applications don’t perform well on the new server. There is another advantage – this process requires less downtime than an In-Place upgrade.
B. In-Place: In this method we upgrade the legacy database server. This process requires more downtime then Side-by-Side method. In case we need to roll back it will be very difficulty, so we need to be cautious with this option.
In case we don’t have the budget for separate hardware to run both the legacy and the new server we should opt for the In-Place upgrade method, or as an alternate we can always create a virtual box to test the new server.
Here is a nice article on advantages and disadvantages of both of the methods by Fancesco Diaz. Overall, I would recommend going for Side-by-Side upgrade if possible.
2. Utilize SQL Server Upgrade Advisor: It is recommended that we should run the Upgrade Advisor prior to running any upgrade, this tool is very useful. It not only scans our SQL Server Box for the possible path blocking in the upgrade but it also suggests the how we can modify the upgrade to avoid issues. The report has a section which highlight any potential issues that are undetectable and we must review them so that the application and end-users do not suffer.
Download Upgrade Advisor for SQL Server 2005
Download Upgrade Advisor for SQL Server 2008
3. Backup system databases: Always make a full back up of user databases, and, at the same time we should also backup system databases such as Master and MSDB so that we do not lose any login information, jobs, maintenance plans or SSIS packages that we have created. Here is a nice article on the backing up & restoring system databases.
4. Compatibility Level: We should always be mindful of the compatibility level since it can directly impact the performance of the applications. Whenever a new version is released it will have a few, minor or major enhancements in different area i.e. T-SQL enhancements such as using where col1 IS NULL instead of col1 = NULL, some features or commands get deprecated. Therefore we should select the appropriate compatibility level to either get maximum benefit of new enhancements in SQL Server or keep backward compatibility (eg compatibility 90 on SQL Server 2008).
There are several articles for further reading on this topic:
SQL Server 2005: http://dotnet.sys-con.com/node/45543 & http://www.devx.com/codemag/Article/27092
SQL Server 2008: http://www.sqlservercentral.com/articles/SQL+Server+2008/65539/
5. Rebuild Index: This is an important point which needs to be carefully considered while upgrading SQL Server to newer version and/or moving to new server. Rebuilding an index will remove all the fragmentation and reclaim the disk space based upon the fill factor, it also rearrange the pages to be in contiguous manner.
6. Update the
statistics: It is recommended that we run Update
state after we rebuild the index. Why it is
required if we already rebuilt the index? I agree that
rebuilding the index will update the statistics for the data that is indexed, but what
about the data that is non-indexed? And so we should run update
statistics after the rebuilding of the index since it doesn’t update the statistics for
non-indexed data.
Another very useful resource for SQL Server upgrades is the MSDN webcast Upgrade Your Database
]]>