SQL Trace for compatibility check?

Question

My idea is to trace a few days all statements (scripted the replay template) on a SQL Server 2008 r2 and replay those against a deployed version of the db on a newer version (SQL Server 2014).

Is a re-playable trace file suitable to figure out server version compatibility issues? Does this scenario make sense?

How can one realize problems? How are errors displayed when replaying queries that have issues?

Answer 1

Before a replay, I would:

1. Run the Upgrade Advisor and Best Practices Analyzer

2. Run a trace looking for deprecation events

3. Look for some of these issues; the above methods are unlikely to find all of them.

Note that none of the above, nor replaying the trace, are guaranteed to find all compatibility issues (consider the case where a stored procedure will only have a problem with very specific parameters are used, and those don't happen to be used during your trace).

And when you replay the trace, you'll have to parse the output file(s) or table(s) yourself, and perhaps even perform diffs to see any cases where there are errors on the new version that didn't happen on the old version. The replay doesn't produce a nice "here's what worked differently from the original activity" report. You can do this by simply performing a join against .

Here is a quick example I whipped up on my own system - you need to add the Error column to your trace, and note that I am absolutely not advocating Profiler for this, it was just the easiest way to demonstrate. First, on 2014, create a database using 100 compatibility level:

CREATE DATABASE Compat100;
GO
ALTER DATABASE Compat100 SET COMPATIBILITY_LEVEL = 100;
GO

Now, run these two batches:

USE Compat100;
GO
DECLARE @x TABLE(n DATETIME2, x AS CONVERT(CHAR(10), n)); -- not style 121
INSERT @x SELECT GETDATE();
-- error:
SELECT CONVERT(INT, LEFT(x,4)) FROM @x;
GO

USE tempdb;
GO
DECLARE @x TABLE(n DATETIME2, x AS CONVERT(CHAR(10), n)); -- style 121
INSERT @x SELECT GETDATE();
-- not an error:
SELECT CONVERT(INT, LEFT(x,4)) FROM @x;

A trace will yield these:

(Again, do not use Profiler for the actual work, this was just to quickly illustrate that a trace can capture error conditions, and that if you had these two batches run identically on two different servers, you should easily be able to join on TextData equality and Error inequality. And yes, I do realize that this example works the opposite way - the error happens on 2008 but works on 2014, but hopefully it still demonstrates the approach.)

Also see this tip.