January 28, 2022 at 7:56 am
Hi All,
We are planning to upgrade both SQL and OS of legacy versions.
We have the following.
SQL 2000 and 2005 running on windows server 2003
SQL 2008R2 running on windows server 2008R2
Can anyone share suggestion, how to upgrade this. I believe we need to go one by one with intermediate versions.
Can we first upgrade the SQL to 2012 on on windows 2003, then upgrade OS (OR) upgrade OS to 2012 R2 then upgrade SQL. Not sure which version are compatible with each others.
January 28, 2022 at 8:09 am
Side by side migrations all the way. Never do in place upgrade, if they fail then your rollback is hours not minutes.
As for upgrade paths, due to support lifecycles you should be upgrading to Windows 2019 or 2022 and SQL 2019. Anything else is really just not supportability wise makes sense any more as 2017 drops into extended support in July, so only 2019 will be receiving bug fixes. If you can hold a bit longer then SQL2022 is due at some point this year also.
But yes you will need to do some intermediary version upgrades.
SQL2000 needs to upgrade to 2005/2008/2008R2, then to SQL2012
SQL2005 needs to upgrade to SQL2008/2008R2/2012
SQL2008/2008R2 depending on the target version may also need an intermediary step to 2012 first.
SQL2017 can take backups from 2008/2008R2/2012/2014/2016/2017
SQL2019 can only take backups from 2012/2014/2016/2017/2019
Then 2012 can be upgraded to which ever latest version you wish to use.
January 28, 2022 at 8:50 am
Thanks for your response. Understood.
https://docs.microsoft.com/en-us/troubleshoot/sql/general/use-sql-server-in-windows
We will go side by side upgrade, let us say I need to do in place upgrade of SQL 2012 on windows 2003, then build widows 2019. Install SQL 2019 on top of it and Backup and restore the SQL 2012 over 2019.
I could see SQL 200, 2005, 2008R2 are not supported in windows 2016, 2019.
January 28, 2022 at 1:36 pm
And, you're hopping a pretty big gap, the cardinality estimation engine change that occurred in 2014. You need to plan for that just as you worry about the exact versions you have to update to in order to arrive at 2019 (no other version makes sense to upgrade to at this point in time).
So, learn about the Query Store, plan forcing, the changes to the cardinality estimation engine, and how to deal with all that as part of your 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
January 28, 2022 at 2:18 pm
As two other folks have suggested, doing an upgrade is not a very good approach.
SQL 2000 cannot be upgraded to anything but SQL 2005. As Grant suggested, the cardinality estimator changed significantly in SQL 2014. You may (will!) experience performance issues with some of your code. Extensive before and after performance testing should be a priority in this process.
You also have very different sets of code for both the OS and SQL. Why leave the old code in place? That is a guaranteed recipe for issues that are nearly impossible to track down.
If you do choose to upgrade as opposed to a side by side migration, prepare yourselves to re-do this in a short period. The volume of potential issues is significant.
Why am I saying DON'T DO THIS to you? Because I've felt this pain many times. As a FTE, I made that mistake myself. That was in the days of physical hardware. You didn't have spare servers laying around. Thankfully, it was not production. As a consultant, I have had to diagnose and correct this mistake for a few clients. Once things got squared away, I made sure that I pointed out to the client that they would have spent far less money had they simply invested in new servers and done a side by side migration.
My question is why? Why is going through multiple steps to do an upgrade your preferred method? If it a financial decision, then you may need to think outside the box.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
January 28, 2022 at 3:47 pm
Hi All,
Cardinality estimator is very good point. Thanks for that Grant.
I have asked to upgrade from legacy to supported version.
We are planning to do side by side upgrade. No idea which SQL version to choose 2012 or 2014. We do not have any 2019 at present.
For both SQL 2000 7 2005 : I believe, It is not supported any of new windows version to build a VM and test - Win2012 R2, 2016 or 2019. I am not, how to move SQL 2000 & 2005 to any supported SQL versions.
For SQL 2008R2: I believe, I can build new VM with win2016 and with SQL 2012SP4, can backup and restore DB to test it out. If needed, we can again build windows 2019 with SQL 2019 and do again side by side migration from win2016 and with SQL 2012SP4.
January 28, 2022 at 4:59 pm
I suspect your main problem may be your applications as SQL2000 and SQL2005 allowed old style outer joins. Unless your applications always use stored procedures you will need to work out all the queries which are being run against the database. You could use profiler or a db proxy like galliumdata to log this information.
With regards moving the databases, if you do not want to script everything out, you will have to restore your SQL2000 databases onto SQL2005 and then restore all the SQL2005 databases onto SQL2008R2. The backups from SQL2008R2 will restore onto every version up to SQL2017.
If you keep your databases in SQL2012 compatibility then you will avoid any problems with the new cardinality estimator. ie Look into this at a later date.
I would also suggest that you do not do in place upgrades of either the OS or SQL Server. ie Use new machines/VMs.
January 29, 2022 at 5:18 am
Thank you Ken McKelvey for sharing the TSQL code incompatibility. I will note down that as well.
Thanks for the database movement process, that really helps me. We will build maybe 2 test VMs so that we can move SQL 2000 to 2005, 2005 to 2008R2 then finally to SQL 2014. I think this is best method comparing data script out and extract. We have almost 10- SQL 2000, 6 -2005 and 12 - SQL 2008R2. One server database is 7 TB.
I am not sure, what OS can I use to install and test the SQL 2005, 2008R2 for the two VMs.
For the 2008R2, it can be on win2012.
Does anyone have suggestion SQL 2000 supported OS versions? and for SQL 2005 as well.
February 2, 2022 at 6:49 pm
We had a directive from corporate to upgrade all our server OSes to 2019. I was informed by IT that they were going to in-place upgrade the SQL Server OSes. I requested that they not do that and was told not to worry, they would have snapshots we could roll back to if anything happened. After the upgrade everything seemed OK so they deleted the snapshots. That's when I logged into one of the server and tried to run SQL Server Configuration Manager. It seems the OS upgrade killed it dead. When I told IT they said the snapshots were already deleted and we couldn't roll back.
I'm only adding this here as another cautionary tale of why in-place upgrades usually are a bad thing. I've never heard of this happening before and maybe it's isolated to Windows Server 2019.
February 2, 2022 at 7:31 pm
We had a directive from corporate to upgrade all our server OSes to 2019. I was informed by IT that they were going to in-place upgrade the SQL Server OSes. I requested that they not do that and was told not to worry, they would have snapshots we could roll back to if anything happened. After the upgrade everything seemed OK so they deleted the snapshots. That's when I logged into one of the server and tried to run SQL Server Configuration Manager. It seems the OS upgrade killed it dead. When I told IT they said the snapshots were already deleted and we couldn't roll back.
I'm only adding this here as another cautionary tale of why in-place upgrades usually are a bad thing. I've never heard of this happening before and maybe it's isolated to Windows Server 2019.
YIKES!!
However, I'm on board with this in general. No, not in-place vs. side-by-side. Rollbacks. Rollbacks are almost never really achievable.
"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 2, 2022 at 8:55 pm
YIKES!!
However, I'm on board with this in general. No, not in-place vs. side-by-side. Rollbacks. Rollbacks are almost never really achievable.
I didn't have a good feeling about their rollbacks from the start. After I told them about breaking Configuration Manager on the QA server, they still went ahead and updated a production SQL Server OS and of course broke that one as well. Now they're upset because they have a mandate to change the service accounts used with these servers and we don't have Configuration Manager to automatically set the correct permissions. The likelihood of them doing it manually and getting all the permissions right is not very high.
February 3, 2022 at 12:53 pm
The likelihood of them doing it manually and getting all the permissions right is not very high.
HA!
WHAT?!?!
No, surely a purely manual process across lots of servers will be perfectly fine...
</sarcasm>
"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 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply