The job of preparing to upgrade SQL Server is one that DBAs look forward to but also dread in equal measure; on the one hand there is the delighted anticipation of new shiny things to work with, on the other hand the dread of knowing what to do if everything breaks or making sure that everything is ready for the changes? The problem is that your databases may be using features that don’t work in the version of SQL Server you’re upgrading to. If so, then they’ve got to be altered to get everything to work. Only if you make sure that all the databases will work on the new version of SQL Server can you look forward to a stress-free upgrade.

SQL Server makes things as easy as possible for you with various tools and features that let you know what if anything will cause problems and therefore stop working if you upgrade, and they will also warn you what features are likely to be problems in a version or two. There is the upgrade advisor tool, a dedicated application that will analyse all the components of SQL Server that you have installed and produce reports for you to review to guide you through an upgrade. This isn’t what we are going to look at, but I wholly recommend that you take a look at the Upgrade Advisor for the SQL Server version you intend to install, even if you are not intending to upgrade yet, and review what might be a problem and what information you will get out of it. Just so that you are one step ahead when the upgrade *does* get started. The SQL Server Upgrade Advisor can be found here:

• SQL Server 2005 http://www.microsoft.com/en-gb/download/details.aspx?id=14845
• SQL Server 2008 http://msdn.microsoft.com/en-GB/library/ms144256(v=sql.100).aspx
• SQL Server 2008 R2 http://msdn.microsoft.com/en-GB/library/ms144256(v=sql.105).aspx
• SQL Server 2012 http://msdn.microsoft.com/en-us/library/ms144256.aspx

A wise DBA will give developers as much warning as possible about the work involved in migrating a database application to a new version. Project Managers don’t like surprises. By making future-proofing part of the development culture, we can avoid all that last-minute scrabbling with code at the point at which the upgrade is due. In this document, we’re going to assume that you are familiar with the upgrade advisor, and so are going to look at ways that we can gain the detailed information simply by using information captured automatically in SQL Server and, show how to use that to inform development and the design team to be ready for new versions of SQL Server well before the time of doing the server upgrades.

No doubt you have heard all about the good things that are found in SQL Server system DMVs (Database Management Views) and we can get started with some information from the dm_os_performance_counters DMV.

So we have here a convenient way to look at the activity going on within our SQL instance that is using features that are destined to be removed from SQL Server in future versions. Let’s see what sort of information it can give us;

So, on my laptop instance we have a few different deprecated features being used. I’m not sure what the language-related items are so I might investigate those at another time. The deprecated features listed that would give me concern on a production system are Deprecated encryption algorithm , ‘::’ function calling syntax, sysdatabases , sysservers , syscolumns , XP_API and More than two-part column name. For a full list of deprecated features in SQL Server 2008 and their descriptions take a look at http://msdn.microsoft.com/en-gb/library/bb510662(v=sql.100).aspx. On our production servers we chose to review “More than two-part column name“ first. This feature is described as

“A query used a 3-part or 4-part name in the column list. Change the query to use the standard-compliant 2-part names. Occurs once per compilation.”

So we are seeing an application accessing our databases in some way that is using TSQL that will cause an error in a future version of SQL Server. It’s going to be useful to our development division to know about this as soon as possible so that they can work on refactoring the code in good time. As you can see though, the dm_os_performance_counters DMV only keeps a record of each time a feature is used, not where, when or how it was executed. We need some other way to collect executions.

Let’s take a look at creating an Extended Events(XE) session to capture the information we need. The events we are interested in are sqlserver.deprecation_announcement and sqlserver.deprecation_final_support. The distinction between these two events is that features in the former are due to be removed from SQL Server within 3 versions whereas those in the latter will not be available in the next version. Clearly features in deprecation_final_support are those that will demand your immediate attention and could potentially prevent an upgrade until the causes are resolved.

To create the XE session on SQL Server 2008 + SQL Server 2008 R2 we need to execute some TSQL;

(Note – if you want to create this session on SQL Server 2012 there is a slight change in the name of the file system target. You need to use

What is this code doing? Let’s review it section by section:

• CREATE SESSION – well this creates the session! Give it a name (your choice) and scope (Server or Database)
• ADD EVENT – the events that you want to capture need to be added to the session. Not sure which ones to use? Check out the results from

• ACTION – What information should be collected when the event gets fired. Not sure what information you can collect? Check out the results from

• ADD TARGET – Where do you want the information to be collected for reference? Some are persistent; others will be zeroed if the instance is stopped or restarted. If you pick a file target then make sure there is room for the storage and if you are tracking events that are performance related don’t use storage that will skew your analysis.
• All details on CREATE EVENT can be found at:
  http://msdn.microsoft.com/en-us/library/bb677289(SQL.100).aspx (2008)
  http://msdn.microsoft.com/en-us/library/bb677289(SQL.105).aspx (2008 R2)
  http://msdn.microsoft.com/en-us/library/bb677289 (2012)

Right, with the XE session created it I started it running and left it for 24 hours. I was somewhat surprised when I came to review the output – I had 1GB of xel files with almost 1M events recorded. Significantly more than I was expecting. Once I had brewed a fresh pot of coffee I set about getting some summary information from all of this data – there was no way it would give me a lot of information I could take action on, I would need to re-run the session with some filtering in place.

There are (always?) a couple of ways to review the data collected by an XE session. We can use [sys].[server_event_sessions], [sys].[dm_xe_sessions]and [sys].[dm_xe_session_targets] to access the data directly:

However, don’t expect details very quickly if you have a lot of data as I did, because the performance will suck: Terribly. It’s also tricky to read through XML results. We need to shred the XML into something easier to work with.

I decided to import the data into a database on a different server so that I could analyse and re-analyse it without compromising my production systems and without the burden of reading XML. Once the files were zipped, copied to the different server and then extracted, I used fn_xe_file_target_read_file to access the XEL files. It’s a pretty simple function that takes 4 parameters – path, mdpath, initial_file_name, initial_offset. path is simply needs the UNC of the XEL file(s), mdpath needs the location of the XEM file that the XE session creates, initial_file_name needs to have the name of the first XEL file if you don’t want to read them all, initial_offset needs the location in the file to start reading. For my purposed I only needed to specify the first two parameters and then NULL for the final two.

This isn’t much better than previous query, let’s add the code to shred the XML and then insert it into a database table.

Notice that I also imported the xml value in its entirety (the column [v].[xml_data]), this means I can quickly shred more information out of the data if I have missed something without having to re-access the xel files.

So here we have it, a database table full of XE data that is using features that are about to exit the SQL Server world. Not so fast, query the XE_Deprecated_Events table for values and you will very likely see ‘SQL_Text Unable to retrieve SQL text’ in lots of places. Where code is executed within a database object it is possible that the SQL Text is not collected.

Needless to say, I was pretty disappointed. We could still get good information from the data; things like which applications were using which deprecated features, which were the worst offenders, which features are on final Support and need urgent attention etc etc but we can’t actually make the final step to get to the line of offending code in some very important cases.

Back to the XE Drawing Board, we need to capture more information. Now, the old fashioned way of getting this information would be SQL Profiler and in that you would look for SP:Completed events, in XE the equivalent is sqlserver.module_end. Adding this to a session will trap huge amounts of data so I created a new XE session with the previous Events but added filters for the client_app_name and the new event with some filters to avoid certain database_ids. I also altered the options parameter TRACK_CAUSALITY which we will review below the code.

Switching TRACK_CAUSALITY=ON means that all events get an extra value in their data

This attach_activity_id value can be used to link the deprecation_announcement and deprecation_final_support events to the module_end events, thus giving us the module (Procedure, Function, Trigger etc) that contains the deprecated code.

Taking the output of the new XE session and importing it into a database table again is done

We are on the home stretch now, we just need to query the XE_Deprecated_Events_TwoPartNames_02 table and tie the deprecation events to their associated module_end events and find where we need to focus the attention of the development team so that they can alter the code to comply with upcoming versions of SQL Server.

The MSDN information explains in detail that when you turn on Causality Tracking each event that gets fired is given a unique Activity ID which is comprised of a GUID for each task and a sequence number for each event that is fired within that task. Taking our extract of example XML above, we would see the GUID and the sequence number as shown below.

In order for us to link the events from both the deprecation events and the module_end events we need to link the data we have by the GUID part of the Activity_ID and then sort by the sequence number. To do this effectively over a large number of events I found it best to alter the table and create a column specifically for the GUID part of the Activity_ID and then create an index on it.

Then, again for performance reasons, I select all the deprecation_events into a temporary table and all the module_end events into another and then join both of those back to the complete set of data.

Now all we need do is take the results and break the news gently to the developers. Giving them a detailed list of database objects to focus on is a much better way for them to tackle this sort of problem rather than watching them review every object in the database trying to hunt down code issues. It will also let them prioritise both the order that work is done and the resources that are put on the work when they see how big the list of changes is.

Wish me luck!