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:
Breaking changes
These are changes that would break your currently running applications, scripts or functionalities that are based on earlier version of Microsoft SQL Server
These are mostly features whose behavior has been changed keeping in mind the newer architectures and designs
Lesson: These are the changes that you need to be most worried about!
Discontinued features
These features are no longer available in the associated version of Microsoft SQL Server
These features used to be “deprecated” in the prior release
Lesson: Without these changes, your database would not be compliant/may not work with the version of Microsoft SQL Server under consideration
Deprecated features
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
The features listed for deprecation will compose the list of discontinued features in the next version of SQL Server
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:
Monitor the current application workload (on a test bed) via the Profiler in order to identify usage of features marked as Deprecated
If none appear, you are all set! (This almost never happens)
Note down all the offending queries and feature usages
Run analysis sessions using the SQL Server upgrade advisor on your database
Based on the inputs from the analysis report and Profiler trace sessions,
Incorporate solutions for the breaking changes first
Next, incorporate solutions for the discontinued features
Revisit and document the upgrade strategy for your deployment scenarios
Revisit the fall-back, i.e. rollback strategies in case the upgrades fail
Because some programming changes are dependent upon the SQL server version, this may need to be done in consultation with the development teams
Before any other enhancements are incorporated by the development team, send out the database changes into QA
QA strategy should involve a comparison between an environment running the old version of SQL Server against the new one
Because minimal application changes have gone in (essential changes for SQL Server version compliance only), this would be possible
As an ongoing activity, keep incorporating changes recommended as per the deprecated features list
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)
Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology Tagged: Upgrade
