tim laqua dot com Thoughts and Code from Tim Laqua

18Jun/120

SQL 2012 Upgrade: Lessons Learned

Monday of last week (2012-06-11) we started our SQL 2012 upgrades by first upgrading our development and staging environment. We encountered very few issues with this particular phase and forged ahead with upgrading one of our monitoring servers - this is the one that hung us up for an hour or two. We decided it didn't need to run enterprise edition and could get by with the Business Intelligence edition. What we didn't notice was that at some point in the past we had setup MDW on that server for evaluation and, after abandoning it, never cleaned it up. The MDW databases uses both partitioning and compression - enterprise only features.

After sorting that out, we moved on to our SSAS presentation server which also received Business Intelligence edition as there are no core limits on SSAS in that edition - that box has 48 cores and the engine licensing scared us a little - then we remembered we don't even run an instance of the engine on that server. Finally, we upgraded our production ETL/DW/SSRS server and that's when we found out that someone declared war on commas in email addresses (I have no idea what standard led them to that decision - I assume they forgot that commas commonly exist in titled email strings). Then pass-through authentication in SSRS tanked, but our DBA quickly caught that one noticing the SSRS config file was completely overwritten and was only allowing NTLM (switching back to Negotiate fixed the issue).

For now, one week later, things appear to be stable and we believe we've found most of the issues. This upgrade certainly didn't yield magic performance improvements as we saw in the 2005 upgrade and the 2008 upgrade and we have yet to use any of the new features - but we're ready. Someone keeps talking about about Tabular instances 😉

As the saga continues and we move on to upgrading our remaining environments, I'll keep the following list updated:

Phase 1: Core ETL, DW, SSRS, and SSAS Servers

  • Context menus are a little different in the new VS environment. Had to manually add the Change Connection command to the context menu that's used when editing XMLA files (SQL 2012: XMLA Change Connection Command Missing)
  • You can't add DATETIME and TIME any more, you have to explicitly CAST the TIME part to DATETIME first
  • Don't worry about the Project Deployment Model in SSIS yet. The Package Deployment Model works fine for now
  • When compiling SSIS packages, they don't sit in the ./bin root by default anymore. This can behavior can be changed by editing the DeploymentOutputPath in the Properties of the solution file
  • Do NOT diagonalgrade a server running Enterprise Edition only features to SQL 2012 Business Intelligence Edition (Upgrading from SQL 2008 R2 Enterprise to SQL 2012 Business Intelligence)
  • The SSRS Config file (RSReportServer.config) gets overwritten. Back yours up before doing the upgrade so you can put the settings back. The one we noticed was the authentication changed to NTLM only rather than Negotiate (we use kerberos primarily) so auth started failing for many data sources
  • The SQLPS (Powershell) shell behaves a little differently. Working with files (copies, moves, etc) will not work correctly unless you break out of the default provider context (it's in the context of SQL's provider when the shell starts). At the top of all of our Powershell type SQL Agent Job Steps we just added a "C:" line to break out of the SQL provider and things worked as expected again
  • In SSAS, go through the data sources in your AS databases and switch any references to SQLNCLI10/10.1 to SQLNCLI11. We've seen SQLNCLI10.1 continue to work fine after the upgrade in some scenarios and fail in others - safest to just update them.
  • msdb.dbo.sp_send_dbmail Msg 14613, Level 16, State 1, Procedure sysmail_verify_addressparams_sp, Line 13. The specified '@recipients' cannot use commas (,) to separate addresses: 'Tim_Laqua@trekbikes.com;Laqua, Tim <tim_laqua@trekbikes.com>;'. To continue, use semicolons (;) to separate addresses." I looked at the validation proc, it tosses out ANY string with a comma in it. Simply remove the commas (so instead of LastName, FirstName use FirstName LastName for titled email addresses)
  • SSRS also declared war on email recipient strings - Some subscriptions will fail with the error "specified string is not in the form required for an e-mail address." Fiddle w/ the email address line, we've seen this be an issue with white space, blanks, etc. Basically, they made the email address validation ridiculously rigid.

Don't try to perfectly plan your upgrade, just be ready to zig when it zags. As a team, we've been through at least two of these major SQL Server upgrades previously and this one was more about being prepared for possible issues rather than spending ridiculous amounts of time trying to perfectly reproduce production load to simulate the production upgrades. We did spend some time tidying up our house first as over the years we have learned that the more you stay away from third party components and assemblies, the smoother these upgrades go. This observation led us to rip all of our third party SSIS components (components from codeplex, purchased components, and ones that we wrote) out of our packages and only use built-in components. Every single package we've had so far upgraded without issues.

Tagged as: No Comments
12Jun/120

Upgrading from SQL 2008 R2 Enterprise to SQL 2012 Business Intelligence

With all the new licensing considerations in SQL 2012, everyone's busy trying to figure out exactly what the cheapest license they can get by with is. For one of our instances, we decided rather than Enterprise, we could run the Business Intelligence edition to save some cash. That upgrade path is explicitly listed in the Supported Version and Edition Upgrades documentation.

What happened?

We clicked through the upgrade wizard as usual and it got through about 85% and then prompted an error:

Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.

We clicked ok, then it finished and the results showed that the SQL engine and a few other features failed the upgrade. Great.

11Jun/120

SQL 2012: XMLA Change Connection Command Missing

I'm fairly sure this has to do with the options I chose when I first opened SSMS, where I asked it to import my settings. Regardless of why it happened, my Change Connection command is gone from my context menu when editing XMLA files:

I use this quite a bit as we have many environments and most of those environments also have staging environments. Here's now to manually add it (and add it to add it to any other context menus you want)