1

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?

Improve this question

1 Answer 1

Reset to default
2

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.

Improve this answer
3
  • I am not sure what you mean by dependent when you say.. If reporting services are not dependent on this then focus on this part first. SSIS packages (which I need to eventually migrate) are populating the database I need to migrate, and the reporting services reports (which I need to eventually migrate) are targeting that database as their data source.
    – Voysinmyhead
    Commented Apr 20, 2015 at 19:16
  • Got it. I would just break down the movement in smaller manageable chunks if possible. For example, your data flow requires data somewhere (seems like the SQL DB), then transformations and ETL is done in SSIS, and then finally Reporting Svc reports the final outcome to the business I'd recommend moving it and getting it to work in that order. First focus on the DB data migration and quality checks, then get SSIS working, and once you're fully comfortable with that, I'd suggest finally moving to SSRS. Let us know if we can help in any way.
    – Ali Razeghi - AWS
    Commented Apr 21, 2015 at 18:52
  • Can an SSIS 2008 r2 package write to a SQL 2012 database? Since we doing a side by side upgrade could we migrate the database (perform the quality checks etc) and then change the database connections of the SSIS packages so they to point to the new 2012 database
    – Voysinmyhead
    Commented Apr 22, 2015 at 18:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.