ASP.NET MVC Hosting Lesson: Elmah and SQL Server 2000
Background:Technically this isn't specific to ASP.NET MVC at all, that just happens to be how I use Elmah. I started work on an ASP.NET MVC project with certain erroneous preconceived notions. I originally started the database with SQL 2008 in mind because I run Windows 7. An easily accessible staging server used SQL 2005 and it was here that I ran into my first compatibility snag. A database project may be set for an earlier revision but you can't transplant those .bak files and expect them to restore. This is generally 101 level stuff but something I tend to keep in the back of my mind at the worst times.
This staging issue foreshadowed production: the shared hosting company I use is running SQL 2000. Argh! Luckily this process was less painful: I used SQL Publishing Wizard and SQL 2000 in a virtual machine to get the database on the lowest common demoninator. Once in SQL 2000, the backup files will easily restore in any future version of SQL. Because the push to production is so infrequent and changes can be diff'd easier than rebuilt I've settled on a structure of Development: 2005, Staging: 2005, and Production: 2000.
Elmah and SQL 2000:I chose an Elmah version (1.1.11517.2009) that dealt specifically with SQL 2005 but was hopefully new enough to be relative to ASP.NET MVC. Switching to SQL 2000 produced a very minor snag: the database script needed to be downgraded.
Fortunately, and with the help of Google code, I could go back to other revisions of the same file in the Elmah codebase and find a version suited for SQL 2000 dated earlier this year. Here's the diff link to show what changes were made: http://code.google.com/p/elmah/source/diff?spec=svn705&r=643&format=side&path=/trunk/src/Elmah/SQLServer.sql&old_path=/trunk/src/Elmah/SQLServer.sql&old=568. When downgrading to SQL 2000, use the left hand side or you could just download the file.
While you are there, you might as well follow the examples given by Scott Mitchell "Keeping ELMAH's Error Log Size In Check" and "Deleting All Records In a Table EXCEPT For the N Most Recently Added Records". Why both? The first prunes any records beyond a date range to keep errors relevant. The second makes sure your database stays small in case of anything that can trigger a large number of records within your date window.