Brandie Tarvin (4/30/2010)
But you will have a LOT of code & stuff to upgrade, so get ready to work hard.
I hope not. The only real reason we're upgrading is to get high availability due to a hardware server crash last week (disk controller), that resulted in 11 hours of downtime, about 4 of which were due to the master database being corrupted. We could "sort of" afford that kind of downtime in the past, but not any more -- not in the daytime anyway. Fortunately (unless you count lost sleep) this one was all at night.
Most of the actual SQL code is generated & issued by a 3rd party application, and it would be impractical or impossible to change most of it. Most of that is inserts and updates, with some reporting.
Most of the reporting functions we wrote ourselves, but mostly it was other people so I don't know what kind of joins are prevalent. I suppose we might have to ultimately stick with a previous version of SQL. (But I still don't know if those versions will run on Win 2008 R2.)
I looked at the Advisor download page, but it doesn't go into details about how it gathers data. Is it a static inspection of the DB and the code stored therein, or does it watch the real-time SQL statement flow a la the Profiler? Most of our reporting queries are sent by external code in MS Access and Visual Basic.