May 31, 2017 at 3:00 pm
Hello All,
Planning to upgrade SQL 2014 database to SQL 2016. DB Sizes are 2 TB and 15 TB on this instance. As we need to perform the following steps post upgrade, it will take more than 24 hours. Is there a way to reduce downtime when cutover from 2014 to 2016?
1. Backup from 2014 and restore db on 2016
2. Change compatibility Level
3. DBCC CHECKDB WITH DATA_PURITY;
4. DBCC UPDATEUSAGE(db_name);
5. Updating Statistics
6. REFRESH VIEW DEFINITIONS
Thanks in advance.
May 31, 2017 at 3:02 pm
We will perform backup restore before cutover and will perform last log backup. How to save time from steps 3-6.
June 7, 2017 at 11:46 am
I don't believe you need to run the update usage anymore. That was really for databases going from SQL 2000 to a newer version.
June 7, 2017 at 12:10 pm
pawana.paul - Wednesday, May 31, 2017 3:00 PMHello All,
Planning to upgrade SQL 2014 database to SQL 2016. DB Sizes are 2 TB and 15 TB on this instance. As we need to perform the following steps post upgrade, it will take more than 24 hours. Is there a way to reduce downtime when cutover from 2014 to 2016?
1. Backup from 2014 and restore db on 2016
2. Change compatibility Level
3. DBCC CHECKDB WITH DATA_PURITY;
4. DBCC UPDATEUSAGE(db_name);
5. Updating Statistics
6. REFRESH VIEW DEFINITIONS
Thanks in advance.
You may be able to speed up the backup and restore by performing a detach, copy, and re-attach.
Why are you using WITH DATA_PURITY? I think it would be better to do this prior to performing the upgrade. Catch the errors first before you move the databases.
UDDATEUSAGE would only need to be run if CHECKDB reports an issue. That step is then optional.
How are you updating stats? Update the individual stats in simultaneous queries. In other words, stats on tables 1 to 10 in one window, 11 through 20 on another, and so forth.
One tried and true method to this is to use log shipping. Set the old databases to read_only, take one last log backup or two, make sure they have been applied to the new server, and cut over. That should take minutes, and then you would only be faced with the maintenance.
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/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply