We currently have a 100GB database which is in simple recovery mode, and it is running on 2008 R2. We have a new server with SQL 2016 Enterprise installed, ready to go - and we're building a migration plan, for how to do the upgrade.
We've ran a test run using SQL 2016's Data Migration Wizard (https://www.microsoft.com/en-us/download/details.aspx?id=53595), which we used to point to our current, live, production DB instance and to restore the DB to '16. This took 15 minutes, which is very quick considering the other approach (backup currently-running DB, copy over to new server) -- mainly because we can't back up / restore over a UNC path.
We've realised that we have a lot of old/unneeded data in our DB, and have kicked off some scheduled tasks using the SQL Agent to perform some deletes, so by the time we're near to our "go live" date then we'll have less data residing in the DB, with the idea being that we should be able to restore the live instance over to '16 quicker with less data.
However, obviously the actual size of the database FILE on disk will not shrink until we perform a DB shrink.
We're thinking of shrinking the DB for these reasons:
- faster backup times
- less files to restore when we take a copy of production, scrub it and restore to a test environment, for a like-for-like copy of production to perform tests against
- there will be a lot of residual space after we've completed these deletes, so makes sense to reclaim it (I'm thinking ~30-35GB, so a third of the DB itself)
I'm aware that by shrinking the DB then the DB size will just grow again in some time, but I think we've previously overlooked some aspect of data growth, to which we've put some processes in place to reduce excessive DB writes. We currently have a 75MB autogrow which happens around 2-3 times a day.
Will this be a good idea? We'll be restoring our DB over, upgrading the SQL compatibility level and then running an index rebuild (using Ola Hallengren's index script) & updating stats. I'm curious if the shrink will massively spike these index operation times as it'll leave the DB pretty fragmented from what I've understood. I'm not sure what the best approach is here - perhaps don't shrink and only rebuild the index on '16 after the data migration?