Migration from SQL v2005 Enterprise to v2008 R2 Standard -- terrible performance
Posted on 2014-03-17
A customer of mine is trying to migrate from v2005 Enterprise to v2008 R2 Standard Edition. They have 20 databases, and all but two have been migrated successfully. The last two databases are mostly OLTP. They attempted to migrate them, but the performance was terribly degraded. They could not identify the problem, so they rolled them back.
I am stepping in to try and help them identify the source of the problem. Initially, of course, I am going to assess both instances and compare the obvious (configuration, settings, etc.). But remember, 18 of their databases migrated just fine. These last two, for some reason, had very notable performance degradation.
I noted the builds for both environments below, and I was hoping for insight on the best approach. Possibly somebody has seen this before, or at least can advise on a suggested approach for my diagnostic efforts.
v2005 - 4 servers in a Windows cluster (3 active, 1 passive) and another server housing mirrors for two databases. SQL v2005 Enterprise is on each server. only one instance per server, all disk is on EMC
v2008 is 4 two-node active/active clusters -- 3 are production, 1 is test. v2008 R2 Standard Edition is on each server. Only 1 instance per server, and all disk is on netapp.
Personally, I am a huge advocate for the Enterprise edition. They are moving from Enterprise to Standard --- but, surely that isn't the sole source of this degradation. Any advice?