October 2, 2019 at 7:27 am
Hi guys, I'm planning to perform an upgrade on my current SQL server 2008R2 SP 1 to SQL Server 2014. Based on my current license limitation 2014 is the max I can go.
I read KB by Microsoft, I need to be on at least SP2 for the upgrade to be successful (https://docs.microsoft.com/en-au/sql/database-engine/install-windows/supported-version-and-edition-upgrades?view=sql-server-2014) if this is the case then how can I proceed? Should there be any problem if I proceed with the upgrade?
I'm planning to use backup & restore to perform the upgrade. I've requested an identical VM running on Windows 2012 to be setup & I'll install SQL server 2014 into that new server.
October 2, 2019 at 10:53 am
Hi,
if you like powershell, take a look at this link:
With this powershell you are able to migrate from the old to the new server with just a view powershell command.
You can copy jobs, user and databases, and it is sooooo easy.
Kind regards, and good luck,
Andreas
October 2, 2019 at 12:58 pm
side by side upgrade, either on the same box or a new box(recommended) will be fine. Could you share why you don't want to patch your SQL server boxes to a newer service pack? Your SQL 2008 R2 SP1 was out of support, SQL 2014 RTM was out of support, too. SQL 2014 is on SP3 now.
October 2, 2019 at 3:28 pm
A few years ago, I did a similar upgrade (2008 R2 to 2014 parallel upgrade). One thing you will want to watch out for:
Queries that use table valued functions will need to be tested for performance (really everything will, but these will merit extra scrutiny). we had one procedure go from running in minutes to more than 24 hours. But then again, it was a procedure querying a TVF that called 12 instances of another TVF that called 2 other TVFs.)
As far as I understand Microsoft licensing, the license (at least an enterprise core license) is good for any supported version of SQL Server. If you wanted to go to SQL 2017, I don't think the license will stand in your way. If this is a license from some other third party application, that could be a different matter.
October 2, 2019 at 5:21 pm
another vote for DBAtools.
October 2, 2019 at 6:14 pm
A few years ago, I did a similar upgrade (2008 R2 to 2014 parallel upgrade). One thing you will want to watch out for:
Queries that use table valued functions will need to be tested for performance (really everything will, but these will merit extra scrutiny). we had one procedure go from running in minutes to more than 24 hours. But then again, it was a procedure querying a TVF that called 12 instances of another TVF that called 2 other TVFs.)
As far as I understand Microsoft licensing, the license (at least an enterprise core license) is good for any supported version of SQL Server. If you wanted to go to SQL 2017, I don't think the license will stand in your way. If this is a license from some other third party application, that could be a different matter.
I would recommend confirming with your Microsoft representative to be sure. With that said - it is my understanding that you can upgrade your instance of SQL Server to the released version at the time you purchased your licenses. So - if you purchased your core licenses in 2015 you cannot upgrade to 2016 or later versions.
Of course - also verify whether or not you purchased SA with those licenses - or you are under SA. Depending on that status - you may be able to upgrade to any version and/or Edition.
As for the migration - an upgrade to SQL Server 2008 R2 SP2 will need to be done before you can migrate the databases to SQL Server 2014. Once that has been done you should can then perform the migration and the databases will be upgraded when they are restored to the new system. Note: make sure you change the compatibility level and test...
Another note - if you have any code that uses a table variable with an order by on the insert - and assume that because the data was inserted in order that it will selected in order - that will fail after upgrading to SQL Server 2014. The fact that on 2008 R2 it actually works that way was a feature (bug) that was 'fixed' in SQL Server 2012.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 3, 2019 at 3:37 am
Thanks all for the reply.
I'm not well verse in using powershell, still rather new in managing a SQL server, so dbatools will gonna be challenging.
As for the versioning, my company inherit the license from our parent company, we split a few years back and was given a set of perpetual license which stated until what version of Windows/SQL we're allowed to upgrade to. If we were to use any version higher then what's mention then we'll need to pay.
So, for the upgrade, it's not recommended to go directly to 2014 SP3 from 2008 R2 SP1 right? Meaning I need to at least do an upgrade to SP2 before I can proceed. Are we still able to find the installer for 2008R2 SP2?
After the upgrade I'll request my application guys to do a thorough test on their application before going to production. Hopefully they're able to identify any abnormalities first.
October 3, 2019 at 2:16 pm
SP2: https://www.microsoft.com/en-us/download/details.aspx?id=30437
Install SP2, then take a backup. Of course, test this to be sure SP2 doesn't break your app.
Then use the backup to restore on a new SQL 2014 instance. You can certainly set compat level down to 2008 R2 if you need, but try to move. I certainly understand the costs, but also let your org know that 2014 will go out of support and you'll be back in this spot quickly, so start budgeting for a move. Go for 2019 or 2021, as I'm sure it will be a few years before you move. Try to resolve any app issues with keywords/naming as those are the big issues with multi version moves.
dbatools makes this really easy to migrate things. It's not that hard, and the idea of running PoSh shouldn't be scary. This might be a good change to practice. Worst case, you whack the 2014 instance and restart that.
October 8, 2019 at 3:58 pm
If you have the full text search and use custom dictionaries then be ready that it will be screwed up after upgrade due to changes Microsoft made to word breaker components.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply