March 31, 2015 at 5:43 am
Kris Gruttemeyer (3/30/2015)
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.
Kinda.
The internal version number (the 782 there) is the version of the database file. It reflects the physical structure of the files. It will ALWAYS match the version of SQL which the database is attached to. A database attached to a SQL Server 2014 instance is a SQL Server 2014 database. Always and without exception.
This is why databases cannot be restored to earlier versions. Part of the checks on starting a restore/attach is to see if the database's internal version is higher than the server's version. If it is, the databse will fail to restore/attach.
The compatibility level affects some aspects of the behaviour of the query optimiser/query processor. In general it's around changed behaviour, not new. So where something behaved one way in SQL 2012and a different way in SQL 2014, then the compatibility level controls which you get. Example here, the new cardinality estimator in SQL 2014.
New features are generally available to any database attached to the instance, regardless of compat mode. Example here, you can create columnstore indexes in a database attached to SQL Server 2012, even if the database's compat mode is 90 (SQL 2005). There are exceptions to this, one being CROSS APPLY which requires a compat mode of 90+
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 31, 2015 at 6:47 am
New features are generally available to any database attached to the instance, regardless of compat mode. Example here, you can create columnstore indexes in a database attached to SQL Server 2012, even if the database's compat mode is 90 (SQL 2005). There are exceptions to this, one being CROSS APPLY which requires a compat mode of 90+
Awesome information. The more you know!
March 31, 2015 at 6:55 am
jellybean (3/31/2015)
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
Not exactly. The upgrade advisor looks at your 2005 box to determine if you have code that is likely to break. It doesn't transfer anything. It just does an assessment.
3. Assess the transfer using #3 and testing (sprocs/views/SSIS packages/etc)
We have to add a step here. Take a backup of your 2005 database and then restore that on 2014. If you need to, you can do log shipping to keep the 2014 box up to date right until you're ready to switch between the two. Also, doing backup & restore allows you to test this before you do the switch over.
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.
Now I agree with these steps.
"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 31, 2015 at 8:31 am
I really like Grant's idea here of Log Shipping to continue to test and keep your new system close to the old one.
I'd follow his steps
April 1, 2015 at 8:36 am
Thanks for the post Grant. Nice. Here is my updated list therefore:
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. Assess the code in 2005 using the Upgrade Advisor in 2014
3. Assuming all is resonably well with step #3
3.1 Backup 2005
3.2 Restore to 2014 (NB: Strongly consider log shipping to keep things up-to-date between the 2 boxes)
4. Test 2014 (sprocs/views/SSIS/packages)
5. Assuming all well with #4 point the application at the new server and test If any problems change the compat mode on 2014 to ??? to assist.
April 1, 2015 at 9:13 am
jellybean (4/1/2015)
Thanks for the post Grant. Nice. Here is my updated list therefore: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. Assess the code in 2005 using the Upgrade Advisor in 2014
3. Assuming all is resonably well with step #3
3.1 Backup 2005
3.2 Restore to 2014 (NB: Strongly consider log shipping to keep things up-to-date between the 2 boxes)
4. Test 2014 (sprocs/views/SSIS/packages)
5. Assuming all well with #4 point the application at the new server and test If any problems change the compat mode on 2014 to ??? to assist.
Yes!
"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
April 1, 2015 at 9:26 am
Thank you to all who contributed experiences/knowledge.
Merci.
J.
April 1, 2015 at 11:26 am
You are welcome, and good list. The one thing I might add is to run through specific steps, or use a trace from the 2005 server and execute this against 2014 to be sure you're getting results and no errors (or the same errors).
You might want to capture some metrics for key queries on time/reads for 2005 and compare it with 2014. You might want a complete index rebuild after the restore.
April 1, 2015 at 11:56 am
Thanks Steve. Was thinking that myself. A couple of nice common workflows thru the software would not go too far amiss.
Actually I made an implicit assuming that if I have to upgraded to SP4 on the 2005 box - this would present little to no problems. I am correct in that - do you agree? A SP upgrade is usually quite low risk, don't you agree?
J.
April 1, 2015 at 8:03 pm
SPs have tended to be low risk. There's a chance they fix a bug you've built a workaround to and that breaks, but usually they've either worked well or the machine blue screens.
Good backups before the SP, including master/model/msdb and you should be OK.
April 2, 2015 at 3:18 am
When we've upgraded we've done it one application at a time so the move to the newer server is gradual.
Databases are backed up and restored on a development server of the new version and tested and any changes done there and re-tested, before being copied (backup & restored) to test/qa and after user acceptance, to new live server where any agent jobs can be set up too and then when ready the front end application is redirected.
I also write ssis packages to transfer the data from old live to new server which can include any required transforms and be pointed at dev, test or live servers to refresh them and do practice runs. This allows data to be refreshed without having to re-amend any stored procedures, views etc which have been altered for the new version and can be run at go-live too for a final data cut.
April 13, 2015 at 8:57 am
Hi everyone,
Sorry for the late reply but I have new information concerning this upgrade that clouds things a bit. The source box is a Sql Server 2005 *Enterprise* SP2. The target box is Sql Server 2014 Standard edition (and this is fixed and cannot be changed - business made the call here)
If we upgrade from 2015 SP2 to SP4 - then there is no upgrade path from 2005 Enterprise SP4 to Sql Server 2014 Standard according to this article: https://msdn.microsoft.com/en-us/library/ms143393.aspx
So, what should I do? I'm open to suggestions?
Should we:
1. Downgrade from Enterprise to Standard on the source box? Then upgrade to SP4 and then port to 2014? Seems risky to me...
2. Adopt the "The Jonathan Kehayias" approach as indicated by this article: http://www.mssqltips.com/sqlservertip/3079/downgrade-from-sql-server-enterprise-edition-to-standard-edition/
3. Something else - any comments much appreciated.
Once again apologies for the late post. I would really appreciate any comments you may have.
Thanks in advance once again,
J.
April 13, 2015 at 9:08 am
The biggest question is are you using Enterprise functionality? If so, going to Standard, regardless of version is going to be an issue.
"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
April 13, 2015 at 9:32 am
Good point Kris. I believe that there exists some system sprocs that can inform the user of the components being used from Enterprise. Would you familiar with any of these?
J.
April 13, 2015 at 9:36 am
sys.dm_db_persisted_sku_features
However I don't know if it's in SQL 2005
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply