Monthly Archives: May 2010

Upgrading from 2005 to 2008 R2

We’re about to take the plunge and upgrade our servers from SQL 2005 to SQL 2008 R2. Real world accounts of people upgrading to R2 are a bit hard to find so I thought it might be useful to blog what happens. (I don’t count marketing ‘case studies’ that just say stuff like “The process was effortless and the upgrade will pay for itself by the end the week.”)
We’re using the database engine, Analysis Services and Reporting Services so upgrading by a major version number was looking a bit daunting. I wasn’t expecting much trouble on the engine side of things but, as most of the action in 2008 and R2 appears to have been on the Reporting and BI front, I expected to have quite a bit of work to do. But our testing so far has been one nice surprise after another:
  • The 2005 backups restore cleanly onto R2.
  • R2’s BI Studio upgraded the Reporting and Analysis Services solutions without any issues.
  • The cubes all deployed and processed just fine.
  • R2 BI Studio interacts fine with TFS 2008 version control.
 I’ll blog some more as things develop.

Unused Indexes Gotcha

I’m currently looking into dropping unused indexes to reduce unnecessary overhead and I came across a very good point in the excellent SQL Server MVP Deep Dives book that I haven’t seen highlighted anywhere else. I was thinking it was simply a case of dropping indexes that didn’t show as being used in DMV sys.dm_db_index_usage_stats (assuming a solid representative workload had been run since the last service start). But Rob Farley points out that the DMV only shows indexes whose pages have been read or updated. An index that isn’t listed in the DMV might still be useful by providing metadata to the Query Optimizer and thus streamlining query plans.

For example, if you have a query like this:

select  au.author_name
        , count(*) as books
from    books b
        inner join authors au on au.author_id = b.author_id
group by au.author_name

If you have a unique index on authors.author_name the Query Optimizer will realise that each author_id will have a different author_name so it can produce a plan that just counts the books by author_id and then adds the author name to each row in that small table. If you delete that index the query will have to join all the books with their authors and then apply the GROUP BY – a much more expensive query.

So be cautious about dropping apparently unused unique indexes.

SQL 2008 R2 and Idera Idera SQL Safe (Freeware Edition)

Good news: the Freeware edition of Idera SQL Safe works on R2. You might not care but I certainly do. Here’s why:
In September last year I started using Idera SQL Safe (the Freeware Edition) to get backup compression on my SQL 2005 servers. It seemed like a good idea at the time – it was free and my backups ran much faster and took up much less disk space. I really thought I’d actually scored a free lunch. Until they discontinued the product. I was thinking about what to do when I heard that R2 Standard would include native backup compression so I’ve just been keeping my fingers crossed since then. So I installed R2 Developer on my laptop, installed SQL Safe and kicked off a restore with it. No problem. Phew! Now I won’t have to do a special, non-compressed backup and restore when we migrate.

R2 download site is looking good

The MSDN downloads for R2 appeared as promised yesterday. Congratulations to everyone on the SQL team. I must have got one of the first downloads of the Developer Edition and it was nice and fast. I’ve just downloaded Standard Edition and it’s still nearly as fast. Nice. I’m guessing they aren’t using GUIDs for the clustered indexes this time! 😉