Plan your migration
When migrating our older databases in versions 7 and 2000 we have a few more things to consider than a migration from 2005 or above to a later version but the gains in ease of administration are fantastic.
As the first step of any scheduled maintenance or migration we should always make a backup of the database and restore it to make sure its a working backup, it’s part of our back out plan and it’s good practice. Also we’ll need to advise other teams as and when the changes are scheduled to be complete, this may include infrastucture for failovers, development for connection changes and reporting/MI teams for connection changes.
Move the required user accounts to new server and this is one step that you prefer to complete as part of your preparation. We need to copy the sid values of the accounts for a seamless move. See this post at http://support.microsoft.com/kb/246133
After you’ve copied the user accounts we can make a copy of the original database and check it for any compatability level, data integrity issues and also perform test runs to indicate timescales. With a plan for maintenance we can run our checks, document our steps and our outcomes, including the changes required for applications etc. At this point we may not even have touched the live database yet apart from making a copy of it to test – it really is all in the planning. Plan your back out so you can return to your original database or a copy of it and also plan and scheduled your changes including time.
Migrate
Assuming our checks are successful, we’ve migrated the user accounts and we’ve scheduled in some maintenance time we can now move database to new server. I prefer the detach and reattach method here, but remember to copy the database files from the original so you have a original copy if required to back out. Once we have reattached the database we can start checking our shiny new version.
Update the database compatability level using
sp_dbcmptlevel ‘yourDatabase’, versionnumber
or if version is 2008 or later you can use
ALTER DATABASE yourDatabase SET COMPATIBILITY_LEVEL = versionnumber
Where versionnumber is 90 for 2005, 100 for 2008/2008R2 and 110 for 2012Check to see if auto shrink is on and turn it off using
ALTER DATABASE yourDatabase SET AUTO_SHRINK OFF
See Paul Randal’s post at http://www.sqlskills.com/blogs/paul/post/Auto-shrink-e28093-turn-it-OFF!.aspx
Set your page verify to checksum with
ALTER DATABASE yourDatabase SET PAGE_VERIFY CHECKSUMThe checksums will only be written when the page is saved to disk, this is part of the reason why we will follow this up later with a database check.
Update statistics with
USE yourDatabase
and then take care of your statistics with the superbly simple
sp_updatestatsDo a full checkdb using
DBCC CHECKDB WITH DATA_PURITY
Whilst this will check the database integrity it will also mean that future checks will automatically be with data purity.
Your plans and connections
Finally we’ll advise the other teams of the changes completed so they can complete their steps.
If our migration plan was in place with timescales and the all important back out plan in place (again with timescales) and we tested and documented our migration plan then we hopefully have our database on a shiny new instance and this will make it easier for us as to administer this database as the tools are easier and faster to use.