Category Archives: Upgrading

SQL 2014 upgrade experience

I upgraded our servers to SQL 2014 and found it a pretty smooth experience. Thomas LaRock has a nice set of advice here – thanks Tom – with explanations of why and links to further reading.

We started from a mix of mostly SQL 2012 but a few stragglers on SQL 2008R2 that, for various reasons, I hadn’t got round to. I kept the compatibility levels as they were for a while to bed things in. There were no problems at all.

So then I changed the compatibility levels to 120 (SQL 2014) which invokes the new cardinality estimator. As far as I can see, performance is at least as good as before but two query plans did go bad. One needed a little refactoring but the other was very stubborn. After running out of other ideas I used the query hint OPTION (QUERYTRACEON 9481) to force the old cardinality model and that fixed it. I don’t like query hints but in this case I’ll make an exception.

I also notice a distinct improvement in the missing index recommendations in the Performance Dashboard report. For several years it’s been recommending two or three indexes that we already have. Those are gone now and that’s a good sign in my book.



SQL Server 2012 edition comparison details are published

Interesting stuff, particularly if you’re doing BI. BISM tabular and Power View will not be in Standard Edition, only in the new – presumably more expensive – Business Intelligence Edition. That kind of makes sense as you need a fairly pricey edition of SharePoint to really get all the benefits, but it’s a shame there won’t be some kind of limited version in Standard Edition. And Always On will be in Standard Edition but limited to 2 nodes. I really expected Always On to be Enterprise-only so this is a great decision. It allows those of us working at a more modest scale to benefit and raises the fault tolerance of SQL Server as a product to a new level.

2008 R2 Collation Gotcha

On my dev machine I installed R2 alongside my existing 2005 instance and it did a funny thing with the new instance’s collation. Our servers are all on Latin1_General_CI_AS, including my 2005 dev instance. But my R2 instance defaulted to SQL_Latin1_General_CI_AS and I’ve only just noticed, which is a damn nuisance. I thought I checked the collation during the setup but I guess I did a mental LIKE ‘%Latin1_General_CI_AS’. I think this article ( explains the reasoning:
SQL Server Collations (SQL_*) 
Select this option to match settings with English-language versions SQL Server 2005 or earlier versions.
Presumably it detected the 2005 instance and was trying to be helpful. Close but no cigar. Just as well my machine is long overdue for a rebuild anyway.

Upgrade to 2008 R2

I don’t like it, Carruthers. It’s just too quiet.

Well, I’ve done the pre-production server, the main live server and the Reporting/BI server with remarkably little trouble. Pre-production and live were rebuilds. I failed live over to our log shipping standby for the duration, which has a gotcha I blogged about before. When I failed back to the primary live server again, it was very quick to bring the databases online. I understand the databases don’t actually get upgraded until you recover them but there was no noticable delay. It’s gone from 2005 Workgroup – limited to 4GB of memory – to 2008 R2 Standard so it can now use nearly all of the 30GB in the server. It’s soo much faster.

The reporting/BI server I upgraded in situ. This took a while but, again, went smoothly. Just watch out, because the master database was left at compatibility level 90. Also the upgrade decided to use the reporting service’s credentials for database access when running reports. It didn’t preserve the existing credentials and I had to go into the Reporting Configuration Manager to put them back in. Make sure you know what credentials your server is using before you upgrade.

All things considered, a fairly painless experience. Now I just have to upgrade and reset our log shipping standby server again!

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.