We're going to upgrading from SQL 2012 to SQL 2014, both Enterprise Edition. The plan is to use new hardware, Windows 2012 R2 (previous was Windows 2008 R2). We're going to use Veritas to backup the DBs on the old SQL 2012 server, restore to the SQL 2014 server. I know that I'll have to run DBCC Updateuseage and update statistics. Am I missing anything else?
Veritas basically said they will support this as long as it's supported by MS. Can someone point me to a whitepaper that says MS supports this (I'm pretty sure they do, I just can't find a white paper saying so).
Thank you in advance for any and all advice offered.
Some reason to want to do this? personally I would avoid the potential uncertainty and just make a backup to file on the Source server, copy that backup to the new Destination server and restore it there. That gives you options to minimise the downtime (Source server / databases can remain running, provided they use FULL Recovery model, until the very last moment (and then use a final LOG backup to cut-over to the new server). If they don't use FULL Recovery model you can do the same thing with a DIFFERENTIAL backup, but the cut-over won't be quite as quick - might still only be a few minutes though.
Here's my notes on what I have found in upgrading server and SQL versions over the years:
Thank you for responding to my post, Kristen. Much appreciated!
The reason that we're using Veritas to do the backup\ restore is DB size. The DB is roughly 5TB. If this DB were smaller, I would use the method you've outlined.
Let me review the notes that you've provided and see if there is anything else I missed.
Is the DB in Full Recovery model? Perhaps its not an OLTP database and a restore to last-full-backup is sufficient for Disaster Recovery?
Using SQL's native compression may well reduce backup size by 75% - could be more. So on that basis you would need 1 - 1.25TB of disk space for the backup ... the file copy is going to take a while of course, so maybe Veritas backup and restore will be quicker? I figure that's still going to take quite a long time!
Another option is just to restore the most recent FULL backup from Veritas and then take a DIFF backup (to disk) and copy that over, and restore, as part of your cut-over - but maybe you have an over-weekend maintenance window and plenty of time, such that the speed of cut-over is not critical. note that as part of migration to new version once you restore the database when it enters RECOVERY then SQL will "upgrade" the contents to the latest version. That seems to take a "measurable amount of time" for my (tiny, relative to yours!) databases, but I don't know if the process is only adjusting meta data, or if the elapsed time is linear with respect to database size, but if so that might take quite a while on 5 TB.
Another option (which I don't think is a good choice) is to take the DB offline and copy the physical files - that doesn't benefit from any compression though, and will copy the unused portions of the file too (which BACKUP will skip) so might not be worthwhile.
Hmmm... I guess that I should have provided more background information.
This is a reporting DB. All the data is extracted from the source into the DB overnight. During the day, reports are run from it using Crystal and Business Objects. The DB is in simple recovery mode.
It sounds like the direction we need to take is outlined in your third paragraph. Instead of using a DIFF to get the latest data, I just need to re-run my extract to bring the DB up to date. However, I'm thinking the DB restore is going to take about 10 hours.
My only concern is that the data protection team thinks that Microsoft doesn't support taking a backup made from SQL 2012 and restore it to SQL 2014. I can't see how that would be true - because then everyone would be doing in place upgrades (UUGGHHH).
Did I miss anything?
Again Kristen, thank you for your response. I really appreciate your insight and assistance.
Does the "import" truncate the tables before running? If so perhaps you could (manually) truncate the tables, then take a backup to disk file (assuming that that is, then, tiny ) and then once its all restored on the new machine run the Overnight Import routine to re-populate the tables.
You can definitely do that, but I don't know if it is possible via Veritas - seems probable though.
Historically MS has supported restoring from up to two (major) version prior, SQL 2012 is only one version behind SQL 2014. But I suppose it is possible that the Veritas "agent" may not be able to restore a 2012-backup onto a 2014 server
The import truncates only certain tables. For the majority of the tables it adds the data incrementally. However, if I truncate the wrong table, it could result in a RUI - Resume Updating Incident.
We're going to proceed in TST very soon. I'll let everyone know how we make out
You can upgrade from SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, and SQL Server 2014. This topic lists the supported upgrade paths from these SQL Server versions, and the supported edition upgrades for SQL Server 2016. https://technet.microsoft.com/en-us/library/ms143393(v=sql.110).aspx
) and then once its all restored on the new machine run the Overnight Import routine to re-populate the tables.