August 9, 2016 at 3:11 am
Hi All,
I finally got some shiny new hardware 😀 and a thumbs up to upgrade from SQL 2008 ! 😎
My dilemma at the moment is whether to go for the latest version 2016, or play it safe and upgrade to 2014, which hopefully has most bug fixes in place by now.
From what I've read as it will only be Standard Edition there isn't much between 14 & 16 in terms of functionality, enterprise is a different story.
The only thing different and that is of interest to me is probably the query store features and possibly the tabular BI stuff.
I have read 2016 does use fewer resources and depending on the tasks it executes faster, which is obviously a big bonus.
In saying this I am leaning towards 2016 as opportunities to upgrade are few and far between, just worried about potential bugs and applying them in 24/7 up time environment with no maintenance window.
I will probably stick with mirroring although deprecated as I can't see a reason to use the HA availability group feature in standard edition. It adds another level of complexity with the implementation of clustering.
Would be great to hear if anyone else has faced the same/similar upgrade path, and ultimately what decision they took.
Any thoughts /comments appreciated.
August 18, 2016 at 6:34 pm
it depends if you may greatly benefit from any of the SQL 2016 new features or improvements. I'm planning to upgrade from SQL Server 2014 to 2016 to be able to use Memory Optimized tables without all the existing limitations and also load balancing features of read only connections through availability groups. you may also find some similar justifications for earlier upgrade.
Pooyan
August 19, 2016 at 5:28 am
I'd go straight for 16. It's a good version, lots of new features that you may find useful (temporal tables, stretch DB, always encrypted, dynamic data masking, etc), and it's a solid release.
Plus Query Store, which is a game-changer in terms of monitoring/fixing performance problems (and it's in ALL editions)
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
August 22, 2016 at 6:15 pm
Is there direct upgrade path from SQL server 2008 to SQL Server 2016? What about earlier versions?
August 23, 2016 at 2:39 am
GilaMonster (8/19/2016)
I'd go straight for 16. It's a good version, lots of new features that you may find useful (temporal tables, stretch DB, always encrypted, dynamic data masking, etc), and it's a solid release.Plus Query Store, which is a game-changer in terms of monitoring/fixing performance problems (and it's in ALL editions)
Yes I have decided to go straight to 2016, also as the first CU is out i think its less risky 😀
August 23, 2016 at 4:17 am
erico (8/22/2016)
Is there direct upgrade path from SQL server 2008 to SQL Server 2016? What about earlier versions?
You can backup+restore (safest way) or detach+attach (not a safe way) from SQL Server 2005 (or higher) to any other newer version. You just can’t do an in-place upgrade to the latest version from SQL Server 2005
Taken from http://www.sqlskills.com/blogs/paul/you-can-upgrade-from-any-version-2005-to-any-other-version/%5B/url%5D
August 23, 2016 at 5:11 am
upgrade to 2016, you'll only have to re run an upgrade at some point to 2016 anyway
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 23, 2016 at 9:34 am
From your answer I take it to mean that there is no straight process for upgrading from SQL Server 2005, because Microsoft does not have a built-in mechanism for an in-place upgrade to the 2016 newest version. This being the case, I think an upgrade will require a couple of steps: 1). Manually moving the legacy data out of SQL Server 2005 (backup/restore, SSIS data package transfer...) to staging area, 2). Importing the backup to the SQL Server 2016 environment from the staging area. Once the data has been imported to the new environment, using built-in tools to reconcile any discrepancies (database seeds and logins, built-in tables...) resulting from the data movement.
What is the latest version of SQL Server for which an in-place upgrade is available then?
Thanks
August 23, 2016 at 10:01 am
You don't need any exporting, or importing, or discrepency checks. While you can't do an in-place upgrade, that's probably not something that you want to be doing in production. Side-by-side, or, since you say new hardware, install the new version on the new hardware directly. Safer, as if something goes wrong it doesn't take the old version out.
Then, backup the SQL 2005 database and restore it to the new 2016 instance.
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
August 24, 2016 at 2:43 pm
That helps!
Issues related to consistency checks and synchronization are addressed prior to the database being imported using SQL Server 2016 Upgrade Adviser in a typical upgrade scenario. When restoring a SQL Server 2005, I wonder if you risk loosing having the advantage of an auto-check if you move the data manually (backup/restore command or SSMS). It probably is safer to run DBCC CHECKDB after the restore is done. Granted that doing a consistency checking should always be done following a restore as a best practice. But you get the point...
August 24, 2016 at 3:55 pm
Upgrade advisor doesn't import databases. It just checks for possible problems that the DB will have on the new version (using removed features, etc).
Backup/restore is how we normally get databases onto a new version in production (or sometimes detach/attach, but that's riskier). Moving the data manually would take way too much time and allows for so much to go wrong due to user error.
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
August 24, 2016 at 4:16 pm
Ok, we're on the same page now.
Thanks!
August 25, 2016 at 11:20 am
What are the risks associated with detach/attach as compared to backup/restore?
August 25, 2016 at 5:26 pm
Detach/Attach can only be done with databases in the same SQL Server compatibility mode - meaning you can only for example Detach/Attach a 2016 database in the SQL server 2016 version; whereas a SQL Server 2008 database backup can be restored onto another version of SQL, say, SQL Server 2016.
August 26, 2016 at 5:39 pm
erico (8/25/2016)
Detach/Attach can only be done with databases in the same SQL Server compatibility mode - meaning you can only for example Detach/Attach a 2016 database in the SQL server 2016 version; whereas a SQL Server 2008 database backup can be restored onto another version of SQL, say, SQL Server 2016.
I don't believe this is correct based on my testing. I am able to detach a SQL 2008 database and attach it to a SQL 2016 server. The attach process goes through a database upgrade process and finishes. Once the attach process is complete, you can change the database compatibility mode to 130 (2016).
In the context of this thread why is a detach/attach upgrade process more risky than a backup/restore?
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply