June 5, 2017 at 8:11 am
We have SQL Server 2012 Standard edition server and planned to upgrade to SQL Server 2014. This is the data source for SSRS reports. Reports developed in SSDT 2010. The model of report deployment is in Report server.
Please let me know any impact on this upgrade.
2. If SSRS also gets upgraded what are the impacts.
June 5, 2017 at 8:31 am
There'll be no particular impact on your reports of upgrading the source database server, provided that the queries that the reports use still run in timely fashion on SQL Server 2014. The new Cardinality Estimator in SQL Server 2014 usually gives an improvement in query speed, but it has been known to slow some queries down. Make sure you test.
I can't imagine there'd be much impact of upgrading your SSRS server, either. But make sure you run the SQL Server 2014 Upgrade Advisor first (if it checks SSRS, that is).
Incidentally, why are you planning to upgrade to 2014 rather than 2016?
John
June 5, 2017 at 11:47 am
Also, make sure your backups are good before you do an upgrade. Nothing worse than having a failed upgrade and no rollback plan.
And I agree, upgrading to 2016 is likely a better option (2012-2016 should have no issues).
If you are upgrading SSRS as well, make sure you have a backup of the encryption keys.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 5, 2017 at 12:41 pm
Strong recommendation, if you're upgrading from 2012, don't go to 2014. Go straight to 2016. There are a lot of reasons for this, but I'll give you one big one. The new Cardinality Estimation routines introduced in 2014 are the biggest pain point for most people making the upgrade. Most queries, most of the time, will probably perform the same. Some queries will improve their performance. A few queries will radically degrade. 2014 offers almost no mitigation for this. You can rewrite the code. You can turn off the cardinality estimation at the database, or modify your code to turn it off at the query. That's it. 2016 adds one giant mitigating factor, Query Store. You can use the Query Store to force the old estimation plan without having to modify any code at all. It makes for a much faster, smoother, safer, upgrade process.
"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
June 5, 2017 at 9:52 pm
This was removed by the editor as SPAM
June 6, 2017 at 8:10 am
Oh, one other piece of advice - Run the upgrade on a TEST environment first so you can see how it all works and test all of your reports.
I'd recommend going to 2016 as well.
But the process I'd use is:
1 - refresh live to test
2 - verify reports are working as expected
3 - find a few reports to use to confirm things are faster or the same speed and time how long it takes to load.
4 - upgrade
5 - time the reports from step 3 from 2014/2016
This will give you a rough idea of what your live upgrade will go like. And will help you with any reports that seem slower to know if you should redesign them before upgrading.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply