February 24, 2014 at 12:05 pm
Hi,
client here is looking to migrate from SQL2005 to 2008 R2 (yes, I know, just go with it) and they are concerned about the timings of the entire migration, the systems are VERY critical and the timings of each migration process have to be exact. They are asking about the importance of each long step in the process (obviously backup and restore are set by db size), but they are questioning Index rebuilds (reorg online to save time?) and the DBCC CHECKDB which at the moment is taking a huge chunk of the migration plan time.
Not really asked this before but is there a way around this? I have used CHECKDB many times and understand it's importance esp after a database migration but just looking for anyone who has had the same issue? Did you run it on a per database level after the migration go live? (Very risky indeed).
TIA,
qh
February 25, 2014 at 2:45 am
When you attach\restore the 2005 database to 2008 the database will go through an internal SQL Server upgrade, this does complete a fair amount of logical checks. Get the database online and schedule the DBCC Checkdb on a maintenance window would be my route.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 25, 2014 at 3:29 am
You could backup & restore using the 'with checksum' option which I think could replace the immediate need for a checkdb run - someone better versed than me in the intricacies may be able to comment on this.
Alternatively, running checkdb with the physical_only and/or tablock options could reduce the time spent, with the caveat that neither does a full checkdb run.
February 25, 2014 at 4:51 am
The one suggestion I can offer to cut the time is to just do a statistics update, not an index rebuild. You actually don't even have to do that. The statistics will get updated as they get accessed, so you could just skip this step. Although, that could lead to slower performance on the system for a while.
No way to reduce the DBCC checks. You need to check both the physical and logical layout after the restore. Too many things change. I get it that they want it fast, but just ask them, fast or right? A really fast process with a corrupt database at the end won't help.
And yes, I'd do checksum too, but not instead of the DBCC checks.
"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
February 25, 2014 at 7:31 am
Excellent suggestions many thanks, I suppose it does come down to how our ScaryDBA friend puts it: FAST or RIGHT? :ermm:
Cheers, god bless SSC! 😎
qh
February 25, 2014 at 8:08 am
As an addendum: I have suggested that say one day before the migration we could prep the target instance as much as we could logins/ssis/etc, then restore a full bk from source instance and run all the checks at that point. Then come migration day the only timings I have to worry about are backup/copy/restore. There is a caveat that your just praying that there aren't any data integrity issues in one day processing.
Cheers
qh
February 25, 2014 at 8:16 am
Or any integrity issues get introduced by the restore process, which can happen. I wouldn't do it that way. Paranoia runs way too deep.
"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
February 25, 2014 at 11:06 am
quackhandle1975 (2/25/2014)
then restore a full bk from source instance and run all the checks at that point
That is another way. Typically time it so the database is migrated fairly soon afterwards.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 26, 2014 at 7:43 am
Grant Fritchey (2/25/2014)
The one suggestion I can offer to cut the time is to just do a statistics update, not an index rebuild. You actually don't even have to do that. The statistics will get updated as they get accessed, so you could just skip this step. Although, that could lead to slower performance on the system for a while.
Hi Grant, do you have any white papers detailing the above process/mechanism? Specifically after a 2005 --> 2008 upgrade
Cheers
qh
February 26, 2014 at 7:50 am
When doing an upgrade from one SQL Server version to another running update stats is kind of critical. The optimizer makes access path decisons based on that and they change the agorithm from version to version. You are risking performance issues on a highly sensitive system if you don't do it. Also, CHECKDB is critical if you ask me... if there is any type of corruption you want to know before you allow users on the system.
A way to cut down the cutover time would be to do a backup/restore before you do the cutover... and leave the db in a state to accept trans log backups.. then apply trans log backups up to the last one then upon applying the last one open up the db for users. That way your backup/restore time is very very small. Of course you have to make sure that the log backups are taken and then applied to the new SQL2008 version of the db. Then run CHECKDB and Update stats and you are ready to go.
February 26, 2014 at 7:58 am
quackhandle1975 (2/26/2014)
Grant Fritchey (2/25/2014)
The one suggestion I can offer to cut the time is to just do a statistics update, not an index rebuild. You actually don't even have to do that. The statistics will get updated as they get accessed, so you could just skip this step. Although, that could lead to slower performance on the system for a while.Hi Grant, do you have any white papers detailing the above process/mechanism? Specifically after a 2005 --> 2008 upgrade
Cheers
qh
Do a couple of searches on MSDN. The statistics generation & storage changes between versions of SQL Server, so, after the upgrade, it's going to update statistics. Personally, when I do an upgrade, I do an UPDATE STATISTICS WITH FULL SCAN against every stat in the system. But, you don't have to. Anyway, the mechanisms are documented by Microsoft somewhere.
"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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply