I have been given the task of planning our BI department's infrastructure upgrade from SQL 2008 R2 to SQL 2012. This process will include updating our data warehouse (database), reporting services/reports (native mode), and SSIS packages. This is my first ever attempt at planning/executing a SQL migration or upgrade. I have found some information regarding upgrading/migrating each of these individually, but not specifically in a context where all three are dependent on each other and all three will be upgraded.
- Minimal down time.
- Testability and fall back in the event of a failure or the unforeseen.
- Ideally we’d like to be able to handle this migration in manageable chunks over time, with some parts going live before others.
- When everything is successfully migrated/tested/live a final cutover occurs and then the old environment goes away.
We have opted to migrate everything rather than doing any in place upgrades. A new server has been setup for us with SQL 2012 installed, incl. a fresh instance of Reporting Services in native mode, SSDT, etc.
My first question is: Given the goals above, what is the correct order to approach the migration of each for example Database > Reporting services reports > Integration services packages?
My second question is: Can I use Mirroring to facilitate this process, is it worth the extra complications? I saw it mentioned here (https://dba.stackexchange.com/a/58696/45140) as part of an answer, but the OP already had mirroring setup, we don’t. Also if this is a good idea, does anyone have a link to a good guide of setting up mirroring specifically to assist with a migration?