Test Compatibility Level

Business Case: An existing application is built on a legacy version of the SQL Server database engine (such as SQL Server 2000). To take advantage of modern capabilities, the application is to be upgraded to a more recent version of the database engine.

Environment: The application is business-critical and unscheduled downtime is not acceptable. The application must be tested to confirm reliability at the higher database version, but full-function application testing is unrealistic due to the complexity of the application.

Approach: To test the application database layer, we will create a script to collect and parse the existing SQL statements under the higher database version, capturing any errors that might be generated.

Solution: To demonstrate the approach, we will simply write and test a few SQL statements. To fully-develop this solution, rather than run the test against our sample SQL table, we could run it against a filtered selection of sys.sql_modules — a system view that contains the SQL definition statement of virtually all objects in the database.

First, let’s set the database context to a database at the target version (compatibility level). We’ll also suppress statement record count reports.

-- Set the database context to a db at the target version
USE [db];
GO

-- Do not show statement record counts
SET NOCOUNT ON;

For demonstration purposes, we’ll create a table variable to store the SQL statements to be tested. In the real-world, a query from the [definition] field of sys.sql_modules would be a good place to start. In this example we’ll test three statements that are valid in SQL Server 2000 but invalid in SQL Server 2008.

-- Create the SQL input table
DECLARE @tblIn TABLE (
    ID INT IDENTITY(1,1) NOT NULL
    , SQLString NVARCHAR(MAX) NOT NULL
    )
;

-- Populate the SQL input table
INSERT INTO @tblIn (SQLString)
VALUES -- these statements are valid at level 80, invalid at level 100
    (N'SET')
    , (N'SELECT o.name FROM sys.objects AS o HOLDLOCK;')
    , (N'SELECT o.name FROM sys.objects AS o, sys.tables AS t WHERE o.[object_id] *= t.[object_id];')
;

Now we’ll create an output table to store the results of our syntax check. We’ll capture the details of our input as well as the level at which we are testing the statement and any errors that might result.

-- Create the SQL output table
DECLARE @tblOut TABLE (
    ID INT NOT NULL
    , SQLString NVARCHAR(MAX) NOT NULL
    , ValidationLevel TINYINT NOT NULL
    , ErrorNumber INT NOT NULL DEFAULT(-1)
    , ErrorMessage NVARCHAR(2000) NOT NULL DEFAULT(N'')
    )
;
GO

Next we’ll begin the automated testing phase. We’ll create a cursor to scroll through the SQL statements to be tested. Start by creating variables to hold relevant information for the cursor, then initialize the cursor definition and iteration process.

-- Declare the cursor output variable(s)
DECLARE
    @intID INT
    , @nstrSQLOrig NVARCHAR(MAX)
    , @nstrSQL NVARCHAR(MAX)
    , @intCompatLevel TINYINT = (
            SELECT [compatibility_level]
            FROM sys.databases
            WHERE database_id = DB_ID()
        )
;

-- Declare and initialize the cursor
DECLARE crs CURSOR FAST_FORWARD
FOR
    -- Define the cursor recordset
    SELECT ID, SQLString
    FROM @tblIn
;

-- Open the cursor
OPEN crs;

-- Initialize the cursor output
FETCH NEXT FROM crs INTO @intID, @nstrSQL;

With the cursor now open, let’s iterate through the records. For each SQL statement to be tested, we’ll first capture the original SQL statement. Then we’ll modify the statement by prepending turning the PARSEONLY setting on. That setting tells SQL Server to check the syntax of the statement, but do not compile it and do not execute it. If, for your situation, you’d like to test statement compilation as well, try setting NOEXEC on. Once parsed, we’ll turn PARSEONLY off so that further statements are executed. And, by wrapping the whole thing in a try-catch block and within a transaction, we can control the flow and error capture to meet our needs.

-- Iterate through the cursor records
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Capture the original SQL statement
    SET @nstrSQLOrig = @nstrSQL
    -- Attempt to parse
    BEGIN TRY
        -- Start transaction
        BEGIN TRANSACTION
            -- Prepend statement with parseonly setting 
            SET @nstrSQL = N'SET PARSEONLY ON; ' + @nstrSQL;
            EXECUTE (@nstrSQL);
            -- Turn off parseonly
            SET PARSEONLY OFF;
            -- Capture statements and validation level
            INSERT INTO @tblOut (ID, SQLString, ValidationLevel)
            VALUES (
                @intID
                , @nstrSQLOrig
                , @intCompatLevel
            ;
        -- Commit transaction
        COMMIT TRANSACTION;
    END TRY
    -- Parse failed; capture error
    BEGIN CATCH
        -- Roll back the transaction
        ROLLBACK TRANSACTION;
        -- Capture statement and error information
        INSERT INTO @tblOut (
            ID
            , SQLString
            , ValidationLevel
            , ErrorNumber
            , ErrorMessage
            )
        VALUES (
            @intID
            , @nstrSQLOrig
            , @intCompatLevel
            , ERROR_NUMBER()
            , ERROR_MESSAGE()
            )
        ;
    END CATCH
    -- Iterate the cursor output
    FETCH NEXT FROM crs INTO @intID, @nstrSQL;
END

Finally, let’s close and deallocate the curser and turn statement counts back on.

-- Close cursor
CLOSE crs;

-- Release cursor from memory
DEALLOCATE crs;

-- Show statement counts
SET NOCOUNT OFF;

What about the results? Well, let’s take a look!

-- View validation report
SELECT * FROM @tblOut;

If the preceding script is run under SQL Server 2000 (compatibility level 80), you’ll see the following output.

Test SQL at level 80

Valid syntax for SQL 2000

And, if run under SQL Server 2008 (compatibility level 100), you’ll see the following.

Test SQL at level 100

Invalid syntax for SQL 2008

Does anyone have any suggestions on a more elegant way to accomplish this type of automated compatibility testing?

Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: