Nakul Vachhrajani is a Technical Specialist and systems development professional with iGATE having a total IT experience of more than 7 years. Nakul is an active blogger with BeyondRelational.com (150+ blogs), and can also be found on forums at SQLServerCentral and BeyondRelational.com. Nakul has also been a guest columnist for SQLAuthority.com and SQLServerCentral.com. Nakul presented a webcast on the “Underappreciated Features of Microsoft SQL Server” at the Microsoft Virtual Tech Days Exclusive Webcast series (May 02-06, 2011) on May 06, 2011. He is also the author of a research paper on Database upgrade methodologies, which was published in a CSI journal, published nationwide. In addition to his passion about SQL Server, Nakul also contributes to the academia out of personal interest. He visits various colleges and universities as an external faculty to judge project activities being carried out by the students. Disclaimer: The opinions expressed herein are his own personal opinions and do not represent his employer’s view in anyway.

Blog | LinkedIn | Twitter | Google+

Let us hear the thoughts of Nakul in first person -

Those who have been following my blogs would be aware that I am recently running a series on the database engine features that have been deprecated in Microsoft SQL Server 2012. Based on the response that I have received, I was quite surprised to know that most of the audience found these to be breaking changes, when in fact, they were not! It was then that I decided to write a little piece on how to plan your database upgrade such that it works with the next version of Microsoft SQL Server.

Please note that the recommendations made in this article are high-level markers and are intended to help you think over the specific steps that you would need to take to upgrade your database.

Refer the documentation – Understand the terms

Change is the only constant in this world. Therefore, whenever customer requirements, newer architectures and designs require software vendors to make a change to the keywords, functions, etc; they ensure that they provide their end users sufficient time to migrate over to the new standards before dropping off the old ones. Microsoft does that too with it’s Microsoft SQL Server product. Whenever a new SQL Server release is announced, it comes with a list of the following features:

1. Breaking changes
   1. These are changes that would break your currently running applications, scripts or functionalities that are based on earlier version of Microsoft SQL Server
   2. These are mostly features whose behavior has been changed keeping in mind the newer architectures and designs
   3. Lesson: These are the changes that you need to be most worried about!
2. Discontinued features
   1. These features are no longer available in the associated version of Microsoft SQL Server
   2. These features used to be “deprecated” in the prior release
   3. Lesson: Without these changes, your database would not be compliant/may not work with the version of Microsoft SQL Server under consideration
3. Deprecated features
   1. These features are those that are still available in the current version of Microsoft SQL Server, but are scheduled for removal in a future version. These may be removed in either the next version or any other future version of Microsoft SQL Server
   2. The features listed for deprecation will compose the list of discontinued features in the next version of SQL Server
   3. Lesson: Plan to make necessary changes required to remove/replace usage of the deprecated features with the latest recommended replacements

Once a feature appears on the list, it moves from bottom to the top, i.e. it is first marked as “Deprecated” and then “Discontinued”. We know of “Breaking change” comes later on in the product life cycle.

What this means is that if you want to know what features would not work with SQL Server 2012 (and you are currently using SQL Server 2008 R2), you need to refer the list of breaking changes and discontinued features in SQL Server 2012.

Use the tools!

There are a lot of tools and technologies around us, but it is rarely that I find teams using these tools religiously and to the best of their potential. Below are the top two tools, from Microsoft, that I use every time I plan a database upgrade.

The SQL Server Upgrade Advisor

Ever since SQL Server 2005 was announced, Microsoft provides a small, very light-weight tool called the “SQL Server upgrade advisor”. The upgrade advisor analyzes installed components from earlier versions of SQL Server, and then generates a report that identifies issues to fix either before or after you upgrade. The analysis examines objects that can be accessed, such as scripts, stored procedures, triggers, and trace files. Upgrade Advisor cannot analyze desktop applications or encrypted stored procedures.

Refer the links towards the end of the post to know how to get the Upgrade Advisor.

The SQL Server Profiler

Another great tool that you can use is the one most SQL Server developers & administrators use often – the SQL Server profiler. SQL Server Profiler provides functionality to monitor the “Deprecation” event, which contains:

• Deprecation announcement – equivalent to features to be deprecated in a future release of SQL Server
• Deprecation final support – equivalent to features to be deprecated in the next release of SQL Server

You can learn more using the links towards the end of the post.

A basic checklist

There are a lot of finer points that need to be taken care of when upgrading your database. But, it would be worth-while to identify a few basic steps in order to make your database compliant with the next version of SQL Server:

1. Monitor the current application workload (on a test bed) via the Profiler in order to identify usage of features marked as Deprecated
   1. If none appear, you are all set! (This almost never happens)
   2. Note down all the offending queries and feature usages
2. Run analysis sessions using the SQL Server upgrade advisor on your database
3. Based on the inputs from the analysis report and Profiler trace sessions,
   1. Incorporate solutions for the breaking changes first
   2. Next, incorporate solutions for the discontinued features
4. Revisit and document the upgrade strategy for your deployment scenarios
5. Revisit the fall-back, i.e. rollback strategies in case the upgrades fail
   1. Because some programming changes are dependent upon the SQL server version, this may need to be done in consultation with the development teams
6. Before any other enhancements are incorporated by the development team, send out the database changes into QA
   1. QA strategy should involve a comparison between an environment running the old version of SQL Server against the new one
   2. Because minimal application changes have gone in (essential changes for SQL Server version compliance only), this would be possible
7. As an ongoing activity, keep incorporating changes recommended as per the deprecated features list
8. As a DBA, update your coding standards to ensure that the developers are using ANSI compliant code – this code will require a change only if the ANSI standard changes

Remember this: Change management is a continuous process. Keep revisiting the product release notes and incorporate recommended changes to stay prepared for the next release of SQL Server.

May the power of SQL Server be with you!

Links Referenced in this post

• Breaking changes in SQL Server 2012: Link
• Discontinued features in SQL Server 2012: Link
• Get the upgrade advisor from the Microsoft Download Center at: Link
• Upgrade Advisor page on MSDN: Link
• Profiler: Review T-SQL code to identify objects no longer supported by Microsoft: Link
• Upgrading to SQL Server 2012 by Vinod Kumar: Link

Reference: Pinal Dave (http://blog.sqlauthority.com)