Tuesday, June 27, 2006

SQL Server Upgrade Advisor and Trace Files

One of the better tools for SQL Server 2005 is definitely the Upgrade Advisor. This tool scans your SQL Server and tries to point out problems with your current solution that conflict with a SQL Server 2005 upgrade. It's better to know this before you install everything, right? Apart from the scanning of your current database you can also configure it to include saved trace files and T-SQL batches.

Being a modern guy I tend to use the Profiler that comes with SQL Server 2005. Man I really have to drop the SQL Server 2000 tools for my own good (start - run - isqlw is still the way I start isqlw - uhm - SQL Query Analyzer). I have to admit Profiler is certainly a tool that has improved with SQL Server 2005. Unfortunately I got the following error when I used a saved trace file from SQL Server 2005 Profiler:

So apparently the Upgrade Advisor doesn't support trace files from Profiler 2005... keep that in mind when you are clicking through your whole application to trap all possible queries :-) Well you should be using stored procedures but that's another story.

Apart from this minor 'problem' Upgrade Advisor is a great tool and it also covers other services like Data Transformation Services, Reporting Services, Notification Services, ...


Anonymous said...

I think you should expand on the comment of why people should be using stored procedures. I do not agree with you and I would like to know your take

WesleyB said...

The internet is stuffed with articles on why you should be using stored procedures but I'm willing to specify a couple of reasons. If you could tell me why you do not agree we can maybe debate on this. As always you have to avoid black/white thinking. There may be reasons not to use stored procedure for some projects but in general I do prefer them.

A couple of reasons are:
- minimizes amount of bytes going over the line (SELECT bla bla FROM blabl JOIN bla bla and many many many more characters vs exec usp_Customer__RetrieveByID 1)

- minimizes the size of your procedure cache (the whole statement is saved in your cache) - same as previous example

- separate responsibilities - developers should not be writing T-SQL queries on large environments (in my humble opinion)

- separate data retrieval from your code so you don't have to redeploy a whole bunch of DLL's when a simple update of a stored procedure would suffice

- easier to avoid SQL injection attacks

- easier to maintain (especially security wise)