February 26, 2024 at 8:07 pm
Hi we have an old sql server 2012 box that (among other things) hosts SSRS (front and back end).
I need to upgrade the version of SQL.
When I do this will SSRS take care of itself or are there other things I need to be mindful of?
I have vague memories of doing this in the distant past and the reports no longer opening as the XML schema version/path in the report code no longer matched the SSRS version.
February 27, 2024 at 1:50 pm
You'll want to update SSRS as well.
Also, you're at the magic inflection point, going past SQL Server 2014 (and do go past it, don't upgrade to 2014 and stop, not only is out out of support, but it doesn't have the tools to deal with the next thing) means dealing with the new cardinality estimation engine. Probably the best way to do this is to upgrade your server, but leave the compatibility mode to the old version. Then, turn on Query Store. Capture the behavior of your systems. Might take a day, a week or a month. Your system. I have no idea. After collecting, change the compatibility mode. When those queries that are going to blow up, blow up, due to the new cardinality engine, use plan forcing to force them back to the old plan while you figure out what's wrong with those queries and fix them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 27, 2024 at 8:06 pm
"You'll want to update SSRS as well"
Thanks, is this a separate process to the usual SQL upgrade? If so, can you provide a URL for guidance please.
I'm getting hassled (rightly so) from the IT department to get SQL to a supported version. It currently hosts 200+ reports that 100+ people rely on daily.
February 28, 2024 at 1:35 pm
I'd start with the source. Here's Microsoft's take on upgrading SSRS. If you get stuck or need details, there are probably other resources that can help.
Testing will be your friend.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply