SQL Server Compatibility Level Testing

I stumbled on interesting version compatibility issues while working with a database in SQL Server 2008R2. The database was originally developed in SQL Server 2000. It was upgraded to MSSQL 2008R2 as a part of a virtualization initiative, however, the Compatibility Level was still set to 80 (MSSQL 2000).

After seeing columns of type VARCHAR(MAX), it got me wondering about compatibility issues since there was no VARCHAR(MAX) in 2000.

Anomaly #1

Doing some research I found this post with a script for Testing all procs before upgrading to Compatibility Level 90. I ran it on the database (while still set to Compatibility Level 80) and it reported a stored procedure that had a syntax error that was not caught by the syntax checker in MSSQL 2000, 2005, or 2008R2. The SQL is below:

SELECT     ChangeLogID

    , CourseID

    , RecordID

    , RecordTable

    , ChangeType

    , ModifiedDateTime

    , ModifiedByEmpNo

    , CASE RecordTable 

        WHEN 'Course' THEN

        ( SELECT CourseName 

          FROM Course 

          WHERE CourseID = cl.CourseID)

        WHEN 'Lesson' THEN

        ( SELECT LessonName 

          FROM Lesson 

          WHERE LessonID = cl.LessonID)

         WHEN 'Content' THEN

        ( SELECT ContentName 

          FROM Content 

          WHERE ContentID = cl.ContentID)

        ELSE "None"

    END AS RecordName

FROM         ChangeLog cl

WHERE     (CourseID = @CourseID)

The error message was: Invalid column name 'None'. Note the quotation marks around “None” after the ELSE. Strangely, the stored procedure executes without error. It only detects the syntax error on CREATE, not when I ALTER the stored procedure.

Anomaly #2

Using the same database described above, the compatibility error detection script I used (while still having a Compatibility Level of 80) did not detect the error:

Msg 8127, Level 16, State 1, Procedure GetNextLessonForLessonNo, Line 49
Column "Lesson.LessonNo" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

The SQL generating this error is:

SELECT     TOP 1 MIN(L.LessonNo) AS LessonNo

    , L.LessonID

    , L.CourseID

    , L.LessonName

    , L.LessonDesc

    , COUNT(Content.ContentID) AS NContentPages

FROM     Course C 

         INNER JOIN Lesson L 

            ON C.CourseID = L.CourseID

         LEFT OUTER JOIN Content 

            ON L.LessonID = Content.LessonID

WHERE     (L.LessonNo > @LessonNo 

       AND L.CourseID = @CourseID)

GROUP BY L.LessonID

    , L.CourseID

    , L.LessonName

    , L.LessonDesc

ORDER BY L.LessonNo

This error is not detected in SSMS (in a query window, or by running the detection script) until the Database Compatibility Level is changed from 80 (MSSQL 2000) to 100 (MSSQL 2008). The issue can be resolved in SQL Server 2008 by adding L.LessonNo to the GROUP BY clause.

photo credit: somegeekintn / CC BY 2.0 

MvcScaffolding uses SQL Express by Default

The fact that the MvcScaffolding package uses SQL Express by default can be both good and deceiving at the same time.

Using an Existing Database (not Code-First)

I used the package on a MVC3 project to scaffold the repository models and controllers for an existing SQL Server Standard database (not SQL Express) that had an existing Ado.Net Entity Framework model. The EF connection string obviously pointed to the existing database. However when running the application, the Index pages didn’t list any of the existing data from my database.

If this looks like the same problem you are having, feel free to skip to the bottom for the solution (convention over configuration).

Where’s the data?

I could add records, but they didn’t show up in the existing database. There was no Express database .mdf file in the application’s App_Data folder. There were no additional  connection strings (other than the EF connection to the existing database) in the web.config files or any of the dbcontext class files. I profiled the existing database with SQL Profiler and the existing database was completely untouched. I even added a <remove name=”LPMEntities” /> line before the EF connection string to make sure any default connection from the server root or machine.config was taken out of the inheritance tree (similar to what you would do for the aspnetdb membership database if you are using a full SQL Server instance).

The new data was being stored somewhere, but where? In a newly created SQL Express database.

How do I See the Data?

Open your SQL Server Management Studio (SSMS) or the version of SSMS for SQL Express. In the Object Explorer window, click Connect. Use .\sqlexpress as the server to connect to using Windows Authentication and voila the new mysterious “hidden” database.

I hope this helps to clarify for other people attempting to use the MvcScaffoling in a Database-First scenario.

The Solution – Understand the Convention over Configuration

It took me a while to find a small subtle, yet crucial, detail in Scott Guthrie’s Using EF “Code First” with an Existing Database.

The following note is stated at the end of Step 5 – Configuring our Database Connection String:

EF “code first” uses a convention where context classes by default look for a connection-string that has the same name as the context class.  Because our context class is called “Northwind” it by default looks for a “Northwind” connection-string to use.  Above our Northwind connection-string is configured to use a local SQL Express database.  You can alternatively point it at a remote SQL Server.

In my case the Entity Framework connection string in the web.config was named “LPMEntities”. The class implementing the DbContext is named LPMContext as shown below.

namespace LPM.Models

{

    public class LPMContext : DbContext

    {

        public DbSet<LPM.Type> Types { get; set; }

        additional stuff here...

    }

}

Since LPMEntities is not the context class name, the EF generates a local SQL Express database.

I just added a connection string to the web.config with a name of LPMContext (same name as the context class) and my MVC forms are populated from the existing database.

Victory!

photo credit: Si1very / CC BY-SA 2.0

Introduction to MSSQL Management Data Warehouse (MDW) presentation

Last night I presented an intro to SQL Server 2008’s MDW to the fwPASS Professional Association. The subject is primarily of interest to DBA’s rather than Developers since it is best leveraged by monitoring and managing an environment of multiple SQL Servers.

Introduction to SQL Server 2008 Management Data Warehouse (MDW)

View more presentations from Dean Willson.

SQL Server Management Data Warehouse

The Management Data Warehouse introduced in SQL Server 2008 is a comprehensive performance monitoring and reporting system. It is a handy way to consolidate performance monitoring and data for all your servers on a single system.

A couple of minor gotcha’s to consider when setting up the data collector. The setup is so easy it is tempting to do it on auto-pilot. Don’t.

1. This is an obvious one, but I’m pretty sure it happens more than you would expect. Make sure to create a blank database on your monitoring server where you will store the aggregated data collection sets. Don’t accidentally pick one of your production databases or it will add all the tables, views, stored procedures, functions, types, schemas, and other logging objects into that database (there are a lot of them). It will also mark that database as a data collection warehouse which will show up in the database list when you configure data collection on your other servers.
2. Make sure to create the temp folder that will be used to cache the performance data until the periodic upload to the Management Data Warehouse. If the folder is not created, the upload jobs will fail.

photo credit: Nick Saltmarsh / CC BY 2.0

SSMS Tools Pack – Handy SQL Server Management Studio Utilities

I ran across the SSMSToolsPack while reading the comments from a post on dynamically generating CRUD Stored Procedures. It has some other nice features like saving snapshots of the execution plan to the clipboard.

It can also generate data insert statements from query resultsets, tables, or a database. That can be pretty handy for a number of reasons like:

• populating lookup tables in the initial deploy scripts of a new application or application update where a lookup table is added
• dropping tables and recreating test data for testing installation scripts and application testing

 

photo attribution http://www.flickr.com/photos/jesse_sneed/2383953694/ CC BY-ND 2.0