March 30, 2015 at 7:53 am
Hi,
Hope I'm in the right forum. Can you help me please? I have an upgrade to do from Sql-Server 2005 to Sql Server 2014.
I am trying to do a bit of preliminary risk analysis.
Firstly I would like to know how I would do the upgrade! Should I install 2014 on another box or the same box (or do I just upgrade over the 2005 version - which seems risky)? How do I transfer all of the objects from 2005 to 2014? I'm referring to the tables/sprocs/views/etc already on 2005?
Anything else I should consider? I'm referring to common risks and stuff like functions that have been deprecated things like that?
I would appreciate any comments/experiences that you would like to share.
Thanks in advance,
Jelly.
March 30, 2015 at 9:14 am
I haven't done this specifically. Only 2005-> 2008 and R2, so bear that in mind.
There is an upgrade advisor you can run from 2014, which will tell you potential issues with your database. I would certainly do this. However, I would also install 2014 on a separate machine and upgrade there (side by side upgrade) not in place. The reason is if there are issues, you can go back and use the old server.
There are lists of issues here: https://msdn.microsoft.com/en-us/library/ms143532.aspx
Also, you need to be SP4 on 2005: https://msdn.microsoft.com/en-us/library/ms143393.aspx
You can set the database compatibility levels lower in 2014, which can help, but certainly there may be code, SSIS packages, other things that don't work as expected. The Upgrade Advisors have been getting better and better in detecting things, so you should have a good understanding of issues, but test thoroughly.
March 30, 2015 at 9:25 am
To get to 2014, you will need to upgrade to 2008 or R2 first.
There is not a direct upgrade path to 2014 from 2005 (2005 compat mode is not even supported in 2014).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 30, 2015 at 10:23 am
Ok, I think I understand (thanks for the replies btw).
So there *does* appear to be a direct upgrade path (as indicated by Steve Jones's article url) from 2005 to 2014 provided that its 2005 SP4 or later. Sorry to disagree with you SQLRNNR are you sure when you say: "There is not a direct upgrade path to 2014 from 2005 (2005 compat mode is not even supported in 2014). "
Assuming that there is an upgrade path from 2005 SP4 to 2014 would the broad steps be as follows:
1-Step Process:
1. Install 2014 on a separate box
2. Run the 2014 Upgrade Advisor and implement the suggestions when upgrading 2005
3. Test everything as indicated (code/SSIS packages/etc...)
or
2-Step Process:
1. Install 2008 R2 and 2014 on a separate box
2. Upgrade 2005 to 2008 R2 using the R2 Upgrade Advisor
3. Test everything as indicated (code/SSIS packages/etc...) on R2
4. Assuming all well upgraded R2 to 2014 using the Upgrade Advisor on 2014
5. Again, Test everything as indicated (code/SSIS packages/etc...)
The 2-step Process doubles the work but could be a safer option. What do ye guys think? I would appreciate any comments at all...
J.
March 30, 2015 at 10:49 am
I have not done the 2005 SP4 upgrade to 2014, but, if the documentation says it supports it, it probably does.
I'd go with the first setup you have suggested there. Or, if you really can't do the 2005 to 2014 in a single step, then I'd go with three servers, your original, one on 2008R2 or 2012, and then the final one on 2014. Don't install 2008R2 alongside the 2014 and then have to uninstall. Since the 2008R2/2012, if you have to use them, would be a very temporary staging step, I'd just do it on a VM that I was going to throw away at the end of the 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
March 30, 2015 at 10:57 am
There is not a direct upgrade path to 2014 from 2005 (2005 compat mode is not even supported in 2014).
You sure can, 2005 SP4 will go to 2014.
We are coming from 2005 SP3 to 2014 this year. We are doing a full migration, going to AGs and upgrading hardware. While there are many things to look out for, I'd caution you about setting all your DBs on 2014 to the 2014 compatibility mode (12, i think). 2014 introduced a new Cardinality Estimator (CE) and, while it improves many things, MAY cause queries to not perform so well and is worth checking out before you take the plunge. I'm going to set the compatibility level to 2012 (11) when we migrate, then I will test the new CE and gradually move our DBs to the new CE.
March 30, 2015 at 10:59 am
The documentation poses an interesting conundrum for which I previously submitted connect items (MS closed saying they won't fix).
If you try to change to compat 90 (sql 2005) you will get this in a 2014 instance.
Msg 15048, Level 16, State 3, Line 3
Valid values of the database compatibility level are 100, 110, or 120.
Msg 5069, Level 16, State 1, Line 3
ALTER DATABASE statement failed.
Since the documentation says that you can upgrade from 2005, I will pull up another test and confirm it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 30, 2015 at 11:01 am
SQLRNNR (3/30/2015)
The documentation poses an interesting conundrum for which I previously submitted connect items (MS closed saying they won't fix).If you try to change to compat 90 (sql 2005) you will get this in a 2014 instance.
Msg 15048, Level 16, State 3, Line 3
Valid values of the database compatibility level are 100, 110, or 120.
Msg 5069, Level 16, State 1, Line 3
ALTER DATABASE statement failed.
Since the documentation says that you can upgrade from 2005, I will pull up another test and confirm it.
When you restore a 2005 DB to a 2014 instance it will default your compatibility level to 2008(100). I do this with our DB backups every 3 days to test them. Hopefully that adds some info so you don't have to go around practice restoring 🙂
March 30, 2015 at 11:19 am
Kris Gruttemeyer (3/30/2015)
SQLRNNR (3/30/2015)
The documentation poses an interesting conundrum for which I previously submitted connect items (MS closed saying they won't fix).If you try to change to compat 90 (sql 2005) you will get this in a 2014 instance.
Msg 15048, Level 16, State 3, Line 3
Valid values of the database compatibility level are 100, 110, or 120.
Msg 5069, Level 16, State 1, Line 3
ALTER DATABASE statement failed.
Since the documentation says that you can upgrade from 2005, I will pull up another test and confirm it.
When you restore a 2005 DB to a 2014 instance it will default your compatibility level to 2008(100). I do this with our DB backups every 3 days to test them. Hopefully that adds some info so you don't have to go around practice restoring 🙂
I had a nice long reply typed up and then it crashed on post - ugh.
Anyway, yeah the restore will auto-upgrade and that part of the documentation is correct. The supported compatibility modes are a bit at odds though. The documentation says one thing but the application shows a different thing. I find it abnormal to not support the compatibility mode of 90 if you support the upgrade from 90-120 without an intermediary step. And maybe it is just because they have an auto-upgrade to 100 that is just rolled into the same upgrade - dunno.
Anyway, here is a quick article to demonstrate why seeing (or reading documentation) is not tantamount to believing.
http://jasonbrimhall.info/2014/05/21/supported-compatibility-levels-in-sql-server/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 30, 2015 at 11:25 am
Even crazier, check out what version it upgrades to, this is from SQL2005 SP3, being restored to SQL2014 CU6:
Database 'Utilities' running the upgrade step from version 781 to version 782.
So when you restore to 2014, it seems like the internal DB version number is brought all the way up to 2014 (version 782), but functionality is controlled by the compatibility mode.
Maybe someone with more nuts and bolts knowledge can confirm that. TIL all about compatibility modes. 🙂
Here's a handy link to the internal version numbers:
http://sqlserverbuilds.blogspot.com/2014/01/sql-server-internal-database-versions.html
March 30, 2015 at 11:58 am
Ok, thanks for all the replies. I think I understand the jist of the conversation (I'm not a DBA but a dev with knowledge of sql server - hence the preliminary risk analysis)
If I can summarise. You can upgrade from Sql Server 2005 SP4 to Sql Server 2014. However it changes the compatibility mode to that of SQL Server 2008 - which is 100. So is that a biggie? Can't it then just be reset by hand to 120 or is this too simple? Am I missing something?
BTW, Kris Gruttemeyer - you caution against a 120 compatibility mode but tather something less like 11. That's fine - thanks for the hint but what is a "AG" if you don't mind me asking.
Merci folks for all contributions so far,
J.
March 30, 2015 at 11:59 am
Ok, thanks for all the replies. I think I understand the jist of the conversation (I'm not a DBA but a dev with knowledge of sql server - hence the preliminary risk analysis)
If I can summarise. You can upgrade from Sql Server 2005 SP4 to Sql Server 2014. However it changes the compatibility mode to that of SQL Server 2008 - which is 100. So is that a biggie? Can't it then just be reset by hand to 120 or is this too simple? Am I missing something?
BTW, Kris Gruttemeyer - you caution against a 120 compatibility mode but tather something less like 11. That's fine - thanks for the hint but what is a "AG" if you don't mind me asking.
Merci folks for all contributions so far,
J.
March 30, 2015 at 12:05 pm
what is a "AG" if you don't mind me asking.
Availability Group, it's a High Availability solution that allows 2 servers to stay in sync and fail over to each other.
Can't it then just be reset by hand to 120 or is this too simple? Am I missing something?
Yes, you can update it directly via the GUI or use
ALTER DATABASE [SomeDatabase] SET COMPATIBILITY_LEVEL = 110
March 30, 2015 at 4:57 pm
jellybean (3/30/2015)
If I can summarise. You can upgrade from Sql Server 2005 SP4 to Sql Server 2014. However it changes the compatibility mode to that of SQL Server 2008 - which is 100. So is that a biggie?
Well, the big deal could be that the application does not support some feature in that compat level, or that the application is dependent on a prior compat mode. Usually it is not a big deal, except for the misdirection within the app that could lead one to believe that compat 90 is available (somebody may try to force it back to 90).
It will just take a few tests to confirm the apps will function properly with the new compat and server version.
De rien.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 31, 2015 at 5:35 am
Ok guys let me summarise before I close off this very excellent thread:-
Process to up-greade to SQL Server 2014 from 2005
0. On the *separate* target box install Sql Server 2014
1. Upgrade to Sql Server 2005 SP4 if not done so already (on source box)
2. Assuming there are not issues with #1 usign the Upgrade Advisor in 2014 to transfer over the data
3. Assess the transfer using #3 and testing (sprocs/views/SSIS packages/etc)
4. Assuming all well with #3 point the application at the new server and test If any problems change the compat mode on 2014 to ??? to assist.
Would ye agree with these steps?
J.
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply