August 24, 2017 at 10:34 pm
Hello everyone,
I was curious as to peoples take on the following...
Lets say you have to move a database to a different network and domain, it could even be an Azure Virtual Machine in the cloud. Now normally, you'd migrate a database to higher SQL version, but if the version you are moving it to is the same, same build/version, then would you still bother updating stats/rebuilding indexes, running DBCC UPDATEUSAGE, DBCC CHECKDB etc.?
Would you run a just some of those?
And finally, if some or all of those were ran immediately before the Database was moved, would you even bother running them after migration?
Any and all opinions welcome. Me? I'd think 'why not'? But is it really necessary? What if time was an issue?
Thanks for reading.
Regards,
D.
August 25, 2017 at 5:12 am
By my understanding in this case most of the post migration tasks does not need to be performed. Because the versions stay the same all indexes, statistics and usage stats are 1-on-1 compatible. I would however always perform the DBCC CHECKDB because that does a check on corruption. Corruption is most of the time related to the underlying hardware and that has changed, so executing this one is a definitely YES.
But if I'm not time limited I'm with you for the "why not, better safe then sorry" opinion!! 😉
August 25, 2017 at 6:46 am
I'm with HanShi. If it's the exact same version and edition, the only maintenance that's really necessary after a restore is a CHECKDB. There's not going to be anything else within the database that you need to do. All the database settings and internals are going to just work after the move, so no need to rebuild or update anything. The only thing you're going to be able to point to as a potential for a problem (after CHECKDB) are the server settings. A different cost threshold or maxdop setting will affect plan generation. Same goes for other server settings. However, none of these are direct within the database concerns.
"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
August 25, 2017 at 1:51 pm
As the Index and Statistics data moves along with the database backup set, consistency check can be (and must be ) run . I would also suggest to select the appropriate collation during installation to mitigate issues which sometimes after migration.
August 27, 2017 at 8:37 pm
Thank you everyone for getting back, yes DBCC CHECKDB will be run, but if the time window is wide enough I may run more.
Thanks Guys.
Regards,
D.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply