SQL Server Upgrade Issues

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
 This is the first and most important
thing to be decided before we start upgrading SQL Server. There are two types
of upgrade methods:

 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.          


 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


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
 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

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
 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
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
 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


Hemantgiri S. Goswami


No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |