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.
Goals are:
- 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?
Best Answer
You have a lot of reading and testing to do. There isn't a magic bullet per say but lots of ways of hitting this issue. Firstly setup the 2nd environment as if it were prod, but restore the databases in a test environment and restore the SSIS packages. If reporting services are not dependent on this then focus on this part first. Give your SSIS package test data to feed and test the validity of the data. You can even have it write to a test table using your business logic, and have the real process do the same thing so you can ensure you get accurate numbers and figures.
Mirroring is a fine solution assuming you're fully up to date, but there's 2 modes to mirroring and high performance is only available in Enterprise. Thus your network has to be fast when you setup mirroring since all transactions must commit on the mirrored side first.
Are you following all the 2012 upgrade best practices, best practices analyzer, upgrade wizard for code issues, researched potential codes, have a well written test plan, and acceptance tests?
User Kin answered a very similar question, so please do research this site for info or duplicate questions, you'll often find a lot:
For SSRS depending on the complexity, you might want to just rebuild it. If you wish to migrate, the SSRS Scripter is a great resource. Kevin Klines "how to" and "how to migrate reports" are also included in the link.