DavidWimbush : Upgrading

Fun with upgrading and BCP

DavidWimbush — Tue, 31 Jul 2012 14:08:00 GMT

I just had trouble with using BCP out via xp_cmdshell. Probably serves me right but that's a different issue. I got a strange error message 'Unable to resolve column level collations' which turned out to be a bit misleading. I wasted some time comparing the collations of the the server, the database and all the columns in the query. I got so desperate that I even read the Books Online article. Still no joy but then I tried the interweb. It turns out that calling bcp without qualifying it with a path causes Windows to search the folders listed in the Path environment variable - in that order - and execute the first version of BCP it can find. But when you do an in-place version upgrade, the new paths are added on the end of the Path variable so you don't get the latest version of BCP by default.

To check which version you're getting execute bcp -v at the command line. The version number will correspond to SQL Server version numbering (eg. 10.50.n = 2008 R2). To examine and/or edit the Path variable, right-click on My Computer, select Properties, go to the Advanced tab and click on the Environment Variables button. If you change the variable you'll have to restart the SQL Server service before it takes effect.

R2 Report Pagination

DavidWimbush — Fri, 02 Jul 2010 12:51:00 GMT

I thought it was all too good to be true. We upgraded our reporting server in place from 2005 Std to 2008 R2 and we've hit a major problem. We have a few reports that are about 60 pages. On 2005 the report would render page 1 of 60 pretty quickly. Now it produces page 1 of 2 that is miles long and takes minutes to render. If you have enough RAM and CPU power in your PC to cope. My dev machine can handle it but the average user's machine just dies. There's a brief spike on the server and then it's all your PC that's doing the work. Seeing as I'm the only person who can run these reports at the moment, guess what I've been spending half my time doing?

I've put it up on Connect here: https://connect.microsoft.com/SQLServer/feedback/details/572160/wrong-pagination-in-report-manager?wa=wsignin1.0

If you encounter the same problem, please vote this bug report up, or if you know the answer, please let me know. Thanks.

R2 Collation Gotcha

DavidWimbush — Fri, 02 Jul 2010 12:36:00 GMT

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 (http://msdn.microsoft.com/en-us/library/ms143508.aspx) 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

DavidWimbush — Tue, 15 Jun 2010 14:00:00 GMT

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 R2

DavidWimbush — Fri, 28 May 2010 14:06:00 GMT

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.