Tuesday, January 14, 2014

Microsoft Project Migration Template for the move to SQL 2012


For those planning a move to SQL Server 2012, although this process can apply for many Database Migrations, perhaps this Microsoft Project Migration Template could help? *
In this plan, there are so many possible steps, it is better to trim down from too many to just those applicable instead of missing steps. As an experienced migrator, you may already know of an even better order of tasks to accomplish a successful migration. By all means share with us below in the comments.   My approach here is to ensure that I delve well into the domain of Project Management as a DBA must/should do from time to time, so that if an official project manager is assigned to you, this document could be handed over to them as a good way of organising the upcoming work.
A quick nota bene at this planning stage of a project is that you should not skip the time estimations, which in turn lead to the analysis of the critical path.  There might be a point where you have to pool resources with another team-member or pull in a consultant to ensure project delivery timelines are met.  Somewhere along the critical path of the project you might want to take this proactive step to mitigate deadline risk. In this way, whole project planning with respect to time estimations is a predecessor to accomplishing this task.
And sorry, for the notes sometimes being in FR, I just tend to mix up the sources/references often enough. This template has a little bit of history. While migrating databases in early 2005 for the Artificial Insemination [of cows] Centre of Quebec (CIAQ)  Mathieu Noel (his profile on linkedin.com) helped me out greatly while writing this document. This version has had major revisions four times so far, with the most recent being this one for SQL 2012.   
 * To view an MPP file without installing Project itself, you can use this free viewer. Exports of the Migration project plan to PDF and Excel are also available on my SkyDrive.
PS as with all migrations, one should constantly try and adhere to the keep it simple rule (K.I.S.S.). Even this old post about a simple Oracle 10 migration to SQL Server 2008 was no exception, so what we did from the very beginning was create insert scripts of all the data into the tables (not a huge database, in the tens of megabytes only), since the schema export was already done for us by a vendor (to which I only had to do minor tweaks to appreciatively).  Before actually going through each table insert script one by one to adjust the fully qualified table names, add Set Identity_Insert On/off statements, with a quick truncate before the begin tran/inserts/commit batch, I had scripted out, in a previous step, all the drop/create foreign key and constraints statements to bring all the data in quickly without per-table FK/Constraint drop/recreation.